MERGE statement and APPEND hint Options

[Starikovsky]

From the functional point of view it is clear to me how the MERGE statement works.
It is also clear to me APPEND hint will allocate space beyond HWM each time an INSERT statement is issued.

> What I'm Wondering Is
How does MERGE statement works with an APPEND hint?

> Scenario
Assume a large partitioned table populated/updated from a staging area via MERGE statement.
MERGE statement will UPDATE when PK match and will INSERT when PK doesn't match.
Assuming insertable and updateable rows are scatered in staging area MERGE will issue either INSERT or UPDATE statements as needed.

> Question
If this is the case, it's safe to say MERGE/Insert will push HWM a little higher each time a new row is inserted? If it works this way process will be allocating a lot of space and leaving huge amounts of unused space under the HWM, is that correct?


Thank you in advance.

[burleson]

Hi,

> each time a new INSERT is issued by MERGE statement HWM would go a little higher.

No, only after the block is full . . . You could insert 100 rows in append mode before changing blocks.

The append hint does not mean one row per block!

The append hints says "When it's time to grab a new block, grab a fresh, empty one."

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

Good question. Oracle does not disclose the internal machinations of the upsert. The docs are conflicting:

This suggests "yes":

http://www.oracle.com/technology/products/...comp_dwflow.pdf

The MERGE-statement combines the sequence of a conditional INSERT and UPDATE in one single atomic statement, Oracle9i overcomesthese 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

This says "no", it's a single scan, optimized internal feature:

http://www.oracle.com/technology/products/...aily/Aug24.html

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.

**********************************************************
As to merge performance, this article does a test and shows that a two-step is faster:

http://www.oracle-developer.net/display.php?id=203

We can see that MERGE performed less well than our two-part SQL solution; with it taking over twice as long.

David Aldridge has these notes, where he says to AVOID the merge for MV's and perhaps more:

http://oraclesponge.wordpress.com/2005/11/...esh-mechanisms/