RE: Fav. Urban Legend...

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 <ORACLE-L_at_fatcity.com> 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: 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: STEVE OLLIG
  INET: sollig_at_lifetouch.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).