Archives of the TeradataForum

Message Posted: Fri, 29 Aug 2003 @ 15:30:30 GMT

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

As Jon says, the order that tables are listed in the FROM clause is not supposed to make a difference in the resulting join logic. I have seen one case in the V2R3 timeframe (since fixed) with a large table, a small table and three medium sized tables where changing the order of the medium tables affected the join. The medium tables were all about the same size and shape, and apparently the order of the tables was the tie-breaker for the optimizer. One of the medium table was much more selective than the others (not obvious from the stats), so its position in the join affected the intermediate row counts and that affected the performance of the query.

However, I want to clarify Eric's post a bit. If you use ANSI INNER JOIN syntax in the FROM clause prior to V2R5.2 (or V2R6.0 depending), your INNER JOINs will take place in the order listed just like OUTER JOINs. This is a side effect of how it was implemented, i.e. as part of the feature that added OUTER JOIN. No effort was made (at the time) to connect the new INNER JOIN syntax to the extensive join analysis logic already being done for WHERE clause joins. I guess the frequency of use on the ANSI syntax has gotten large enough (through tool vendors, perhaps) that NCR decided that they had to fix it.

For some time now I've been 'fixing' broken/poorly performing SQL by rewriting it using the ANSI syntax. I'm suspect that 1) I tend to put the more selective tables near the front, and 2) the optimizer chooses conservative join sequences, which is why this technique has worked so well. I shudder to think how many of the 'fixes' will come unglued when we upgrade to V2R5.2. Hopefully by then, the underlying problems that brought the SQL to my attention in the first place will be corrected. Otherwise, I'm going to be one busy, busy programmer.

/dave "ansi oops" hough

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