Re: Sql query

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
> 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: Jared Still
  INET: jkstill_at_cybcon.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).