0

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)
  • Can you post your query and its execution plan? Also using hints should be select /*+hint*/ ... you cannot substitute it for the double-dash style comment. – Fiisch Mar 5 at 19:54
  • Post the execution plan. I suspect you are reading it in the wrong order. – Balazs Papp Mar 5 at 20:08
  • @Fiisch Hints work just fine with --+ syntax as well. – Balazs Papp Mar 5 at 20:08
  • @BalazsPapp I don't know how to post the execution plan. I am using oracle developer and it displays the query plan in a tree like view. I cannot copy paste the tree view for some reason and the export as html option exports the html to the remote machine which I don't have ssh access to. – Aditya Mar 5 at 20:10
  • 1. explain plan for select column1, column2, column3 from table where column1 >= :cutoff and trunc(column2) = trunc(sysdate-1) and column3 = :cutoff. 2. select * from table(dbms_xplan.display); – Balazs Papp Mar 5 at 20:11
1

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/

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.