Join transitive closure

Preprocessing applies transitive closure to join columns for normal equijoins if join transitive closure is enabled at the server or session level. 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 (t1t2t3), (t2t1t3), (t2t3t1),and (t3t2t1). By adding the join on t1.c11 = t3.31, the optimizer expands the list of join orders with these possibilities: (t1t3t2) and (t3t1t2). Search argument transitive closure applies the condition specified by t3.c31 = 1 to the join columns of t1 and t2.

Transitive closure is used only for normal equijoins, as shown above. Join transitive closure is not performed for: