Maximum number of tables in a query (16) exceeded

Maximum number of tables in a query (16) exceeded

Post by Matthew D. Healy, Ph. » Thu, 08 Jun 1995 04:00:00





> Hi,

> While programming with powerbuilder 4, I had this message for one of my
> (critical ;-(  ) queries:

> "Unable to allocate ancillary table for query optimization. Maximum number of
> tables in a query (16) exceeded"

> Any clue to change that behavior ? (I am new to the sybase/powerbuilder
> business)

You can't.  As of the latest version of Sybase System 10 the 16-tables
per join limit is still hardcoded and unbreakable.  Rumor has it the
forthcoming System 11 will remove this limitation...

You have two options:

1. simplify your query.  In particular, look for big "in" clauses.

2. if you cannot simplify the query, then you'll have to break
your query up into smaller pieces that generate temporary tables
with select into, then do the final select on the temp table(s).

You may need to expand tempdb first, if the intermediate tables
thus generated will be very large...

Unless your requirements are _very_ unusual, the apparent need to
use such complex joins in production queries almost always means
you need to rethink your database design from the ground up; you
simply shouldn't need to reference that many tables on a regular
basis unless you really _have_ a situation that is unusually
complex.

Perhaps you could post more details of your query and your table
layout, so people could help you simplify it.
----

Scotty, I NEED WARP NINE!
But captain, she needs a new Sierpinski Gasket!
http://paella.med.yale.edu/~healy/matt_healy.html