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

From: Randolf Geist <mahrah_at_web.de>
Date: Fri, 3 Apr 2009 00:39:50 -0700 (PDT)
Message-ID: <89ea2188-6c09-474e-9d28-e77e07f239a3_at_h28g2000yqd.googlegroups.com>



On Apr 3, 12:36 am, Mladen Gogala <gogala.mla..._at_gmail.com> 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.

CREATE INDEX <new_index> ON CUSTOMERS_MASTER( firstname, lastname, ' '
);

or

CREATE INDEX <new_index> ON CUSTOMERS_MASTER( 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:

CREATE INDEX <new_index> ON CUSTOMERS_MASTER( 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:
http://oracle-randolf.blogspot.com/

SQLTools++ for Oracle (Open source Oracle GUI for Windows): http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/ Received on Fri Apr 03 2009 - 02:39:50 CDT

Original text of this message