oracle blevel降低,oracle 数据处理方法

我的测试方法

测试环境t4400+2G+5400转/秒硬务。表大小248MB，1997760条数据。

建立一个基表：

create tablea_object as

select object_name,object_type,created,rownum+49944 id  from dba_objects

alter table a_object addprimary 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 intoa_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 fromb_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 ofb_object.object_name%type index by binary_integer;

type t_type is table ofb_object.object_type%type index by binary_integer;

type t_createdis table ofb_object.created%type index by binary_integer;

type t_Id is table ofb_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 intoobjects,types,createds,ids limit 5000;

for i in 1..ids.count loop

insert intoa_object values (objects(i),types(i),createds(i),ids(i)+1997760);

end loop;

exit whencur%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 curbulk collect into objects limit10000;

forall i in 1..objects.last

insert intoa_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

fetchcur bulk collect intoobjects limit 10000;

exit when objects.count=0;

v_counter:=v_counter+1;

forall i in 1..objects.last

insert intoa_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

opencur;

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

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

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

)

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;

altertable p_object truncatepartitionp2;

由于索引失效还需重建索引

alter indexSYS_C005787rebuild 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' andowner='SCOTT'

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