本文共 1900 字，大约阅读时间需要 6 分钟。

分析表的行链接和行迁移情况

为了分析表的行链接和行迁移情况，我执行以下操作：

analyze table HCP.HR_PERSONNEL_BASE list chained rows into chained_rows;

接下来，查看发生行迁移的数据量：

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

完成分析后，需要重新执行分析操作：

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操作：

ALTER TABLE HCP.HR_CARDING MOVE PARTITION {partition_name} NOLOGGING PARALLEL 2;

如果需要更具体地执行MOVE PARTITION操作，可以参考以下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;

如果需要重建分区索引，可以执行以下操作：

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;