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 <[EMAIL PROTECTED]> 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: [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). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: 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).
