Solved

Merge performance

Posted on 2007-11-26
10
595 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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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

MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SYS password changed. Now can't log in as SYS 27 62
error doing substr 3 52
how to tune the query 17 87
Oracle performance tuning 2 55
Why doesn't the Oracle optimizer use my index? Querying too much data Most Oracle developers know that an index is useful when you can use it to restrict your result set to a small number of the total rows in a table. So, the obvious side…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
This video shows setup options and the basic steps and syntax for duplicating (cloning) a database from one instance to another. Examples are given for duplicating to the same machine and to different machines
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

751 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