OJNK? Your term Larry? Or do I need to RTFM?
Didn't realize this had a name. Jared On Wednesday 26 June 2002 18:48, Larry Elkins wrote: > Greg, > > When the situation is appropriate, and I don't want a correlated approach, > I will typically use the hash aj as illustrated by your second example, the > NOT IN using a hash aj. That's my preference, when correlation is not > appropriate. And I work with some DB's where always_anti_join is set to > hash and don't even have to use the HASH_AJ hint, *if* the conditions for > using a hash aj are met. > > And that's where the beauty of the first approach comes in. Suppose the > query is such that you *don't* want it correlated, and the conditions for > using a hash aj *can't* be met, you can use the first approach, and force a > hash join (outer) if required. You will often times get similar performance > as the hash aj. For example, if you forced a hash join via hints in your > first example, the OJNK approach (outer join null key, pronounced "O Junk", > no need for a GNU discussion) might provide very similar results as the > hash aj approach. And that's when I use the OJNK approach, when I want a > hash type of join, but the conditions for using a hash aj can't be met. > That is its real strength. I avoid the OJNK approach otherwise simply > because some will be confused by the coding, not realizing an anti-join is > being performed. And yes, you can include comments saying so, but some > folks will still mistake its purpose ;-) > > Anyway, here is an example similar to yours, but I forced a hash join when > using the OJNK approach. But I'm with you, I lean towards using the > HASH_AJ, when appropriate, over the OJNK approach, but mileage varies, not > one size fits all, fill in your favorite cliché here. Just wanted to point > out that the OJNK approach can be a *savior* when the conditions for a hash > aj being used can't be met and a correlated approach is the wrong way to > go. The numbers: > > select count(*) > from code_master > where code not in (select /*+ HASH_AJ */ code from code_detail) > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ------ ---- > Parse 1 0.03 0.03 0 0 0 > 0 > Execute 2 0.00 0.00 0 0 0 > 0 > Fetch 1 2.33 53.23 5855 859 144 > 1 > ------- ------ -------- ---------- ---------- ---------- ---------- > ------ ---- > total 4 2.36 53.26 5855 859 144 > 1 > > Rows Row Source Operation > ------- --------------------------------------------------- > 1 SORT AGGREGATE > 100 HASH JOIN ANTI > 100000 INDEX FAST FULL SCAN (object id 67350) > 299600 VIEW VW_NSO_1 > 299600 INDEX FAST FULL SCAN (object id 69013) > > *************************************************************************** >* **** > > select /*+ ORDERED USE_HASH(cd) */ count(*) > from code_master cm, code_detail cd > where cm.code = cd.code (+) > and cd.code is null > > call count cpu elapsed disk query current > rows > ------- ------ -------- ---------- ---------- ---------- ---------- > ------ ---- > Parse 1 0.03 0.03 0 0 0 > 0 > Execute 2 0.00 0.00 0 0 0 > 0 > Fetch 1 2.39 57.47 5855 859 144 > 1 > ------- ------ -------- ---------- ---------- ---------- ---------- > ------ ---- > total 4 2.42 57.50 5855 859 144 > 1 > > Rows Row Source Operation > ------- --------------------------------------------------- > 1 SORT AGGREGATE > 100 FILTER > 299700 HASH JOIN OUTER > 100000 INDEX FAST FULL SCAN (object id 67350) > 299600 INDEX FAST FULL SCAN (object id 69013) > > And you don't want to see the numbers for the correlated nested loops > approach, more than an hour. > > Okay, OJNK a little slower in this case, but if a hash aj *couldn't* be > used, and a correlated approach was *not* the way to go, the OJNK approach > can save the day and be a nice alternative if you utilize a hash join. > > Regards, > > Larry G. Elkins > [EMAIL PROTECTED] > 214.954.1781 > -----Original Message----- > Sent: Wednesday, June 26, 2002 5:44 PM > To: Multiple recipients of list ORACLE-L > > > 2 versions of SQL + TKPROF excerpts > > 8.1.6, Solaris > > ---------------------------- > SQL version #1 > ---------------------------- > > select count(*) > from customers c > , sales s > where c.customer_id = s.customer_id(+) > and s.customer_id is null > > count cpu elapsed disk query current rows > > total 4 7.33 7.36 1 200352 4 1 > > Rows Row Source Operation > ------- ---------------------------------------- > 1 SORT AGGREGATE > 99105 FILTER > 169049 NESTED LOOPS OUTER > 100000 INDEX FAST FULL SCAN (object id 22199) > 69944 INDEX RANGE SCAN (object id 22232) > > ---------------------------- > SQL version #2 > ---------------------------- > > select count(*) > from customers c > where c.customer_id not in ( > select /*+ hash_aj */ > s.customer_id > from sales s ) > > count cpu elapsed disk query current rows > > total 5 1.17 1.94 159 356 8 1 > > Rows Row Source Operation > ------- ------------------------------------------ > 1 SORT AGGREGATE > 99105 HASH JOIN ANTI > 99999 INDEX FAST FULL SCAN (object id 22199) > 69945 VIEW VW_NSO_1 > 69945 INDEX FAST FULL SCAN (object id 22232) -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Jared Still 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).
