Re: Are These 2 Stateents Identical

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.