ITPub博客

首页 > Linux操作系统 > Linux操作系统 > update的更新效率相关测试及调优方案

update的更新效率相关测试及调优方案

原创 Linux操作系统 作者:lirenquan 时间:2011-12-05 12:40:41 0 删除 编辑
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 processed
 
C、有索引更新
(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博客 ” ,链接:http://blog.itpub.net/12350275/viewspace-712734/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2010-12-24

  • 博文量
    24
  • 访问量
    111633