0

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?

  • 1
    Please tag the question with the appropriate database and version. It looks like Oracle to me, but I don't know enough about the other databases to make that determination. If it is Oracle, you really should be using the CBO as the RBO was de-supported as of Oracle 11g (it was deprecated long before that, since Oracle 7.1 I believe). – Kris Johnston May 31 '17 at 16:14
  • @KrisJohnston You're right, I totally forgot to mention it's Oracle 11gr2. – Pierre Jun 1 '17 at 7:27
  • @JSapkota It cannot work because date_received is an Oracle date type. Indeed, Oracle date type manages hours, minutes and seconds so your query is equivalent to this one: SELECT * FROM event WHERE date_received = TO_DATE('12/05/2017 00:00:00', 'DD/MM/YYYY HH24:MI:SS'); that is not what I want: I need all the events of the day. However, I don't have any index on date_received column. – Pierre Jun 1 '17 at 8:00
  • Oh, as suggested by Kris, consider using CBO instead of RBO. – JSapkota Jun 1 '17 at 8:21
  • Cost-based optimizer is using the index to access the table for the same query, example is here:dbfiddle.uk/… – JSapkota Jun 1 '17 at 8:29
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.