Archives of the TeradataForum

Message Posted: Thu, 28 May 2004 @ 00:14:38 GMT

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

Folks,

I have a sql query which does exactly what I want to do when I have the predicate in the query. The date filter on the PI precedes the product join, thereby computing the rank for a specified date

     SELECT DISTINCT
             d.uniqueid, x.calendar_date as msc_date, d.ocd, d.cid, a.ce_rank as MSC_Rank
      FROM
      (sys_calendar.calendar x JOIN DER_CSS d ON (x.calendar_date >= cast(d.SSSDt as date)
              AND x.calendar_date <= coalesce(cast(d.SSEDt as date),CURRENT_DATE))
       JOIN INTER_CSS_CRGRNK a ON (a.uniqueid = d.uniqueid AND a.cid = d.cid))
       where x.calendar_date = cast( '2001-06-01'  as   date)
      GROUP BY d.Uniqueid, msc_date QUALIFY rank( MSC_Rank asc) = 1

However, when I have the predicate outside a subquery, the explain shows the date filter getting applied in the penultimate step. We would like to have the predicate applied first, before other joins are processed. We just migrated to V2R5.1 and we used to get the plans we wished when we stayed in V2R4

     select * from
     ( SELECT DISTINCT
               d.uniqueid, x.calendar_date as msc_date, d.ocd, d.cid, a.ce_rank as MSC_Rank
       FROM
       (sys_calendar.calendar x JOIN DER_CSS d ON (x.calendar_date >= cast(d.SSSDt as date)
          AND x.calendar_date <= coalesce(cast(d.SSEDt as date),CURRENT_DATE))
       JOIN INTER_CSS_CRGRNK a ON (a.uniqueid = d.uniqueid AND a.cid = d.cid))
       GROUP BY d.Uniqueid, msc_date QUALIFY rank( MSC_Rank asc) = 1
     ) x
     where x.msc_date = cast( '2001-06-01'  as   date)

I appreciate any solution that will push the predicate inside the derived table or view.

thanks

------------------------------------
Bhupesh Singu
Database Administrator
ITE, Dept. of Administrative Services
-----------------------------------

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