equijoin predicate transitive closure applied where applicable

The optimizer applies transitive closure to join columns for a normal equijoin. The following query specifies the equijoin of t1.c11 and t2.c21, and the equijoin 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.c31, the query processor expands the list of join orders with these possibilities: (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, equijoin transitive closure is also applied to equijoins 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 the following query 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 using the indexes of S very effectively.

Another example of join transitive closure is its application to nonsimple 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 equijoins, as shown. join transitive closure is not performed for:

Nonequijoins; 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

As of Adaptive Server Enterprise 15.0, the sp_configure option to turn on or off join transitive closure and sort merge join has been discontinued. Whenever applicable, join transitive closure is always applied in Adaptive Server Enterprise 15.0 and later.