本文主要是介绍Row Chaining and Migrating--行连接，希望对大家解决编程问题提供一定的参考价值，需要的开发者们随着小编来一起学习吧！
文章转载地址：http://woodbow.net/283.html
表里的一行对于一个数据块太大的情况有二种(一行在一个数据块里放不下)
第一种情况:
INSERT的时候，INSERT时候行的大小就超一个块的大小。Oracle把这行的数据存储在一连串的数据块里(Oracle Stores the data for the row in a chain of data blocks)，这种情况称为行链接(Row Chain)，一般不可避免(除非使用更大的数据块)。
第二种情况:
UPDATE的时候，UPDATE之后导致行的大小超过一个块的大小，Oracle将迁移这行数据(整行)到一个新的数据块(假设更新的行在新的数据块下放得下，否则这里发生一次行链接)，在行迁移之前的地方有一个指向迁移后数据块的指针，行的rowid不会改变。
-- 行链接 |
SQL> create table t01 |
2 ( |
3 col01 varchar2(4000), |
4 col02 varchar2(4000), |
5 col03 varchar2(2000) |
6 ) |
7 / |
Table created. |
SQL> insert into t01 values(lpad('*',4000,'*'),lpad('*',4000,'*'),lpad('*',2000,'*')); |
1 row created. |
SQL> commit; |
Commit complete. |
SQL> select rowid from t01; |
ROWID |
------------------ |
AAASpTAAEAAAALdAAA |
SQL> select * from t01 where rowid='AAASpTAAEAAAALdAAA'; |
Execution Plan |
---------------------------------------------------------- |
Plan hash value: 2326159110 |
----------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
----------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 1 | 5018 | 1 (0)| 00:00:01 | |
| 1 | TABLE ACCESS BY USER ROWID| T01 | 1 | 5018 | 1 (0)| 00:00:01 | |
----------------------------------------------------------------------------------- |
Statistics |
---------------------------------------------------------- |
0 recursive calls |
0 db block gets |
2 consistent gets |
0 physical reads |
-- 行迁移 |
SQL> show parameter block_size |
NAME TYPE VALUE |
------------------------------------ ----------- ------------------------------ |
db_block_size integer 8192 |
SQL> create table t01 |
2 ( |
3 col01 varchar2(4000), |
4 col02 varchar2(4000), |
5 col03 varchar2(2000) |
6 ) |
7 / |
Table created. |
SQL> insert into t01 |
2 values(lpad('*',3000,'*'),lpad('*',3000,'*'),lpad('*',1000,'*')); |
1 row created. |
SQL> insert into t01 |
2 values('a','a','a'); |
1 row created. |
SQL> commit; |
Commit complete. |
SQL> select rowid from t01 where col01='a'; |
ROWID |
------------------ |
AAASpWAAEAAAALrAAB |
SQL> select * from t01 where rowid='AAASpWAAEAAAALrAAB'; |
Execution Plan |
---------------------------------------------------------- |
Plan hash value: 2326159110 |
----------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
----------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 1 | 5018 | 1 (0)| 00:00:01 | |
| 1 | TABLE ACCESS BY USER ROWID| T01 | 1 | 5018 | 1 (0)| 00:00:01 | |
----------------------------------------------------------------------------------- |
Statistics |
---------------------------------------------------------- |
0 recursive calls |
0 db block gets |
1 consistent gets |
0 physical reads |
SQL> update t01 |
2 set col01 = lpad(col01, 3000, 'a'), |
3 col02 = lpad(col02, 3000, 'a'), |
4 col03 = lpad(col03, 1000, 'a') |
5 where col01 = 'a'; |
1 row updated. |
SQL> commit; |
Commit complete. |
SQL> select rowid from t01 where col01 like 'a%'; |
ROWID |
------------------ |
AAASpWAAEAAAALrAAB |
SQL> select rowid from t01 where col01 like 'a%'; |
ROWID |
------------------ |
AAASpWAAEAAAALrAAB |
SQL> set autot trace |
SQL> select * from t01 where rowid='AAASpWAAEAAAALrAAB'; |
Execution Plan |
---------------------------------------------------------- |
Plan hash value: 2326159110 |
----------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
----------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 1 | 5018 | 1 (0)| 00:00:01 | |
| 1 | TABLE ACCESS BY USER ROWID| T01 | 1 | 5018 | 1 (0)| 00:00:01 | |
----------------------------------------------------------------------------------- |
Statistics |
---------------------------------------------------------- |
0 recursive calls |
0 db block gets |
2 consistent gets |
0 physical reads |
-- 检查行迁移的记录 |
SQL> truncate table chained_rows; |
Table truncated. |
SQL> analyze table t01 list chained rows into chained_rows; |
Table analyzed. |
SQL> exec print_table('select * from chained_rows') |
OWNER_NAME : YAO |
TABLE_NAME : T01 |
CLUSTER_NAME : |
PARTITION_NAME : |
SUBPARTITION_NAME : N/A |
HEAD_ROWID : AAASpWAAEAAAALrAAB |
ANALYZE_TIMESTAMP : 2012-12-24 11:31:51 |
----------------- |
PL/SQL procedure successfully completed. |
-- 消除行迁移 |
SQL> alter table t01 move; |
Table altered. |
SQL> select rowid from t01 where col01 like 'a%'; |
ROWID |
------------------ |
AAASpZAAEAAAAL8AAA |
SQL> set autot trace |
SQL> select * from t01 where rowid='AAASpZAAEAAAAL8AAA'; |
Execution Plan |
---------------------------------------------------------- |
Plan hash value: 2326159110 |
----------------------------------------------------------------------------------- |
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | |
----------------------------------------------------------------------------------- |
| 0 | SELECT STATEMENT | | 1 | 5018 | 1 (0)| 00:00:01 | |
| 1 | TABLE ACCESS BY USER ROWID| T01 | 1 | 5018 | 1 (0)| 00:00:01 | |
----------------------------------------------------------------------------------- |
Statistics |
---------------------------------------------------------- |
0 recursive calls |
0 db block gets |
1 consistent gets |
0 physical reads |
这篇关于Row Chaining and Migrating--行连接的文章就介绍到这儿，希望我们推荐的文章对编程师们有所帮助！
