Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
"better to use pl/sql" - I don't understand that. Why do you think that PL/SQL is the better solution? (And you didn't even show that) –  a_horse_with_no_name Aug 29 '13 at 9:30
    
I didn't mention pl/sql is better. I have read so in some places and hence this question. Here is one such link - dba-oracle.com/t_merge_upsert_performance.htm –  Sanjay Nambiar Aug 29 '13 at 9:49
    
The link (from a very doubtful website btw) doesn't show any implementation of your question in PL/SQL. –  a_horse_with_no_name Aug 29 '13 at 9:55
    
Loop through the cursor in the using statement and then update T1 based on the condition inside ON will be a pl/sql alternative I can think of. Leaving that aside, even in the company I work for, Merge queries are usually not encouraged. That's why I wanted to know whether merge has any performance issues. If any, are there any specific circumstances where there will be performance issues. –  Sanjay Nambiar Aug 29 '13 at 10:11
    
Usually CTAS is the way to go with such scenario. –  kubanczyk Aug 29 '13 at 20:02
show 1 more comment

Know someone who can answer? Share a link to this question via email, Google+, Twitter, or Facebook.

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.