-- collect global statistics on Partition Tables
Option # 1
set serveroutput on
set time on
declare
this_day char(8);
part_name char(10);
tab_name varchar2(30);
v_num number;
v_string varchar2(4000);
cursor tab_cur IS select table_name from user_tables where PARTITIONED='YES' and table_name in ('EVENT_LOG1','EVENT_LOG2');
begin
for table_rec in tab_cur loop
exit when tab_cur%NOTFOUND;
tab_name := table_rec.table_name;
dbms_stats.gather_table_stats( ownname =>'SCOTT', tabname => tab_name, granularity=> 'GLOBAL', estimate_percent => 15, method_opt => 'FOR ALL INDEXED COLUMNS SIZE 1', degree => 1, cascade => TRUE );
end loop;
end;
/
Note : granularity is GLOBAL, which means only GOBAL statustics on partition table is going to be collected.
For other options, check Gather statistics of Partition Tables Part 1
Option # 2
begin
dbms_stats.gather_table_stats(ownname=>SCOTT',
tabname=>'EVENT_LOG1’,
granularity=>'ALL',
estimate_percent=>15,
method_opt=>'FOR ALL COLUMNS SIZE AUTO', degree => 1, cascade=>TRUE); end;
/
Note : granularity is ALL, which means Gathers all (subpartition, partition, and global) statistics.
Hope this help. Regards Rupam
