Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Wed, 08 Jan 2004 @ 03:07:17 GMT

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

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.


  <Prev Next>   <<First <Prev Next> Last>>  
  Top Home Privacy Feedback  
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 28 Jun 2020