Re: Update statement performance
- From: Mohamed Houri <mohamed.houri@xxxxxxxxx>
- To: Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>
- Date: Wed, 26 Feb 2014 08:51:49 +0100
Without a row source plan it is still not easy to know which is the most
time and energy consuming operations in the plan
Nevertheless, I would have tried to add a virtual column so that it will be
easy to exclude, from the update, rows that have not null mycolumn (which I
have supposed below to be of number datatype):
alter table mytable add my_null_col number generated always as (case when
mycolumn is null then -1 else null end) virtual;
create index ind_virt on mytable (my_null_col);
update mytable a set a.mycolumn = (select b.mycolumn from
mytable b
where a.joincolumn = b.joincolumn
and b.anothercol ='someval')
where a.my_null_col = -1;
Best Regards
Mohamed Houri
www.hourim.wordpress.com
2014-02-26 8:17 GMT+01:00 Jonathan Lewis <jonathan@xxxxxxxxxxxxxxxxxx>:
>
>
>
> 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.*
>
--
Bien Respectueusement
Mohamed Houri
Other related posts:
