Re: type of join in sql

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

1. check if the involved tables have indexes
2. verify if the indexes, tables involved have the statistics have been calculated frequently
3. 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 )
4. 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
5. if permitted hints (USE_NL ) can be used
6. 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" <big_planet_2000_at_hotmail.com>
> Sent by: root_at_fatcity.com
> 07/23/2002 06:58 PM
> Please respond to ORACLE-L
>
>
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> 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: cgrabowy_at_fcg.com
>
> 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: ListGuru_at_fatcity.com (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: Kavitha Muthukumaren
  INET: Kavitha.Muthukumaren_at_oracle.com

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: ListGuru_at_fatcity.com (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).



text/x-vcard attachment: Card for Kavitha Muthukumaren