A、准备
create table postcode_test as
select * from postcode where 1=2;
insert /*+ append parallel(t1,4) */ into postcode_test t1
select * from postcode;
commit;
create table a_test_code as
select distinct qxcode from postcode_test
B、无索引更新
（1）语句
update postcode_test t1
set recflag = 2
where exists (select 1 from a_test_code t2 where t2.qxcode = t1.qxcode)
;
（2）结果显示：
SQL> update postcode_test t1
2 set recflag = 2
3 where exists (select 1 from a_test_code t2 where t2.qxcode = t1.qxcode)
4 ;
27353506 rows updated.
Elapsed: 00:18:36.25
Execution Plan
----------------------------------------------------------
Plan hash value: 2295785612
--------------------------------------------------------------------------------
-------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Tim
e |
--------------------------------------------------------------------------------
-------
| 0 | UPDATE STATEMENT | | 27M| 443M| 38198 (4)| 00:
07:39 |
| 1 | UPDATE | POSTCODE_TEST | | | |
|
|* 2 | HASH JOIN RIGHT SEMI| | 27M| 443M| 38198 (4)| 00:
07:39 |
| 3 | TABLE ACCESS FULL | A_TEST_CODE | 2761 | 19327 | 3 (0)| 00:
00:01 |
| 4 | TABLE ACCESS FULL | POSTCODE_TEST | 27M| 260M| 37875 (3)| 00:
07:35 |
--------------------------------------------------------------------------------
-------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("T2"."QXCODE"="T1"."QXCODE")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1273 recursive calls
28028092 db block gets
319266 consistent gets
107440 physical reads
SP2-0642: SQL*Plus internal error state 1075, context 1:4:4294967295
Unsafe to proceed
839 bytes sent via SQL*Net to client
816 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
2 sorts (memory)
0 sorts (disk)
27353506 rows processedC、有索引更新
（1）创建索引：
create index idx_postcode_test on postcode_test (qxcode);
（2）语句：
update /*+ ordered use_nl(t2,t1) */ postcode_test t1
set recflag = 2
where exists (select 1 from a_test_code t2 where t2.qxcode = t1.qxcode)
; （3）结果显示：
SQL> update /*+ ordered use_nl(t2,t1) */ postcode_test t1
2 set recflag = 2
3 where exists (select 1 from a_test_code t2 where t2.qxcode = t1.qxcode)
4 ;
27353506 rows updated.
Elapsed: 00:14:56.08
Execution Plan
----------------------------------------------------------
Plan hash value: 2320489204
--------------------------------------------------------------------------------
----------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time |
--------------------------------------------------------------------------------
----------
| 0 | UPDATE STATEMENT | | 27M| 443M| 135K (2)|
00:27:04 |
| 1 | UPDATE | POSTCODE_TEST | | | |
|
| 2 | NESTED LOOPS | | 27M| 443M| 135K (2)|
00:27:04 |
| 3 | SORT UNIQUE | | 2761 | 19327 | 3 (0)|
00:00:01 |
| 4 | TABLE ACCESS FULL| A_TEST_CODE | 2761 | 19327 | 3 (0)|
00:00:01 |
|* 5 | INDEX RANGE SCAN | IDX_POSTCODE_TEST | 9901 | 99010 | 27 (8)|
00:00:01 |
--------------------------------------------------------------------------------
----------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("T2"."QXCODE"="T1"."QXCODE")
Note
-----
- dynamic sampling used for this statement
Statistics
----------------------------------------------------------
1859 recursive calls
27963424 db block gets
225785 consistent gets
177248 physical reads
SP2-0642: SQL*Plus internal error state 1075, context 1:4:4294967295
Unsafe to proceed
826 bytes sent via SQL*Net to client
846 bytes received via SQL*Net from client
4 SQL*Net roundtrips to/from client
3 sorts (memory)
0 sorts (disk)
27353506 rows processed
3、使用join生成临时表来替换update操作
（1）语句
create table postcode_test2 as
select t1.zipcode,t1.sfcode,t1.dqcode,t1.qxcode,t1.zipname,2 as recflag
from postcode_test t1,a_test_code t2
where t1.qxcode = t2.qxcode
; （2）结果显示
由于create table as是ddl语句，看不到执行计划，正面用append insert替换：
两次执行的时间只有1分钟左右，只有update的十分之一不到，性能提升相当明显
D、综述：
在更新大量数据时，使用索引与否不是性能提升的关键，同时，对于大量数据的更新，意味着超大量的成本。而使用ctas或append insert中间表，使其在各各项成本上，都得到了大大的节省。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/12350275/viewspace-712734/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
