Archives of the TeradataForum

Message Posted: Fri, 30 Aug 2003 @ 00:05:34 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

Even with outer joins, the optimizer is not required to exactly match the join order the ANSI syntax implies. For instance:

T1
  left join
T2
    on T1.X = T2.X
  left join
T3
    on T1.X = T3.X;

The optimizer can join t1 to t2 and join the result to t3 as the query implies, but it can also join t1 to t3 first, and then join the result to t2. The result will be correct with either join order.

On the other hand:

T1
  left join
T2
    on T1.X = T2.X
  left join
T3
    on T2.Y = T3.Y;

will have to be joined in the order the query specifies.

Until things get fixed in 5.0.2, I would recommend avoiding ANSI syntax for inner joins. The reason is that ANSI syntax sometimes inhibits transitive closure. This is because terms in ON clauses are currently ignored when performing transitive closure.

Of course, once in a while transitive closure runs amok (or, more accurately, estimates go haywire due to a bunch of transitive terms) and you can improve a plan by introducing ANSI syntax specifically for the purpose of inhibiting transitive closure.

Each query is different. And tricks like adding or removing ANSI syntax to get better plans are tricks that may suddenly stop working in some future release. For instance, using derived tables to force certain join orders is likely to be ineffective in release 5.

	<Prev	Next>		<<First	<Prev	Next>	Last>>