1.行連結和行遷移概念
有關行連結和行遷移的概念請見下面的官方文件中的描述。
參考連結：
引於此處：
Row Chaining and Migrating

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

也就是說，行連結（Row Chaining）發生在INSERT階段資料塊無法容納過大資料時，而行遷移（Row Migrating）發生在UPDATE階段時原資料塊無法容納增大的資料時。

2.模擬行連結
1）建立包含四個CHAR型別欄位的表t_row_chaining
sec@ora10g> create table t_row_chaining (x char(2000), y char(2000), z char(2000), q char(2000));

Table created.

2）初始化一條資料
sec@ora10g> insert into t_row_chaining values('x','y','z','q');

1 row created.

sec@ora10g> commit;

Commit complete.

3）使用ANALYZE命令對錶t_row_chaining進行分析，以便驗證該表是否發生了行連結。
sec@ora10g> ANALYZE TABLE t_row_chaining LIST CHAINED ROWS;
ANALYZE TABLE t_row_chaining LIST CHAINED ROWS
*
ERROR at line 1:
ORA-01495: specified chain row table not found

如果出現上面的錯誤不要驚慌，原因是CHAINED_ROWS表沒有建立導致的。
可以使用Oracle自帶的utlchain.sql指令碼建立CHAINED_ROWS表
sec@ora10g> @?/rdbms/admin/utlchain.sql

Table created.

再次嘗試，成功。
sec@ora10g> ANALYZE TABLE t_row_chaining LIST CHAINED ROWS;

Table analyzed.

4）由於初始化的資料無法在一個資料塊中存放，此時發生了行連結。
sec@ora10g> col OWNER_NAME for a10
sec@ora10g> col TABLE_NAME for a16
sec@ora10g> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME TABLE_NAME       HEAD_ROWID         ANALYZE_TIMESTAMP
---------- ---------------- ------------------ -------------------
SEC        T_ROW_CHAINING   AAATMLAAFAAAdqcAAA 2010-01-05 23:33:40

3.模擬行遷移
1）此時我們建立包含四個VARCHAR2型別的表t_row_migrating
sec@ora10g> create table t_row_migrating (x varchar2(2000), y varchar2(2000), z varchar2(2000), q varchar2(2000));

Table created.

2）初始化一條資料
sec@ora10g> insert into t_row_migrating values ('x','y','z','q');

1 row created.

sec@ora10g> commit;

Commit complete.

3）此時由於是VARCHAR2型別的欄位，此時資料完全可以在一個資料塊中存放，因此此時不會發生行連結。
sec@ora10g> ANALYZE TABLE t_row_migrating LIST CHAINED ROWS;

Table analyzed.

sec@ora10g> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME TABLE_NAME       HEAD_ROWID         ANALYZE_TIMESTAMP
---------- ---------------- ------------------ -------------------
SEC        T_ROW_CHAINING   AAATMLAAFAAAdqcAAA 2010-01-05 23:33:40

4）我們使用t_row_chaining中的大資料對錶t_row_migrating進行更新，使之發生行遷移。
sec@ora10g> update t_row_migrating set (x,y,z,q)=(select * from t_row_chaining);

1 row updated.

sec@ora10g> commit;

Commit complete.

5）對錶t_row_migrating進行驗證
sec@ora10g> ANALYZE TABLE t_row_migrating LIST CHAINED ROWS;

Table analyzed.

6）此時表t_row_migrating已經赫然出現在chained_rows表的記錄中。
sec@ora10g> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME TABLE_NAME       HEAD_ROWID         ANALYZE_TIMESTAMP
---------- ---------------- ------------------ -------------------
SEC        T_ROW_CHAINING   AAATMLAAFAAAdqcAAA 2010-01-05 23:33:40
SEC        T_ROW_MIGRATING AAATMMAAFAAAdqoAAA 2010-01-05 23:35:24

4.小結
在判斷表是否發生行連結和行遷移的方法不僅限於文中提到的方法，使用一般的表分析方法，結合ALL_TABLES檢視的CHAIN_CNT列一樣可以順利的發現哪些表出現了行連結和行遷移，這裡不贅述。

Good luck.

secooler
10.01.05

-- The End --

【概念】行連結和行遷移的概念、模擬及甄別

相關文章