Oracle資料庫中資料行遷移與行連結

在Oracle資料庫中存在兩種資料儲存現象：行遷移和行連結。這兩者現象嚴重時都會導致資料庫查詢效能嚴重低下，處理好資料儲存的行連結或者行遷移也是有效提升資料庫效能的有效方式。

一、行遷移和行連結的定義：

1.行遷移:
ORACLE一個BLOCK的DEFAULT SIZE是8K,事實上,一個BLOCK不可以儲存8K的資料.一個BLOCK可以儲存多少資料,由PCTFREE,PCTUSED引數控制(對於以前的手工管理的表空間而言).

PCTFREE：是指BLOCK保留空閒空間的百分比,用於UPDATE。對於已經插入到BLOCK的行而言，後面的UPDATE操作有可能使行的長度增加；PCTFREE就是用於容納增加的那部分長度而保留的空閒空間。如果UPDATE時PCTFREE再也不能夠容納行增加的長度，則ORACLE會將整個行遷移到一個新的BLOCK，行的ROWID保留(不是太明白為什麼ORACLE不改變ROWID)，原來的BLOCK有一個指標指向ROW存放的新BLOCK。這就是行遷移。

可見，行遷移是由於UPDATE操作所導致。從字面上理解，所謂遷移，肯定先有存在這一行，才能叫著遷移.

2.行連結:
是指一個BLOCK不能容納一行(行的長度太大),而必須將此行存放於幾個BLOCK.行連結一般是在Insert時產生的.一個BLOCK能否用於insert是由PCTUSED控制.

PCTUSED：是指BLOCK用於INSERT的百分比。對於INSERT操作，BLOCK可用於容納新行的最大空間為Blocksize-pctfree-overhead.當BLOCK資料儲存已高於PCTUSED，ORACLE會將該塊從自由連結串列中移除，直到該塊已使用空間降到PCTUSED以下，才會再次將此塊重新加入到Freelist(這是ORACLE以前手工管理的表空間管理空閒塊的原理,現在ORACLE推薦使用ASSM).

對錶分析後檢查表中行遷移的情況：

select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT from user_tables where table_name='EMP';

（其中欄位CHAIN_CNT顯示的就是行遷移或者行連結的表中的行數）

二、行遷移與行連結的分析檢測：

檢測表中是否存在行遷移或者行連結需要對錶進行分析：
表分析的方法有兩種：

1, analyze table emp compute statistics;2, exec dbms_stats.gather_schema_stats('scott','emp');   --前者為使用者名稱，後者為表名

但是ORACLE的dbms_stats.gather_schema_stats只會收集最佳化器統計資訊,不會檢測表的記錄是否存在行遷移和行連結，因此要分析行連結或者行遷移必須用analyse命令。

行遷移和行連結的檢測：

select b.NAME,a.VALUE from v$mystat a,v$statname b where a.STATISTIC#=b.STATISTIC# and b.NAME like 'table fetch continued row';

當有返回值時，可以知道表的資料存在行遷移和行連結。
行遷移和行連結的清除：
能過REBUILD資料來清除行遷移：

create table MM_PM_temp as select * from MM_PM; truncate table MM_PM; insert into MM_PM select * from MM_PM_temp

再重新分析表：

analyze table MM_PM compute statistics;

分析過後再檢視：

select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %" from user_tables t where t.chain_cnt > 0;

如果該表的chain_cnt變為0時，表示原先的chain_cnt全部是行遷移，而不是行連結。
如果REBUILD資料後chain_cnt變少，但還大於0，則可以證明，這個表即包含行遷移，又包含真正的行連結。

事實證明，行遷移是可以透過REBUILD資料和增加PCTFREE%來清除和減少發生頻率的。

注意，對於ASSM，PCTUSED，FREELIST，FREELIST GROUPS引數會被忽略。
但對於真正的行連結，只能透過將表移植到大的BLOCSIZE的表空間上。

例如：

建立一個16K的表空間：

CREATE TABLESPACE LARGETBS BLOCKSIZE 16 K LOGGING DATAFILE '/data/app/oracle/oradata/ora33/LARGETBS_01.dbf' SIZE 64M AUTOEXTEND ON NEXT 10M MAXSIZE UNLIMITED EXTENT MANAGEMENT LOCAL SEGMENT SPACE MANAGEMENT AUTO ;

在建立的時候報了個ORA-的錯，原因我們沒有設定16的DB_Buffer_cache,我們設定一下：

 alter system set db_16k_cache_size=34603008;

將表MOVE到16K的表空間：

 alter table SRC_CS move tablespace LARGETBS;
 alter table MM_PM move tablespace LARGETBS;

由於進行了遷移，表的索引會失效，所以我們要REBUILD索引：

 alter index PK_SRC_CS rebuild; alter index PK_MM_PM rebuild;

再重新分析：

 analyze table SRC_CS compute statistics; analyze table MM_PM compute statistics;

重新查詢：

 select t.table_name, t.num_rows, t.chain_cnt, t.avg_row_len, round((t.chain_cnt / t.num_rows) * 100, 2) as "chained rows %" from user_tables t where t.chain_cnt > 0 order by t.table_name;

發現，這些表都沒有ROW CHAIN了。可見，MOVE到16K的表空間可以清除ROW CHAIN。

三、消除遷移的方法:

一：生成一張表(chained_rows),儲存遷移的行的rowid

@?/rdbms/admin/utlchain

使用分析命令將產生遷移的行的rowid插入到chained_rows表:

analyze table test01 list chained rows into chained_rows;create table tmp as select * from test01 where rowid in (select head_rowid from chained_rows);delete test01 where rowid in (select head_rowid from chained_rows);insert into test01 select * from tmp;drop table tmp purge;

再分析表：

analyze table test01 compute statistics;

再檢視字典中的統計資訊：

select TABLE_NAME,NUM_ROWS,AVG_ROW_LEN,BLOCKS,EMPTY_BLOCKS,CHAIN_CNT from user_tables where table_name='TEST01';

二、將存在行遷移的表用匯出工具匯出資料庫，將原表truncate後，再講資料重新匯入。