TDV automatically assesses SQL Join blocks (inner joins and all types of outer joins) to determine suitability for rewriting the SQL join order. A Join block is a set of contiguous joins. The TDV query engine analyzes each join block of the query independently, and attempts to rewrite the query to maximize the number of joins pushed down to data sources. While inner joins can be reassociated without restriction, the TDV query optimizer checks a number of technical rules to determine if it is safe to reorder a block that contains outer joins.

For queries that meet complex criteria, TDV rewrites the SQL join order if it expects that to yield a performance gain. Joins are reordered so that tables from the same data source are performed first, which maximizes push down and makes the query more efficient.

The following examples show how SQL join ordering works.

The query as written would perform first the outer join and then the inner join, because by default joins are left-associative. The plan for this query looks like this:

The numbers in parentheses represent number of rows produced by each operator. The annotations in square brackets are data source names.

The same query could be rewritten like this:

The join between the two tables from the same data source is performed first, and the optimizer is able to push down this join to the data sources. The corresponding query plan looks like this:

TDV has to perform only one join. Most of the data is filtered out at the data source, reducing data transfer. TDV performs automatically rewrites join blocks that include inner joins and all types of outer joins.

A join block is a set of contiguous joins. The query engine analyzes each join block of the query independently and attempts to rewrite the query to maximize the number of joins pushed down to data sources. While inner joins can be reassociated without restriction, the optimizer checks a number of technical rules to determine if it is safe to reorder a block that contain outer joins. Cross-joins are not eligible for reordering. Join reordering is not performed if it introduces new cross joins. A join node with hints is not considered for reordering.

A precondition of the join ordering algorithm is that all outer joins can be simplified. Here is an example:

The left outer join can be converted to an inner join because the inner join above it has a null-rejecting predicate that applies to the null-filled side of the outer join. The ON clause of the top join rejects any rows produced by the left outer join that have NULL for the O100BIN_FLOAT column.

The plan for this query looks like this:

This shows that left outer join was converted to inner join but that join reordering did not take place because the join hint implies that the inner join (in the input query) cannot be reordered.

TDV performs the SQL join reordering analysis automatically. Special configurations or SQL options are needed only if you want to enforce SQL processing as it is written because you want table construction or table relationships.

You can join the tables in any order you want. (See Views and Table Resources.)

You can direct the query engine to follow the order in which you joined the tables. For information on how to specify joins, see Views and Table Resources.