Re: performance problem

Hi Martin,

Thanks for your suggestions but by accident I found the true cause for my problem (or at least the major part of it): a mismatch in data type (INT_KEYFLD4 is a varchar2 but the parameter :b1 is a number). Because of this the index is not used and a full table scan on r5interface is the result. Unfortunately Explain Plan assumes the bind variable to be of the correct data type and does not tell the truth in this case!

Geoffrey

"Martin Haltmayer" <Martin.Haltmayer_at_0800-einwahl.de> wrote in message news:3B331F0D.3E08890_at_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 Thu Jul 19 2001 - 10:57:16 CDT