oracle怎么删除效率高,Oracle删除效率测试

文章版权所有Jusin Hao(luckyfriends)，支持原创，转载请注明。

create table test2 as select * from dba_objects;

insert into test2 select * from dba_objects;

insert into test2 select * from dba_objects;

insert into test2 select * from dba_objects;

insert into test2 select * from dba_objects;

commit;

SQL> select count(*) from test2;

COUNT(*)

----------

65900

SQL>

hao@ORCL11G> set timing on

hao@ORCL11G> create index i_test2_oid on test2(object_id);

索引已创建。

已用时间:  00: 00: 01.54

hao@ORCL11G> create index i_test2_ot on test2(object_type);

索引已创建。

已用时间:  00: 00: 00.07

hao@ORCL11G> set autotrace traceonly

------方法1)：

hao@ORCL11G> create table test2_allbak as select * from test2;

表已创建。

已用时间:  00: 00: 05.35

hao@ORCL11G> create table test2_bak as select * from test2 where object_type not in('TABLE') and object_id >200;

表已创建。

已用时间:  00: 00: 01.89

hao@ORCL11G> drop table test2_bak;

表已删除。

已用时间:  00: 00: 00.03

hao@ORCL11G> create table test2_bak as select * from test2 where object_id in (select object_id from test2 where object_type not in('TABLE') and object_id

>200);

表已创建。

已用时间:  00: 00: 06.21

hao@ORCL11G>

hao@ORCL11G> truncate table test2;

表被截断。

已用时间:  00: 00: 00.04

hao@ORCL11G> select  'alter Index  '|| a.INDEX_name ||' unusable;'  from user_indexes a Where a.table_name='TEST2';

'ALTERINDEX'||A.INDEX_NAME||'UNUSABLE;'

-----------------------------------------------------

alter Index  I_TEST2_OT unusable;

alter Index  I_TEST2_OID unusable;

已用时间:  00: 00: 00.01

hao@ORCL11G> select  'alter Index  '|| a.INDEX_name ||' rebuild;'  from user_indexes a Where a.table_name='TEST2';

'ALTERINDEX'||A.INDEX_NAME||'REBUILD;'

----------------------------------------------------

alter Index  I_TEST2_OT rebuild;

alter Index  I_TEST2_OID rebuild;

已用时间:  00: 00: 00.01

hao@ORCL11G> select  'alter Index  '|| a.INDEX_name ||' unusable;'  from user_indexes a Where a.table_name='TEST2_bak';

未选定行

已用时间:  00: 00: 00.01

hao@ORCL11G> alter Index  I_TEST2_OT unusable;

索引已更改。

已用时间:  00: 00: 00.06

hao@ORCL11G> alter Index  I_TEST2_OID unusable;

索引已更改。

已用时间:  00: 00: 00.01

hao@ORCL11G> select count(*) from test2;

COUNT(*)

----------

0

已用时间:  00: 00: 00.01

hao@ORCL11G> select count(*) from test2_bak;

COUNT(*)

----------

58060

已用时间:  00: 00: 00.18

hao@ORCL11G> insert into /*+ append */  test2 select * from test2_bak;

已创建58060行。

已用时间:  00: 00: 10.28

hao@ORCL11G>

hao@ORCL11G> truncate table test2;

表被截断。

已用时间:  00: 00: 00.15

hao@ORCL11G> set autotrace on

hao@ORCL11G> insert into /*+ append */  test2 select * from test2_bak;

已创建58060行。

已用时间:  00: 00: 30.59

执行计划

----------------------------------------------------------

Plan hash value: 2384912978

--------------------------------------------------------------------------------------

| Id  | Operation                | Name      | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | INSERT STATEMENT         |           | 61164 |    12M|   216   (1)| 00:00:03 |

|   1 |  LOAD TABLE CONVENTIONAL | TEST2     |       |       |            |          |

|   2 |   TABLE ACCESS FULL      | TEST2_BAK | 61164 |    12M|   216   (1)| 00:00:03 |

--------------------------------------------------------------------------------------

Note

-----

- dynamic sampling used for this statement (level=2)

统计信息

----------------------------------------------------------

2509  recursive calls

89445  db block gets

4641  consistent gets

9  physical reads

24108520  redo size

920  bytes sent via SQL*Net to client

1021  bytes received via SQL*Net from client

4  SQL*Net roundtrips to/from client

1  sorts (memory)

0  sorts (disk)

58060  rows processed

hao@ORCL11G> set autotrace off

hao@ORCL11G> select count(*) from test2;

COUNT(*)

----------

58060

已用时间:  00: 00: 00.01

hao@ORCL11G> commit;

提交完成。

已用时间:  00: 00: 00.00

hao@ORCL11G> alter Index  I_TEST2_OT rebuild;

索引已更改。

已用时间:  00: 00: 02.34

hao@ORCL11G> alter Index  I_TEST2_OID rebuild;

索引已更改。

已用时间:  00: 00: 00.21

hao@ORCL11G> alter Index  I_TEST2_OT rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.21

hao@ORCL11G> alter Index  I_TEST2_OID rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.20

hao@ORCL11G>

hao@ORCL11G> truncate table test2;

表被截断。

已用时间:  00: 00: 00.21

hao@ORCL11G> insert into /*+ append */  test2 select * from test2_bak;

已创建58060行。

已用时间:  00: 00: 28.73

hao@ORCL11G>

hao@ORCL11G> select count(*) from test2;

COUNT(*)

----------

58060

已用时间:  00: 00: 00.01

hao@ORCL11G> alter Index  I_TEST2_OT rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.15

hao@ORCL11G> alter Index  I_TEST2_OID rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.20

hao@ORCL11G>

------方法2)：

hao@ORCL11G> insert into /*+ append */  test2 select * from test2_allbak;

已创建65900行。

已用时间:  00: 00: 34.29

hao@ORCL11G>

hao@ORCL11G> delete from test2 where object_id in (select object_id from test2 where object_type not in('TABLE') and object_id >200);

已删除58060行。

已用时间:  00: 00: 53.00

hao@ORCL11G> commit;

提交完成。

已用时间:  00: 00: 00.00

hao@ORCL11G> alter table  test2 move tablespace TEST;

表已更改。

已用时间:  00: 00: 01.60

hao@ORCL11G> alter Index  I_TEST2_OT rebuild;

索引已更改。

已用时间:  00: 00: 00.07

hao@ORCL11G> alter Index  I_TEST2_OID rebuild;

索引已更改。

已用时间:  00: 00: 00.07

hao@ORCL11G>

hao@ORCL11G> alter Index  I_TEST2_OT rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.10

hao@ORCL11G> alter Index  I_TEST2_OID rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.10

----方法3)

hao@ORCL11G> truncate table test2;

表被截断。

已用时间:  00: 00: 00.01

hao@ORCL11G>

hao@ORCL11G> Select '''' || object_type || ''','From dba_objects;

hao@ORCL11G> Insert Into /*+ append */

2  Test2

3     Select *

4     From Test2_Allbak

5     Where Object_Type In ('EDITION', 'INDEX PARTITION', 'CONSUMER GROUP', 'SEQUENCE', 'TABLE PARTITION', 'SCHEDULE',

6                              'QUEUE', 'RULE', 'PROCEDURE', 'OPERATOR', 'LOB PARTITION', 'DESTINATION', 'WINDOW',

7                              'SCHEDULER GROUP', 'LOB', 'PACKAGE', 'PACKAGE BODY', 'LIBRARY', 'PROGRAM', 'RULE SET', 'CONTEXT',

8                              'TYPE BODY', 'TRIGGER', 'JOB CLASS', 'UNDEFINED', 'DIRECTORY', 'MATERIALIZED VIEW', 'INDEX',

9                              'SYNONYM', 'VIEW', 'FUNCTION', 'CLUSTER', 'TYPE', 'RESOURCE PLAN', 'JOB', 'EVALUATION CONTEXT') And

10                             Object_Id > 200;

已创建58060行。

已用时间:  00: 00: 23.89

hao@ORCL11G> alter Index  I_TEST2_OT rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.14

hao@ORCL11G> alter Index  I_TEST2_OID rebuild nologging;

索引已更改。

已用时间:  00: 00: 00.15

=========================================================================================

如下在cpu多路的情况下，开启并行和nologging后效果效率要好；

=========================================================================================

create index I_BD_CORP_jszc01 on bd_corp(CORPTYPE) parallel 4 nologging;

create table bd_corp_bak nologging parallel 4 as select /*+ parallel(4) */ * from bd_corp where pk_corp='1001';

insert into /*+ append parallel(4)*/  bd_corp_bak select /*+ parallel(4) */ * from  bd_corp nologging;

alter Index  I_BD_CORP_jszc01 rebuild parallel 4 nologging;