Solved

Merge performance

Posted on 2007-11-26
10
599 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
Industry Leaders: 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

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Shell script to create broker configuration file using current broker Configuration, solely for purpose of backup on Linux. Script may need to be modified depending on OS-installation. Please deploy and verify the script in a test environment.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

630 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