Re: oracle update query is taking lot of time..
From: Rajesh <mrajesh1305_at_gmail.com>
Date: Fri, 3 Apr 2009 02:09:49 -0700 (PDT)
Message-ID: <3aeda829-4611-4671-a382-8c114c25f339_at_k2g2000yql.googlegroups.com>
On Apr 3, 12:39 pm, Randolf Geist <mah..._at_web.de> wrote:
> 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/
Date: Fri, 3 Apr 2009 02:09:49 -0700 (PDT)
Message-ID: <3aeda829-4611-4671-a382-8c114c25f339_at_k2g2000yql.googlegroups.com>
On Apr 3, 12:39 pm, Randolf Geist <mah..._at_web.de> wrote:
> 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/
after creating index half time is reduced...
thank you very much experts. Received on Fri Apr 03 2009 - 04:09:49 CDT