Join indexes should be defined using outer joins if they are intended to cover both inner and outer join queries. To understand how this is possible, an outer join can be thought of as producing a result consisting of two sets of rows. The first set corresponds to the set of matched rows obtained when a row from the outer table matches one or more rows from the inner table (this set corresponds to the set of rows defined by the inner join with the same join condition). The second corresponds to the set of unmatched rows: those rows from the outer table that do not match any rows from the inner table.
Except for the presence of the unmatched row set, an outer join is the same as an inner join, and produces the same result. Therefore if an inner join query can be completely satisfied by the matched set of rows from an outer join index, the Optimizer uses it.
Join Index With Outer Join in Its Definition
Consider the following join index defined on the three tables t1, t2, and t3. Tables t1 and t2 are joined with an inner join, and the result is joined with table t3 using an outer join. The outer table is the result of joining tables t1 and t2.
CREATE JOIN INDEX ji1 AS SELECT a1, a2, a3 FROM (t1 INNER JOIN t2 ON a1 = a2) LEFT OUTER JOIN t3 ON a1 = a3;
Column a3 is the unique primary index for table t3. Column a3 might be specified in the table definition explicitly as a primary index, or simply as unique. This means that all of the rows from the join of t1 with t2 are in the join index exactly once, either in the matched set, or in the unmatched set. Therefore, the following query can be satisfied by the join index:
SELECT a1, a2 FROM t1, t2 WHERE a1 = a2;
Extended Query Coverage With Outer Joins in Index Definition
A coverage algorithm determines that there is partial coverage, and the Optimizer uses the join index to join with the base tables to project the non-covered columns if the cost is lower than performing the query using the base tables alone.
The join index optimizations introduced by extending query coverage through defining extra foreign key-primary key joins does not negate the advantage of defining outer joins in your join index definitions because the normalization of outer joins to inner joins used by that optimization is specific to a particular class of queries. Queries that do not meet those specific criteria continue to benefit from the unnormalized outer join definitions in the join index.
Join indexes defined with outer joins can cover a query submitted in inner join format directly. Once the Optimizer converts the outer join in the query to an inner join by taking advantage of the equivalency of outer and inner joins for foreign key-primary key relationships (see Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query), the system can make a coverage test instead of identical matching. In other words, the predicates in the join index definition and in the query need not be identical.
More On Outer Join Index Coverage of Queries
A join index can to be used to cover a wide variety of queries as long as the rows required in these queries form a subset of the row set contained in the join index. For example, consider a join index defined with the following SELECT query, where x1, x2 is a foreign key-primary key pair:
CREATE JOIN INDEX loj_cover AS SELECT x1, x2 FROM t1, t2 WHERE x1=x2;
Any query of the following form can use this join index, where c represents any set of constant conditions:
SELECT x1, x2
FROM t1 LEFT OUTER JOIN t2 ON x1=x2 AND c;
This property greatly increases the applicability of many join indexes.
Both the join index and the query are normalized to inner joins when the original form is defined with an outer join and there is a foreign key-primary key relationship between the join column set (see Restriction on Coverage by Join Indexes When a Join Index Definition References More Tables Than a Query). The result is that a less restrictive coverage test can be applied to both the query and to the join index.