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 (t1, t2, t3), (t2, t1, t3), (t2, t3, t1),and (t3, t2, t1). By adding the join on t1.c11 = t3.31, the optimizer 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.
Transitive closure is used only for normal equijoins, as shown above. Join transitive closure is not performed for:
Non-equijoins; for example, t1.c1 > t2.c2
Equijoins that include an expression; for example, t1.c1 = t2.c1 + 5
Equijoins under an or clause
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
Columns of incompatible datatypes
