Re: USE_HASH hint

The problem with the USE_HASH hint is due to the order of the tables in the FROM clause. The hint was:

/*+ ORDERED USE_HASH(c) */

But in the FROM clause the "c" table was listed first. Without the hint I got a Nested Loops, with the hint I got a Sort-Merge.

But when I simply switched the order of the tables in the FROM clause I got the Hash Join.

I am on 8.1.6 on Solaris, and tried several combinations to be sure this problem was related to the requirement that when you have two tables in the from clause, the one you specify in the USE_HASH hint must be listed last.

Guess this behavior makes sense when you think about it.

• Original Message ----- To: "Multiple recipients of list ORACLE-L" <ORACLE-L_at_fatcity.com> Sent: Sunday, September 02, 2001 12:20 AM

> Does anyone know why a USE_HASH hint that's properly coded might not work?
> Without it my query uses nested loops, but with it the plan changes to
> sort-merge, not a hash join like I want.
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Greg Moore
> INET: sqlgreg_at_pacbell.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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Moore
  INET: sqlgreg_at_pacbell.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).