Avatar of Steve Berger
Steve BergerFlag for United States of America asked on

What is mean by ANALYZE command in oracle?

Hi,
Wht is the purpose of ANALYZE command. if we ANALYZE the table/index wht happens to the object and how it improves the performance? Because why i am asking is, I have table which is contain fuction based index like UPPER(column_name). When i execute the below query, it is getting full table access.

select * from table_name where upper(column_name) = upper('ABC');

At the same time, when i analyze the table it is getting index based scan in the explain plan output.
ANALYZE TABLE table_name COMPUTE STATISTICS;

One more thing is when i execute DBMS_STATS package also the table is getting full table access. See my execute command below.
EXEC DBMS_STATS.gather_index_stats('schema_name', 'index_name', estimate_percent => 30);

Can anyone share with me the solution of this? Thanks in advance.
Enterprise Software

Avatar of undefined
Last Comment
rleni59

8/22/2022 - Mon
ASKER CERTIFIED SOLUTION
Guy Hengel [angelIII / a3]

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
rleni59

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Your help has saved me hundreds of hours of internet surfing.
fblack61