行迁移，行链接

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE 行迁移: 当执行 update 导致记录行长增加， block 的剩余空间不足以存放这条记录，就会产生行迁移，发生行迁移时 rowid 不会改变，原来的 block 中会用一个指针存放这条记录在新的 block 中的地址，发生行迁移会对性能产生影响，因为读这条记录会读两个 BLOCK 。

 

行链接:当执行insert导致一个BLOCK不足以存放下一条记录的时候，就会发生行链接，这个时候oracle会把这条记录分成几个部分，分别存放在几个block中，然后把这几个block chain起来。行连接同样会影响性能，因为读一条记录至少会读两个BLOCK.

Normal 0 7.8 pt 0 2 false false false EN-US ZH-CN X-NONE实验目的：理解行迁移，行链接，并掌握如何消除行迁移。

实验结果：通过设置pctfree，模拟测试表产生行迁移，再消除之。

实验参考：P13-4 of “Administrator’s Guide”

范例：

1. 模拟产生行迁移

         1) execute utlchain.sql or utlchn1.sql to create chained_rows table

         SQL>@?/rdbms/admin/utlchain;

Table created.

         2) create a test table specifying pctfree=0;

         SQL>create table order_hist(

                   x number,

                   y varchar2(100)) pctfree 0;

Table created.

         3) insert 3000 rows to the test table

         SQL> begin

       2     for i in 1..3000 loop

        3             insert into order_hist values(i,'jay');

4     end loop;

5     commit;

6  end;

7  /

PL/SQL procedure successfully completed.

         4) update data to produce migrated rows.

         SQL> update order_hist set y='jayjayjayjayjayjayjay';

3000 rows updated.

         5) collect information about migrated and chained rows

         SQL> analyze table order_hist list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows

         2     where table_name='ORDER_HIST';

         COUNT(*)

----------

      2660

2. 消除行迁移

         1) create an intermediate table

         SQL> create table int_order_hist as select * from order_hist where rowid in

         2     (select head_rowid from chained_rows where table_name='ORDER_HIST');

Table created.

         2) delete the migrated and chained rows.

         SQL> delete from order_hist where rowid in

       2     (select head_rowid from chained_rows where table_name='ORDER_HIST');

2660 rows deleted.

         3) insert the rows of intermediate table into the existing table.

         SQL> insert into order_hist select * from int_order_hist;

2660 rows created.

         4) drop the intermediate table

         SQL> drop table int_order_hist;

Table dropped.

         5) delete the information collected before

         SQL> delete from chained_rows where table_name='ORDER_HIST';

2660 rows deleted.

         6) Use the analyze statement again, and query the output table

         SQL> analyze table order_hist list chained rows;

Table analyzed.

SQL> select count(*) from chained_rows;

         COUNT(*)

----------

         0

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

转载于:http://blog.itpub.net/24068527/viewspace-666786/