Oracle 行遷移 & 行連結的檢測與消除

什麼是行遷移 & 行連結?

#     以下描述來自Oracle 11gR2 Document

1.       行連結

The row is too large to fit into one data block when it is first inserted.

In row chaining, Oracle Database stores the data for the row in a chain of one or more data blocks reserved for the segment. Row chaining most often occurs with large rows. Examples include rows that contain a column of data type LONG or LONG RAW, a VARCHAR2(4000) column in a 2 KB block, or a row with a huge number of columns. Row chaining in these cases is unavoidable.


2.       行遷移
A row that originally fit into one data block is updated so that the overall row length increases, but insufficient free space exists to hold the updated row.

In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.

本文的主題是消除行遷移,既然如此,那就必須先類比出行遷移來:

--建立chained_rows表SQL> @$ORACLE_HOME/rdbms/admin/utlchain.sqlTable created.SQL>--建立測試表EMPLOYEESSQL> CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR.EMPLOYEES;Table created.SQL> COMMIT;Commit complete.SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知當前測試表EMPLOYEES上不存在行遷移SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>--更新測試表EMPLOYEES結構SQL> alter table employees modify FIRST_NAME varchar2(2000);Table altered.SQL> alter table employees modify LAST_NAME varchar2(2000);Table altered.SQL> alter table employees modify EMAIL varchar2(2000);Table altered.SQL> alter table employees modify PHONE_NUMBER varchar2(2000);Table altered.SQL>--更新測試表EMPLOYEESSQL> update employees set FIRST_NAME=LPAD('1',2000,'*'),LAST_NAME=LPAD('1',2000,'*'),EMAIL=LPAD('1',2000,'*'),PHONE_NUMBER=LPAD('1',2000,'*');107 rows updated.SQL> commit;Commit complete.SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知已經產生行遷移SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------       106SQL>--行遷移類比成功,我把這個過程儲存為一個指令碼reset_employees.sql:CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR.EMPLOYEES;alter table employees modify FIRST_NAME varchar2(2000);alter table employees modify LAST_NAME varchar2(2000);alter table employees modify EMAIL varchar2(2000);alter table employees modify PHONE_NUMBER varchar2(2000);update employees set FIRST_NAME=LPAD('1',2000,'*'),LAST_NAME=LPAD('1',2000,'*'),EMAIL=LPAD('1',2000,'*'),PHONE_NUMBER=LPAD('1',2000,'*');--之後類比行遷移直接執行這個指令碼就OK了.

以上就產生了行遷移類比指令碼

第一種消除行遷移的方式:

--準備指令碼Solution1.sqlCREATE TABLE employees_tmp tablespace test16k AS SELECT * FROM employees WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'EMPLOYEES');DELETE FROM employees WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'EMPLOYEES');INSERT INTO EMPLOYEES SELECT * FROM EMPLOYEES_TMP;DROP TABLE EMPLOYEES_TMP;DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'EMPLOYEES';COMMIT;--執行指令碼Solution1.sqlSQL> @Solution1.sqlTable created.106 rows deleted.106 rows created.Table dropped.106 rows deleted.Commit complete.SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知行遷移已經消除SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>

第二種消除行遷移的方式:

--清理測試環境SQL> truncate table chained_rows;Table truncated.SQL> drop table employees;Table dropped.SQL> commit;Commit complete.SQL>--以上清理過程也儲存為指令碼clear_employees.sql:truncate table chained_rows;drop table employees;commit;--之後的清理工作都會使用這個指令碼來進行--類比行遷移SQL> @reset_employees.sqlTable created.Table altered.Table altered.Table altered.Table altered.107 rows updated.SQL> commit;Commit complete.SQL>--準備指令碼Solution2.sqlcreate table employees_tmp tablespace test16k as select * from employees;truncate table employees;insert into employees select * from employees_tmp;drop table employees_tmp;commit;--執行指令碼Solution2.sqlSQL> @Solution2.sqlTable created.Table truncated.107 rows created.Table dropped.Commit complete.SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知行遷移已經消除SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>

第三種消除行遷移的方式:

--清理測試環境SQL> @clear_employees.sqlTable truncated.Table dropped.Commit complete.SQL>--類比行遷移SQL> @reset_employees.sqlTable created.Table altered.Table altered.Table altered.Table altered.107 rows updated.SQL> commit;Commit complete.SQL>--EXP匯出測試表EMPLOYEES[oracle@Server ~]$ exp SCOTT tables=employees file=scott_employees.dmpExport: Release 11.2.0.3.0 - Production on Wed Aug 14 20:03:05 2013Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport done in ZHS16GBK character set and AL16UTF16 NCHAR character setAbout to export specified tables via Conventional Path .... . exporting table                      EMPLOYEES        107 rows exportedExport terminated successfully without warnings.[oracle@Server ~]$--刪除測試表EMPLOYEESSQL> drop table employees purge;Table dropped.SQL> commit;Commit complete.SQL>--IMP匯入測試表EMPLOYEES[oracle@Server ~]$ imp SCOTT tables=employees file=scott_employees.dmpImport: Release 11.2.0.3.0 - Production on Wed Aug 14 20:05:25 2013Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.Password:Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsExport file created by EXPORT:V11.02.00 via conventional pathimport done in ZHS16GBK character set and AL16UTF16 NCHAR character set. importing SCOTT's objects into SCOTT. importing SCOTT's objects into SCOTT. . importing table                    "EMPLOYEES"        107 rows importedImport terminated successfully without warnings.[oracle@Server ~]$--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知行遷移已經消除SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>

第四種消除行遷移的方式

--清理測試環境SQL> @clear_employees.sqlTable truncated.Table dropped.Commit complete.SQL>--類比行遷移SQL> @reset_employees.sqlTable created.Table altered.Table altered.Table altered.Table altered.107 rows updated.SQL> commit;Commit complete.SQL>--確認表上沒有索引SQL> select table_name,index_name from user_indexes where table_name='EMPLOYEES';no rows selectedSQL>                                                                                                                     --把測試表EMPLOYEES遷移到資料表空間TBS_16SQL> alter table employees move tablespace tbs_16;Table altered.SQL> commit;Commit complete.SQL>--Moving a table changes the rowids of the rows in the table.--This causes indexes on the table to be marked UNUSABLE, and DML accessing the table using these indexes will receive an ORA-01502 error.--The indexes on the table must be dropped or rebuilt.--Likewise, any statistics for the table become invalid and new statistics should be collected after moving the table.--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知行遷移已經消除SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>

關於行連結:

#      You can eliminate chained rows only by increasing your data block size. #      It might not be possible to avoid chaining in all situations.

#      Chaining is often unavoidable with tables that have a LONG column or large CHAR or VARCHAR2 columns.

#      除非你有足夠大的資料區塊容納下記錄,否則,行連結是不可避免的.

      來看一個樣本:

--測試表EMPLOYEES當前在Block_Size=16K的資料表空間裡SQL> select a.table_name,a.tablespace_name,b.block_size from user_tables a join dba_tablespaces b on a.tablespace_name=b.tablespace_name where a.table_name='EMPLOYEES';TABLE_NAME                     TABLESPACE_NAME                BLOCK_SIZE------------------------------ ------------------------------ ----------EMPLOYEES                      TEST16K                             16384SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知當前測試表EMPLOYEES上沒有行連結SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>--把測試表EMPLOYEES移動到Block_Size=8K的資料表空間裡SQL> select tablespace_name,block_size from dba_tablespaces where tablespace_name='USERS';TABLESPACE_NAME                BLOCK_SIZE------------------------------ ----------USERS                                8192SQL> alter table employees move tablespace users;Table altered.SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知產生了行連結SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------       107SQL>--把測試表EMPLOYEES遷回TEST16K資料表空間SQL> alter table employees move tablespace test16k;Table altered.SQL>                                 --清空Chained_rows表                                                                                                                         SQL> delete from chained_rows where table_name='EMPLOYEES';107 rows deleted.SQL>--分析測試表EMPLOYEESSQL> analyze table employees list chained rows into chained_rows;Table analyzed.SQL>--查詢可知行連結已經被清除SQL> select count(*) from chained_rows where table_name='EMPLOYEES';  COUNT(*)----------         0SQL>

THE END

本文出自 “Xin23的流水賬” 部落格，謝絕轉載！