Avatar of dplinnane
dplinnane
Flag 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
System ProgrammingOracle Database

Avatar of undefined
Last Comment
dplinnane

8/22/2022 - Mon
SOLUTION
DonConsolio

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
DonConsolio

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
slightwv (䄆 Netminder)

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Devinder Singh Virdi

Gather table stats also.
SOLUTION
Sean Stuber

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
SOLUTION
Naveen Kumar

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy