ORACLE 分区表move操作

我这里指定了需要分析的数据表
–分析表的行链接和行迁移情况

analyze table HCP.HR_PERSONNEL_BASE list chained rows into chained_rows;

–查看发生行迁移的数据量

SELECT table_name, count(*) from sys.chained_rows GROUP BY table_name;

–MOVE 完成分析后，重新分析，重新查询

DELETE FROM sys.chained_rows WHERE TABLE_NAME='HR_CARDING';
analyze table HCP.HR_PERSONNEL_BASE list chained rows into chained_rows;

–查看表大小

 SELECT TRUNC(SUM(BYTES)/1024/1024)||'MB' FROM DBA_SEGMENTS WHERE SEGMENT_NAME='HR_CARDING';
 

—MOVE TABLE 消除行迁移
—MOVE PARTITION TABLE (SQL 拼接)

select 'ALTER TABLE ' || 'HCP.' || table_name || ' MOVE PARTITION ' ||
       partition_name || ' NOLOGGING PARALLEL 2;'
  from dba_tab_partitions
 where table_owner = 'HCP'
   AND table_name = 'HR_CARDING';

–查看索引状态
–普通索引

SELECT OWNER,INDEX_NAME,TABLE_NAME,STATUS FROM DBA_INDEXES WHERE TABLE_NAME='HR_OVERTIME';
ALTER INDEX HCP.HR_OVERTIME_IDX_DUP REBUILD NOLOGGING PARALLEL 2;

–分区索引

SELECT T.OWNER, T.TABLE_NAME, I.INDEX_NAME, I.PARTITION_NAME, I.STATUS
  FROM DBA_IND_PARTITIONS I, DBA_PART_INDEXES T
 WHERE I.INDEX_NAME = T.INDEX_NAME
   AND T.OWNER = 'HCP'
   AND TABLE_NAME = 'HR_CARDING'
 ORDER BY 3, 4;

–REBUILD PARTITION INDEX(SQL 拼接)

SELECT 'ALTER INDEX ' || 'HCP.' || INDEX_NAME || ' REBUILD PARTITION ' ||
       PARTITION_NAME || ' ONLINE PARALLEL 12;'
  FROM (SELECT T.OWNER,
               T.TABLE_NAME,
               I.INDEX_NAME,
               I.PARTITION_NAME,
               I.STATUS
          FROM DBA_IND_PARTITIONS I, DBA_PART_INDEXES T
         WHERE I.INDEX_NAME = T.INDEX_NAME
           AND T.OWNER = 'HCP'
           AND TABLE_NAME = 'HR_CARDING'
         ORDER BY 3, 4) WHERE PARTITION_NAME='YEAR_2020';

–重新收集统计信息

begin
 dbms_stats.gather_table_stats(ownname =>'HCP',tabname =>'HR_CARDING' );
end;

begin
 dbms_stats.gather_index_stats(ownname =>'HCP',indname =>'HR_CARD_ABSENCE_INDX1' );
end;