Home Page for the TeradataForum
 

Archives of the TeradataForum

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


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


Subj:   Pushing the predicate inside a derived table or view
 
From:   Singu, Bhupesh

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>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016