【概念】行連結和行遷移的概念、模擬及甄別
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 --
有關行連結和行遷移的概念請見下面的官方文件中的描述。
參考連結:
引於此處:
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 --
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/519536/viewspace-624408/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- 模擬Oracle行遷移和行連結Oracle
- Oracle 行遷移 & 行連結的檢測與消除Oracle
- [20190120]行連結遷移與dml.txt
- [20180402]行連結行遷移與ITL槽6.txt
- Oracle資料庫中資料行遷移與行連結Oracle資料庫
- 程式、執行緒和協程的概念執行緒
- 連結串列的概念、實踐和麵試題
- 深入瞭解程序和執行緒:概念、區別和最佳化執行緒
- 堆和棧的概念和區別
- Linux中程式與執行緒的概念以及區別Linux執行緒
- 執行緒的基本概念執行緒
- .NET程式執行原理及基本概念詳解
- Python類、模組、包的概念及區別Python
- JavaScript單執行緒概念JavaScript執行緒
- 執行緒基本概念執行緒
- 執行緒概念淺談執行緒
- 執行緒池的基本概念執行緒
- Java虛擬機器執行機制與相關概念Java虛擬機
- C# 程式集、模組和型別概念及關係C#型別
- 虛擬機器的概念虛擬機
- 多執行緒基本概念執行緒
- 使用Mobilenet和Keras進行遷移學習!Keras遷移學習
- 遷移執行緒migration執行緒
- CSS中 screenX clientX pageX 的概念和區別CSSclient
- Dart的混入概念和Vue3的混入概念DartVue
- 多執行緒(一)、基礎概念及notify()和wait()的使用執行緒AI
- Nestjs模組機制的概念和實現原理JS
- 多元函式的極限與連續 概念總結函式
- “多執行緒”重點概念整理執行緒
- Servlet基本概念及執行流程Servlet
- web前端高階webpack - 初識webpack 的安裝執行及核心概念Web前端
- 1.5 使用nvicat和kettle進行全量遷移
- 【java】【多執行緒】程式、執行緒的基本概念(1)Java執行緒
- 雲伺服器和CDN概念區別伺服器
- 『現學現忘』Docker相關概念 — 4、虛擬化概念Docker
- 【PHP資料結構】圖的概念和儲存結構PHP資料結構
- 探討Java中的多執行緒概念 - foojayJava執行緒
- 計算機網路概念和結構計算機網路
- iOS多執行緒詳解:概念篇iOS執行緒