• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 948
  • 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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

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