Skip to Main Content

Enhance Transitive Closure to include Join Predicates

Simon MooreMay 8 2018 — edited May 8 2018

The optimiser will only use transitive closure to propagate filter predicates, for example: WHERE t1.col1=t2.col2 and t2,col2=100 the optimiser will infer the condition: t1.col1=100

In the situation where we have a set of join predicates: WHERE t1.col1=t2.col2 AND t2.col2=t3.col3 the optimiser will not infer the condition: t1.col1=t3.col3 This means that the choice of join order is unnecessarily restricted.

In the scenario where there are value predicates on other columns of t1 and t3 but not on t2, it would make sense to join t2 as the final step after joining t1 to t3, but to do this efficiently, the statement needs to specify the join that should have been inferred: AND t1.col1=t3.col3

If a join order hint is used to put t2 at the end, a Cartesian join between t1 and t3 is performed, showing that the optimiser is unaware of the condition that can be inferred.

Post Details
Added on May 8 2018