现在的位置: 首页 > 综合 > 正文

利用Merge代替复杂的UPDATE语句

2012年01月01日 ⁄ 综合 ⁄ 共 7745字 ⁄ 字号 评论关闭

有朋友问我一个update语句,下面两种写法哪个性能更高,一个是没HINT的,一个是有HINT的

UPDATE CS_PERFORMANCE_CURRENT C
   SET C.PERFORMANCE_SCORE = '0.00', C.ASSESS_DESC = '劳动纪律考核'
 WHERE C.TARGET_MODE_SEQ_ID =
       (SELECT D.SEQ_ID
          FROM CS_TARGET_MODEL_REL D, CS_AREA A
         WHERE D.ASSESS_ID = 1265
           AND C.TARGET_MODE_SEQ_ID = D.SEQ_ID
           AND D.STAFF_ID = 8
           AND C.COM_DATE = TO_DATE('20110801','YYYYMMDD')
           AND D.TARGET_MODEL_ID = 332
           AND D.STATE = TO_CHAR(2)
           AND D.AREA_ID = A.AREA_ID
           AND A.AREA_NAME = '永兴支局');

Plan hash value: 2321634139
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |                        |     1 |    41 |   679   (1)| 00:00:09 |
|   1 |  UPDATE                         | CS_PERFORMANCE_CURRENT |       |       |            |          |
|*  2 |   FILTER                        |                        |       |       |            |          |
|   3 |    TABLE ACCESS FULL            | CS_PERFORMANCE_CURRENT |   124K|  4968K|   664   (1)| 00:00:08 |
|*  4 |    FILTER                       |                        |       |       |            |          |
|   5 |     NESTED LOOPS                |                        |     1 |    52 |     3   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL    |     1 |    26 |     2   (0)| 00:00:01 |
|*  7 |       INDEX UNIQUE SCAN         | PK_CS_TARGET_RULE_REL  |     1 |       |     1   (0)| 00:00:01 |
|*  8 |      TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    26 |     1   (0)| 00:00:01 |
|*  9 |       INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C"."TARGET_MODE_SEQ_ID"= (SELECT "D"."SEQ_ID" FROM "CS_AREA" 
              "A","CS_TARGET_MODEL_REL" "D" WHERE :B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') 
              AND "D"."SEQ_ID"=:B2 AND "D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 
              AND "D"."STATE"='2' AND "D"."AREA_ID"="A"."AREA_ID" AND "A"."AREA_NAME"='永兴支局'))
   4 - filter(:B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND 
              "D"."STATE"='2')
   7 - access("D"."SEQ_ID"=:B1)
   8 - filter("A"."AREA_NAME"='永兴支局')
   9 - access("D"."AREA_ID"="A"."AREA_ID")

已用时间:  00: 00: 00.29

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      32670  consistent gets
         21  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        978  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed
UPDATE /*+ PUSH_SUBQ(@D) */CS_PERFORMANCE_CURRENT C
   SET C.PERFORMANCE_SCORE = '0.00', C.ASSESS_DESC = '劳动纪律考核'
 WHERE C.TARGET_MODE_SEQ_ID =
       (SELECT /*+ QB_NAME(D) */ D.SEQ_ID
          FROM CS_TARGET_MODEL_REL D, CS_AREA A
         WHERE D.ASSESS_ID = 1265
           AND C.TARGET_MODE_SEQ_ID = D.SEQ_ID
           AND D.STAFF_ID = 8
           AND C.COM_DATE = TO_DATE('20110801','YYYYMMDD')
           AND D.TARGET_MODEL_ID = 332
           AND D.STATE = TO_CHAR(2)
           AND D.AREA_ID = A.AREA_ID
           AND A.AREA_NAME = '永兴支局')
           
Plan hash value: 4189652906
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT                |                        |     1 |    39 |    43   (0)| 00:00:01 |
|   1 |  UPDATE                         | CS_PERFORMANCE_CURRENT |       |       |            |          |
|*  2 |   TABLE ACCESS FULL             | CS_PERFORMANCE_CURRENT |     1 |    39 |    40   (0)| 00:00:01 |
|*  3 |    FILTER                       |                        |       |       |            |          |
|   4 |     NESTED LOOPS                |                        |     1 |    47 |     3   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS BY INDEX ROWID| CS_TARGET_MODEL_REL    |     1 |    25 |     2   (0)| 00:00:01 |
|*  6 |       INDEX UNIQUE SCAN         | PK_CS_TARGET_RULE_REL  |     1 |       |     1   (0)| 00:00:01 |
|*  7 |      TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    22 |     1   (0)| 00:00:01 |
|*  8 |       INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter("C"."TARGET_MODE_SEQ_ID"= (SELECT /*+ PUSH_SUBQ QB_NAME ("D") */ "D"."SEQ_ID" FROM 
              "CS_AREA" "A","CS_TARGET_MODEL_REL" "D" WHERE :B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd 
              hh24:mi:ss') AND "D"."SEQ_ID"=:B2 AND "D"."ASSESS_ID"=1265 AND "D"."STAFF_ID"=8 AND 
              "D"."TARGET_MODEL_ID"=332 AND "D"."STATE"='2' AND "D"."AREA_ID"="A"."AREA_ID" AND 
              "A"."AREA_NAME"='永兴支局'))
   3 - filter(:B1=TO_DATE(' 2011-08-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter("D"."ASSESS_ID"=1265 AND "D"."STAFF_ID"=8 AND "D"."TARGET_MODEL_ID"=332 AND 
              "D"."STATE"='2')
   6 - access("D"."SEQ_ID"=:B1)
   7 - filter("A"."AREA_NAME"='永兴支局')
   8 - access("D"."AREA_ID"="A"."AREA_ID")

统计信息
----------------------------------------------------------
          1  recursive calls
          0  db block gets
      32670  consistent gets
         21  physical reads
          0  redo size
        536  bytes sent via SQL*Net to client
        978  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

 

可以看到,逻辑读都是一样的,那么这个SQL,加不加HINT性能都一样。其实对于Oracle中的update 语句,如果SQL很复杂,需要关联多个表去做update,这个时候应该用

merge 语句代替 update,用Merge 改写之后 :

MERGE INTO CS_PERFORMANCE_CURRENT A
USING (SELECT D.SEQ_ID
       FROM   CS_TARGET_MODEL_REL D,
              CS_AREA             M
       WHERE  D.ASSESS_ID = 1265 AND
              D.STAFF_ID = 8 AND
              D.TARGET_MODEL_ID = 332 AND
              D.STATE = TO_CHAR(2) AND
              D.AREA_ID = M.AREA_ID AND
              M.AREA_NAME = '永兴支局') H
ON (A.TARGET_MODE_SEQ_ID = H.SEQ_ID)
WHEN MATCHED THEN
  UPDATE SET A.PERFORMANCE_SCORE = '0.00', A.ASSESS_DESC = '劳动纪律考核' WHERE A.COM_DATE = TO_DATE('20110801', 'YYYYMMDD');
 
 ----------------------------------------------------------------------------------------------------------
 | Id  | Operation                       | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------------------------
 |   0 | MERGE STATEMENT                 |                        |     1 |   167 |   750   (1)| 00:00:10 |
 |   1 |  MERGE                          | CS_PERFORMANCE_CURRENT |       |       |            |          |
 |   2 |   VIEW                          |                        |       |       |            |          |
 |*  3 |    HASH JOIN                    |                        |     1 |   210 |   750   (1)| 00:00:10 |
 |   4 |     NESTED LOOPS                |                        |     1 |    52 |    85   (2)| 00:00:02 |
 |*  5 |      TABLE ACCESS FULL          | CS_TARGET_MODEL_REL    |     1 |    26 |    84   (2)| 00:00:02 |
 |*  6 |      TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    26 |     1   (0)| 00:00:01 |
 |*  7 |       INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |
 |   8 |     TABLE ACCESS FULL           | CS_PERFORMANCE_CURRENT |   124K|    18M|   664   (1)| 00:00:08 |
 ----------------------------------------------------------------------------------------------------------
  
 Predicate Information (identified by operation id):
 ---------------------------------------------------
  
    3 - access("A"."TARGET_MODE_SEQ_ID"="D"."SEQ_ID")
    5 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND 
               "D"."STATE"='2')
    6 - filter("M"."AREA_NAME"='永兴支局')
    7 - access("D"."AREA_ID"="M"."AREA_ID")
          
          
统计信息
----------------------------------------------------------
        333  recursive calls
          0  db block gets
       3474  consistent gets
          3  physical reads
          0  redo size
        546  bytes sent via SQL*Net to client
        998  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
         15  sorts (memory)
          0  sorts (disk)
          0  rows processed          

逻辑读下降了近10倍,这里 CS_PERFORMANCE_CURRENT 是全表扫描,它有100W的数据量,所以建立索引在TARGET_MODE_SEQ_ID列上

Plan hash value: 3908453434
 
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT                  |                        |     1 |   167 |    88   (2)| 00:00:02 |
|   1 |  MERGE                           | CS_PERFORMANCE_CURRENT |       |       |            |          |
|   2 |   VIEW                           |                        |       |       |            |          |
|   3 |    TABLE ACCESS BY INDEX ROWID   | CS_PERFORMANCE_CURRENT |     1 |   158 |     3   (0)| 00:00:01 |
|   4 |     NESTED LOOPS                 |                        |     1 |   210 |    88   (2)| 00:00:02 |
|   5 |      NESTED LOOPS                |                        |     1 |    52 |    85   (2)| 00:00:02 |
|*  6 |       TABLE ACCESS FULL          | CS_TARGET_MODEL_REL    |     1 |    26 |    84   (2)| 00:00:02 |
|*  7 |       TABLE ACCESS BY INDEX ROWID| CS_AREA                |     1 |    26 |     1   (0)| 00:00:01 |
|*  8 |        INDEX UNIQUE SCAN         | PK_CS_AREA_NEW         |     1 |       |     0   (0)| 00:00:01 |
|*  9 |      INDEX RANGE SCAN            | IDX_CS_PFC_CURRENT     |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   6 - filter("D"."STAFF_ID"=8 AND "D"."ASSESS_ID"=1265 AND "D"."TARGET_MODEL_ID"=332 AND 
              "D"."STATE"='2')
   7 - filter("M"."AREA_NAME"='永兴支局')
   8 - access("D"."AREA_ID"="M"."AREA_ID")
   9 - access("A"."TARGET_MODE_SEQ_ID"="D"."SEQ_ID")

已用时间:  00: 00: 00.18

统计信息
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        386  consistent gets
          0  physical reads
          0  redo size
        544  bytes sent via SQL*Net to client
        998  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

现在逻辑读降低到386

在Oracle当中,强烈建议 =使用 merge 代替 update语句

 

抱歉!评论已关闭.