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 in the performance would be
1. If nested loops join - would give better performance for your query
then perform thefollowing
a. check if the involved tables have indexes
b. verify if the indexes, tables involved have the statistics have been
calculated
frequently
c. the ordering of the tables in the 'from clause' should be in such a way
that the largest table (more rows) needs to be placed at the end or
towards
the end in the 'from clause'
for eg : select c1, c2 from b, a where a.id = b.id ===> (a is largest table
)
d. Attempt should be made such that cost of sort-merge join would be more
expensive than the nested loops join (for eg) - so that the optimizer
will
choose nested loops
1. if permitted hints (USE_NL ) can be used
2. optimizer_index_cost_adj ( can be used - so that indexes will be used
in the plan )
Question :
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 ?
Answer:
Driving table in nested loops join is the one which is used to select each
row
from ( when an ORDERED HINT is used the 'first table' in the from clause
assuming 2 tables
is the 'driving' table the other table is called 'inner table ' )
fro eg: select c1, c2 from a, b where a.id = b.id ====> usually the driving
table is "a" and
the inner table is "b" ( you can double verify this in the 10053 output )
Usually the 'inner table' if it has more rows than the 'driving table' ===>
that is when
the nested loops is advantageous - that is considered the same by the
optimizer as well
Thanks,
Kavi
"Grabowy, Chris" wrote:
> bp,
>
> Checkout a white paper entitled: A Look Under The Hood of CBO: The 10053
> Event. You can find this whitepaper on the hotsos.com website. Your
> probably going to be looking at the General Plans section of the trace.
>
> Goodluck.
>
> Chris
>
> --------------------------------------------------
> "BigP" <[EMAIL PROTECTED]>
> Sent by: [EMAIL PROTECTED]
> 07/23/2002 06:58 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <[EMAIL PROTECTED]>
> cc:
> 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
>
>
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: Grabowy, Chris
> 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).
begin:vcard
n:;Kavitha
x-mozilla-html:FALSE
adr:;;;;;;
version:2.1
email;internet:[EMAIL PROTECTED]
fn:Kavitha Muthukumaren
end:vcard
