RE: Fav. Urban Legend...

Also,

optimizer_search_limit (I think hidden in 8.1) defaults to 5, which means, consider all permutations, including Cartesian product joins, if the # of tables in the from clause is 5 or less. For more than 5 tables, Cartesian products are not considered initially.

Another parameter is optimizer_max_permutations which defaults to 80,000 which is the max no. of join orders. Don't know how close anybody has got to this though. Jonathan?

Paul

-----Original Message-----
Sent: Thursday, March 14, 2002 10:49 AM
To: Multiple recipients of list ORACLE-L

Oracle eliminates lots of options
by tracking 'best cost so far'.

The frist step of optimisation is
'single table access path' i.e. if
I make each table in turn the driving
table for the query, how much does it
cost to get all the data I need from just that table.

Then assume that the cost of the full query is 88 if the order of tables is A,B,C,D,E but the cost of the single table access path into E was 92, then Oracle can spot that there is no point in trying any access paths that start with table E. That's just eliminated 24 paths out of 120.

Jonathan Lewis
http://www.jlcomp.demon.co.uk

Next Seminar - UK, April 3rd - 5th
http://www.jlcomp.demon.co.uk/seminar.html

Host to The Co-Operative Oracle Users' FAQ http://www.jlcomp.demon.co.uk/faq/ind_faq.html

Author of:
Practical Oracle 8i: Building Efficient Databases

-----Original Message-----
To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com> Date: 14 March 2002 15:26

|another possible source of the max 5 tables in a join myth could be
that
|Sybase and SQLServer's query optimizer would only consider all
possible join
|orders for up to 5 tables. this was true through at least vers 11.5
for
|Sybase. do the math - there are 120 possible join orders for 5
tables, 720
|for 6, 5040 for 7 - an optimizer has to draw the line somewhere or we
would
|spend more time optimizing than executing.
|
|anybody know how Oracle draws that line?
|

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Jonathan Lewis
  INET: jonathan_at_jlcomp.demon.co.uk

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: 
  INET: Paul.Parker_at_bmwna.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).