title: Performance of MERGE vs. UPDATE with subquery
body: <p>Note that I've modified table/field names etc. for readability. Some of the original names are quite confusing.</p>
  
  <p>I have three different tables:</p>
  
  <pre><code>Retailer (Id+Code is a unique key)
  - Id
  - Code
  - LastReturnDate
  - ...
  
  Delivery/DeliveryHistory (combination of Date+RetailerId is unique)
  - Date
  - RetailerId
  - HasReturns
  - ...
  </code></pre>
  
  <p><code>Delivery</code> and <code>DeliveryHistory</code> are almost identical. Data is periodically moved to the history table, and there's no surefire way to know when this last happened. In general, the Delivery-table is quite small -- usually less than 100,000 rows -- while the history table will typically have millions of rows.</p>
  
  <p>My task is to update the <code>LastReturnDate</code> field for each retailer based on the current highest date value for which <code>HasReturns</code> is true in Delivery <em>or</em> DeliveryHistory.</p>
  
  <p>Previously this has been solved with a view defined as follows:</p>
  
  <pre class="lang-sql prettyprint-override"><code>SELECT Id, Code, MAX(Date) Date
     FROM Delivery
     WHERE HasReturns = 1
     GROUP BY Id, Code
  UNION
  SELECT Id, Code, MAX(Date) Date
     FROM DeliveryHistory
     WHERE HasReturns = 1
     GROUP BY Id, Code
  </code></pre>
  
  <p>And the following UPDATE statement:</p>
  
  <pre class="lang-sql prettyprint-override"><code>UPDATE Retailer SET LastReturnDate = (
      SELECT MAX(Date) FROM DeliveryView
      WHERE Retailer.Id = DeliveryView.Id AND Retailer.Code = DeliveryView.Code)
  WHERE Code = :Code AND EXISTS (
      SELECT * FROM DeliveryView
      WHERE Retailer.Id = DeliveryView.Id AND Retailer.Code = DeliveryView.Code
      HAVING
          MAX(Date) &gt; LastReturnDate OR
          (LastReturnDate IS NULL AND MAX(Date) IS NOT NULL))
  </code></pre>
  
  <p>The EXISTS-clause guards against updating fields where the current value is greater than the new one, but this is actually not a significant concern, because it's hard to see how that could ever happen during normal program execution. Note also how the <code>AND Max(Date) IS NOT NULL</code> part is in fact superfluous, since it's impossible for Date to be null in DeliveryView. But the EXISTS-clause appears to actually improve performance slightly.</p>
  
  <p>However, the performance of the UPDATE has recently been horrendous. In a database where the Retailer table contains only 1000-2000 relevant entries, the UPDATE has been taking more than five minutes to run. Note that it does this even if I remove the entire EXISTS clause, i.e. with this very simply statement:</p>
  
  <pre class="lang-sql prettyprint-override"><code>UPDATE Retailer SET LastReturnDate = (
      SELECT MAX(Date) FROM DeliveryView
      WHERE Retailer.Id = DeliveryView.Id AND Retailer.Code = DeliveryView.Code)
  WHERE Code = :Code
  </code></pre>
  
  <p>I've therefore been looking into a better solution. My first idea was to create a temporary table, but after a while I tried to write it as a MERGE statement:</p>
  
  <pre class="lang-sql prettyprint-override"><code>MERGE INTO Retailer
  USING (SELECT Id, Code, MAX(Date) Date FROM DeliveryView GROUP BY Id, Code)
      ON (Retailer.Id = DeliveryView.Id AND Retailer.Code = DeliveryView.Code)
  WHEN MATCHED THEN
      UPDATE SET LastReturnDate = Date WHERE Code = :Code
  </code></pre>
  
  <p>This seems to work, and it's more than an order of magnitude faster than the UPDATE.</p>
  
  <p><strong>I have three questions</strong>:</p>
  
  <ol>
  <li>Can I be certain that this will have the same effect as the UPDATE in all cases (disregarding the edge case of LastReturnDate already being larger than MAX(Date))?</li>
  <li>Why is it so much faster?</li>
  <li>Is there some better solution?</li>
  </ol>
  
  <h1>Query plans</h1>
  
  <h2>MERGE plan</h2>
  
  <p>Cost: 25,831, Bytes: 1,143,828</p>
  
  <h3>Plain language</h3>
  
  <ol>
  <li>Every row in the table SCHEMA.Delivery is read.</li>
  <li>The rows were sorted in order to be grouped.</li>
  <li>Every row in the table SCHEMA.DeliveryHistory is read.</li>
  <li>The rows were sorted in order to be grouped.</li>
  <li>Return all rows from steps 2, 4 - including duplicate rows.</li>
  <li>The rows from step 5 were sorted to eliminate duplicate rows.</li>
  <li>A view definition was processed, either from a stored view SCHEMA.DeliveryView or as defined by steps 6.</li>
  <li>The rows were sorted in order to be grouped.</li>
  <li>A view definition was processed, either from a stored view SCHEMA. or as defined by steps 8.</li>
  <li>Every row in the table SCHEMA.Retailer is read.</li>
  <li>The result sets from steps 9, 10 were joined (hash).</li>
  <li>A view definition was processed, either from a stored view SCHEMA.  or as defined by steps 11.</li>
  <li>Rows were merged.</li>
  <li>Rows were remotely merged.</li>
  </ol>
  
  <h3>Technical</h3>
  
  <pre><code>Plan                                            Cardinality Distribution
  14 MERGE STATEMENT REMOTE  ALL_ROWS
  Cost: 25 831  Bytes: 1 143 828                                              3 738   
      13 MERGE SCHEMA.Retailer ORCL                                           
          12 VIEW SCHEMA.                                         
              11 HASH JOIN  
              Cost: 25 831  Bytes: 1 192 422                                  3 738   
                  9 VIEW SCHEMA. 
                  Cost: 25 803  Bytes: 194 350                            7 475   
                      8 SORT GROUP BY  
                      Cost: 25 803  Bytes: 194 350                        7 475   
                          7 VIEW VIEW SCHEMA.DeliveryView ORCL
                          Cost: 25 802  Bytes: 194 350                    7 475   
                              6 SORT UNIQUE  
                              Cost: 25 802  Bytes: 134 550                7 475   
                                  5 UNION-ALL                 
                                      2 SORT GROUP BY  
                                      Cost: 97  Bytes: 25 362         1 409   
                                          1 TABLE ACCESS FULL TABLE SCHEMA.Delivery [Analyzed] ORCL
                                          Cost: 94  Bytes: 210 654    11 703  
                                      4 SORT GROUP BY  
                                      Cost: 25 705  Bytes: 109 188        6 066   
                                          3 TABLE ACCESS FULL TABLE SCHEMA.DeliveryHistory [Analyzed] ORCL
                                          Cost: 16 827  Bytes: 39 333 636     2 185 202   
                  10 TABLE ACCESS FULL TABLE SCHEMA.Retailer [Analyzed] ORCL
                  Cost: 27  Bytes: 653 390                            2 230
  </code></pre>
  
  <h2>UPDATE plan</h2>
  
  <p>Cost: 101,492, Bytes: 272,060</p>
  
  <h3>Plain language</h3>
  
  <ol>
  <li>Every row in the table SCHEMA.Retailer  is read.</li>
  <li>One or more rows were retrieved using index SCHEMA.DeliveryHasReturns . The index was scanned in ascending order.</li>
  <li>Rows from table SCHEMA.Delivery  were accessed using rowid got from an index.</li>
  <li>The rows were sorted in order to be grouped.</li>
  <li>One or more rows were retrieved using index SCHEMA.DeliveryHistoryHasReturns . The index was scanned in ascending order.</li>
  <li>Rows from table SCHEMA.DeliveryHistory  were accessed using rowid got from an index.</li>
  <li>The rows were sorted in order to be grouped.</li>
  <li>Return all rows from steps 4, 7 - including duplicate rows.</li>
  <li>The rows from step 8 were sorted to eliminate duplicate rows.</li>
  <li>A view definition was processed, either from a stored view SCHEMA.DeliveryView  or as defined by steps 9.</li>
  <li>The rows were sorted in order to be grouped.</li>
  <li>A view definition was processed, either from a stored view SCHEMA.  or as defined by steps 11.</li>
  <li>Rows were updated.</li>
  <li>Rows were remotely updated.</li>
  </ol>
  
  <h3>Technical</h3>
  
  <pre><code>Plan                                        Cardinality Distribution
  14 UPDATE STATEMENT REMOTE  ALL_ROWS
  Cost: 101 492  Bytes: 272 060                                       1 115   
      13 UPDATE SCHEMA.Retailer ORCL                                      
          1 TABLE ACCESS FULL TABLE SCHEMA.Retailer [Analyzed] ORCL
          Cost: 27  Bytes: 272 060                                1 115   
          12 VIEW SCHEMA. 
          Cost: 90  Bytes: 52                                 2   
              11 SORT GROUP BY  
              Cost: 90  Bytes: 52                             2   
                  10 VIEW VIEW SCHEMA.DeliveryView ORCL
                  Cost: 90  Bytes: 52                         2   
                      9 SORT UNIQUE  
                      Cost: 90  Bytes: 36                     2   
                          8 UNION-ALL                     
                              4 SORT GROUP BY  
                              Cost: 15  Bytes: 18             1   
                                  3 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA.Delivery [Analyzed] ORCL
                                  Cost: 14  Bytes: 108        6   
                                      2 INDEX RANGE SCAN INDEX SCHEMA.DeliveryHasReturns [Analyzed] ORCL
                                      Cost: 2     12  
                              7 SORT GROUP BY  
                              Cost: 75  Bytes: 18             1   
                                  6 TABLE ACCESS BY INDEX ROWID TABLE SCHEMA.DeliveryHistory [Analyzed] ORCL
                                  Cost: 74  Bytes: 4 590          255 
                                      5 INDEX RANGE SCAN INDEX SCHEMA.DeliveryHistoryHasReturns [Analyzed] ORCL
                                      Cost: 6     509
  </code></pre>