DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: What is the max limit on the tables to query?

  1. #1
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    I was wondering whether any one know of what is the max number of tables that one could use for quering on sqlplus/oracle.


    I heard that SQLserver restricts with a max of 16 tables. IS there any hard and fast limits on oracle too? if so what it is and any white papers or notes that talk on these similar issues.


    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  2. #2
    Join Date
    Nov 2000
    Posts
    344
    For fun I just tried a query with a bunch of joins on dual...

    I ended up with a cartestian join on over 300 dual tables
    and Oracle didn't have much of a problem with it.

    So if there is a limit, it's over 300!

    I didn't see any limit on the number of tables in a query in
    the documentation either. There is a limit on the number
    of subqueries... 255, and a limit on the size of your sql text
    ... 64k, but I didn't see anything other than that.

    -John

  3. #3
    Join Date
    Oct 2000
    Location
    Saskatoon, SK, Canada
    Posts
    3,925
    That was one good information John, Thanx,

    Sam
    Thanx
    Sam



    Life is a journey, not a destination!


  4. #4
    Join Date
    Nov 2000
    Location
    Baltimore, MD USA
    Posts
    1,339
    But realize that the number of join orders for a set of tables is #Tables!(factorial). Therefore, when you reach 16 tables, there are 20,922,789,888,000 possilbe join combinations.

    This why Oracle will not attempt every combination once you have more than (by default) 5 tables in your statement. After that, it will only attempt PK/FK joins and those specified by your WHERE clause. Of course, this can still be a very large number when you are dealing with lots of tables.

    Just something to keep in mind.

    - Chris

    BTW - I think that is an old limit from SQLServer. I'd be very surprised if it still existed.

  5. #5
    Join Date
    Apr 2001
    Location
    Louisville KY
    Posts
    295

    Talking

    Back in the dark ages of V5/V6, there was a 15 table limit. It was around for at least some of V7. I have since found (in some commercial software) a 40 table join, but I have not seen anything removing the limit.
    Joseph R.P. Maloney, CSP,CDP,CCP
    'The answer is 42'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width