文章版权所有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;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/14710393/viewspace-1100923/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/14710393/viewspace-1100923/