Link to home
Start Free TrialLog in
Avatar of dplinnane
dplinnaneFlag for United States of America

asked on

Function based index not working

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

Uses Index
SOLUTION
Avatar of DonConsolio
DonConsolio
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
ASKER CERTIFIED SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Gather table stats also.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of dplinnane

ASKER

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.