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;