-
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!
-
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
-
That was one good information John, Thanx,
Sam
Thanx
Sam
Life is a journey, not a destination!
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|