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

> 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.

-- 
http://mgogala.freehostia.com