Oracle行遷移實驗

作為ORACLE DBA，您應該定期對資料做體檢，今天給大家介紹如何解決Oracle 行遷移和行連結技術。
在什麼情況下會出現行遷移，在對錶進行頻繁update時會產生行遷移。行連結出現一般是由於資料表
設計不合理造成的，
注：
行遷移：頻繁update的表會產生，要注意！
行連結：表設計不合理會產生。
我們透過實驗理解一下：
該實驗的目的是詳細理解表的儲存。
行遷移的形成：由update造成的。
當行長增加的時候，本資料塊沒有足夠的空閒空間。導致該行被迫儲存到其它資料塊，在原資料塊保留訪問的指標。
當資料庫訪問該行時，要進行二次io。導致資料庫的效能下降。
-----------------------------------------實驗------------------------------------------------------
SQL>conn scott/tiger
SQL>drop table MG_CHAIN purge;
SQL> create table MG_CHAIN(name varchar2(30));
SQL> alter table  MG_CHAIN pctfree 0;--使表中的資料塊都存放資料。不預留空間
SQL>begin-- 製造8000行資料。
   for i in 1 .. 8000 loop
      insert into MG_CHAIN values ('abcdef');
   end loop;
end;
/
SQL>commit;
SQL> ANALYZE TABLE t1 COMPUTE STATISTICS;--分析表可以得到表的行遷移資訊，透過user_tables中的列CHAIN_CNT來獲得。
SQL> select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
注意檢視結果，CHAIN_CNT如果為0說明沒有遷移的行。
SQL>update MG_CHAIN set name='qwertyuiopasdfghjklzxcvbnm26';--更新為26個字母。欄位的長度變長了。
SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;
SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
注意檢視結果，CHAIN_CNT應該有8000左右，說明行發生了遷移。BLOCKS的數量比上次增加了很多。
--------------------------------我們透過上面的方法制造了行遷移，並透過分析表的方式找到了行遷移-----------------------------
下面我們來消除行遷移。
1.移動表
2.給表做外科手術方式來消除遷移的行
3.Exp/imp透過匯入匯出的方式

重點講第2種
1.移動表方式非常簡單
SQL> alter table MG_CHAIN move tablespace users;
SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;
SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
我們消除了遷移的行,表BLOCKS塊,下降了,AVG_ROW_LEN平均行長下降了.CHAIN_CNT變0了。
2.給表做外科手術方式來消除遷移的行
SQL>update MG_CHAIN set name='qwertyuiopasdfghjklzxcvbnm26';--在次修改更新為26個字母。製造行遷移。
SQL> ANALYZE TABLE MG_CHAIN COMPUTE STATISTICS;
SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';--行遷移又產生了。
SQL> @%oracle_home%\rdbms\admin\utlchain.sql--我們執行一個指令碼，來記錄行遷移資訊，這個指令碼其實很簡單，只是一個簡單的表，大家可以
開啟看看。該表的功能只是收集記錄行遷移資訊。
SQL> desc CHAINED_ROWS
 Name                                                  Null?    Type
 ----------------------------------------------------- -------- ------------
 OWNER_NAME                                                     VARCHAR2(30)
 TABLE_NAME                                                     VARCHAR2(30)
 CLUSTER_NAME                                                   VARCHAR2(30)
 PARTITION_NAME                                                 VARCHAR2(30)
 SUBPARTITION_NAME                                              VARCHAR2(30)
 HEAD_ROWID                                                     ROWID
 ANALYZE_TIMESTAMP                                              DATE
SQL> ANALYZE TABLE MG_CHAIN  list chained rows;--執行這條語句，就會把行遷移的資訊記錄到上面新建的表CHAINED_ROWS中。
SQL> select * from CHAINED_ROWS;

SQL> create table temp_chain as select * from MG_CHAIN  where rowid in(select HEAD_ROWID from CHAINED_ROWS);--temp_chain 表中臨時儲存被遷移的行。
SQL> delete MG_CHAIN  where rowid in(select HEAD_ROWID from CHAINED_ROWS);--刪除所有遷移的行
SQL> insert into MG_CHAIN   select * from  temp_chain ;--再將被刪除的行插入到原來的表中
SQL> commit;

SQL> ANALYZE TABLE MG_CHAIN   COMPUTE STATISTICS;--再次分析下表

SQL>select TABLE_NAME,NUM_ROWS,BLOCKS,EMPTY_BLOCKS,AVG_SPACE,CHAIN_CNT,AVG_ROW_LEN
    from user_tables where table_name='MG_CHAIN';
外科手術式的消除遷移的行,因為insert不產生遷移,update才會發生遷移。
3.Exp/imp透過匯入匯出的方式
使用EXP先把表匯出，再使用IMP匯入。
其實就實現了先把表資料刪除了，再插入到表中。