Skip to Main Content

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

dbms_stats : partition vs granularity stats

104183Feb 19 2008 — edited Feb 19 2008
What is the difference between dbms_statting on a partition level with the following statement (forget about skewonly for now) :

dbms_stats.gather_table_stats(ownname => schema_in,tabname => get_unana_tables_rec.table_name,partname => partition_name ,estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size skewonly', degree => 4, cascade => TRUE);

and gathering table stats (no specific partition mentioned) but with granularity set to ALL as in following statement :

dbms_stats.gather_table_stats(ownname => schema_in,tabname => get_unana_tables_rec.table_name,estimate_percent => dbms_stats.auto_sample_size, method_opt => 'for all columns size skewonly', degree => 4, cascade => TRUE, granularity => 'ALL');

1) Will the second statement with granularity => ALL analyze all partitions in table ?
2) OR Will the second statement analyze all partitions in table and provide MORE statistics than 1st statement ?

To gather partition level stats, which is better statement to use ?

Thanks
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Mar 18 2008
Added on Feb 19 2008
2 comments
27,461 views