oracle查询行迁移是否打开,Oracle行迁移&行链接的检测与消除

最新推荐文章于 2024-05-31 00:33:58 发布

weixin_39554775

最新推荐文章于 2024-05-31 00:33:58 发布

阅读量799

点赞数

文章标签： 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(*)

----------

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.