ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 【概念】行链接和行迁移的概念、模拟及甄别

【概念】行链接和行迁移的概念、模拟及甄别

Linux操作系统 作者:dbSeeSee 时间:2016-06-28 23:17:11 0 删除 编辑
1.行链接和行迁移概念
有关行链接和行迁移的概念请见下面的官方文档中的描述。
参考链接:http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/logical.htm#sthref338
引于此处:
Row Chaining and Migrating

In two circumstances, the data for a row in a table may be too large to fit into a single data block. In the first case, the row is too large to fit into one data block when it is first inserted. In this case, Oracle stores the data for the row in a chain of data blocks (one or more) reserved for that segment. Row chaining most often occurs with large rows, such as rows that contain a column of datatype LONG or LONG RAW. Row chaining in these cases is unavoidable.

However, in the second case, a row that originally fit into one data block is updated so that the overall row length increases, and the block's free space is already completely filled. In this case, Oracle migrates the data for the entire row to a new data block, assuming the entire row can fit in a new block. Oracle preserves the original row piece of a migrated row to point to the new block containing the migrated row. The rowid of a migrated row does not change.

When a row is chained or migrated, I/O performance associated with this row decreases because Oracle must scan more than one data block to retrieve the information for the row.

也就是说,行链接(Row Chaining)发生在INSERT阶段数据块无法容纳过大数据时,而行迁移(Row Migrating)发生在UPDATE阶段时原数据块无法容纳增大的数据时。

2.模拟行链接
1)创建包含四个CHAR类型字段的表t_row_chaining
sec@ora10g> create table t_row_chaining (x char(2000), y char(2000), z char(2000), q char(2000));

Table created.

2)初始化一条数据
sec@ora10g> insert into t_row_chaining values('x','y','z','q');

1 row created.

sec@ora10g> commit;

Commit complete.

3)使用ANALYZE命令对表t_row_chaining进行分析,以便验证该表是否发生了行链接。
sec@ora10g> ANALYZE TABLE t_row_chaining LIST CHAINED ROWS;
ANALYZE TABLE t_row_chaining LIST CHAINED ROWS
*
ERROR at line 1:
ORA-01495: specified chain row table not found

如果出现上面的错误不要惊慌,原因是CHAINED_ROWS表没有创建导致的。
可以使用Oracle自带的utlchain.sql脚本创建CHAINED_ROWS表
sec@ora10g> @?/rdbms/admin/utlchain.sql

Table created.

再次尝试,成功。
sec@ora10g> ANALYZE TABLE t_row_chaining LIST CHAINED ROWS;

Table analyzed.

4)由于初始化的数据无法在一个数据块中存放,此时发生了行链接。
sec@ora10g> col OWNER_NAME for a10
sec@ora10g> col TABLE_NAME for a16
sec@ora10g> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME TABLE_NAME       HEAD_ROWID         ANALYZE_TIMESTAMP
---------- ---------------- ------------------ -------------------
SEC        T_ROW_CHAINING   AAATMLAAFAAAdqcAAA 2010-01-05 23:33:40

3.模拟行迁移
1)此时我们创建包含四个VARCHAR2类型的表t_row_migrating
sec@ora10g> create table t_row_migrating (x varchar2(2000), y varchar2(2000), z varchar2(2000), q varchar2(2000));

Table created.

2)初始化一条数据
sec@ora10g> insert into t_row_migrating values ('x','y','z','q');

1 row created.

sec@ora10g> commit;

Commit complete.

3)此时由于是VARCHAR2类型的字段,此时数据完全可以在一个数据块中存放,因此此时不会发生行链接。
sec@ora10g> ANALYZE TABLE t_row_migrating LIST CHAINED ROWS;

Table analyzed.

sec@ora10g> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME TABLE_NAME       HEAD_ROWID         ANALYZE_TIMESTAMP
---------- ---------------- ------------------ -------------------
SEC        T_ROW_CHAINING   AAATMLAAFAAAdqcAAA 2010-01-05 23:33:40

4)我们使用t_row_chaining中的大数据对表t_row_migrating进行更新,使之发生行迁移。
sec@ora10g> update t_row_migrating set (x,y,z,q)=(select * from t_row_chaining);

1 row updated.

sec@ora10g> commit;

Commit complete.

5)对表t_row_migrating进行验证
sec@ora10g> ANALYZE TABLE t_row_migrating LIST CHAINED ROWS;

Table analyzed.

6)此时表t_row_migrating已经赫然出现在chained_rows表的记录中。
sec@ora10g> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME TABLE_NAME       HEAD_ROWID         ANALYZE_TIMESTAMP
---------- ---------------- ------------------ -------------------
SEC        T_ROW_CHAINING   AAATMLAAFAAAdqcAAA 2010-01-05 23:33:40
SEC        T_ROW_MIGRATING  AAATMMAAFAAAdqoAAA 2010-01-05 23:35:24

4.小结
在判断表是否发生行链接和行迁移的方法不仅限于文中提到的方法,使用一般的表分析方法,结合ALL_TABLES视图的CHAIN_CNT列一样可以顺利的发现哪些表出现了行链接和行迁移,这里不赘述。

Good luck.

secooler
10.01.05

-- The End --

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/29023300/viewspace-2121150/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2013-07-04

  • 博文量
    28
  • 访问量
    96115