Update statement performance

From: Praveen Ramanan <pramanan_at_gmail.com>
Date: Tue, 25 Feb 2014 22:00:42 -0600
Message-ID: <CAJXKVHHs0zoxvx+JVU8o4+0YRHW7mjKDtu=j5MLEfu-mLy=C2A_at_mail.gmail.com>



Hello All,

I am using oracle 11gr2 on windows server 2008. This is a test system.Stats are current and archivelog is turned off temporarily.

I am trying to update a table that has over 1.1 million rows. I have a simple update statement that does the following. (basically updates mycolumn when it is null, to somevalue)

update mytable a set a.mycolumn = (select b.mycolumn from

                                                                  mytable b
where

a.joincolumn = b.joincolumn and

b.anothercol='someval')
where a.mycolumn is null;

The number of rows where mycolumn is null is around 1/5th of the total. (225000 rows)

The above update statement seems to be running forever.On checking the explain plan this is what i see.


| Id  | Operation                    | Name                     | Rows  |
Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT             |                          |   224K|
2630K|  6454M  (1)|127:19:43 |
|   1 |  UPDATE                      | mytable            |       |
|            |          |

|* 2 | TABLE ACCESS FULL |mytable | 224K| 2630K|
6644 (1)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| mytable | 1 | 21 |
28752 (1)| 00:00:03 |
|* 4 | INDEX RANGE SCAN | mytable_someval_INDEX |
874K| | 3318 (1)| 00:00:01 | ---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):


   2 - filter("a"."mycolumn" IS NULL)
   3 - filter("b"."joincolumn"=:B1)
   4 - access("b"."anothercol"='somecol')


I know i can use CTAS to achieve my task.However i am not sure why updating a .225 million rows would take hours.

Can my update statement be written more efficiently? (perhaps using merge update?)

Maybe there is some other bottleneck that I need to look?

Any thoughts would be very well appreciated. Thanks!!!

-- 
*Thousands of candles can be lighted from a single candle, and the life of
the single candle will not be shortened. Happiness never decreases by being
shared.*

--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 26 2014 - 05:00:42 CET

Original text of this message