2012-04-02Distinguishing Access and Filter-Predicates

The Oracle database uses three different ways to where clauses (predicates):

Access Predicate (“access”)

The access predicates express the start and stop conditions of the leaf node traversal.

Index Filter Predicate (“filter” for index operations)

Index filter predicates are applied during the leaf node traversal only. They do not contribute to the start and stop conditions and do not narrow the scanned range.

Table level filter predicate (“filter” for table operations)

Predicates on columns which are not part of the index are evaluated on table level. For that, the database must load the row from the heap table first.

Note

Index filter predicates give a false sense of safety; even though an index is used, the performance degrades rapidly on a growing data volume or system load.

Execution plans created with the DBMS_XPLAN utility (see Section 1.1) show the index usage in the "Predicate Information" section below the tabular execution plan:

------------------------------------------------------
| Id | Operation         | Name       | Rows  | Cost |
------------------------------------------------------
|  0 | SELECT STATEMENT  |            |     1 | 1445 |
|  1 |  SORT AGGREGATE   |            |     1 |      |
|* 2 |   INDEX RANGE SCAN| SCALE_SLOW |  4485 | 1445 |
------------------------------------------------------

Predicate Information (identified by operation id):
   2 - access("SECTION"=:A AND "ID2"=:B)
       filter("ID2"=:B)

The operations with additional predicate information are marked by an asterisk in the “Id” column of the execution plan. The predication information section lists the access and filter predicates by operation id.

It's a book!
You are just reading a book. Here is the table of content

The sample is from the “Testing and Scalability” chapter and shows an INDEX RANGE SCAN that has access and filter predicates. The Oracle database shows the ID2 predicate in both sections—as access predicate and as filter predicate. That’s just an Oracle peculiarity.

Important

If a condition shows up as filter predicate, it is a filter predicate. No matter if it is shown as access predicate as well.

That means, the INDEX RANGE SCAN from the example will scan the entire range for the condition "SECTION"=:A and apply the filter "ID2"=:B on each row that qualifies the clause on SECTION.

Filter predicates on table level are shown for the respective table access like TABLE ACCESS BY INDEX ROWID or TABLE ACCESS FULL.

Please note that different tools display the predicate information differently. Oracle SQL Developer, for example, shows the predicate information below the respective operation.

Figure A.1. Access and filter predicates in Oracle SQL Developer


Some tools don’t show the predicate information at all. Remember that you can always fall back to DBMS_XPLAN as explained in “Getting an Execution Plan”.

Recent Questions at Ask.Use-The-Index-Luke.com

0
votes
1
answer
229
views

query regd the CBO decision

Apr 17 at 10:27 Hulda(suspended)
index-choice optimizer
0
votes
3
answers
2.0k
views

Examples for Function Based Indexes?

Mar 25 at 15:52 Castorp 1
function-based
0
votes
1
answer
610
views

Updating multiple rows using a subquery in SQL

Jan 08 at 09:52 Jan 26
subquery update sql