merge的魅力——update 语句效率的优化

 

--创建更新目的表：

DROP TABLE test_xie;

CREATE   TABLE test_xie(

     member_id varchar2(64),

     score_1 NUMBER,

     score_2 NUMBER);

--生成测试数据 100W

DECLARE

BEGIN

  FOR i IN 1..1000000 LOOP

INSERT INTO test_xie (member_id, score_1, score_2)

VALUES ('m'||i, i, NULL);

IF i MOD 10000 = 0 THEN

COMMIT;

END IF;

  END LOOP;

  COMMIT;

END;

 

--数据源表

DROP TABLE test_xie_2;

CREATE   TABLE test_xie_2(member_id varchar2(64),score_1 NUMBER, score_2 NUMBER);

 

--源数据100W

INSERT INTO test_xie_2

        (member_id, score_1, score_2)

        SELECT member_id, NULL, 1 + score_1  FROM test_xie;

COMMIT;

 

 15:11:07 SQL> set linesize 1000;
15:11:28 SQL> set timing on;
15:11:29 SQL> SET TIME ON;
15:11:29 SQL> set autotrace traceonly;
15:11:29 SQL> UPDATE test_xie a
15:11:29   2  SET score_2 = (SELECT score_2 FROM test_xie_2 b WHERE a.member_id = b.member_id)
15:11:29   3  WHERE EXISTS (SELECT 1 FROM test_xie_2 b WHERE a.member_id = b.member_id);

1000000 rows updated.

Elapsed: 00:00:29.66

 

Execution Plan

 

--------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                  |   986K|    76M|  9347   (1)|
|   1 |  UPDATE                      | TEST_XIE         |       |       |            |
|*  2 |   HASH JOIN RIGHT SEMI       |                  |   986K|    76M|  9347   (1)|
|   3 |    INDEX FAST FULL SCAN      | U_IND_TEST_XIE_2 |  1048K|    33M|   699   (2)|
|   4 |    TABLE ACCESS FULL         | TEST_XIE         |   986K|    44M|  1204   (1)|
|   5 |   TABLE ACCESS BY INDEX ROWID| TEST_XIE_2       |     1 |    47 |     3   (0)|
|*  6 |    INDEX UNIQUE SCAN         | U_IND_TEST_XIE_2 |     1 |       |     2   (0)|
-------------------------------------------------------------------------------------

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

   2 - access("A"."MEMBER_ID"="B"."MEMBER_ID")
   6 - access("B"."MEMBER_ID"=:B1)

 

 

Statistics
----------------------------------------------------------
        169  recursive calls
    1777989  db block gets
    2261811  consistent gets
     479808  physical reads
  256558164  redo size
        844  bytes sent via SQL*Net to client
        925  bytes received via SQL*Net from client
          3  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
    1000000  rows processed

15:12:00 SQL>  

 

 

--update 方法2

set timing on;

set autotrace traceonly; 

UPDATE (SELECT a.member_id, a.score_2 AS score_2_a, b.score_2 AS score_2_b

        FROM test_xie a, test_xie_2 b

        WHERE a.member_id = b.member_id)

SET score_2_a = score_2_b;

--执行计划

1000000 rows updated.

 

Elapsed: 00:00:12.64

 

Execution Plan

----------------------------------------------------------------------------------
| Id  | Operation           | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT    |            |   986K|    88M| 10326   (1)| 00:02:04 |
|   1 |  UPDATE             | TEST_XIE   |       |       |            |          |
|*  2 |   HASH JOIN         |            |   986K|    88M| 10326   (1)| 00:02:04 |
|   3 |    TABLE ACCESS FULL| TEST_XIE   |   986K|    44M|  1204   (1)| 00:00:15 |
|   4 |    TABLE ACCESS FULL| TEST_XIE_2 |  1048K|    46M|   726   (2)| 00:00:09 |
----------------------------------------------------------------------------------

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

   2 - access("A"."MEMBER_ID"="B"."MEMBER_ID")

 

Statistics

----------------------------------------------------------

       1032  recursive calls

    1778004  db block gets

       7455  consistent gets

      26932  physical reads

  256557256  redo size

        845  bytes sent via SQL*Net to client

        932  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

         13  sorts (memory)

          2  sorts (disk)

    1000000  rows processed

 

15:22:33 SQL> 15:22:33 SQL>

 

--方法3 ：使用merge

MERGE INTO test_xie a

USING (SELECT member_id, score_2 AS score_2_b FROM test_xie_2) b

ON (a.member_id = b.member_id)

WHEN MATCHED THEN

        UPDATE SET score_2 = score_2_b;

 

15:22:33 SQL> 15:22:33 SQL> MERGE INTO test_xie a

15:25:32   2  USING (SELECT member_id, score_2 AS score_2_b FROM test_xie_2) b

15:25:32   3  ON (a.member_id = b.member_id)

15:25:32   4  WHEN MATCHED THEN

15:25:32   5          UPDATE SET score_2 = score_2_b;

 

1000000 rows merged.

 

Elapsed: 00:00:13.69

 

Execution Plan

----------------------------------------------------------

Plan hash value: 3736152574

 

-----------------------------------------------------------------------------------

| Id  | Operation            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |

-----------------------------------------------------------------------------------

|   0 | MERGE STATEMENT      |            |   986K|    24M| 12046   (1)| 00:02:25 |

|   1 |  MERGE               | TEST_XIE   |       |       |            |          |

|   2 |   VIEW               |            |       |       |            |          |

|*  3 |    HASH JOIN         |            |   986K|   111M| 12046   (1)| 00:02:25 |

|   4 |     TABLE ACCESS FULL| TEST_XIE_2 |  1048K|    46M|   726   (2)| 00:00:09 |

|   5 |     TABLE ACCESS FULL| TEST_XIE   |   986K|    67M|  1204   (1)| 00:00:15 |

-----------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("A"."MEMBER_ID"="MEMBER_ID")

 

Note

-----

   - dynamic sampling used for this statement

 

 

Statistics

----------------------------------------------------------

        230  recursive calls

    1778018  db block gets

       7370  consistent gets

      31342  physical reads

  256557052  redo size

        845  bytes sent via SQL*Net to client

        927  bytes received via SQL*Net from client

          3  SQL*Net roundtrips to/from client

          1  sorts (memory)

          2  sorts (disk)

    1000000  rows processed

 

15:25:46 SQL>

 

总结：

1.         使用方法2，即内嵌视图的方法，大大的提高了效率；

2.         更直观更简捷的方法是使用merge

注意：

在使用第二种方法的时候，需要在源表test_xie_2的列member_id上有唯一约束或索引才可以，否则报错：
ORA-01779: cannot modify a column which maps to a non key-preserved table