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
used.
HTH
p.s. See the "Database Performance Guide and
Reference" section of the docs. for more info.
-----Original Message-----
From: BigP [mailto:[EMAIL PROTECTED]] Sent: Tuesday, July 23, 2002 9:59 PM To: Multiple recipients of list ORACLE-L Subject: type of join in sql can some body shed some light on how does the
optimizer decides to choose the kind of join i.e. nested loop, sort merge
or hash join . In one of queries if i tweak the join condition it changes the
type of join and start using index , otherwise it doesn't .
I thought it depends on statistics .. if optimize
finds that are more qualifying records in inner table then it will prefer to go
for sort merge and will do full scan of inner table , but if it thinks there are
less records in inner table it will user nested loop . am I correct
?
TIA ,
bp
|
- type of join in sql BigP
- Re: type of join in sql Richard Huntley
- Re: type of join in sql Jared . Still
- Re: type of join in sql Kavitha Muthukumaren