行連結 行遷移的消除

模拟行連結：

如何模仿行連結？

首先要了解三個語句：

第1句：運作$ORACLE_HOME/rdbms/admin/utlchain.sql 腳本，SQL> @D:\oracle\product\10.1.0\Db_1\RDBMS\ADMIN\utlchain.sql建立chained_rows表，用于存放發生行遷移

        的行的rowid。

第2句：運作analyze table table_name list chained rows into chained_rows; 把産生行遷移的rowid 移動到這個表中。

第3句：運作select * from chained_rows where table_name= 'table_name'; 檢視産生的行遷移的rowid。

行連結好模仿，如下：

SQL> create table test (x int primary key ,a char(2000),b char(2000),c char(2000),d

char(2000),e char(2000)) tablespace test2;

test2的塊大小是8K，5個char(2000)的字段,這樣每行記錄約為10k。肯定超過一個塊大小。

SQL> insert into test (x) values (1);

SQL> commit;

SQL>

select * from CHAINED_ROWS ;

未標明行

此時還沒産生行連結，

SQL> update test set a='test',b='test',c='test',e='test' where x=1;

已更新 1 行。

SQL> commit;

送出完成。

SQL> ANALYZE TABLE test LIST CHAINED ROWS;

表已分析。

SQL> select * from CHAINED_ROWS;

    TABLE_NAME     HEAD_ROWID         

----------------------- --------- ------------------------------                           

  TEST               AAAMFaAAHAAACVCAAA

可見産生的是行連結，因為一個塊的大小隻有8k,而此時這條記錄的大小已經約為10k,意味着将跨資料塊存儲。

那麼請問，如何模仿行遷移呢？

下面是一種模仿行遷移的辦法：

block的大小為：8k

test@ORCL>create table test (id int,name varchar2(4000),job varchar2(2200),sal varchar(2000) ) pctfree 0 pctused 99;

表已建立。

test@ORCL>insert into test values(1,'dd','xx','sss');

已建立 1 行。

test@ORCL>insert into test values(2,'dx','xx','sss');

test@ORCL>insert into test values(3,'dx','xx','sss');

test@ORCL>insert into test values(4,'dx','xx','sss');

test@ORCL>commit;

test@ORCL>update test set name=RPAD('Z',4000,'Z') where id=4;

test@ORCL>select dbms_rowid.rowid_block_number(rowid) as block_number from test;

BLOCK_NUMBER

------------

         106

可見所有的記錄都是在106号資料塊裡面。

test@ORCL>ANALYZE TABLE test LIST CHAINED ROWS;

test@ORCL>select * from CHAINED_ROWS;

此時并沒有産生行連結或者行遷移。

----執行完上一條update的時候（也就是update test set name=RPAD('Z',4000,'Z') where id=4;），此塊剩下的大小不足4k;是以執行下面的update将肯定導緻行遷移。

test@ORCL>update test set job=RPAD('J',2200,'J'),sal=RPAD('S',2000,'S') where id=3;

OWNER_NAME      TABLE_NAME      CLUSTER_NAME     PARTITION_NAME    

SUBPARTITION_NAME        HEAD_ROWID          ANALYZE_TI

------------  -------------- ---------------- ------------------    ------------------- ----------------          ----------

TEST             TEST                                                    N/A              AAAOqKAAKAAAABqAAC      25-1月 -10

test@ORCL>select id from test where rowid in (select head_rowid from chained_rows);

    ID

-------

    3

可見是ID為3的記錄産生了行遷移。而此時ID為3的記錄所在的塊号是不會變化的，盡管是行遷移：

-------下面将通過簡單的delete insert 的方式來解決這個行遷移的問題：

test@ORCL>create table temp as select * from test  where rowid in (select head_rowid from chained_rows);

test@ORCL>delete from test  where rowid in (select head_rowid from chained_rows);

已删除 1 行。

test@ORCL>select id from test;

        ID

----------

         1

         2

         4

test@ORCL>select id from temp;

         3

test@ORCL>select * from chained_rows;

OWNER_NAME        TABLE_NAME       CLUSTER_NAME       PARTITION_NAME     SUBPARTITION_NAME           HEAD_ROWID           ANALYZE_TI

------------- ------------------ ---------------- ------------------   -------------------        ------------------        -------

TEST                 TEST                                                    N/A                AAAOqKAAKAAAABqAAC        25-1月 -10

而此時chained_rows表裡還有之前的行遷移的統計資訊，好，我們删了，從新來過：

對表從新分析後，可見行遷移已經被幹掉了。我們再來看看block number:

         107

test@ORCL>select dbms_rowid.rowid_block_number(rowid) as block_number from test

    where rowid=(select rowid from test where id=3);

可見test表裡，id為3的記錄已經被從106号資料塊裡放到了107号資料塊裡面，而不是像之前的那樣：在106号資料塊id=3的那條記錄裡存儲了一個指向其他資料塊的位址。此時id=3的記錄已經被單獨放到了107号資料塊裡。

總結：

行遷移可以通過簡單的delete,insert該條資料的方式解決。而行連結的話delete和insert的方式是不能見效的。行連結隻有考慮使用更大的資料塊，來解決。

總結行清除行遷移的各種方法：

1、傳統的清除行遷移的方法

具體步驟如下：

（1）執行$ORACLE_HOME/rdbms/admin目錄下的 utlchain.sql腳本建立chained_rows表。

（2）将存在行遷移的表（此處用table_name 代替） 中的産生行遷移的行的rowid 放入到chained_rows表中。

analyze table table_name list chained rows into chained_rows;

（3）将表中行遷移的rowid 放入臨時表中儲存

create table table_name_temp as select *　from table_name where rowid in (select head_rowid from chained_rows where table_name= 'table_name');

（4） 删除表中原來存在的行遷移的記錄行

delete table_name where rowid in (select head_rowid from chained_rows where table_name= 'table_name');

（5）從臨時表中取出且重新把那些被删除了的資料插入到原來的表中，并删除臨時表

insert into table_name select * from table_name_temp;

drop table table_name_temp;

這種傳統的清除行遷移（RM）的方法，優點是執行起來的比較簡單，容易實作。但是這種算法的缺陷是沒有考慮到表關聯的情況。但是在真正的資料庫應用中，很多表都是和别的表關聯在一起的，如果有外鍵的限制，這樣步驟3中的delete是不能删除那條發生行遷移的行的。這種方法在插入和删除行的時候沒有disable掉索引，這樣導緻時間删除和插入時維持索引樹的均衡上了，如果記錄數多的話，耗時太嚴重。

2、改進的清除行遷移的方法

（2）禁用所有其他表上關聯到此表的所有限制。

（6）啟用所有其他表上關聯到此表的所有限制。

下面是一個具體的示例：

select index_name,index_type,table_name from user_indexes where table_name= 'TERMINAL'; —— 查出表對應的索引

select constraint_name,constraint_type,table_name from user_constraints where r_constraint_name = 'PK_TERMINAL_ID';

——查出外鍵對應的表

alter table 外鍵表 disable constraint sys_c003200

建立臨時表——插入有行連接配接的資料行——删除原表中有行連接配接的行。——将臨時表中的資料插入原表——删除臨時表——啟用限制

3、使用toad工具清除行遷移的方法

4、使用emp/imp工具清除行遷移的方法

檢查行遷移的方法：

1）        運作$ORACLE_HOME/rdbms/admin/utlchain.sql

2）        analyze table table_name list chained rows into CHAINED_ROWS

3）        select * from CHAINED_ROWS where table_name='table_name';

清除的方法：

方法1：create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows);

       Delete from table_name where rowed in (select head_rowid from chained_rows);

       Insert into table_name select * from table_name_tmp;

方法2：create table table_name_tmp select * from table_name ;

truncate table table_name

insert into table_name select * from table_name_tmp

方法3：用exp工具導出表，然後删除這個表，最後用imp工具導入這表

方法4：alter table table_name move tablespace tablespace_name，然後再重新表的索引

上面的4種方法可以用以消除已經存在的行遷移現象，但是行遷移的産生很多情況下時由于PCT_FREE參數設定的太小所導緻，是以需要調整PCT_FREE參數的值。