关于分区表数据清理，这里做下记录，便于以后查找:

1、查看每个子分区的记录

select TABLE_NAME,PARTITION_NAME,NUM_ROWS from dba_tab_partitions
where table_name='NHM_INTERFACE_KPI'
and TABLE_OWNER='NHM'
and num_rows <> 0
order by PARTITION_NAME;

TABLE_NAME                     PARTITION_NAME                   NUM_ROWS
------------------------------ ------------------------------ ----------
NHM_INTERFACE_KPI              A0905                             5795085
NHM_INTERFACE_KPI              A0906                             6988101
NHM_INTERFACE_KPI              A0907                             7894069
NHM_INTERFACE_KPI              A0908                             7246849
NHM_INTERFACE_KPI              A0909                             7120092
NHM_INTERFACE_KPI              A0910                             5853758
NHM_INTERFACE_KPI              A0911                             6317514
NHM_INTERFACE_KPI              A0912                             6866450

2、查看索引的索引列

 set linesize 230
 col column_name format a30
 select table_name,index_name,column_name from dba_ind_columns where table_name='NHM_INTERFACE_KPI' and table_owner='NHM';

TABLE_NAME                     INDEX_NAME                     COLUMN_NAME
------------------------------ ------------------------------ ------------------------------
NHM_INTERFACE_KPI              IDX2                           DEVICE_UUID
NHM_INTERFACE_KPI              IDX1                           UUID
NHM_INTERFACE_KPI              NHM_INTERFACE_IDX1             TIME_STAMP
NHM_INTERFACE_KPI              NHM_INTERFACE_IDX3             TIME_STAMP
NHM_INTERFACE_KPI              NHM_INTERFACE_IDX3             DEVICE_UUID
NHM_INTERFACE_KPI              NHM_INTERFACE_IDX3             UUID

3、检查分区表的索引是否LOCAL类型

select table_name,index_name,LOCALITY from dba_part_indexes where table_name='NHM_INTERFACE_KPI' and owner='NHM';

TABLE_NAME                     INDEX_NAME                     LOCALI
------------------------------ ------------------------------ ------
NHM_INTERFACE_KPI              IDX1                           LOCAL
NHM_INTERFACE_KPI              IDX2                           LOCAL
NHM_INTERFACE_KPI              NHM_INTERFACE_IDX1             LOCAL
NHM_INTERFACE_KPI              NHM_INTERFACE_IDX3             LOCAL

4、如果都是LOCAL索引，直接清除表分区数据（如果是全局索引，清除完成后需要重建)

alter table NHM_INTERFACE_KPI truncate partition A0905;
alter table NHM_INTERFACE_KPI truncate partition A0906;
alter table NHM_INTERFACE_KPI truncate partition A0907;
alter table NHM_INTERFACE_KPI truncate partition A0908;

5、重新收集该表的统计信息

begin
  dbms_stats.gather_table_stats(ownname          => 'NHM',
                                tabname          => 'NHM_INTERFACE_KPI',
                                estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
                                method_opt       => 'for all columns size repeat',
                                degree           => DBMS_STATS.AUTO_DEGREE,
                                granularity      => 'ALL',
                                cascade          => TRUE);
end;

然后检查一下，OK。