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

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中间表，使其在各各项成本上，都得到了大大的节省。

通过 Wiz 发布

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

转载于:http://blog.itpub.net/12350275/viewspace-712734/