消除行迁移 table fetch continued row
1、搭建实验环境
1.1、创建新表，同时把表的pctfree设置为0
SCOTT@ prod>create table emp3 as select * from emp where 1=2;
SCOTT@ prod>alter table emp3 modify empno number(10);
SCOTT@ prod>alter table emp3 modify ENAME varchar2(30);
SCOTT@ prod>alter table emp3 pctfree 0;
1.2、创建序列
CREATE SEQUENCE emp3_empno
INCREMENT BY 1
START WITH 1
MAXVALUE 100000000
CACHE 10000
NOCYCLE;
1.3、插入数据
SCOTT@ prod>begin
2 for i in 1..20000000 loop
3 insert into emp3
4 values (emp3_empno.nextval,'lvxinghao','SALESMAN',7698,sysdate,1600,300,30);
5 if mod(i,1000)=0 then
6 commit;
7 end if;
8 end loop;
9 commit;
10 end;
11 /
1.4、修改表emp3，使其产生行迁移
undo表空间开启自动扩展。
SCOTT@ prod>update emp3 set ename='LVXINGHAO_BEIJING';
20000000 rows updated.
Elapsed: 00:21:55.54
SCOTT@ prod>commit;
1.5、然后分析表，查看行迁移的数量
SCOTT@ prod>analyze table emp3 estimate statistics;
SCOTT@ prod>select chain_cnt from user_tables where table_name='EMP3';
CHAIN_CNT
----------
4702154
2、解决方案：消除行迁移
2.1、查看EMP3表的pct_free
SYS@ prod>select owner,table_name,tablespace_name,pct_free
from dba_tables where owner='SCOTT' and table_name='EMP3';
OWNER TABLE_NAME TABLESPACE_NAME PCT_FREE
---------- ---------- -------------------- ----------
SCOTT EMP3 TEST3 0
SYS@ prod>alter table scott.emp3 pctfree 10;
Table altered.
SYS@ prod>select owner,table_name,tablespace_name,pct_free
from dba_tables where owner='SCOTT' and table_name='EMP3';
OWNER TABLE_NAME TABLESPACE_NAME PCT_FREE
---------- ---------- -------------------- ----------
SCOTT EMP3 TEST3 10
2.2、生成chained_rows表
SCOTT@ prod>@?/rdbms/admin/utlchain.sql
SCOTT@ prod>analyze table emp3 list chained rows into chained_rows;
Table analyzed.
Elapsed: 00:04:15.55
2.3、建立新表存放行迁移的数据
SCOTT@ prod>create table emp3_tmp as select * from emp3
2 where rowid in (select HEAD_ROWID from chained_rows);
Table created.
Elapsed: 00:01:14.16
2.4、删除原表中发生行迁移的行
SCOTT@ prod>delete emp3 where rowid in (select HEAD_ROWID from chained_rows);
3772857 rows deleted.
Elapsed: 00:12:04.48
2.5、重新插入这些数据
SCOTT@ prod>insert into emp3 select * from emp3_tmp;
3772857 rows created.
Elapsed: 00:01:51.93
2.6、分析表、查看行迁移的数量
SCOTT@ prod>analyze table emp3 estimate statistics;
Table analyzed.
Elapsed: 00:00:07.60
SCOTT@ prod>select chain_cnt from user_tables where table_name='EMP3';
CHAIN_CNT
----------
0
Elapsed: 00:00:00.02
SCOTT@ prod>
【检查行迁移的方法】
1） 运行$ORACLE_HOME/rdbms/admin/utlchain.sql
2） analyze table table_name list chained rows into CHAINED_ROWS
3） select * from CHAINED_ROWS where table_name='table_name';
【消除行迁移的方法】
方法1：
create table table_name_tmp as select * from table_name where rowed in (select head_rowid from chained_rows);
Delete from table_name where rowed in (select head_rowid from chained_rows);
Insert into table_name select * from table_name_tmp;
方法2：
create table table_name_tmp as select * from table_name ;
truncate table table_name
insert into table_name select * from table_name_tmp
方法3：
用exp工具导出表，然后删除这个表，最后用imp工具导入这表
方法4：
alter table table_name move tablespace tablespace_name，然后再重新创建表的索引
上面的4种方法可以用以消除已经存在的行迁移现象，但是行迁移的产生很多情况下时由于PCT_FREE参数设置的太小所导致，所以需要调整PCT_FREE参数的值。
吕星昊
2014.9.10