oracle处理数据处理,oracle的数据处理之insert,delete,truncate partition,bulk collect into

我的测试方法

测试环境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 tableb_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_objectselect

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 intodeclaretype 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吧。

最后测一下merge intomerge into scott.p_object a

using  (select object_name,object_type,created,id+1997760 ID from b_object) b  oN (a.id=b.id)

--when matched then

-- NULL;

-- update set a.ename=b.ename

when not matched then

insert values(b.object_name,b.object_type,b.created,b.id)

其实不测也应当可以判定，这个不咋地,10秒都没出结果。。。。

以下开始测删除，从傻瓜式删除开始：

deletefroma_Object where id>1997760

--377.14秒

altertable a_object drop constraintsSYS_C005373

删除索引后再试，结果惨不忍睹。

建分区表：create table SCOTT.P_OBJECT

(

object_name VARCHAR2(128),

object_type VARCHAR2(19),

created     DATE,

id          NUMBER

)

partitionby 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'