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不会改变。

view source

-- 行链接

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