Archives of the TeradataForum

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

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

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>>