RE: How to speed up an update

I want to do it as fast as possible and yes the column being modified is part of the primary key. CTAS would
be my preferred approach, but I am not able to do it. I did get a good result after I dropped the index.

thank you

Gene Gurevich

                                                                           
             "Stephens, Chris"                                             
             <chris_stephens_at_a                                             
             dmworld.com>                                               To 
                                       <genegurevich_at_discover.com>,        
             03/05/2009 12:59          <oracle-l_at_freelists.org>            
             PM                                                         cc 
                                                                           
                                                                   Subject 
                                       RE: How to speed up an update       
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           
                                                                           

Are you wanting this to run as fastest possible or would you like other operations to be capable of getting on the cpu while doing this?

Is the column you are updating the primary key? If not, why would dropping the pk make a difference?

If there are very few columns (I guess more specifically bytes other than the bytes affected by the update) and you are updating each row as indicated by the vague update statement, you may be better off with ctas + drop original table + rename existing table.

Hopefully you have a test system to verify which option is more efficient.

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of genegurevich_at_discover.com
Sent: Thursday, March 05, 2009 12:23 PM
To: oracle-l_at_freelists.org
Subject: How to speed up an update

Hi everyone:

I am trying to tune the following update:

update TABLE set column = decode (column, vlaue1..);

The TABLE has about 13mil rows and a primary key. I am thinking of dropping
the PK and running the update
with higher degree of parallelism and rebuilding the PK after the update.
Is there anything else I should consider? I am running oracle 10.2.0.2

thank you

Gene Gurevich

Please consider the environment before printing this email.

--

http://www.freelists.org/webpage/oracle-l

CONFIDENTIALITY NOTICE:
             This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this communication in error, please notify us immediately by email reply.

Please consider the environment before printing this email.

--

http://www.freelists.org/webpage/oracle-l Received on Thu Mar 05 2009 - 15:42:06 CST