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

From: Mark D Powell <Mark.Powell_at_eds.com>
Date: Thu, 2 Apr 2009 08:14:34 -0700 (PDT)
Message-ID: <d31b8a3e-81ce-4628-80d0-b42316d31a9b_at_a23g2000vbl.googlegroups.com>

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 -- Received on Thu Apr 02 2009 - 10:14:34 CDT