我的测试方法
测试环境t4400+2G+5400转/秒硬务。表大小248MB，1997760条数据。
建立一个基表：
create table a_object as
select object_name,object_type,created,rownum+49944 id from dba_objects
alter table a_object add primary key (id);
建立一个影响表：
create table b_object as select * from a_Object
1 查询速度比较
a_object
SQL> select count(*) from a_object;
COUNT(*)
----------
499440
Executed in 0.219 seconds
SQL> select count(*) from b_object;
COUNT(*)
----------
499440
Executed in 0.766 seconds
这个容易看出，a_object有主键，快，没啥好解释的
SQL> alter system flush buffer_cache;
SQL> alter system flush shared_pool;
SQL> select count(*) from b_Object a;
COUNT(*)
----------
1997760
Executed in 4.344 seconds
SQL> select /*+parallel(a,2)*/ count(*) from b_object a;
COUNT(*)
----------
1997760
Executed in 2 seconds
加parallel还是有点用，如果cpu更多，可能会更快。
2 插入
a_Object:insert into a_object
select object_name,object_type,created,rownum+998880 id from b_object
59秒
b_object:insert into b_object
select object_name,object_type,created,rownum+998880 id from b_object
25秒
有索引肯定慢些，这个没什么好解释的
insert /*+append*/ into a_object
select object_name,object_type,created,rownum+1997760 id from b_object
107.531秒
insert /*+parallel(a,2)*/ into a_object a
select object_name,object_type,created,rownum+1997760 id from b_object
175.172秒，还慢些
insert into a_object a
select object_name,object_type,created,rownum+1997760 id from b_object
197.125秒，最慢
insert /*+append parallel(a,2)*/into a_object a
select object_name,object_type,created,rownum+1997760 id from b_object
193.703秒,看样子parallel对表的insert 用处不大，用处最大的是append
alter table a_object nologging 让表不记日志，再
insert /*+append*/ into b_object
select object_name,object_type,created,rownum+1997760 id from b_object
57.688秒最快
bulk collect insert into
declare type t_object is table of b_object.object_name%type index by binary_integer;
type t_type is table of b_object.object_type%type index by binary_integer;
type t_created is table of b_object.created%type index by binary_integer;
type t_Id is table of b_object.id%type index by binary_integer;
objects t_object ;
types t_type ;
createds t_created ;
ids t_id ;
cursor cur is select * from b_object;
begin
open cur;
loop
fetch cur bulk collect into objects,types,createds,ids limit 5000;
for i in 1..ids.count loop
insert into a_object values (objects(i),types(i),createds(i),ids(i)+1997760);
end loop;
exit when cur%notfound;
end loop;
close cur;
end;
468.888秒。这个肯定最慢。
改写
declare
type object_type is record
(
object_name b_object.object_name%type,
object_type b_object.object_type%type,
created b_object.created%type,
id b_object.id%type
);
type t_object_type is table of object_type;
objects t_object_type ;
v_counter PLS_INTEGER := 0;
cursor cur is select object_name,object_type,created,id+1997760 id from b_object;
begin
open cur;
loop
fetch cur bulk collect into objects limit 10000;
for i in 1..objects.last loop
insert into a_object values objects(i);
end loop;
v_counter:=v_counter+1;
exit when cur%notfound;
end loop;
dbms_output.put_line('counter is :'||v_counter);
close cur;
end;
363秒。还是很慢，用forall改写，
declare
type object_type is record
(
object_name b_object.object_name%type,
object_type b_object.object_type%type,
created b_object.created%type,
id b_object.id%type
);
type t_object_type is table of object_type;
objects t_object_type ;
v_counter PLS_INTEGER := 0;
cursor cur is select object_name,object_type,created,id+1997760 id from b_object;
begin
open cur;
loop
fetch cur bulk collect into objects limit 10000;
forall i in 1..objects.last
insert into a_object values objects(i);
v_counter:=v_counter+1;
exit when cur%notfound;
end loop;
dbms_output.put_line('counter is :'||v_counter);
close cur;
end;
快了不少，131秒。这个速度比较理想。
declare
type object_type is record
(
object_name b_object.object_name%type,
object_type b_object.object_type%type,
created b_object.created%type,
id b_object.id%type
);
type t_object_type is table of object_type;
objects t_object_type ;
v_counter PLS_INTEGER := 0;
cursor cur is select object_name,object_type,created,id+1997760 id from b_object;
begin
open cur;
loop
fetch cur bulk collect into objects limit 10000;
exit when objects.count=0;
v_counter:=v_counter+1;
forall i in 1..objects.last
insert into a_object values objects(i);
end loop;
dbms_output.put_line('counter is :'||v_counter);
close cur;
end;
这个有点离谱，居然变成84.6秒，应当是刚才没刷shared_pool和buffer_cache所致。
alter system flush shared_pool;
alter system flush buffer_cache;
注意，以上两条仅限测试环境。
刷新后：158.875秒
。。。。继续更改：
declare
type object_type is record
(
object_name b_object.object_name%type,
object_type b_object.object_type%type,
created b_object.created%type,
id b_object.id%type
);
type t_object_type is table of object_type;
objects t_object_type ;
v_counter PLS_INTEGER := 0;
cursor cur is select object_name,object_type,created,id+1997760 id from b_object;
begin
open cur;
loop
fetch cur bulk collect into objects limit 10000;
exit when objects.count=0;
v_counter:=v_counter+1;
forall i in 1..objects.last
insert /* +append */ into a_object values objects(i);
end loop;
dbms_output.put_line('counter is :'||v_counter);
close cur;
end;
加append;134.672秒。当然这个仅仅是测试，显得有点冗余，但当有条件时，写比较复杂的的转换，又不是对单表操作时尽量用bulk collect forall吧。
以下开始测删除，从傻瓜式删除开始：
delete from a_Object where id>1997760
--377.14秒
alter table a_object drop constraints SYS_C005373
删除索引后再试，结果惨不忍睹。
建分区表：
create table SCOTT.P_OBJECT
(
object_name VARCHAR2(128),
object_type VARCHAR2(19),
created DATE,
id NUMBER
)
partition by range (id) (
partition p1 values less than (1997760),
partition p2 values less than (3995520)
);
alter table p_object nologging;
insert /*+append*/ into p_object select * from a_object;
alter table p_object truncate partition p2;
由于索引失效还需重建索引
alter index SYS_C005787 rebuild nologging;
insert into 花时248秒，truncate 分区1秒，重建索引15秒。
上一步也可以用再线重定义分区表做。。
顺便 提一下这里的统计信息
begin
dbms_stats.gather_table_stats('SCOTT','P_OBJECT', cascade => true);
end;
用这个收集后不准了。
select num_rows,t.EMPTY_BLOCKS,t.BLOCKS*8/1024,t.LAST_ANALYZED,t.logging from all_tables t where table_name='P_OBJECT' and owner='SCOTT'
select table_name,partition_position,num_rows,blocks*8/1024,sample_size,last_analyzed,high_value from all_tab_partitions where table_owner='SCOTT'
select i.INDEX_NAME,i.table_name,i.num_rows,i.blevel,i.logging,i.DISTINCT_KEYS,i.LAST_ANALYZED from all_indexes i where table_name='P_OBJECT'