 |
 |
Archives of the TeradataForum
Message Posted: Mon, 10 Sep 2001 @ 17:33:43 GMT
| Subj: | | Re: SELECT vs. Temporary Table |
| |
| From: | | Pekka Kostamaa |
As Jim Judge points out, using a search condition (such as A.date = 'XXX') in an ON clause, which specifies joins, is not recommended.
For inner joins, the results are what the user wants, but not for outer joins. It is best to always place search conditions in the WHERE
clause, "where" they belong.
As for the question about query performance, use of views, derived tables, or temp tables, we do not have enough information. If the
query (or queries) are such that the view is "materialized" more than once, then the temp table solution will improve performance. The same
applies for derived tables. If the derived table is specified more than once, and if the optimizer does not (or cannot) recognize common
spool use, then the temp table solution will perform better.
There can be also an improvement in the temp table solution, if the primary key of the temp table can be customized to improve the
performance of the subsequent join steps.
Pekka Kostamaa
ex-Teradata Performance Analyst and Architect
| |
