Re: oracle update query is taking lot of time..
From: Robert Klemme <shortcutter_at_googlemail.com>
Date: Fri, 03 Apr 2009 09:42:06 +0200
Message-ID: <73lsutFu2go6U1_at_mid.individual.net>
On 03.04.2009 00:36, Mladen Gogala wrote:
> On Thu, 02 Apr 2009 05:37:23 -0700, Rajesh wrote:
>
>
> 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.
Date: Fri, 03 Apr 2009 09:42:06 +0200
Message-ID: <73lsutFu2go6U1_at_mid.individual.net>
On 03.04.2009 00:36, Mladen Gogala wrote:
> 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.
Needless to say that if this is a one off UPDATE the index creation overhead does not pay off since you'll get your FTS either way. :-)
Which brings us back to the topic of missing information...
If this is needed more often and if the status solely depends on the contents of other columns then with 11g OP could make column STATUS a virtual column and even index it. Or, they could add another column as virtual for recording the particular situation when any of the other columns is NULL.
Kind regards
robert Received on Fri Apr 03 2009 - 02:42:06 CDT