Oracle-L: RE: Sql query

From: Larry Elkins <elkinsl_at_flash.net>
Date: Wed, 26 Jun 2002 17:48:21 -0800
Message-ID: <F001.00489CFF.20020626174821@fatcity.com>

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
elkinsl_at_flash.net
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: Larry Elkins
  INET: elkinsl_at_flash.net

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 Jun 26 2002 - 20:48:21 CDT