I have a query which updates a table with data from another table. The current query is written as a correlated update with an exists clause to remove unnecessary rows. The problem is since both of these tables are large data marts, the query is running real slow. I am thinking of using a merge query, but are their any performance impacts on running merge query? I have read in some places that it is better to use pl/sql insert and then update vs merge.
Below are my two queries:
1) Without merge:
UPDATE T1 SET (A, B, C) = ( SELECT A, B, C FROM T2 WHERE T2.PARTITION_COLUMN_T2 = 1234 AND T2.ID = T1.ID ) WHERE EXISTS ( SELECT 1 FROM T2 WHERE T2.PARTITION_COLUMN_T2 = 1234 AND T2.ID = T1.ID ) AND PARTITION_COLUMN_T1 = 'ABC';
2) With Merge:
MERGE into T1 USING ( SELECT A, B, C FROM T2 WHERE T2.PARTITION_COLUMN_T2 = 1234 ) SRC ON (SRC.ID = T1.ID AND T1.PARTITION_COLUMN_T1 = 'ABC') WHEN MATCHED THEN UPDATE SET T1.A = SRC.A, T1.B = SRC.B, T1.C = SRC.C;
By the plan, clearly merge performs a lot better. But is there any performance implications with merge? The tables are large data marts and they don't have primary keys or constraints.