Best Method to Gather Stats of Partition Tables When Using Granularity

 Best Method to Gather Stats of Partition Tables When Using Granularity

What is the best option when using granularity:

1. exec dbms_stats.gather_table_stats(ownname=>'IBM',tabname=>'dm_sku_partition_stg',GRANULARITY => 'PARTITION',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true);
Or
2. exec dbms_stats.gather_table_stats(ownname=>'IBM',tabname=>'dm_sku_partition_stg',GRANULARITY=>'GLOBAL AND PARTITION',estimate_percent=>dbms_stats.auto_sample_size,cascade=>true,degree=>8);

SOLUTION

If you are specifying a granularity => PARTITION, then you should specify Partition name also in the stats command.  But if you are specifying granularity => GLOBAL, it is not required to specify Partition name in the stats command.

Either of the below methods are the best practices to gather the stats of Partitioned tables:

exec dbms_stats.gather_table_stats(ownname=>'AAA',tabname=>'BBB',partname=>'CCC',granularity=>'PARTITION',CASCADE=> true,estimate_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree);

(or)

exec dbms_stats.gather_table_stats (ownname=>'AAA',tabname=>'BBB',granularity =>'GLOBAL',CASCADE=> true,estimate_percent=>dbms_stats.auto_sample_size,degree=>dbms_stats.auto_degree);