Archives of the TeradataForum
Message Posted: Wed, 08 Jan 2004 @ 03:07:17 GMT
| Subj: | | Re: View Performance |
| |
| From: | | Christie, Jon |
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.
Jon
| 
