什么是行迁移 & 行链接?
# 以下描述来自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>
第三种消除行迁移的方式:
--清理测试环境
SQL> @clear_employees.sql
Table truncated.
Table dropped.
Commit complete.
SQL>
--模拟行迁移
SQL> @reset_employees.sql
Table 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.dmp
Export: Release 11.2.0.3.0 - Production on Wed Aug 14 20:03:05 2013
Copyright (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 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
Copyright (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 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 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.
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 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
1万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
