Filter predicates executing before Access predicates

Question

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)

Answer 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/