Search code examples
oracle-databaseindexingoptimizer-hints

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

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 ?

Solution

  • 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.