Archives of the TeradataForum
Message Posted: Wed, 08 Jan 2004 @ 03:07:17 GMT
There is more than just the transitive closure issue that currently can cause the optimizer to produce different plans with ansi and non-ansi syntax, but I don't know all the gory details.
The transitive closure issue is fairly straightforward, though. Terms appearing in ON clauses are ignored when doing transitive closure. So with ansi syntax you could get fewer transitive terms. With non-ansi syntax (which does not have ON clauses) you could get more transitive terms.
I've used this both ways. Sometimes, lots of transitive terms cause cardinality estimates to be too low. In that case, I might be able to reduce the number of transitive terms by "hiding" terms that lead to transitive term in ON clauses. But other times, useful transitive terms might not be generated because the terms that lead to them are in an ON clause. In that case, the terms need to be "liberated" by switching to non-ansi syntax.
If you try using this trick, remember it probably won't be effective from version 5.1.0 up because Teradata plans to fix the problem.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 27 Dec 2016|