We have observed that the MERGE statement is causing row re-distribution across all the AMPS even though we have the same PI defined at the Source and Target tables. It seems that some kind of derived tables is getting created when we use the MERGE statement.As we are procesisng a huge amount of data, row re-ditribution is causing alot of issues with the DB.
But row re-distribution is not happening when we are using join between SOURCE and Target tables.
I was under the impression that MERGE will be always faster than other DML Operations.
Can you please let us know how to avoid the row re-distribution with the MERGE statement.
when both sorce and target share the same PI definition you should get AMP-local processing without redistribution, this is one of the advantages of Merge.
Could you show some more details, DDL and actual Merge?
Btw, Merge is not always faster, but it's usually not slower :-)