RE: Update statement performance
- From: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- To: "pramanan@xxxxxxxxx" <pramanan@xxxxxxxxx>, "oracle-l@xxxxxxxxxxxxx" <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 26 Feb 2014 07:17:48 +0000
Look at the access path into the subquery table - it's using an index on
"anothercol" which is deemed to be very expensive, and then visiting the table
to check "joincolumn" to limit the result set to a single row.
It looks as if you don't have a suitable index into mytable to make this
subquery approach efficient.
Two possible strategies - use a hash join (either through a MERGE command or by
using an UPDATEABLE JOIN VIEW, if you have suitable constraints in place) or
create an index that starts with joincolumn and anothercol in some order to
avoid visiting the subquery table (or an index on joincolumn, perhaps, to
minimise visits to the subquery table)
Regards
Jonathan Lewis
http://jonathanlewis.wordpress.com
@jloracle
________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx [oracle-l-bounce@xxxxxxxxxxxxx] on behalf
of Praveen Ramanan [pramanan@xxxxxxxxx]
Sent: 26 February 2014 04:00
To: oracle-l@xxxxxxxxxxxxx
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:
