title: Massive UPDATE vs. MERGE performance on Oracle
body: <p>I am trying to update a destination table from a source table using a single, massive <code>UPDATE</code> statement, but the execution time is way longer than it should.</p>
  <p><strong>Query</strong></p>
  <pre><code>UPDATE MY_DEST
  SET (DEST_B, DEST_C) = (
    SELECT SRC_A + SRC_B, SRC_B
    FROM MY_SRC
    WHERE SRC_KEY = DEST_KEY AND SRC_DATE = DEST_DATE
  );
  </code></pre>
  <p>Both tables contain roughly 10 to 13 million rows, they have matching primary keys and we can safely assume that each row in the destination table has a corresponding row in the source table.</p>
  <p><strong>Table definitions</strong></p>
  <pre><code>CREATE TABLE MY_SRC (
    SRC_KEY VARCHAR2(50),
    SRC_DATE DATE,
    SRC_A NUMBER(15,2),
    SRC_B NUMBER(15,2),
    CONSTRAINT MY_SRC_PK PRIMARY KEY (SRC_KEY, SRC_DATE)
  );
  
  CREATE TABLE MY_DEST (
    DEST_KEY VARCHAR2(50),
    DEST_DATE DATE,
    DEST_B NUMBER(15,2),
    DEST_C NUMBER(15,2),
    CONSTRAINT MY_DEST_PK PRIMARY KEY (DEST_KEY, DEST_DATE)
  );
  </code></pre>
  <p><strong>Execution plan</strong></p>
  <pre><code>Plan hash value: 3904754293
  
  ------------------------------------------------------------------------------------------
  | Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
  ------------------------------------------------------------------------------------------
  |   0 | UPDATE STATEMENT             |           |    12M|   675M|   128M (20)| 01:23:55 |
  |   1 |  UPDATE                      | MY_DEST   |       |       |            |          |
  |   2 |   TABLE ACCESS FULL          | MY_DEST   |    12M|   675M| 69756   (1)| 00:00:03 |
  |   3 |   TABLE ACCESS BY INDEX ROWID| MY_SRC    |     1 |    46 |     4   (0)| 00:00:01 |
  |*  4 |    INDEX UNIQUE SCAN         | MY_SRC_PK |     1 |       |     3   (0)| 00:00:01 |
  ------------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
  4 - access(&quot;SRC_KEY&quot;=:B1 AND &quot;SRC_DATE&quot;=:B2)
  </code></pre>
  <p><strong>Questions</strong></p>
  <ol>
  <li><p>Is the above <code>UPDATE</code> query hopelessly forced to use a slow, row-by-row execution plan?</p>
  </li>
  <li><p>Can I optimize the above query only by rewriting it as a <code>MERGE</code> statement, such as the following one?</p>
  <ul>
  <li><p>Alternate query, rewritten as a <code>MERGE</code></p>
  <pre><code>MERGE INTO MY_DEST
  USING (SELECT SRC_KEY, SRC_DATE, SRC_B, SRC_A + SRC_B AS SRC_C FROM MY_SRC)
  ON (DEST_KEY = SRC_KEY AND DEST_DATE = SRC_DATE)
  WHEN MATCHED THEN UPDATE SET DEST_B = SRC_B, DEST_C = SRC_C;
  </code></pre>
  </li>
  <li><p>Alternate execution plan</p>
  <pre><code>Plan hash value: 2444580570
  
  ----------------------------------------------------------------------------------------
  | Id  | Operation            | Name    | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
  ----------------------------------------------------------------------------------------
  |   0 | MERGE STATEMENT      |         |    12M|   638M|       |   359K  (1)| 00:00:15 |
  |   1 |  MERGE               | MY_DEST |       |       |       |            |          |
  |   2 |   VIEW               |         |       |       |       |            |          |
  |*  3 |    HASH JOIN         |         |    12M|  2260M|   716M|   359K  (1)| 00:00:15 |
  |   4 |     TABLE ACCESS FULL| MY_SRC  |    12M|   568M|       |   162K  (1)| 00:00:07 |
  |   5 |     TABLE ACCESS FULL| MY_DEST |    12M|  1695M|       | 69756   (1)| 00:00:03 |
  ----------------------------------------------------------------------------------------
  
  Predicate Information (identified by operation id):
  ---------------------------------------------------
  
     3 - access(&quot;DEST_KEY&quot;=&quot;SRC_KEY&quot; AND &quot;DEST_DATE&quot;=&quot;SRC_DATE&quot;)
  </code></pre>
  </li>
  </ul>
  </li>
  <li><p>Can I get the same, good performance of the alternate <code>MERGE</code> statement when using an <code>UPDATE</code>?</p>
  </li>
  </ol>
  <p>I am using Oracle 12c.</p>