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.


Back to Index of Topics