Filter predicates executing before Access predicates

optimizationoracleoracle-11g

This is oracle 11g. When I try to do a select query on a table, in the explain plan option I see that the filter predicates of the query(predicates on column without indexes) are executed before the Index access predicate. I don't understand why the query optimizer is picking this plan?

I tried the –+ ordered_predicates option, but it ignored that.

Filter predicates are just as horrible as full table scans right?

Edit Query and the plan:

select column1, column2, column3 from table where column1 >= :cutoff and trunc(column2) = trunc(sysdate-1) and column3 = :column_value

The index is defined on column 1.

This is the query plan: 

Plan hash value: 4098448107

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                       |  1619K|    49M|  4433K  (1)| 00:02:54 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| TABLE_NAME            |  1619K|    49M|  4433K  (1)| 00:02:54 |
|*  2 |   INDEX RANGE SCAN                  | NAME_OF_THE_INDEX     |    87M|       |   648K  (1)| 00:00:26 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("TABLE_NAME"."column3"=:column_value AND TRUNC(INTERNAL_FUNCTION("TABLE_NAME"."column2"))=TRUNC(SYSDA
              TE@!-1))
   2 - access("TABLE_NAME"."column1">=TO_NUMBER(:CUTOFF) AND "TABLE_NAME"."column1" IS NOT NULL)

Best Answer

The order of steps displayed in an execution plan is not the order of steps in which they are executed.

The database first accesses the index called NAME_OF_THE_INDEX using these predicates:

2 - access("TABLE_NAME"."column1">=TO_NUMBER(:CUTOFF) AND "TABLE_NAME"."column1" IS NOT NULL)

Then the database accesses the table called TABLE_NAME using the rowid from index NAME_OF_THE_INDEX using these predicates:

1 - filter("TABLE_NAME"."column3"=:column_value AND TRUNC(INTERNAL_FUNCTION("TABLE_NAME"."column2"))=TRUNC(SYSDATE@!-1))

So it works as expected, and uses the access predicates first, and the filter predicates after that.

Unfortunately I could not find a good example with explanation in the official documentation, but I can recommend something better. To have a better understanding of execution plans, read these series (there are 14 parts):

https://www.red-gate.com/simple-talk/sql/oracle/execution-plans-part-1-finding-plans/

Related Question