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

From: Mladen Gogala <>
Date: Thu, 2 Apr 2009 22:36:47 +0000 (UTC)
Message-ID: <gr3elv$mko$>

On Thu, 02 Apr 2009 05:37:23 -0700, Rajesh 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

No entity such as "PARTITION BY option in query" exists. What you can do is to use cooked values like -1 for the mobilenumber and 'ZZZZZZ' for the names and addresses. The advantage of that is that, if there are not many records satisfying the conditions, a normal B*Tree index can be used and will efficiently retrieve the requested records. The disadvantage is that a value like 'ZZZZZZZ' or -1 for a cell phone number will interfere with the sorts and you can't do anything about it. 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. 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.

Received on Thu Apr 02 2009 - 17:36:47 CDT

Original text of this message