Why the function-based index is not used

functionsindexindex-tuningoracle-11g-r2

I have a table EVENT with a column DATE_RECEIVED (the type of this column is DATE):

CREATE TABLE "EVENT"
   ("EVENT_ID" VARCHAR2(60 BYTE) NOT NULL ENABLE, 
[...]
   "DATE_RECEIVED" DATE, 
[...]
);

I also create a function-based index on DATE_RECEIVED:

CREATE INDEX IDX_EVENT_TRUC_DATE_RECEIVED ON EVENT(TRUNC("DATE_RECEIVED"));

But this index is not used. When I run the following request, I got a TABLE ACCESS FULL:

SELECT * FROM event
WHERE trunc(date_received) = TO_DATE('30/05/2016', 'DD/MM/YYYY');

In the plan table, I get:

----------------------------------                                                                                                                                                                                                                                                                          
| Id  | Operation         | Name |                                                                                                                                                                                                                                                                          
----------------------------------                                                                                                                                                                                                                                                                          
|   0 | SELECT STATEMENT  |      |                                                                                                                                                                                                                                                                          
|*  1 |  TABLE ACCESS FULL| CORE |                                                                                                                                                                                                                                                                          
----------------------------------                                                                                                                                                                                                                                                                          

Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                          
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

   1 - filter(TRUNC(INTERNAL_FUNCTION("DATE_RECEIVED"))=TO_DATE('                                                                                                                                                                                                                                           
              2016-05-30 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))                                                                                                                                                                                                                                              

Note                                                                                                                                                                                                                                                                                                        
-----                                                                                                                                                                                                                                                                                                       
   - rule based optimizer used (consider using cbo)

I found that INTERNAL_FUNCTION means there is an implicit conversion. But I don't know why because TRUNC accept DATE type.

What can I do to use my index?

Best Answer

As @JSapkota said in the comments, I need to use CBO instead of RBO. To do it, I need to gather stats on the EVENT table:

EXEC DBMS_STATS.gather_table_stats('USER', 'EVENT');

This time, the index is used:

------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                              | 20000 |    25M|  2166   (1)| 00:00:26 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EVENT                        | 20000 |    25M|  2166   (1)| 00:00:26 |
|*  2 |   INDEX RANGE SCAN          | IDX_EVENT_TRUC_DATE_RECEIVED |  8000 |       |    88   (0)| 00:00:02 |
------------------------------------------------------------------------------------------------------------