JL Computer Consultancy
Is there a limit to the number of tables in a
query?
(Recreated from an original written for the Dizwell Wiki) |
Jan 2007 |
For any realistic SQL,
the answer is effectively no. As a basic test, I once tried executing a query
with 254 tables in a simple join, one index per table, joining from table to
table along the indexed column with an equality predicate. Using the rule-based
optimizer, the optimization stage took a few hours (something like 4.5 –
on a laptop running at 700 MHz); using the Cost-based optimizer, the
optimization stage took a couple of seconds.
There may be an actual limit -
but it’s not in the list of database limits in
the database server reference guide (but see footnote). If there is a limit, it
is possible that it is a limit per query block, which means you might be able
to circumvent it by using inline views (with the /*+
no_merge */ hint) or subquery factoring (with the /*+ materialize */ hint). In fact, one of the concepts
mentioned explicitly in the database SQL reference guide is
the “maximum levels of subqueries in an SQL
statement”, which includes “Unlimited in the FROM clause
of the top-level query”.
Given the number of ‘unlimited’
options listed in the database server reference, if there is a limit it’s
most likely to be a soft limit imposed by a memory constraint on the size of
the shared pool rather than a hard limit coded into the optimizer
itself. For example, because the cost of the query exceeded the numeric limit
supported on my platform, a test of a 26-way Cartesian self-join on a very
large table managed to crash my session with
ORA-00600: internal error code, arguments: [15160],[],[],[],[]
Perhaps a more interesting question would
be “is the number of indexes per table unlimited” (as it
says in the manuals), or will the optimizer crash if you manage to create a
table with a huge number of indexes. It’s a moot point, of course. No-one
should be planning to create a table with a couple of thousand indexes, no-one
should be planning to write SQL which joins (as in my silly
test) 254 tables - although I have seen a whitepaper from Oracle that mentions
a query joining 189 tables somewhere in Oracle Applications.
Footnote
The SQL Reference
Appendix B (for 10g Release 2) lists details of Oracle’s compliance with
the (no longer published) FIPS 127-2 document. The list of
requirements has an explicit statement that the number of tables Oracle can
reference in a single SQL statement is unlimited.
