Home Page for the TeradataForum

Archives of the TeradataForum

Message Posted: Tue, 14 Jul 2004 @ 00:29:26 GMT

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

Subj:   Re: Joining to current month
From:   Fred Pluebell

Here are some options to consider. Create a view that supplies the literal value:

     CREATE VIEW MYDB.MOST_RECENT(period_end_date) AS
         SELECT date '2004-05-31';

Of course, you would need to do REPLACE VIEW whenever the "most recent" end date changes.

Or make certain the optimizer recognizes the view expression as returning a single row with high confidence, e.g.

     CREATE VIEW MYDB.MOST_RECENT(period_end_date) AS
         SELECT MAX(period_end_date) FROM MYDB.END_DATES_TABLE;

The latter will nearly always duplicate the one row on all AMPS and do a "product join" to filter during a RETRIEVE step. That's still not as good as a literal, which can be used as a residual condition for a JOIN (eliminating the separate RETRIEVE step and spool), but it often provides significant improvement.

  <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: 23 Jun 2019