Thanks for posting the results. I'm endlessly fascinated (and frustrated)
by Optimizer behavior.
Jay Miller
x48355
-----Original Message-----
Sent: Sunday, September 02, 2001 5:50 PM
To: Multiple recipients of list ORACLE-L
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" <[EMAIL PROTECTED]>
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: [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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Greg Moore
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).
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Miller, Jay
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).
