Oracle SQL Index DATE vs Index TRUNC(DATE)

Question

I have a Table named DEXTRACTO and I need to consult a period of time based on column F_EXTRACTO (which is DATE format) with a BETWEEN DATE1 AND DATE2 condition (DATE1 and DATE2 can change). Here's some data of the table:

SQL> SELECT MIN(F_EXTRACTO), MAX(F_EXTRACTO), COUNT(1)
  2    FROM DEXTRACTO
  3  /
 
MIN(F_EXTRACTO) MAX(F_EXTRACTO)   COUNT(1)
--------------- --------------- ----------
03/01/2005      06/01/2017        13772806
 

SQL> SELECT COUNT(1) FROM DEXTRACTO WHERE F_EXTRACTO IS NULL
  2  /
 
  COUNT(1)
----------
         0
 
SQL> 

I want to use an index but I don't know which approach is better. Should I use it on column F_EXTRACTO? Or should I use an index on TRUNC(F_EXTRACTO)? I know it's not a good idea use index with function, but testing both approaches I got this...

SQL> create index INDEX_DATE on DEXTRACTO (F_EXTRACTO)
  2  /
 
Index created

SQL> create index INDEX_TRUNC on DEXTRACTO (TRUNC(F_EXTRACTO))
  2  /
 
Index created
 
SQL> 

Testing index on F_EXTRACTO:

SQL> explain plan for
  2  
  2  SELECT /*+ index (dextracto INDEX_DATE)  */ *
  3    FROM dextracto
  4   WHERE f_extracto
  5         BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND SYSDATE
  6  /
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
---------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost  |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |    12M|  1088M|   250K|
|   1 |  FILTER                      |            |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEXTRACTO  |    12M|  1088M|   250K|
|   3 |    INDEX RANGE SCAN          | INDEX_DATE |    12M|       | 36972 |
---------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version
 
13 rows selected
 
SQL> 

Testing index on TRUNC(F_EXTRACTO):

SQL> explain plan for
  2  
  2  SELECT /*+ index (dextracto INDEX_TRUNC) */ *
  3    FROM dextracto
  4   WHERE TRUNC(f_extracto)
  5         BETWEEN to_date('01/01/2005','dd/mm/yyyy') AND SYSDATE
  6  /
 
Explained
 
SQL> select plan_table_output from table(dbms_xplan.display());
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
----------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost  |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             | 32437 |  2787K|  1130 |
|   1 |  FILTER                      |             |       |       |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEXTRACTO   | 32437 |  2787K|  1130 |
|   3 |    INDEX RANGE SCAN          | INDEX_TRUNC | 58387 |       |   169 |
----------------------------------------------------------------------------
Note
-----
   - 'PLAN_TABLE' is old version
 
13 rows selected
 
SQL> 

So... if I use index F_EXTRACTO the cost is 250000, but if I use index TRUNC(F_EXTRACTO) the cost is 1130. Can somebody tell me why exists such a big difference between this two approaches? If you need some additional information, please tell me.

Answer 1

Huge difference between estimated number of rows (12M vs 58387 ) is very likely attributed to the out of date statistics. I'd suggest collecting statistics (for instance, with DBMS_STATS.gather_table_stats ) after adding indexes.

Also, EXPLAIN PLAN doesn't guarantee that the plan is what will be actually used. I'd rather run query, and then check actual execution plan with dbms_xplan.display_cursor. It also makes sense to look into v$sql/v$sqlarea views for execution details.