Re: Are These 2 Stateents Identical

No, being a developer, I really don't have access to tkprof on our Unix server, and our DBA's won't put down their coffee cups long enough to help us.

All I can really do are explain plans. The first does 2 FTSs. The 2nd does nested loops, using an index, which may explain the speed difference. I might try re-analyzing the tables.

I have run into other situations where the 2nd form is faster than the first, too. So I can't really account for it.

But I won't argue with success, I am just worried that I have captured the essence of the first statement, since it is vendor code.

"Ron Reidy" <rereidy_at_indra.com> wrote in message news:3BD5D540.DDAE2A6B_at_indra.com...
> Buck Turgidson wrote:
> >
> > I need to change some vendor code for tuning purposes. I was
hoping
> > that someone could verify that the 2nd statment is identical to
the
> > first?
> >
> > The 2nd runs much more quickly, but I just want to be safe.
> >
> > DELETE
> > FROM CONTRACT
> > WHERE EXISTS ( SELECT 'X'
> > FROM CONTRACT_TEMP J
> > WHERE J.CONTRACT_NUM = CONTRACT.CONTRACT_NUM
> > AND J.CONTR_SEQ = CONTRACT.CONTR_SEQ
> > AND J.CONTR_DT = CONTRACT.CONTR_DT
> > AND J.CONTR_TYPE = CONTRACT.CONTR_TYPE );
> >
> > DELETE
> > FROM CONTRACT
> > WHERE (CONTRACT.CONTRACT_NUM,
> > CONTRACT.CONTR_SEQ,
> > CONTRACT.CONTR_DT,
> > CONTRACT.CONTR_TYPE )
> > IN ( SELECT J.CONTRACT_NUM,J.CONTR_SEQ,J.CONTR_DT,J.CONTR_TYPE
> > FROM CONTRACT_TEMP J);
> The first one will (at least should) only delete from contract for
the
> first occurance of the inner query.
>
> I am surprised the second query runs quicker. Did you run tkprof on
> this?
> --
> Ron Reidy
> Oracle DBA
> Reidy Consulting, L.L.C.
Received on Tue Oct 23 2001 - 17:24:26 CDT