Hi
The choice of of whether to use nested loop, hash join, sort-merge join
is driven by the cost (logical ios performed ) and cardinality ( count of
rows source for given
operation ). Optimizer formulates several plan - before the best plan among
them is chosen
Approach one can take to benefit
The
optimizer will use the availability of indexes in deciding which type of join to
use.
no
indexes = merge join
indexes = nested loops, although the optimizer may
dynamically choose to perform a hash join.
Your
tweaking of the join conditions is what causes the different joins to be
Here's how I determine that:
http://www.amazon.com/exec/obidos/ASIN/0130123811
http://otn.oracle.com/docs/products/oracle9i/doc_library/release2/server.920/a96533/toc.htm
Sorry for the RTFM, but what you're asking is a rather large topic, one
which I can't attempt to answer, at least, not while
