Re: oracle update query is taking lot of time..

From: Palooka <nobody_at_nowhere.com>
Date: Thu, 02 Apr 2009 22:08:01 +0100
Message-ID: <SO9Bl.162506$cy5.157761_at_newsfe18.ams2>

Michael Austin wrote:
> Mark D Powell wrote:

>> On Apr 2, 8:37 am, Rajesh <mrajesh1..._at_gmail.com> wrote:
>>> Dear Experts,
>>>
>>> I have a very huge table having 89 columns and 100,00,000(1CRORE)
>>> records.
>>>
>>> update customers_master set status='R'
>>>  where REC_ID in
>>>  ( select REC_ID from  customers_master where
>>>  (
>>>    (firstname is null and
>>>    lastname is null) or
>>>    address1 is null or
>>>    mobilenumber is null
>>> )
>>> and batchnumber='1');
>>>
>>> basically we are marking customer record as "R" if any of the above
>>> listed fields(in the query) are blank.
>>>
>>> can we use PARTITION BY option in query to optimize it.
>>>
>>> thanks in advance
>>
>> The explain as mentioned would be helpful but why are you using an in
>> clause subquery when you could just be testing the columns in the
>> where clause directly using ( condition_1 or condition_2 etc ...)?
>>
>> Just test each row as you process the table sequentially from top to
>> bottom.
>>
>> HTH -- Mark D Powell --

>
> -- and make sure you have the appropriate indexing to support the WHERE
> clause.
>
> a good start would be batchnumber depending on how "selective" this is
> you may not need additional, YMMV.
>
> so, if the full table has 1M rows and batchnumber=1 is only 500 then you
> should use it to your advantage....
>
> Let me break your query down for you and what it is going to do (no
> explain plan necessary, but, I think it will be pretty close...
>
> 1) select REC_ID from customers_master where
> (firstname is null and
> lastname is null) or
> address1 is null or
> mobilenumber is null
> this portion is going to do a FTS unless you have indexes that START with:
> firstname
> lastname
> address1
> mobilenumber
>
> THEN that result set will be reduce by: batchnumber='1');
>
> too make the whole thing more efficient:
>
> update customers_master set status='R'
> where batchnumber='1'
> AND ((firstname is null and lastname is null)
> OR address1 is null
> OR mobilenumber is null
> )
>
> if you have an index with batchnumber as the leading edge, it will
> reduce the"lookup" from 100,00,000 to the size of that batch (say... 500
> records before evaluating the rest of the conditions)
>

Just a thought though: depending, as you say, on the selectivity, histograms may be called for.

But judging by the original query posted (the totally unnecessary subquery), I suspect that OP hasn't really a clue. Sorry if this sounds harsh.

Palooka Received on Thu Apr 02 2009 - 16:08:01 CDT