Re: performance problem

From: Martin Haltmayer <Martin.Haltmayer_at_0800-einwahl.de>
Date: Fri, 22 Jun 2001 12:33:49 +0200
Message-ID: <3B331F0D.3E08890@0800-einwahl.de>

Hi Geoffrey,

okay, this is a completely different situation if the index is not unique. Obviously this is your problem. Then you can try one of the following:

UPDATE --+ bypass_ujvc
(
select d.ATD_STATUS
from R5ARCTRACKDATA d, R5INTERFACE i
WHERE d.ATD_STATUS != 'P'

AND d.ATD_TRANSORGID = :b1
and i.INT_KEYFLD4 (+) = d.ATD_TRANSORGID
and i.int_keyfld4 is null	-- antijoin

and rownum <= 1
) x
set x.ATD_STATUS='D'
/

or take your original update statement and add "and rownum <= 1" into the where clause of the not exists subquery. This will then only retrieve one row instead of 7000.

Martin

Geoffrey van Heerde wrote:
>
> Hi Martin,
>
> Thanks for your suggestion. I am afraid I cannot guarantee that the column
> int_keyfld4 will always be unique. It is true for the current situation. I
> just mentioned it to illustrate the high cardinality of the non-unique
> index. The 'not exists' subquery should therefore retrieve a maximum of one
> record for each update. I fail to understand why Oracle needs to read 7000
> blocks for this. And am I correct that your reformulated update statement
> will also work with a non-unique index (because of the antijoin)?
>
> Geoffrey
Received on Fri Jun 22 2001 - 05:33:49 CDT