equi-join predicate transitive closure applied where applicable

The optimizer applies transitive closure to join columns for a normal equi-join. The following query specifies the equi-join of t1.c11 and t2.c21, and the equi-join of t2.c21 and t3.c31:

select * 
from t1, t2, t3
where t1.c11 = t2.c21
      and t2.c21 = t3.c31
      and t3.c31 = 1

Without join transitive closure, the only join orders considered are (t1, t2, t3), (t2, t1, t3), (t2, t3, t1),and (t3, t2, t1). By adding the join on t1.c11 = t3.31, the query processor expands the list of join orders to include: (t1, t3, t2) and (t3, t1, t2). Search argument transitive closure applies the condition specified by t3.c31 = 1 to the join columns of t1 and t2.

Similarly, equi-join transitive closure is also applied to equi-joins with or predicates as follows:

select *
from R,S 
where R.a = S.a
and (R.a = 5 OR S.b = 6)

The query optimizer infers that this would be equivalent to:

select * 
from R,S 
where R.a = S.a 
and (S.a = 5 or S.b = 6)

The or predicate could be evaluated on the scan of S and possibly be used for an or optimization, thereby effectively using the indexes of S.

Another example of join transitive closure is its application to compex SARGs, so that a query such as:

select * 
from R,S 
where R.a = S.a and (R.a + S.b = 6)

is transformed and inferred as:

select * 
from R,S 
where R.a = S.a 
and (S.a + S.b = 6)

The complex predicate could be evaluated on the scan of S, resulting in significant performance improvements due to early result set filtering.

Transitive closure is used only for normal equi-joins, as shown. join transitive closure is not performed for:

• Non-equi-joins; for example, t1.c1 > t2.c2

• Outer joins; for example, t1.c11 *= t2.c2, or left join or right join

• Joins across subquery boundaries

• Joins used to check referential integrity or the with check option on views