Indexes on Expressions, aka FBI’s

Available since Oracle 8i, an index used to be used only if the expression that was indexed was referenced.  Since 11.2.0.2 this has been changed.

rajesh@ORA11G> create table t

  2  as

  3  select *

  4    from all_objects; 

Table created. 

rajesh@ORA11G> create index t_idx1 on t(trunc(created)); 

Index created. 

rajesh@ORA11G> create index t_idx2 on t(substr(object_name,1,10)); 

Index created. 

rajesh@ORA11G> exec dbms_stats.gather_table_stats(user,'T'); 

PL/SQL procedure successfully completed. 

rajesh@ORA11G>  

So far we have two function based indexes T_IDX1 and T_IDX2. When we run the below queries in a 10g database, we see the index not used.

rajesh@ORA10G> set autotrace traceonly explain

rajesh@ORA10G> select *

  2  from  t

  3  where created = sysdate; 

Execution Plan

----------------------------------------------------------

Plan hash value: 1601196873

--------------------------------------------------------------------------

| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------

|   0 | SELECT STATEMENT  |      |    25 |  2425 |   186   (2)| 00:00:03 |

|*  1 |  TABLE ACCESS FULL| T    |    25 |  2425 |   186   (2)| 00:00:03 |

-------------------------------------------------------------------------- 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("CREATED"=SYSDATE@!)

 

But the same query in 11g database will make use of indexes by adding appropriate filter conditions.

 

rajesh@ORA11G> set autotrace traceonly explain

rajesh@ORA11G> select *

  2  from  t

  3  where created = sysdate; 

Execution Plan

----------------------------------------------------------

Plan hash value: 4055207394

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |    58 |  6438 |    19   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T      |    58 |  6438 |    19   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX1 |  1023 |       |     3   (0)| 00:00:01 |

-------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):

--------------------------------------------------- 

   1 - filter("CREATED"=SYSDATE@!)

   2 - access(TRUNC(INTERNAL_FUNCTION("CREATED"))=TRUNC(SYSDATE@!))

 

rajesh@ORA11G> select *

  2  from t

  3  where object_name ='SAMPLE_DATA'; 

Execution Plan

----------------------------------------------------------

Plan hash value: 3371054274

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     2 |   228 |     3   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| T      |     2 |   228 |     3   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | T_IDX2 |     3 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

--------------------------------------------------- 

   1 - filter("OBJECT_NAME"='SAMPLE_DATA')

   2 - access(SUBSTR("OBJECT_NAME",1,10)='SAMPLE_DAT') 

rajesh@ORA11G> set autotrace off