Oracle CBO performs transitivity as the first step in optimization. Transitivity means generating additional predicates based on existing predicates. The main advantage is the inclusion of extra access methods. Transitivity is not done for join predicates.

Assume A, B, and C are columns. If A=5 and A=B, you can conclude that B=5. This can be applied to queries as shown on the following page. Note that A=5 contains a constant.
However, in terms of joins, if A=B and B=C, it is not concluded that A=C; in other words, the Oracle server does not generate transitive closures for join predicates.
For more details about transitivity, see Doc ID 68979.1

 

0 replies

Leave a Reply

Want to join the discussion?
Feel free to contribute!

Leave a Reply

Your email address will not be published. Required fields are marked *