This discussion is archived
10 Replies Latest reply: Jan 14, 2013 7:20 PM by missymichi RSS

Interpreting Predicate  and Filter information from the explain plan

448778 Journeyer
Currently Being Moderated
Can somebody help in understanding how does the predicate and filter operation effects the execution plan.or how can I conclude the predicate or filter operation chosen by optimizer is not optimized.
  • 1. Re: Interpreting Predicate  and Filter information from the explain plan
    448778 Journeyer
    Currently Being Moderated
    guys any comments,I was hoping to see some comments from Jonathan,Justin ,Randolf..Don etc..
  • 2. Re: Interpreting Predicate  and Filter information from the explain plan
    Justin Cave Oracle ACE
    Currently Being Moderated
    Others are certainly better qualified to weigh in here, but...

    It really depends on the type of operation the filter condition is being applied to as well as the information from the plan. If you see a full table scan on a million row table on one step of the query plan and the cardinality estimate for that line is, say, 10,000 and you see a particular filter condition then assuming that the optimizer knows that the table is a million row table, you can infer that the optimizer believes that the particular filter condition excludes 99% of the data and includes 1% of the data (assuming my math is correct). Knowing your data, you can presumably determine whether that selectivity is a reasonable estimate or whether the optimizer is wildly off.

    Justin
  • 3. Re: Interpreting Predicate  and Filter information from the explain plan
    448778 Journeyer
    Currently Being Moderated
    Due respect to all other members in this forum,I just remember only those names..I know this community is full of Oracle expertise..
  • 4. Re: Interpreting Predicate  and Filter information from the explain plan
    CharlesHooper Expert
    Currently Being Moderated
    Paraphrased (or possibly a quote) from "Cost-Based Oracle Fundamentals"
    "In an execution plan, the access predicates show the predicates used to generate the start and stop keys for the index, but the filter predicates show the predicates that cannot be used until the leaf blocks have been reached."

    That book, and Jonathan's website provide a more complete explanation. You might try a search of his website.

    What do you mean by "optimized"? It is typically more efficient for the SQL statement to execute if the restriction criteria of the WHERE clause appears in an access predicate, rather than in a filter predicate. Full tablescans are a possible exception.

    You may also see access predicates appearing in joins.

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 5. Re: Interpreting Predicate  and Filter information from the explain plan
    448778 Journeyer
    Currently Being Moderated
    Thanks Charles,

    Can you please clarify on this "How sql performs better if the if the restriction criteria of the WHERE clause appears in an access predicate, rather than in a filter predicate:
  • 6. Re: Interpreting Predicate  and Filter information from the explain plan
    CharlesHooper Expert
    Currently Being Moderated
    User445775,

    The paraphrase that I provided in my previous post was from page 74 of "Cost-Based Oracle Fundamentals".

    How to word the explanation...

    Assume that you have a database table which contains all of the phone numbers and addresses for people in a state. A query is executed to find user445775 in the city named "Redmond". Assume that the query looks like this:
    SELECT
      PHONE
    FROM
      PHONE_NUMBERS
    WHERE
      CITY = 'Redmond'
      AND FULL_NAME = 'user445775';
    Assume that there are no indexes on the table. The DBMS_XPLAN would show two filter predicates applied during a full tablescan - this probably indicates an inefficient access path, especially if there are a very small percentage of people matching the WHERE clause restrictions on the table.

    Now, assume that an index is created on the CITY column. The DBMS_XPLAN would show an access predicate applied to the index on the CITY column, and a filter predicate on the table access by index for the FULL_NAME. We eliminated a large number of possible rows by applying the access predicate to jump directly to the rows with the city of interest, and then filtered out those names which were not 'user445775'. This is not terribly efficient, especially if there are a large number of people in 'Redmond' that need to be filtered out.

    Now, assume that we drop the index on the CITY column and create an index on the FULL_NAME column. The DBMS_XPLAN would show an access predicate applied to the index on the FULL_NAME column, and a filter predicate on the table access by index for the CITY column. This could be a fairly efficient plan if there are only a couple rows in the table with FULL_NAME of 'user445775', as few rows will be discarded after the index access to find those with CITY = 'Redmond'.

    Now, assume that we drop the index on the FULL_NAME column and create a composite index on CITY,FULL_NAME. The DBMS_XPLAN would show an access predicate applied to the index on the CITY and FULL_NAME columns and there would not be a filter predicate on the table access by index - in this case, we will not discard any rows once retrieved by the index access.

    Page 211 of "Troubleshooting Oracle Performance" also shows a clear explanation of access and filter predicates.

    Think of access predicates (on indexes at least) as throwing out rows before they are retrieved from disk (or memory), and filter predicates as throwing out rows after they are retrieved from disk (or memory).

    Charles Hooper
    IT Manager/Oracle DBA
    K&M Machine-Fabricating, Inc.
  • 7. Re: Interpreting Predicate  and Filter information from the explain plan
    448778 Journeyer
    Currently Being Moderated
    Awesome explanation Charles..Thank you so much..you rock!!!!!!!!!!!!
  • 8. Re: Interpreting Predicate  and Filter information from the explain plan
    984540 Newbie
    Currently Being Moderated
    Thanks for clear explanation
    Seshi
  • 9. Re: Interpreting Predicate  and Filter information from the explain plan
    Jan-Marten Spit Explorer
    Currently Being Moderated
    most nodes in an explain plan represents an operation that takes rows as input from it's child nodes (or database structures in case of leaf nodes), and produces rows as output to it's parent nodes.

    an access predicate specifies how the input is accessed.

    a filter predicate specifies what data is thrown out after acessing it, so that it won't passed on to the parent operation.

    the filter predicates listed in v$sql_plan are optimized in terms of the order in which they are applied (most restictive filters evaluated first, for example)
  • 10. Re: Interpreting Predicate  and Filter information from the explain plan
    missymichi Newbie
    Currently Being Moderated
    I really learned from this :) awesome!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points