Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Merge performance

Posted on 2007-11-26
10
Medium Priority
?
605 Views
Last Modified: 2013-12-07
Hi, i have a merge statement which taking lots of time to execute, below is the sample of it

OldTable --> Column (ColA, ColB, ColC, ColD, ColE, Value)
               - Index on ColA until ColE
NewTable --> Column (ColA, ColB, ColC, ColD, ColE, Type, Value)
              - Index on ColA until ColE

-- Merge Newtable into Oldtable

MERGE INTO OldTable Old
USING (SELECT ColA, ColB, ColC, ColD, ColE, Type, value FROM NewTable) New      
ON (Old.ColA = New.ColA AND Old.ColB = New.ColB AND Old.ColC = New.ColC And Old.ColD = New.ColD AND (Old.ColE = New.ColE OR (Old.ColE IS NULL AND New.ColE IS NULL)))

WHEN MATCHED THEN
   UPDATE SET Old.value =
         (CASE WHEN New.Type = 1 THEN Old.value + New.value
                  WHEN New.Type = 2 THEN
                             (CASE WHEN New.Value < Old.Value THEN New.Value END)
                  ELSE  Old.Value
        END)
WHEN NOT MATCHED THEN                
   INSERT (Old.ColA, Old.ColB, Old.ColC, Old.ColD, Old.ColE, Old.Value)
   VALUES(New.ColA, New.ColB, New.ColC, New.ColD, New.ColE, New.value);

Anyone know how to optimize it? Thanks
0
Comment
Question by:yuching
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 2
  • 2
10 Comments
 
LVL 27

Expert Comment

by:sujith80
ID: 20348839
What indexes do you have on OldTable ?
Try to have a composite index on (colA, colB, colC, colD).
0
 
LVL 11

Author Comment

by:yuching
ID: 20348856
i have a composite index on (colA, ColB, ColC, ColD, ColE) on old and new table.
0
 
LVL 11

Author Comment

by:yuching
ID: 20348864
But i dont have an index on Newtable. Type , is this will affect the performance as i used it in case when?
And the order of the colA, ColB, ColC --> ColE for the index is not based on the most granulate one. In this case, ColC is more unique as compare to ColA, should i put colC as 1st order of the composite index, how it will affect the performance?
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 27

Expert Comment

by:sujith80
ID: 20348935
Analyze the oldTable.
Post the execution plan.
0
 
LVL 11

Author Comment

by:yuching
ID: 20349019
Execution Plan
----------------------------------------------------------
   0      MERGE STATEMENT Optimizer=CHOOSE (Cost=24515 Card=8168 Bytes
          =4761944)

   1    0   MERGE OF 'NEWTABLE'
   2    1     VIEW
   3    2       NESTED LOOPS (OUTER) (Cost=24515 Card=8168 Bytes=2352384)
   4    3         TABLE ACCESS (FULL) OF 'OLDTABLE' (Cost=11 Card=8168 Bytes=1200696)
   5    3         VIEW (Cost=3 Card=1 Bytes=141)
   6    5           TABLE ACCESS (BY INDEX ROWID) OF 'NEWTABLE'  (Cost=3 Card=1 Bytes=59)
   7    6             INDEX (RANGE SCAN) OF 'INDEX_U01' (UNIQUE) (Cost=2 Card=1)
0
 
LVL 11

Accepted Solution

by:
yuching earned 0 total points
ID: 20679036
hi, my resolution

MERGE INTO OldTable Old
USING (SELECT ColA, ColB, ColC, ColD, ColE, Type, value FROM NewTable) New      
ON (Old.ColA = New.ColA AND Old.ColB = New.ColB AND Old.ColC = New.ColC And Old.ColD = New.ColD AND
---Replace this line
--(Old.ColE = New.ColE OR (Old.ColE IS NULL AND New.ColE IS NULL)))
(NVL(Old.ColE, '') = NVL(New.ColE, '') ))

WHEN MATCHED THEN
   UPDATE SET Old.value =
         (CASE WHEN New.Type = 1 THEN Old.value + New.value
                  WHEN New.Type = 2 THEN
                             (CASE WHEN New.Value < Old.Value THEN New.Value END)
                  ELSE  Old.Value
        END)
WHEN NOT MATCHED THEN                
   INSERT (Old.ColA, Old.ColB, Old.ColC, Old.ColD, Old.ColE, Old.Value)
   VALUES(New.ColA, New.ColB, New.ColC, New.ColD, New.ColE, New.value);
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 20756010
Closed, 500 points refunded.
modus_operandi
EE Moderator
0
 
LVL 1

Expert Comment

by:modus_operandi
ID: 20756011
Question PAQ'ed with refund of points.
modus_operandi
EE Moderator
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Truncate is a DDL Command where as Delete is a DML Command. Both will delete data from table, but what is the difference between these below statements truncate table <table_name> ?? delete from <table_name> ?? The first command cannot be …
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…

704 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question