Oracle行迁移&行链接的检测与消除

什么是行迁移行链接?#以下描述来自Oracle11gR2Document1.行链接Therowistoolargetofitintoonedatablockwhenitisfirstinserted.Inrowchaining,OracleDatabasestor

什么是行迁移 & 行链接?

# 以下描述来自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. Table altered. Table altered. 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>