行链接和行迁移案例
两则之间的区别：
行链接是指一个行存储在多个块中的情况，因为一个该行的长度超过了一个块的可用空间大小。（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 无法存放，发生行迁移
