Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Mon, 10 Sep 2001 @ 17:33:43 GMT

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

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

  <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: 15 Jun 2023