• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 954
  • Last Modified:

Analyze Table in Oracle

Hi,
   When i analyze the table with below command, the table is getting analyzed. I want to know the corresponding index also got analyzed or not?

 DBMS_STATS.gather_table_stats ('XXQP', 'XXQP_SAMPLE', estimate_percent => 30);

Thanks
0
Suriyaraj_Sudalaiappan
Asked:
Suriyaraj_Sudalaiappan
  • 4
  • 2
1 Solution
 
sdstuberCommented:
by default NO, indexes are not gathered with table stats.

However, in 10g and above the default is configurable
you can check the default with this in 10g and above

BEGIN
    IF DBMS_STATS.to_cascade_type(DBMS_STATS.get_param('CASCADE'))
    THEN
        DBMS_OUTPUT.put_line('indexes gathered with table stats');
    ELSE
        DBMS_OUTPUT.put_line('indexes NOT gathered with table stats');
    END IF;
END;
0
 
sdstuberCommented:
In 8i and 9i the default is always FALSE for "cascade"
0
 
Suriyaraj_SudalaiappanAuthor Commented:
Hi,
  I have oracle 10g and i am running the your code. It is returning 'indexes NOT gathered with table stats'.
How?

Thanks
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
sdstuberCommented:
"How?"  what are you asking?


The output means your default has not been modified.
So indexes are not gathered when you gather table stats unless you override that default.

set cascade  TRUE if you want to gather indexes when you gather tables

DBMS_STATS.gather_table_stats ('XXQP', 'XXQP_SAMPLE', estimate_percent => 30, cascade=>TRUE);

0
 
Suriyaraj_SudalaiappanAuthor Commented:
GOOD ONE
0
 
sdstuberCommented:
glad I could help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now