| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: Long running SQL Problem?
('binary' encoding is not supported, stored as-is)
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)"
><clchan_at_nie.edu.sg>
>To: Multiple recipients of list ORACLE-L
><ORACLE-L_at_fatcity.com>
>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
-- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Stephane Faroul INET: sfaroult_at_oriolecorp.com 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: ListGuru_at_fatcity.com (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).Received on Wed Apr 03 2002 - 02:43:18 CST
 |
 |