Solved

Merge performance

Posted on 2007-11-26
10
593 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

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

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
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.
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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…

820 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