Oracle 常用指令 & SQL - Partition 篇 (partition section)

Check Partition Rows

column table_name         format a20
column partition_name     format a20
column subpartition_name  format a20
 
select table_name,
   partition_name,
   num_rows
from
   dba_tab_partitions
where table_owner = 'schema_name'
order by
   table_name,
   partition_name;

Gather Special Partition Name Statstics

If you are specifying a granularity => PARTITION, then you should specify Partition name also in the stats command.

begin
  dbms_stats.gather_table_stats(ownname=> 'SCHEMA',
                                tabname=> 'TABLE_NAME',
                                partname=> 'PARTITION_NAME',
                                granularity=> 'PARTITION',
                                cascade=> true,
                                estimate_percent=> dbms_stats.auto_sample_size,
                                degree=> dbms_stats.auto_degree);
end;
/

Gather Partition Table All Statstics

If you are specifying granularity => GLOBAL, it is not required to specify Partition name in the stats command.

begin
  dbms_stats.gather_table_stats(ownname=> 'SCHEMA',
                                tabname=> 'TABLE_NAME',
                                granularity  => 'GLOBAL',
                                cascade=> true,
                                estimate_percent=> dbms_stats.auto_sample_size,
                                degree=> dbms_stats.auto_degree);
end;
/

tuncate partition data

alter table schema.table_name truncate partition partition_name;