I need to perfom the simple task of adding new data to a large existing table. My source data is on Teradata and is in a table that looks exactly like my target table (field names, datatype, PI, ...) . For example, I need to add 10 million rows on to a table that already has 800 million rows.
My question is related to performance. Is there a performance difference between a SQL INSERT statement and the MERGE statement? Like:
INSERT INTO targ_tbl SELECT * FROM srce_tble;
and a MERGE statment:
MERGE into ...
The target table can be loaded via an MLOAD. This means the target table does NOT have constructs on it like RI or USIs. I just wanted to mention that so you are aware of the type of table we are trying to perform this operation on.
The end result would be the same but I just wanted to inquire if there are any performance issues in using one versus the other.
I think in your case the INSERT INTO will be better than MERGE INTO... The later is kind of a two step process where the matching rows are updated while the new ones are inserted, while the former one just inserts the rows in existing table without checking whether the rows exists or not...
In your case there's probably no difference, but if it's a bit more complex (e.g. SIs or any kind of calculation/WHERE-condition in the Select part) MERGE tends to be more efficient than Ins/Sel or Update.
There's an Orange Book on this topic called "ANSI MERGE Enhancements"