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

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

• 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) Received on Thu Apr 02 2009 - 14:38:45 CDT