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

From: Rajesh <>
Date: Fri, 3 Apr 2009 02:09:49 -0700 (PDT)
Message-ID: <>

On Apr 3, 12:39 pm, Randolf Geist <> wrote:
> On Apr 3, 12:36 am, Mladen Gogala <> wrote:
> > There is a logical reason for using NULL value but the problem with the NULL value
> > is the fact that the "IS NULL" condition cannot be resolved by using a
> > B*Tree index.
> It can be resolved using b*tree indexes, by adding a constant non-null
> expression to the column(s) that can be null, e.g.
> firstname, lastname, ' '
> );
> or
> address1, ' '
> );
> This is effectively going to index all null values of firstname and
> lastname / address1 etc. and is marked as function-based index by the
> way.
> Of course, to make the index even more efficient, one could use an
> expression like that as function-based index:
> case when firstname is null then 'X' else null end, case when lastname
> is null then 'X' else null end
> );
> and use the same expression as filter predicate. Provided that there
> are not too many null values in relation to the total number of rows
> this should be very efficient. The same can be done for the remaining
> fields. It needs to be tested if Oracle can combine these indexes (may
> be an INDEX_COMBINE hint is required) for a very selective access to
> the table. Another option could be a concatenation transformation
> resp. trying to use separate queries using the UNION (ALL) operator.
> Sample:
> update customers_master set status='R'
>   where batchnumber='1'
>     AND ((case when firstname is null then 'X' else null end = 'X' and
> case when lastname is null then 'X' else null end = 'X')
>     OR   address1 is null
>     OR   mobilenumber is null
>          )
> The batchnumber can be added to the index if it is selective. If
> batchnumber is mandatory it could simply be added to the index instead
> of the constant expression to index the null values.
> > You can create a functional index on NVL(mobilenumber,-1)
> > and the same for the rest of the columns, but unless you are on version
> > 11g, you cannot use statistics on that index. You can, however, use hints.
> Why can't you use statistics on that function-based index? What about
> e.g. "FOR ALL HIDDEN COLUMNS" as method_opt parameter in DBMS_STATS
> calls, and the virtual / hidden columns added behind the scenes are
> covered by "FOR ALL COLUMNS" (at least in 10.2) as well.
> Regards,
> Randolf
> Oracle related stuff blog:
> SQLTools++ for Oracle (Open source Oracle GUI for Windows):

after creating index half time is reduced...

thank you very much experts. Received on Fri Apr 03 2009 - 04:09:49 CDT

Original text of this message