什麼是行遷移 & 行連結?

# 以下描述來自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.sql

Table created.

SQL>

--建立測試表EMPLOYEES

SQL> CREATE TABLE EMPLOYEES TABLESPACE TEST16K PCTFREE 0 AS SELECT * FROM HR.EMPLOYEES;

Table created.

SQL> COMMIT;

Commit complete.

SQL>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知當前測試表EMPLOYEES上不存在行遷移

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

--更新測試表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>

--更新測試表EMPLOYEES

SQL> 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>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知已經產生行遷移

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

106

SQL>

--行遷移模擬成功,我把這個過程儲存為一個指令碼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.sql

CREATE 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.sql

SQL> @Solution1.sql

Table created.

106 rows deleted.

106 rows created.

Table dropped.

106 rows deleted.

Commit complete.

SQL>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知行遷移已經消除

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

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

--清理測試環境

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.sql

Table created.

Table altered.

107 rows updated.

SQL> commit;

Commit complete.

SQL>

--準備指令碼Solution2.sql

create 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.sql

SQL> @Solution2.sql

Table created.

Table truncated.

107 rows created.

Table dropped.

Commit complete.

SQL>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知行遷移已經消除

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

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

--清理測試環境

SQL> @clear_employees.sql

Table truncated.

Table dropped.

Commit complete.

SQL>

--模擬行遷移

SQL> @reset_employees.sql

Table created.

Table altered.

107 rows updated.

SQL> commit;

Commit complete.

SQL>

--EXP匯出測試表EMPLOYEES

[oracle@Server ~]$ exp SCOTT tables=employees file=scott_employees.dmp

Export: Release 11.2.0.3.0 - Production on Wed Aug 14 20:03:05 2013

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export done in ZHS16GBK character set and AL16UTF16 NCHAR character set

About to export specified tables via Conventional Path ...

. . exporting table EMPLOYEES 107 rows exported

Export terminated successfully without warnings.

[oracle@Server ~]$

--刪除測試表EMPLOYEES

SQL> drop table employees purge;

Table dropped.

SQL> commit;

Commit complete.

SQL>

--IMP匯入測試表EMPLOYEES

[oracle@Server ~]$ imp SCOTT tables=employees file=scott_employees.dmp

Import: Release 11.2.0.3.0 - Production on Wed Aug 14 20:05:25 2013

Password:

Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

With the Partitioning, OLAP, Data Mining and Real Application Testing options

Export file created by EXPORT:V11.02.00 via conventional path

import done in ZHS16GBK character set and AL16UTF16 NCHAR character set

. importing SCOTT's objects into SCOTT

. . importing table "EMPLOYEES" 107 rows imported

Import terminated successfully without warnings.

[oracle@Server ~]$

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知行遷移已經消除

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

第四種消除行遷移的方式

--清理測試環境

SQL> @clear_employees.sql

Table truncated.

Table dropped.

Commit complete.

SQL>

--模擬行遷移

SQL> @reset_employees.sql

Table created.

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 selected

SQL>

--把測試表EMPLOYEES遷移到表空間TBS_16

SQL> 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.

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知行遷移已經消除

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

關於行連結:

# 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 16384

SQL>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知當前測試表EMPLOYEES上沒有行連結

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

--把測試表EMPLOYEES移動到Block_Size=8K的表空間裡

SQL> select tablespace_name,block_size from dba_tablespaces where tablespace_name='USERS';

TABLESPACE_NAME BLOCK_SIZE

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

USERS 8192

SQL> alter table employees move tablespace users;

Table altered.

SQL>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知產生了行連結

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

107

SQL>

--把測試表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>

--分析測試表EMPLOYEES

SQL> analyze table employees list chained rows into chained_rows;

Table analyzed.

SQL>

--查詢可知行連結已經被清除

SQL> select count(*) from chained_rows where table_name='EMPLOYEES';

COUNT(*)

----------

0

SQL>

THE END

Oracle行連結行遷移Oracle

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

相關文章