Re: Update statement performance

I used the suggestions of both Jonathan's and Mohamed's post to create a virtual column on mycolumn as well as
index on the joincolumn and was able to reduce the update statement by executing the query
exactly as Mohamed pointed out

The update took just around 40seconds !!!

Thanks once again for the pointers!!

On Wed, Feb 26, 2014 at 1:51 AM, Mohamed Houri <mohamed.houri_at_gmail.com>wrote:

> 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_at_jlcomp.demon.co.uk>:
>
>
>>
>>
>> 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
>> _at_jloracle
>> ------------------------------
>> *From:* oracle-l-bounce_at_freelists.org [oracle-l-bounce_at_freelists.org] on
>> behalf of Praveen Ramanan [pramanan_at_gmail.com]
>> *Sent:* 26 February 2014 04:00
>>
>> *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.*
>>
>
>
>
> --
> Bien Respectueusement
> Mohamed Houri
>

-- 
*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