Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Are These 2 Stateents Identical

Re: Are These 2 Stateents Identical

From: Ron Reidy <rereidy_at_indra.com>
Date: Tue, 23 Oct 2001 14:38:24 -0600
Message-ID: <3BD5D540.DDAE2A6B@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 - 15:38:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US