Is there a oracle hint to execute the where clauses in a specific order?

Question

Consider the following two queries on a table where datecolumn is indexed -

Q1: select * from table where datecolumn > sysdate - 5;
Q2: select * from table where datecolumn > sysdate - 5 and datecolumn < sysdate - 1;

Q1 uses the index. But, Q2 somehow does a full table scan. Is it because oracle somehow chooses to execute "datecolumn < sysdate - 1" first ? In that case, is there a way to enforce order of execution of where clauses involving one column ?

First thing to do when encountering this sort of thing is to get explain plans for both queries. This will help to determine the actual cause of the plan change. *Then*, you might use one or more of the techniques given in the answers below - but for better reasons than "it seems to work". — Jeffrey Kemp, Feb 22 '13 at 07:49

score 0 · Accepted Answer · answered Feb 21 '13 at 06:16

0

You could specify an index hint, something like this:

select /*+ INDEX (table datecolumn_ix)*/ 
       * 
  from table 
  where datecolumn > sysdate - 5 and datecolumn < sysdate - 1;

See Oracle Index Hint for more details.

answered Feb 21 '13 at 06:16

JoshL

10,737
11
55
61

Florin Ghita · Answer 2 · 2013-02-21T06:22:12.323

0

Sure there is the index hint.

/*+ INDEX(table index_name) */

But, in your case maybe is better to collect statistics of your table.

Use DBMS_STATS.GATHER_TABLE_STATS('schema','table') procedure.

edited Feb 21 '13 at 06:22

answered Feb 21 '13 at 06:16

Florin Ghita

17,525
6
57
76

lazylead · Answer 3 · 2020-01-23T10:51:18.420

Oracle doesn't guarantee by default that your tables will be joining in the same order as you mentioned, thus you may use these hints:

ordered https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#5555 It will join your tables in the same order as you mentioned in where statement
leading https://docs.oracle.com/cd/B10500_01/server.920/a96533/hintsref.htm#5730 you may specify the join orders of your tables
```
select /*+ leading (c b a) */
  a.*
from
  tablea a
  , tableb b
  , tablec c
where
  a.some_id = b.some_id
  and c.some_id = b.other_id
```
Also, for your Q2 you may try the between option https://docs.oracle.com/cd/B28359_01/server.111/b28286/conditions011.htm

Is there a oracle hint to execute the where clauses in a specific order?

3 Answers3