Thursday 5 April 2007

ANALYZE to compute statistics

I use the ANALYZE command to calculate quickly and to see what effect on the execution plan of a query the new statistics will have.

The statistics calculation method recommended by many is actually the DBMS_STATS package. Search this blog for examples of DBMS_STATS.

Analyze a table with a percent estimate:

ANALYZE TABLE X ESTIMATE STATISTICS SAMPLE 10 PERCENT;

Analyze a table partition:

ANALYZE TABLE X PARTITION (Z) COMPUTE STATISTICS;

Analyze a table partition using a percent estimate:

ANALYZE TABLE X PARTITION (Z) ESTIMATE STATISTICS SAMPLE 10 PERCENT;


No comments: