Re: oracle update query is taking lot of time..
From: Rajesh <mrajesh1305_at_gmail.com>
Date: Fri, 3 Apr 2009 01:59:26 -0700 (PDT)
Message-ID: <369a4fba-f849-4cea-ab8c-ada5090bfa3e_at_l1g2000yqk.googlegroups.com>
On Apr 2, 5:46 pm, gar..._at_jamms.org wrote:
> On 2 Apr, 13:37, 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
>
> Full Oracle version and an Explain Plan will get you more help.
>
> HTH
>
> -g- Hide quoted text -
>
> - Show quoted text -
Date: Fri, 3 Apr 2009 01:59:26 -0700 (PDT)
Message-ID: <369a4fba-f849-4cea-ab8c-ada5090bfa3e_at_l1g2000yqk.googlegroups.com>
On Apr 2, 5:46 pm, gar..._at_jamms.org wrote:
> On 2 Apr, 13:37, 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
>
> Full Oracle version and an Explain Plan will get you more help.
>
> HTH
>
> -g- Hide quoted text -
>
> - Show quoted text -
ORACLE Version i am using is:
SQL*Plus: Release 9.2.0.1.0 - Production on Fri Apr 3 14:28:30 2009
Copyright (c) 1982, 2002, Oracle Corporation. All rights reserved.
Connected to:
Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.1.0 - Production
Received on Fri Apr 03 2009 - 03:59:26 CDT