how to re write the update statment if high volume records are there [message #442767] |
Wed, 10 February 2010 08:06  |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
UPDATE GT_TEST TEST
SET TEST.KUTRS =
(SELECT SUM((CASE
WHEN DTL.TYPE LIKE '20G-%' THEN
11
WHEN DTL.TYPE LIKE '40K-%' THEN
21
WHEN DTL.TYPE LIKE '45M-%' THEN
21.25
WHEN DTL.TYPE LIKE '35N-%' THEN
122.75
END))
FROM PACKDTL DTL
WHERE DTL.DOCUMENTID = TEST.DOCUMENTID
)
WHERE TEST.type_code in ('ABL')
AND TEST.MODE = 21
AND TEST.TYPE IN
('ABC', 'DEL', 'SEF');
|
|
|
|
|
Re: how to re write the update statment if high volume records are there [message #442774 is a reply to message #442770] |
Wed, 10 February 2010 08:30   |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
Michel Cadot wrote on Wed, 10 February 2010 19:43Current time?
Current number of updated rows?
Current execution plan?
Current indexes?
Current triggers?
Regards
Michel
Current time=1 min.
Current number of updated rows=0.2 million records.
Current execution plan = these sequence of update statements are executing within in a package.
Current indexes= no indexes were created because it is updating the columns on global temporary table.
Current triggers=no triggers.
|
|
|
|
|
|
|
|
|
Re: how to re write the update statment if high volume records are there [message #442986 is a reply to message #442918] |
Thu, 11 February 2010 20:49   |
rleishman
Messages: 3728 Registered: October 2005 Location: Melbourne, Australia
|
Senior Member |
|
|
All of that may be true, but it is also true that MERGE is the single most powerful weapon in your arsenal to combat high volume update-joins.
In many cases you can use an Updatable Join View, but most implementations of UJVs will knock up against Key Preservation errors. These in turn can be countered using undocumented hints. But then when it comes to a choice between undocumented hints and the MERGE statement, there is no choice in my book.
Quite simply, if you are updating tens of thousands of rows with nested-subquery UPDATEs, I hope you have a good, big, book and lots of snacks.
Ross Leishman
|
|
|
|
Re: how to re write the update statment if high volume records are there [message #443150 is a reply to message #442825] |
Sat, 13 February 2010 01:05   |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
rleishman wrote on Thu, 11 February 2010 06:01Why not use the same technique as for your other thread - MERGE.
Ross Leishman
Rleishman,
Is it okay?
MERGE INTO GT_TEST test
USING (SELECT spt.DOCUMENTID ,
SUM((CASE
WHEN DTL.TYPE LIKE '20G-%' THEN
11
WHEN DTL.TYPE LIKE '40K-%' THEN
21
WHEN DTL.TYPE LIKE '45M-%' THEN
21.25
WHEN DTL.TYPE LIKE '35N-%' THEN
122.75
END)) cont
FROM PACKDTL DTL, GT_TEST SPT
WHERE DTL.DOCUMENTID = TEST.DOCUMENTID
AND TEST.type_code in ('ABL')
AND TEST.MODE = 21
AND TEST.TYPE IN ('ABC', 'DEL', 'SEF')
GROUP BY spt.DOCUMENTID ) new
on (test.DOCUMENTID = new.DOCUMENTID )
WHEN MATCHED THEN
UPDATE set test.KUTRS = NVL(new.cont, test.KUTRS);
|
|
|
Re: how to re write the update statment if high volume records are there [message #443158 is a reply to message #443150] |
Sat, 13 February 2010 01:26   |
chaituu
Messages: 115 Registered: June 2008
|
Senior Member |
|
|
i have modified little bit.
MERGE INTO GT_TEST test
USING (SELECT DTL.DOCUMENTID,
SUM((CASE
WHEN DTL.TYPE LIKE '20G-%' THEN
11
WHEN DTL.TYPE LIKE '40K-%' THEN
21
WHEN DTL.TYPE LIKE '45M-%' THEN
21.25
WHEN DTL.TYPE LIKE '35N-%' THEN
122.75
END)) cnt
FROM PACKDTL DTL
GROUP BY DTL.DOCUMENTID) NEW
ON (test.DOCUMENTID = NEW.DOCUMENTID)
WHEN MATCHED THEN
UPDATE
SET SPT.KUTRS = NVL(NEW.cnt, SPT.KUTRS)
WHERE TEST.type_code in ('ABL')
AND TEST.MODE = 21
AND TEST.TYPE IN ('ABC', 'DEL', 'SEF');
|
|
|
|
|
|