行迁移

行迁移试验准备

1.建表
drop table r_staff_temp purge;
create table r_staff_temp as select * from r_staff;
create index idx_staff_id r_staff_temp(staff_id);
2.扩大字段
alter table r_staff_temp modify name varchar2(1000);
alter table r_staff_temp modify sex varchar2(1000);
alter table r_staff_temp modify addr varchar2(1000);
alter table r_staff_temp modify email varchar2(1000);
alter table r_staff_temp modify idno varchar2(1000);
3.更新表
update r_staff_temp set name = lpad('1',1000,'*'), sex = lpad('1',1000,'*'), addr = lpad('1', 1000, '*'), email = lpad('1',1000,'*') idno = lpad('1', 1000, '*');
commit;
4.行迁移优化前，看下语句逻辑读(=当前读+一致性读)情况
set autotrace traceonly statistics
set linesize 1000
select * from r_staff_temp;

发现及解决
a.建chained_rows相关表（必须）
在plsql命令窗口执行 @?/rdbms/admin/utlchain.sql
analyze table r_staff_temp list chained rows into chained_rows;
analyze table r_staff_temp compute statistics;
select count(*) from chained_rows where table_name = 'R_STAFF_TEMP';--此结果即为发生行迁移的数量

b.禁用其他表关联到此表上的所有限制
select index_name, index_type, table_name from user_indexes where table_name = 'R_STAFF_TEMP';
select constraint_name, constraint_type, table_name from user_constraints where r_constraint_name = 'PK_STAFF_TEMP_ID';
alter table r_staff disable constraints FK_STAFF_TEMP_ID;

c.分析r_staff_temp表
anylyze table r_staff_temp list chained rows into chained_rows;
analyze table r_staff_temp compute statistics;
select count(*) from chained_rows where table_name = 'R_STAFF_TEMP';

d.操作
drop table r_staff_temp_bak;
create table r_staff_temp_bak as select * from r_staff_temp where rowid in (select head_rowid from chained_rows);
delete from r_staff_temp where rowid in (select head_rowid from chained_rows);
insert into r_staff_temp select * from r_staff_temp_bak;
delete from chained_rows;
commit;

e.验证
analyze table r_staff_temp list chained rows into chained_rows;
select count(*) from chained_rows where table_name = 'R_STAFF_TEMP';

 参考至：http://czmmiao.iteye.com/blog/2185542