RE: Update statement performance

From: Chitale, Hemant K <Hemant-K.Chitale_at_sc.com>
Date: Wed, 26 Feb 2014 13:35:56 +0800
Message-ID: <0BDF2A25A09ADD40908745EEFC0A0FB60176B1A0_at_HKJUMXMB103B.zone1.scb.net>



Your UPDATE statement doesn't make sense. If table "a" and "b" are the same table, then "a.mycolumn" is the same as "b.mycolumn" -- meaning that your are updating a value back to itself (for a subset of rows in the table). So for those rows by "anothercol"='someval', you are updating "mycolumn" to itself.  

I presume that either "b" is not the same table as "a" or that the "mycolumn" in b is not the same as "mycolumn" in a.      

Hemant K Chitale    

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Praveen Ramanan Sent: Wednesday, February 26, 2014 12:01 PM To: oracle-l_at_freelists.org
Subject: Update statement performance  

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.


This email and any attachments are confidential and may also be privileged. If you are not the intended recipient, please delete all copies and notify the sender immediately. You may wish to refer to the incorporation details of Standard Chartered PLC, Standard Chartered Bank and their subsidiaries at https://www.sc.com/en/incorporation-details.html.
--
http://www.freelists.org/webpage/oracle-l
Received on Wed Feb 26 2014 - 06:35:56 CET

Original text of this message