/*+ use_hash(x) */
will force a hash join if the 'x' is the target for a
hash join. This does not exclude 'x' from being the
driver table in a nested or merge join. Typically an
additional ordered predicate might be required to
avoid the join inputs being swapped over.
hth
connor
---
Hi All,
Please give me
inputs on this hint and the affect of having a large
HASH_AREA.
Thanks,
Rajesh
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
I find it interesting that the optimizer seems
to be increasingly fussy about hints as time
passes.
Once upon a time, it used to be the case
that hints could be very sketchy and you
would get the result you wanted. Now it seems
that your hints have to be very precise, or
Oracle may find a path
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
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