Re: Update statement performance
- From: Milo <xueyuan.luo@xxxxxxxxx>
- To: pramanan <pramanan@xxxxxxxxx>
- Date: Wed, 26 Feb 2014 12:16:28 +0800
Hi, Ramanan
Could you get the sql report on this update sql? May be some statistics of the
sql can explain why.
Thanks very much.
Best Regards,
Milo
From: Praveen Ramanan
Date: 2014-02-26 12:00
To: oracle-l
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.
Other related posts:
