This content has been marked as final. Show 10 replies
guys any comments,I was hoping to see some comments from Jonathan,Justin ,Randolf..Don etc..
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.
Due respect to all other members in this forum,I just remember only those names..I know this community is full of Oracle expertise..
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.
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
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:
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:
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.
SELECT PHONE FROM PHONE_NUMBERS WHERE CITY = 'Redmond' AND FULL_NAME = 'user445775';
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).
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc.
Awesome explanation Charles..Thank you so much..you rock!!!!!!!!!!!!
Thanks for clear explanation
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)
I really learned from this :) awesome!