Home » SQL & PL/SQL » SQL & PL/SQL » how to re write the update statment if high volume records are there
how to re write the update statment if high volume records are there [message #442767] Wed, 10 February 2010 08:06 Go to next message
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 #442769 is a reply to message #442767] Wed, 10 February 2010 08:09 Go to previous messageGo to next message
cookiemonster
Messages: 13965
Registered: September 2008
Location: Rainy Manchester
Senior Member
Define high volume
Re: how to re write the update statment if high volume records are there [message #442770 is a reply to message #442767] Wed, 10 February 2010 08:13 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Current time?
Current number of updated rows?
Current execution plan?
Current indexes?
Current triggers?

Regards
Michel
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 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
Michel Cadot wrote on Wed, 10 February 2010 19:43
Current 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 #442786 is a reply to message #442774] Wed, 10 February 2010 08:53 Go to previous messageGo to next message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Quote:
Current execution plan = these sequence of update statements are executing within in a package.

This does not prevent you from getting the execution plan, just activate the trace.

Quote:
Current time=1 min.
Current number of updated rows=0.2 million records.

1 minute to update 0.2 million rows is good performances (0.3 ms per row updated).

Quote:
Current indexes= no indexes were created because it is updating the columns on global temporary table.

Just a remark, you can have indexes on a GTT.

Regards
Michel
Re: how to re write the update statment if high volume records are there [message #442797 is a reply to message #442767] Wed, 10 February 2010 11:49 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
Also, if your PACKDTL table is large, you could benefit from an index only access during your correlated select.

create xyz on packdtl (documentid,type);

This index should allow you to do your sum without accessing the underlying table. Depending upon how expensive this is for you this could be a significant improvement or not.

Good luck, Kevin
Re: how to re write the update statment if high volume records are there [message #442809 is a reply to message #442797] Wed, 10 February 2010 13:37 Go to previous messageGo to next message
Littlefoot
Messages: 21823
Registered: June 2005
Location: Croatia, Europe
Senior Member
Account Moderator
Should be CREATE INDEX, i presume?
Re: how to re write the update statment if high volume records are there [message #442810 is a reply to message #442767] Wed, 10 February 2010 13:38 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
yes ofcourse thanks.
Re: how to re write the update statment if high volume records are there [message #442825 is a reply to message #442810] Wed, 10 February 2010 18:31 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why not use the same technique as for your other thread - MERGE.

Ross Leishman
Re: how to re write the update statment if high volume records are there [message #442918 is a reply to message #442767] Thu, 11 February 2010 05:36 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
MERGE is an option but let me reiterate what I have said about MERGE in other threads.

1) most people think MERGE is an implementation of UPSERT and at a high level I suppose it is. But in the details MERGE does not act like a variation of UPSERT. This means nothing to most people until they run MERGE against a table with triggers. Then they learn that MERGE causes triggers to fire in a sequence they do not expect (they were thinking it would do something simliar to UPSERT (if they thought about it at all)), and more importantly a sequences that makes the logic in their triggers broken. Thus if MERGE is being used in a database, then the use of table triggers must be re-evaluated even to the point of not using them.

2) MERGE is not compatible with INSTEAD-OF-TRIGGERS. It will generate an error if applied to a view with an INSTEAD-OF-TRIGGER. Thus one must make a choice with an understanding of how it affects future design in your database. A decision must be made about which is more important to you: MERGE or INSTEAD-OF-TRIGGERS. You can't have both. INSTEAD-OF-TRIGGERS are in my mind way more valuable than MERGE so in my databases I never allow MERGE to be written in any piece of code and should I find one at a later date I put a change request in the get it removed and replaced by the older UPSERT sequence. Then I schedule an interview with the developer who coded it and ask them to explain what they did not understand about my original instructions.

People want to believe that MERGE is great but it is a pain in my book. MERGE is a feature that denys the DBA and Database Designor opportunities to extend their database in the future. Thus in my mind it should never be used.

Consider this. If you have code that executes a MERGE, then you cannot later decide to deploy ORACLE WORKSPACE MANAGER on your database. OWM is among other things a way to create temporal databases. OWM is as its name implies and Oracle product.

Interesting that one Oracle feature is not compatible with another. That aside, the point is that adding transparent behavior to your database is a great feat that can: same many man hours, lots of money, offer valuable new functionality, and is done using INSTEAD-OF-TRIGGERS. All that goes out the window if you allow MERGE in your system. You tie your hands with MERGE and for what?, a faster way to an update once in a while?

I look forward to the opinions of others.

Kevin
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 Go to previous messageGo to next message
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 #443072 is a reply to message #442767] Fri, 12 February 2010 07:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2103
Registered: December 1999
Location: Connecticut USA
Senior Member
And here we see the true nature of the question at hand.

IT is imperfect. Imperfection ultimately makes the demand of choice. Like it or not, we are required to choose how we want to build systems and what we can live with and what we can't.

Kevin
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 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
rleishman wrote on Thu, 11 February 2010 06:01
Why 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 Go to previous messageGo to next message
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');

Re: how to re write the update statment if high volume records are there [message #443177 is a reply to message #443158] Sat, 13 February 2010 05:04 Go to previous messageGo to next message
rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Why not give it a run and find out. If it does what you think it should and does it fast, then it is probably pretty good.

Ross Leishman
Re: how to re write the update statment if high volume records are there [message #443181 is a reply to message #443177] Sat, 13 February 2010 06:41 Go to previous messageGo to next message
chaituu
Messages: 115
Registered: June 2008
Senior Member
among those queries which query is correct and fast?

one query is standard sub query and other one is correlated sub query?
Re: how to re write the update statment if high volume records are there [message #443193 is a reply to message #443181] Sat, 13 February 2010 09:09 Go to previous message
Michel Cadot
Messages: 68737
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
How is this related to the previous question?

Explain what is your question? Give examples of whay you mean.

Regards
Michel
Previous Topic: how can increase the performance of existing procedure?
Next Topic: Duplicate NULL
Goto Forum:
  


Current Time: Sat Feb 15 15:05:00 CST 2025