Archives of the TeradataForum
Message Posted: Tue, 14 Jul 2004 @ 00:29:26 GMT
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.
|Copyright 2016 - All Rights Reserved|
|Last Modified: 23 Jun 2019|