Catherine, I am sure that Larry Elkins will forgive my taking the opportunity of being 7 hours ahead of him (and 7 hours behind yourself) for singing the praise of the 'hash anti-join' in his name. And anyway you could remind your senior DBA the existence of NOT EXISTS, far better than COUNT(*) in this case. Basically, in case A you have a non-correlated sub-query, and in case B a correlated one. A correlated sub-query means that for each row from Table_1 you must search Table_2. For one thing, if the corresponding columns are not indexed, you're dead. Even if they are, it may really be bad if Table_1 is huge AND THERE IS NO OTHER CRITERION, because you will have a full scan of Table_1. A non-correlated subquery is executed only once. If it returns few rows, you will have a full TS of Table_1 in both cases, but the NOT IN is likely to be slightly more efficient. If it returns many rows, if Table_1 is big, if there is no other criterion and if col3 and col4 are not null, then the NOT IN with a hint asking for a hash anti-join will outperform a NOT EXISTS, not to mention a 0 = (select COUNT(*) ...). The nested loops of a correlated subquery will be excellent when you have fairly selective criteria besides, and when the correlated subquery is, so to speak, the icing on the cake. Let me add that an external join with a test for nullity usually gives fairly good results too (in fact, it often goes the hash antijoin way) and that I have also had excellent results under some circumstances with an inline view (typically when you have additional criteria bearing on Table_2) ...
To summarize, abruptly saying 'this sucks' exposes you to be proved wrong once in a while. >----- Original Message ----- >From: "CHAN Chor Ling Catherine (CSC)" ><[EMAIL PROTECTED]> >To: Multiple recipients of list ORACLE-L ><[EMAIL PROTECTED]> >Sent: Tue, 02 Apr 2002 21:13:19 > >Hi Gurus, > >My senior DBA always tell us that the "not in" >command sucks and we are all >encourage to use the select count(*). SQL A is >greatly frowned upon and SQL >B will be the best. > >SQL A : >SELECT col1,col2 > FROM Table_1 > WHERE (col1,col2) NOT IN (SELECT col3,col4 > FROM Table_2 > WHERE col3 = col1 > AND col4 = col2); >SQL B : >SELECT col1,col2 > FROM Table_1 A > WHERE (0=(SELECT COUNT(*) FROM Table_2 b WHERE >b.col3=a.col1 AND >b.col4=a.col2)); > >Qn : Is it true ? Could someone shed some light ? >Please advise. Thanks. > >Regds, >Catherine > Stephane Faroult Oriole Corporation Performance Tools & Free Scripts -------------------------------------------------------------- http://www.oriole.com, designed by Oracle DBAs for Oracle DBAs -------------------------------------------------------------- -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: [EMAIL PROTECTED] Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
