Posts

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