Can anyone explain why the function based index is doing a full table scan using TRUNC(approval_date) in the where clause but uses the index for approval_date in the where clause. Thanks
CREATE INDEX PRICE_CHANGE_I9 ON PRICE_CHANGE
TRUNC(approval_date)
NOLOGGING COMPUTE STATISTICS;
SELECT *
FROM price_change
TRUNC(approval_date) = SYSDATE
FULL TABLE SCAN
SELECT *
FROM price_change
approval_date = SYSDATE
SYSDATE was just used as an example as I could not get an explain plan with value SYSDATE + 1
Anyway the solution was to add (TRUNC(approval_date)) and not TRUNC(approval_date) when creating the index.