View previous topic :: View next topic
|
Author |
Message |
subratarec
Active User
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
|
|
|
|
I have a question on MERGE query.
In my project I had come across a scenario where I had to replace an Insert stmt with Merge query (to avoid -803). That's the time I got to about MERGE stmt. My code gone to production. Few days back I got a mail by saying my MERGE query is taking very long time. They said that I should not include those Columns are in ON condition (they are all primary columns) in UPDATE stmt. Example
My MERGE stmt:
-------------------------------------------------------
Code: |
EXEC SQL
MERGE INTO <TABLE-NAME> AS T
USING (VALUES (:HOST-VAR1
,:HOST-VAR2
,:HOST-VAR3
,:HOST-VAR4
,:HOST-VAR5))
AS S(COL1
,COL2
,COL3
,COL4
,COL5)
ON T.COL1_Primary = S.COL1
AND T.COL2_Primary = S.COL2
WHEN MATCHED THEN
UPDATE SET [b] T.COL1_Primary = S.COL1[/b] --> I should not include.
[b]T.COL2_Primary = S.COL2[/b] --> I should not include.
T.COL3_Normal = S.COL3
T.COL4_Normal = S.COL4
T.COl5_Normal = S.COL5
WHEN NOT MATCHED THEN
INSET (COL1
,COL2
,COL3
,COL4
,COL5)
VALUES(:HOST-VAR1
,:HOST-VAR2
,:HOST-VAR3
,:HOST-VAR4
,:HOST-VAR5) |
Both the columns mentioned above are Primary Key for this table. I know "WHEN MATCHED THEN" then no need to include Primary key column under UPDATE stmt because it is overwriting the same value but will this slow down the performance?
Is it something like every time I overwrite primary key column value with same value it is rebuilding the entire index ??
Thanks in advance |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
How about the indexes on -T.COL1_Primary and T.COL2_Primary ? Are you sure they have right indexes defined?
Code: |
UPDATE SET [b] T.COL1_Primary = S.COL1[/b] --> I should not include.
[b]T.COL2_Primary = S.COL2[/b] --> I should not include. |
That's right, why wouyld you update these keys anyways? Have you thought of Reorg and Runstat on this table <if this is oftenly get inserted/updated/deleted>? if not then please have a process in place. |
|
Back to top |
|
|
subratarec
Active User
Joined: 22 Dec 2007 Posts: 126 Location: Bangalore
|
|
|
|
Hi Rohit, Yes T.COL1_Primary and T.COL2_Primary are used in Index. So Index contains 2 columns (T.COL1_Primary and T.COL2_Primary).
I thought the same that every time my SQL updating (not actually updates with new value but over writing the same value) DB2 is rebuilding the same index again and again. But I just wanted to confirm whether my understanding is correct or not.
Thanks |
|
Back to top |
|
|
Mickeydusaor
Active User
Joined: 24 May 2006 Posts: 258 Location: Salem, Oregon
|
|
|
|
Why no handle the -803 and get rid of the overhead of the merge |
|
Back to top |
|
|
Rohit Umarjikar
Global Moderator
Joined: 21 Sep 2010 Posts: 3048 Location: NYC,USA
|
|
|
|
Quote: |
Why no handle the -803 and get rid of the overhead of the merge |
Right, but if he does that then there will be two EXEC SQL threads(INSERT and UPDATE) instead one (with MERGE).
Subratarec,
Quote: |
I thought the same that every time my SQL updating (not actually updates with new value but over writing the same value) DB2 is rebuilding the same index again and again. But I just wanted to confirm whether my understanding is correct or not. |
Yes Correct and I hope your respective DBA has told you the same.
But however you still got to check upon REORG, RUNSTAT, REBIND options as to how often is required. |
|
Back to top |
|
|
|