具体可参照:
http://docs.oracle.com/cd/E11882_01/server.112/e40540/logical.htm#CNCPT1053
首先看下产生行链接以及行迁移的原因。
-
The row is too large to fit into one data block when it is first inserted.
行太大,一个数据块装不下
-
In row migration, Oracle Database moves the entire row to a new data block, assuming the row can fit in a new block. The original row piece of a migrated row contains a pointer or "forwarding address" to the new block containing the migrated row. The rowid of a migrated row does not change.
开始行的资料单个数据块可以装下,但是更新后的资料过大,发生行链接
-
A row has more than 255 columns.
Oracle Database can only store 255 columns in a row piece. Thus, if you insert a row into a table that has 1000 columns, then the database creates 4 row pieces, typically chained over multiple blocks.
列大于255个。
再
看数据库中块的大小,可估算到一个块可存多少字节数据。
SQL> conn / as sysdba
Connected.
建立表格t1,其行资料略大于一个块8192,一个数据块装不下。
SQL> create table t1(c1 varchar2(4000),c2 varchar2(4000));
Table created.
SQL> insert into t1 values(lpad('Z',4000,'A'),lpad('Z',4000,'A'));
1 row created.
SQL> commit;
Commit complete.
SQL> @/u01/app/oracle/11.2.0/db_1/rdbms/admin/utlchain.sql
Table created.
分析表t1
SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T1';
COUNT(*)
----------
1
我们再建立一表t2,其块大小为32k,这样,一行资料一个块就能装下了
SQL> alter system set db_32k_cache_size=16M;
System altered.
SQL> create tablespace tbs_32 blocksize 32K datafile '/u01/app/oracle/oradata/orcl/tbs_32.dbf' size 100M;
Tablespace created.
SQL> truncate table chained_rows ;
Table truncated.
SQL> ANALYZE TABLE t2 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T2';
COUNT(*)
----------
0
行链接主要是由于数据库的
db_block_size
不够大,对于一些大的字段没法在一个
block
中存储下,从而产生了行链接。对于行链接我们除了增大
db_block_size
之外没有别的任何办法去避免
,
因此行链接的产生几乎是不可避免的,也没有太多可以调整的地方。行迁移则主要是由于更新表的时候,由于表的
pctfree
参数设置太小,导致
block
中没有足够的空间去容纳更新后的记录,从而产生了行迁移。
SQL> truncate table t1;
Table truncated.
SQL> insert into t1 values('x1','x2');
1 row created.
SQL> insert into t1 values(lpad('Z',2000,'A'),lpad('Z',2000,'A'));
1 row created.
SQL> commit;
Commit complete.
SQL> TRUNCATE TABLE chained_rows;
Table truncated.
SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T1';
COUNT(*)
----------
0
SQL> update t1 set c1=lpad('Z',3000,'A'),c2=lpad('Z',3000,'A') where c1='x1';
1 row updated.
SQL> commit;
Commit complete.
SQL> TRUNCATE TABLE chained_rows;
Table truncated.
SQL> ANALYZE TABLE t1 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T1';
COUNT(*)
----------
1
如何消除行迁移呢,其实方法很多比较常用的就是重新建立表
SQL> create table t3
2 as
3 select * from t1;
Table created.
SQL> ANALYZE TABLE t3 LIST CHAINED ROWS INTO chained_rows;
Table analyzed.
SQL> select count(*) from chained_rows where table_name='T3';
COUNT(*)
----------
0
再就是move,或者导入,导出都可。
也许有人会问,行链接和行迁移怎么验证时为什么都有
chained_rows中,没有分开区分,其实ORACLE对于这不是分开讲的,是一齐讲的。