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?
date_received
is an Oracledate
type. Indeed, Oracledate
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 ondate_received
column.