ITPub博客

首页 > 数据库 > Oracle > Oracle删除效率测试

Oracle删除效率测试

原创 Oracle 作者:luckyfriends 时间:2014-03-04 16:43:29 0 删除 编辑

文章版权所有Jusin Haoluckyfriends),支持原创,转载请注明。

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;

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14710393/viewspace-1100923/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
个人博客网 www.leosunny 长期从事数据库相关工作,涉及oracle,mysql, sqlsever,db2 , OGG, SSIS以及非关系数据库和大数据平台等。 希望一直分享一些个人案例和知识,和广大技术宅们共同进步。

注册时间:2009-06-06

  • 博文量
    468
  • 访问量
    3160548