Archives of the TeradataForum

Message Posted: Tue, 12 Feb 2002 @ 18:39:13 GMT

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

I once asked this question of NCR, and this is my recollection of their answer...

Plan differences can arise because the INNER JOIN syntax isolates the join conditions in the ON phrase from any additional constraints in the WHERE clause. In the older syntax, all the constraints are combined in the WHERE clause alone, making it easier to do global optimization. At the time I asked, NCR was putting a lot of effort into the optimizer so that WHERE conditions would be more visible to the ON clause processor.

Queries are not normally sensitive to things like ON versus WHERE syntax, or the order of the tables in the FROM clause. Since the variations in syntax can produce slightly different parse trees, however, those differences can be the deciding factor on which execution plan is chosen if two candidate plans have nearly equal cost.

NCR wanted me to send them any queries where equivalent ON/WHERE syntax or order dependencies resulted in large (>5x) differences in run times. And they are more interested in ON/WHERE and table order issues than things like IN/EXISTS or NOT IN/NOT EXISTS.

My experience has been that this is difficult to do, because the optimizer takes so many things into consideration when choosing a plan. Typically, moving the problem query to another (test) system changes the behavior, as does refreshing the statistics or modifying the content of the underlying tables.

The one really annoying example we had changed behavior over twenty efix levels or so, and finally lost its syntax sensitivity altogether. The final plan was a little slower than the original, but it was worth it for the consistency.

/dave hough

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