1、创建基表lvzz.t2，表的行数为5525120，容量623MB
create table lvzz.t2 as select * from dba_objects where 1=2;
insert into lvzz.t2 select * from dba_objects;
commit;
insert into lvzz.t2 select * from lvzz.t2; --多次执行
commit;
select count(*) from lvzz.t2; --5525120
select bytes/1024/1024 from dba_segments where segment_name='T2'; --623
2、目标表无索引
create table lvzz.t3 as select * from lvzz.t2 where 1=2;
----执行插入操作的前后分别记录当前会话的redo量，差值即为redo的生成量
----redo size：Total amount of redo generated in bytes
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
insert into lvzz.t3 select * from lvzz.t2;
commit;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='T3';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92943 and dataobj#=92943;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T3';
测试结果：insert操作耗时 67秒，生成的redo数据量是 615MB，表的db_block_changes_delta 数量是 430752，表的大小为 624MB。
3、目标表有一个索引列，无排序插入
create table lvzz.t4 as select * from lvzz.t2 where 1=2;
create index lvzz.idx_t4_object_name on lvzz.t4(object_name);
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
insert into lvzz.t4 select * from lvzz.t2;
commit;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='T4';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92944 and dataobj#=92944;
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='IDX_T4_OBJECT_NAME';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92945 and dataobj#=92945;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T4';
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='IDX_T4_OBJECT_NAME';
测试结果：insert操作耗时约10分钟，生成的redo数据量是 2313MB，
表的db_block_changes_delta 数量是 428320，表的大小为 624MB，
索引的db_block_changes_delta 数量是 3940064，索引的大小为 296MB。
4、目标表有一个索引列，有排序插入
create table lvzz.t5 as select * from lvzz.t2 where 1=2;
create index lvzz.idx_t5_object_name on lvzz.t5(object_name);
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
insert into lvzz.t5 select * from lvzz.t2 order by object_name;
commit;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='T5';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92946 and dataobj#=92946;
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='IDX_T5_OBJECT_NAME';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92947 and dataobj#=92947;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T5';
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='IDX_T5_OBJECT_NAME';
测试结果：insert操作耗时约3分钟，生成的redo数据量是 1459MB，
表的db_block_changes_delta 数量是 428384，表的大小为 624MB，
索引的db_block_changes_delta 数量是 371424，索引的大小为 215MB。
5、目标表有一个索引列，无排序插入，插入前unsable索引，完成后再重建索引
create table lvzz.t6 as select * from lvzz.t2 where 1=2;
create index lvzz.idx_t6_object_name on lvzz.t6(object_name);
alter index lvzz.idx_t6_object_name unusable;
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
insert into lvzz.t6 select * from lvzz.t2;
commit;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='T6';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92948 and dataobj#=92948;
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='IDX_T6_OBJECT_NAME';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92949;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T6';
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='IDX_T6_OBJECT_NAME';
----重建索引
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
alter index lvzz.idx_t6_object_name rebuild;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92948 and dataobj#=92948;
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='IDX_T6_OBJECT_NAME';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92949 and dataobj#=92950;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T6';
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='IDX_T6_OBJECT_NAME';
测试结果：insert操作耗时80秒，生成的redo数据量是 614MB，
表的db_block_changes_delta 数量是 428320，表的大小为 624MB，
索引的db_block_changes_delta 数量是 0，索引的大小为 0（未分配索引段）
重建索引的情况：重建索引耗时77秒，生成的redo数据量是 224MB，
表的db_block_changes_delta 数量是 70688，
索引的db_block_changes_delta 数量是 1328，索引的大小为 224MB
总结：通过插入前unusable索引，插入完成后重建索引的方式，redo的生成量是614+224=838MB，重建索引时，索引的 db_block_changes_delta 数量只有1328，但重建索引时，数据表的 db_block_changes_delta 也会增加。
6、目标表有一个索引列，有排序插入，插入前unsable索引，完成后再重建索引
create table lvzz.t7 as select * from lvzz.t2 where 1=2;
create index lvzz.idx_t7_object_name on lvzz.t7(object_name);
alter index lvzz.idx_t7_object_name unusable;
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
insert into lvzz.t7 select * from lvzz.t2 order by object_name;
commit;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='T7';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92951 and dataobj#=92951;
select object_id,data_object_id from dba_objects where owner='LVZZ' and object_name='IDX_T7_OBJECT_NAME';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92952;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T7';
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='IDX_T7_OBJECT_NAME';
----重建索引
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
alter index lvzz.idx_t7_object_name rebuild;
exec DBMS_WORKLOAD_REPOSITORY.CREATE_SNAPSHOT();
select value from v$mystat my,v$statname st where my.statistic#=st.statistic# and st.name='redo size';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92951 and dataobj#=92951;
select object_id,data_object_id from dba_objects where owner='LVZZ'
and object_name='IDX_T7_OBJECT_NAME';
select db_block_changes_delta from dba_hist_seg_stat where obj#=92952 and dataobj#=92953;
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='T7';
select bytes/1024/1024 from dba_segments where owner='LVZZ' and segment_name='IDX_T7_OBJECT_NAME';
测试结果：insert操作耗时90秒，生成的redo数据量是 614MB，
表的db_block_changes_delta 数量是 432192
重建索引的情况：重建索引耗时69秒，生成的redo数据量是 222MB，
表的db_block_changes_delta 数量是 50800，
索引的db_block_changes_delta 数量是 1344，索引的大小为 224MB
总结：通过插入前unusable索引，插入（有排序）完成后重建索引的方式，redo的生成量是614+222=836MB。
7、以上测试情况总结
源表的数据行数为 5525120，数据容量 623MB。
目标表无索引列，无排序插入：insert操作耗时67秒，生成的redo数据量是 615MB，表的db_block_changes_delta 数量是 430752
目标表一个索引，无排序插入：insert耗时约10分钟，生成的redo数据量是2313MB，表的db_block_changes_delta 数量是 428320
目标表一个索引，有排序插入：insert耗时约 3分钟，生成的redo数据量是1459MB，表的db_block_changes_delta 数量是 428384
无排序时：索引的db_block_changes_delta 数量是 3940064，索引的大小为 296MB
有排序时：索引的db_block_changes_delta 数量是 371424， 索引的大小为 215MB
通过插入前unusable索引，插入完成后重建索引的方式
目标表一个索引，无排序插入：insert操作耗时80秒，生成的redo数据量是 614MB，表的db_block_changes_delta 数量是 428320
目标表一个索引，有排序插入：insert操作耗时90秒，生成的redo数据量是 614MB，表的db_block_changes_delta 数量是 432192
重建索引的redo量
无排序插入：生成的redo数据量是 224MB，表的db_block_changes_delta 数量是 70688，索引的db_block_changes_delta 数量是 1328，索引的大小为 224MB
有排序插入：生成的redo数据量是 222MB，表的db_block_changes_delta 数量是 50800，索引的db_block_changes_delta 数量是 1344，索引的大小为 224MB
总共：
无排序插入：redo的生成量是614+224=838MB
有排序插入：redo的生成量是614+222=836MB
当插入的目标表有索引字段时，执行大批量的插入操作前最好先将索引unusable，插入完成后再重建索引字段，避免产生大量的redo数据。
