Call now: 252-767-6166  
Oracle Training Oracle Support Development Oracle Apps

 E-mail Us
 Oracle Articles
New Oracle Articles

 Oracle Training
 Oracle Tips

 Oracle Forum
 Class Catalog

 Remote DBA
 Oracle Tuning
 Emergency 911
 RAC Support
 Apps Support
 Oracle Support

 SQL Tuning

 Oracle UNIX
 Oracle Linux
 Remote s
 Remote plans
 Application Server

 Oracle Forms
 Oracle Portal
 App Upgrades
 SQL Server
 Oracle Concepts
 Software Support

 Remote S


 Consulting Staff
 Consulting Prices
 Help Wanted!


 Oracle Posters
 Oracle Books

 Oracle Scripts

Don Burleson Blog 








Using the APPEND hint with the MERGE (upsert) SQL

Oracle Tips by Stephanie Filio


I have a really big partitioned table, that I used MERGE to populate/update it.  I understand the MERGE will UPDATE and INSERT upon matching the PK.  If insert and update table rows become scattered, the MERGE will mitigate which statements need INSERT or UPDATE in the staging area.  Assume MERGE statement is processing a whole bunch of rows -millions of them, where some of the rows are UPDATEing and some of the rows are INSERTing on the target table. Our actual mix appears to be 60% update vs. 40% insert in about 25 million rows a day.

>> Does MERGE actually issues a mixed set of UPDATE and INSERT commands -as needed- against the target table?

Answer:  The Oracle docs reveal that MERGE statement makes it easy to update OR insert rows in one pass through the data, without the overhead of multiple insert/update statements.  Here are the reasons Oracle suggests using MERGE instead of older methods:

  • The entire operation can be expressed much more simply as a single SQL statement.
  • You can parallelize statements transparently.
  • You can use bulk DML.
  • Performance will improve because your statements will require fewer scans of the source table.

As far as the question about the MERGE statement issuing UPDATE/INSERT commands, the answer is much less conclusive.  Oracle has documentation supporting both sides.  Here, the docs imply that MERGE will mix the INSERT and UPDATE into an atomic statement.

The MERGE-statement combines the sequence of a conditional INSERT and UPDATE in one single atomic statement, Oracle9i overcomes these deficiencies and makes the implementation of warehousing applications more simple and intuitive.  The ?IF ROW EXISTS -THEN-UPDATE ELSE-INSERT? - conditional sequence is also referred to as UPSERT

Yet here, the docs suggest that MERGE consists of a solitary optimized  internal scan. In this, INSERT and UPDATE become conditional and processes as individual statements.  In this, there is a single scan with the DML command appropriate for serial or parallel issues. 

The new MERGE command overcomes these deficiencies, processing the conditional INSERT-or-UPDATE within a single statement. The data is scanned only once, and the appropriate DML command is issued, either serially or in parallel.

The new MERGE command brings major performance benefits by providing an optimized internal feature for the common Upsert task within ETL processing.


>> Since my developers coded a APPEND hint, each time a new INSERT is issued by MERGE statement HWM would go a little higher. Following this line of thinking if millions of INSERT commands are issued by MERGE, HWM would go a little higher millions of times leaving a huge quantity of under used data block behind and consequently "wasting" a lot of space.  Is this the correct way that an upsert (merge) works with the append hint?


The "append" hint simply means "DO NOT re-use data block from the freelist (as released by the PCTFREE threshold)". Whenever the merge needs a new block, it raises the HWM and grabs file brand new empty blocks.

Note:  INSERT APPEND supports only the subquery syntax of the INSERT statement, not the VALUES clause.

Despite the new ease of the MERGE (upsert) command, there are still some complexities which need explanation.  Here is an example of a forum user who has some questions regarding MERGE and APPEND hints-

Basically, the APPEND hint will keep the data blocks that are on the freelists from being reused.  This being the case, if there is a MERGE with a new block, the HWM takes fresh empty blocks and is raised.  Try viewing this article on INSERT performance by Don Burleson on the APPEND hints.  This article by Mark Rittman shows a sample MERGE command with an example of an APPEND hint:

 alter session enable parallel dml;
merge /*+ parallel(contract_dim,10) append */
into contract_dim d
using TABLE(trx.go(
CURSOR(select /*+ parallel(contracts_\
file,10) full (contracts_file) */ *
from contracts_file ))) f
on d.contract_id = f.contract_id
when matched then
update set desc = f.desc,
init_val_loc_curr = f.init_val_loc_curr,
init_val_adj_amt = f.init_val_adj_amt
when not matched then
insert values ( f.contract_id,

You may want to take a look at this forum thread to get more information from the experts and fellow users about this.

Regarding raising the HWM with MERGE statements, this will only occur when the block becomes logically full (pctfree), otherwise before the blocks change, there could be multiple rows in the append mode.  This is because the APPEND hint only implies that a a fresh block will be grabbed once it is necessary to get a new data block.

Performance wise, the merge/upsert would bring improved performance because the ETL processing contains an Upsert, which has the optimized internal feature.  If it is MERGE performance that you are interested in, you may be interested in this article by David Aldridge, which discourages MERGE for refreshing MV's.

If you like Oracle tuning, you might enjoy my book "Oracle Tuning: The Definitive Reference", with 950 pages of tuning tips and scripts. 

You can buy it direct from the publisher for 30%-off and get instant access to the code depot of Oracle tuning scripts.



Oracle Training at Sea
oracle dba poster

Follow us on Twitter 
Oracle performance tuning software 
Oracle Linux poster


Burleson is the American Team

Note: This Oracle documentation was created as a support and Oracle training reference for use by our DBA performance tuning consulting professionals.  Feel free to ask questions on our Oracle forum.

Verify experience! Anyone considering using the services of an Oracle support expert should independently investigate their credentials and experience, and not rely on advertisements and self-proclaimed expertise. All legitimate Oracle experts publish their Oracle qualifications.

Errata?  Oracle technology is changing and we strive to update our BC Oracle support information.  If you find an error or have a suggestion for improving our content, we would appreciate your feedback.  Just  e-mail:  

and include the URL for the page.


Burleson Consulting

The Oracle of Database Support

Oracle Performance Tuning

Remote DBA Services


Copyright © 1996 -  2020

All rights reserved by Burleson

Oracle ® is the registered trademark of Oracle Corporation.