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? -----Original Message----- Sent: Wednesday, March 13, 2002 5:09 PM To: Multiple recipients of list ORACLE-L Never true. I think the reason it sprang into existence was that on the AND-EQUAL path, which combines single-column indexes to access a single table, the maximum number of indexes that can be combined is five. 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: 13 March 2002 21:22 |> My next to favourite legends. |> Oracle can only use 5 indexes in a query | |Was this ever true, on v6 or v7? | |I was told by Oracle support about six years ago about the 5 index max. It |seemed to work for me. On a six table query, with five indexes used, when I |disabled one index Oracle started using another and this helped performance. |So it seemed like the max of 5 was true. | | -- 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: STEVE OLLIG 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).
