Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 609
  • Last Modified:

Merge performance

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
yuching
Asked:
yuching
  • 4
  • 2
  • 2
1 Solution
 
sujith80Commented:
What indexes do you have on OldTable ?
Try to have a composite index on (colA, colB, colC, colD).
0
 
yuchingAuthor Commented:
i have a composite index on (colA, ColB, ColC, ColD, ColE) on old and new table.
0
 
yuchingAuthor Commented:
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!

 
sujith80Commented:
Analyze the oldTable.
Post the execution plan.
0
 
yuchingAuthor Commented:
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
 
yuchingAuthor Commented:
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
 
modus_operandiCommented:
Closed, 500 points refunded.
modus_operandi
EE Moderator
0
 
modus_operandiCommented:
Question PAQ'ed with refund of points.
modus_operandi
EE Moderator
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 4
  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now