dbms_stats : partition vs granularity stats
104183Feb 19 2008 — edited Feb 19 2008What 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