Oracle SQL tip

Tweet

While reading Mark Gurry’s OOW05 presentation (PDF), I noticed a simple yet an effective way to tune a query. Here is an example that demonstrates the trick.

Everyone who has worked with Oracle knows that the optimizer will not use an index if a function is applied to the indexed column.

eddie@ora92> create table t as
  2  select owner, object_name, created
  3  from all_objects
  4  where rownum <= 20000;

Table created.

eddie@ora92> create index t_indx on t(created);

Index created.

eddie@ora92> set autotrace on
eddie@ora92> select *
  2  from t
  3  where trunc(created) = trunc(sysdate);

no rows selected


Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        123  consistent gets
          0  physical reads
          0  redo size
        293  bytes sent via SQL*Net to client
        353  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

Nothing new here except the fact that the index was not used because I applied the function trunc to the indexed column created. Note that the date and time are stored in the column created. So, how do I select all rows that have the date of today and not use any function on the column created? Here is how:

eddie@ora92> select *
  2  from t
  3  where created between
  4  trunc(sysdate) and
  5  trunc(sysdate) + .99999;

no rows selected

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   TABLE ACCESS (BY INDEX ROWID) OF 'T'
   2    1     INDEX (RANGE SCAN) OF 'T_INDX' (NON-UNIQUE)

Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
        293  bytes sent via SQL*Net to client
        353  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          0  rows processed

In this case, the optimizer used the index. Notice the number of consistent gets, it dropped from 123 to just 2, a good thing.

To recap, If you have an index on a date column and you want the optimizer to use this index and you have a requirement to equate this date to another leaving out the time portion, always use:

date1 between trunc(date2) and trunc(date2) + .99999

instead of:

trunc(date1) = trunc(date2)

Simple.

Related articles:

• DUAL behavior change
• “Between” CASE and DECODE
• Cool Undocumented SQL Function SYS_OP_MAP_NONNULL and Some Alternatives

Filed in Oracle, Tips on 29 Nov 05 | Tags: tuning

---