关于update转merge的优化

最新推荐文章于 2024-10-26 09:55:21 发布

kelly1984

最新推荐文章于 2024-10-26 09:55:21 发布

阅读量653

点赞数

分类专栏： sql

本文链接：https://blog.youkuaiyun.com/kelly1984/article/details/8287091

版权

sql 专栏收录该内容

4 篇文章

订阅专栏

--对于批量的sql 的update，我们可以通过使用merge来改写，使用hash-join
--来提高update的效率。

--原始sql:

 update f_claim_evt t1
   set t1.flag = (select t2.flag 
                 from prplclaimloss t2 
                where t1.claim_no = t2.claimno
                  and t1.serialno = t2.serialno
                  )
 where t1.evt_type_claim_id = 5 
   and t1.claim_date_id between date '2012-01-01' and date '2012-12-31';

--执行时间太长了，半个小时过去了。干脆control+c了。。。

ERROR at line 1:
ORA-01013: user requested cancel of current operation
Elapsed: 00:33:08.95

执行计划如下：

SQL> explain plan for
  2  update f_claim_evt t1
  3     set t1.flag = (select t2.flag 
  4                   from prplclaimloss t2 
  5                  where t1.claim_no = t2.claimno
  6                    and t1.serialno = t2.serialno
  7                    )
  8   where t1.evt_type_claim_id = 5 
  9     and t1.claim_date_id between date '2012-01-01' and date '2012-12-31';

Explained.

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3166260189

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |               |  1409 | 50724 | 90918   (1)| 00:18:12 |
|   1 |  UPDATE            | F_CLAIM_EVT   |       |       |            |          |
|*  2 |   TABLE ACCESS FULL| F_CLAIM_EVT   |  1409 | 50724 | 90918   (1)| 00:18:12 |
|*  3 |   TABLE ACCESS FULL| PRPLCLAIMLOSS |     1 |    27 | 19926   (1)| 00:04:00 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("T1"."CLAIM_DATE_ID"<=TO_DATE(' 2012-12-31 00:00:00',
              'syyyy-mm-dd hh24:mi:ss') AND "T1"."EVT_TYPE_CLAIM_ID"=5 AND
              "T1"."CLAIM_DATE_ID">=TO_DATE(' 2012-01-01 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss'))
   3 - filter("T2"."CLAIMNO"=:B1 AND "T2"."SERIALNO"=:B2)

--改写merg 如下：

create view t1 as select * from f_claim_evt where evt_type_claim_id = 5 and claim_date_id between date '2012-01-01' and date '2012-12-31'

merge into t1
 using prplclaimloss t2
 on(t1.claim_no = t2.claimno and t1.serialno = t2.serialno)
 when matched then
 update set t1.flag=t2.flag; 
 
 449765 rows merged.

Elapsed: 00:14:43.43

执行时间为14分钟。提高至少一半

执行计划如下：

QL> explain plan for
  2  merge into t1
  3   using prplclaimloss t2
  4   on(t1.claim_no = t2.claimno and t1.serialno = t2.serialno)
  5   when matched then
  6   update set t1.flag=t2.flag; 

Explained.

Elapsed: 00:00:00.03
SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2352162984

--------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT      |               |   535 |  9095 |   111K  (1)| 00:22:13 |
|   1 |  MERGE               | F_CLAIM_EVT   |       |       |            |          |
|   2 |   VIEW               |               |       |       |            |          |
|*  3 |    HASH JOIN         |               |   535 |   130K|   111K  (1)| 00:22:13 |
|*  4 |     TABLE ACCESS FULL| F_CLAIM_EVT   |  1409 |   221K| 90918   (1)| 00:18:12 |
|   5 |     TABLE ACCESS FULL| PRPLCLAIMLOSS |  6896K|   585M| 20057   (2)| 00:04:01 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CLAIM_NO"="T2"."CLAIMNO" AND "SERIALNO"="T2"."SERIALNO")
   4 - filter("CLAIM_DATE_ID"<=TO_DATE(' 2012-12-31 00:00:00', 'syyyy-mm-dd
              hh24:mi:ss') AND "EVT_TYPE_CLAIM_ID"=5 AND "CLAIM_DATE_ID">=TO_DATE('
              2012-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))