行链接和行迁移案例

两则之间的区别：

             行链接是指一个行存储在多个块中的情况，因为一个该行的长度超过了一个块的可用空间大小。（Insert）

             行迁移是指一个数据行不适合放入当前块而被重新定位到另一个块，但在原始块中保留一个指针，原始块中的指针是必需的，因为索引的rowid项仍然指向原始位置.（update）         

            行链接通常与行的长度和oracle数据库块中的大小有关，而行迁移通常是当一个更新操作的长度增加且又要保持该行在同一块中，而该块又缺少可用空间时产生的问题，oracle在决定行连接之前先试图进行行迁移。

行连接

1、建立表

04:50:36 SQL> show user;
USER is "SCOTT"
04:50:39 SQL>  create table t_row_chaining (x char(2000), y char(2000), z char(2000), q char(2000));

Table created.

04:50:51 SQL> insert into t_row_chaining values('x','y','z','q');

1 row created.

04:51:05 SQL> commit;

Commit complete.

2、执行脚本，建立行链接和行迁移视图

04:51:10 SQL> @?/rdbms/admin/utlchain.sql

Table created.

04:51:32 SQL> select * from tab;

TNAME                          TABTYPE  CLUSTERID

------------------------------ ------- ----------
DEPT                           TABLE
EMP                            TABLE
BONUS                          TABLE
SALGRADE                       TABLE
EMP1                           TABLE
EMP5                           TABLE
SALES                          TABLE
SYS_IOT_OVER_12021             TABLE
SALES_INFO                     TABLE
DEPT_EMP_CLU                   CLUSTER
DEPARTMENT                     TABLE            1
EMPLOYEE                       TABLE            2
TMP1                           TABLE
TMP2                           TABLE
T_ROW_CHAINING                 TABLE
CHAINED_ROWS                   TABLE

16 rows selected.

04:51:40 SQL> desc CHAINED_ROWS

 Name                                                                                Null?    Type
 ----------------------------------------------------------------------------------- -------- --------------------------------------------------------
 OWNER_NAME                                                                                   VARCHAR2(30)
 TABLE_NAME                                                                                   VARCHAR2(30)
 CLUSTER_NAME                                                                                 VARCHAR2(30)
 PARTITION_NAME                                                                               VARCHAR2(30)
 SUBPARTITION_NAME                                                                            VARCHAR2(30)
 HEAD_ROWID                                                                                   ROWID
 ANALYZE_TIMESTAMP  
 
 3、分析表的行链接信息                                                                         DATE

04:51:50 SQL> ANALYZE TABLE t_row_chaining LIST CHAINED ROWS;

Table analyzed.

04:52:09 SQL>

04:52:09 SQL> select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME                     TABLE_NAME                     HEAD_ROWID         ANALYZE_T

------------------------------ ------------------------------ ------------------ ---------
SCOTT                          T_ROW_CHAINING                 AAAC9KAAEAAAAB3AAA 25-MAR-12

-------由于插入的记录长度超过了block的free space ，发生行链接

行迁移

1、建立表

04:53:06 SQL>  create table t_row_migrating (x varchar2(2000), y varchar2(2000), z varchar2(2000), q varchar2(2000));

Table created.

04:54:01 SQL> insert into t_row_migrating values ('x','y','z','q');

1 row created.

04:54:12 SQL> commit;

Commit complete.

04:54:20 SQL> delete chained_rows;

1 row deleted.

04:54:42 SQL> ANALYZE TABLE t_row_migrating LIST CHAINED ROWS;

Table analyzed.

04:54:54 SQL>  select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

no rows selected

2、对表做update 实验

04:55:03 SQL>  update t_row_migrating set (x,y,z,q)=(select * from t_row_chaining);

1 row updated.

04:57:17 SQL> commit;

Commit complete.

04:57:33 SQL> ANALYZE TABLE t_row_migrating LIST CHAINED ROWS;

Table analyzed.

04:57:41 SQL>  select OWNER_NAME,TABLE_NAME,HEAD_ROWID,ANALYZE_TIMESTAMP from chained_rows;

OWNER_NAME                     TABLE_NAME                     HEAD_ROWID         ANALYZE_T

------------------------------ ------------------------------ ------------------ ---------
SCOTT                          T_ROW_MIGRATING                AAAC9MAAEAAAACGAAA 25-MAR-12

04:57:53 SQL>

no rows selected

04:55:03 SQL>

----------更新后的记录变长，在block free space 无法存放，发生行迁移