行链接:当执行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/