Randy Jordan writes
> Alexy Timonin writes
> > I started a quite large query and suddenly receive the
> > following error message:
> >> Too many table names in the query. The maximum
> >> allowable is16. (Msg 106, Level 15, State 1).
> > Why there is so small limit ? Of course I can decrease the
> > number of table names, but I'd like to know is this constant
> > limitation?
> If you need to join more than 16 tables together, reexamine you
> query requirements... failing this create views of several tables
> and then join the views together.
There is indeed a limit of 16 tables per query in Sybase 4 (I do
not know about Sybase 10.
(Sidenote: What happened to Sybase 5, 6, 7, 8 and 9? :-) Maybe
this was just a marketing hype way for Sybase to leapfrog Oracle in
its release number in a single bound :-)
Why Sybase chose this number eludes me. Moreover, I have not done
any experimentation to determine whether this is in a single FROM
clause or in the entire query. Randy's comment about reexamining
query requirements if you need to use more than 16 tables seems
somewhat kneejerk. In a well-designed relational database, there
will often be a number of smaller "satellite" tables that surround
the main tables. If you have several major tables, the need to do
a join between several major tables and most or all of the
satellite tables often comes up. If in addition, you have some
subqueries that look at data in other tables, this 16 table limit
can indeed be a severe restriction.
I would agree that accessing lots of tables can cause performance
hits, but saying that someone should reevaluate their performance
expectations in light of using more than 16 tables is far different
from suggesting that someone reevaluate their query requirements.
What I *want* and *need* to do is much different than what I might
expect in terms of performance from trying to do it.
I have always believed that, with the exception of true physical
constraints (like available memory, real or virtual, and the size
of atomic data values, etc), programs should be written with no
restrictions or at least none that can't be removed with by setting
the appropriate parameters. 16 is WAY TO LOW for any reasonable
program. If the number was 32,768, I might understand (they are
using a small int as an identifier for objects used in a query).
But there seems to be no justifiable reason for a restriction of 16
except lazy architecture and implementation. This sounds something
like a fixed array somewhere in the code (like "int tables")
which simply should not be done in good system software (this
should be a dynamically expandable array or list or something that
doesn't impose an artifical limit).
BTW, I do not believe the view approach will help, either, because
most databases effectively merge views and select statements
together, resulting in a single execution plan which includes all
the tables from the view (with some minor differences).