模拟并消除行迁移(Row Migration)

--什么是行迁移？
A situation in which Oracle Database moves a row from one data block to another data block because the row growstoo large to fit in the original block.

注：尽管行迁移与行链接是两个不同的事情，但是在oracle内部，它们被当作一回事。所以当你检测行迁移与行链接时，你应该仔细的分析当前你正在处理的是行迁移还是行链接。

--模拟实验EODA@PROD1> create table t(x int, y varchar2(50));Table created.EODA@PROD1> begin  2  for i in 1..1000 loop  3    insert into t values (i,'A');  4  end loop;  5  commit;  6  end;  7  /PL/SQL procedure successfully completed.EODA@PROD1> analyze table t compute statistics;Table analyzed.--chain_cnt计算了存在行迁移和行链接的行数EODA@PROD1> select table_name,num_rows,chain_cnt from user_tables where table_name='T';TABLE_NAME NUM_ROWS  CHAIN_CNT------------------------------ ---------- ----------T     1000   0EODA@PROD1> update t set y=rpad('A',50,'B');1000 rows updated.EODA@PROD1> commit;Commit complete.EODA@PROD1> analyze table t compute statistics;Table analyzed.EODA@PROD1> select table_name,num_rows,chain_cnt from user_tables where table_name='T';TABLE_NAME NUM_ROWS  CHAIN_CNT------------------------------ ---------- ----------T     1000 967--使用脚本创建CHAINED_ROWS表EODA@PROD1> @?/rdbms/admin/utlchainTable created.EODA@PROD1> desc chained_rows Name       Null?Type ----------------------------------------------------- -------- ------------------------------------ OWNER_NAMEVARCHAR2(30) TABLE_NAMEVARCHAR2(30) CLUSTER_NAMEVARCHAR2(30) PARTITION_NAME VARCHAR2(30) SUBPARTITION_NAMEVARCHAR2(30) HEAD_ROWIDROWID ANALYZE_TIMESTAMPDATEEODA@PROD1> analyze table t list chained rows into chained_rows;  --自动分析并存入CHAINED_ROWS表Table analyzed.EODA@PROD1> select count(*) from chained_rows;  COUNT(*)----------       967--通过重新插入解决行迁移EODA@PROD1> create table tmp as select * from t where rowid in (select head_rowid from chained_rows);Table created.EODA@PROD1> delete t where rowid in (select head_rowid from chained_rows);967 rows deleted.EODA@PROD1> insert into t select * from tmp;967 rows created.EODA@PROD1> commit;Commit complete.EODA@PROD1> analyze table t compute statistics;Table analyzed.EODA@PROD1> select table_name,num_rows,chain_cnt from user_tables where table_name='T';TABLE_NAME NUM_ROWS  CHAIN_CNT------------------------------ ---------- ----------T     1000   0EODA@PROD1> drop table tmp purge;Table dropped.