Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Thu, 08 Jul 2010 @ 17:01:41 GMT


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


Subj:   Re: PPI Behavior
 
From:   Curley, David

It's a bug. Timestamp-based PPIs will use partition elimination only for a single equality predicate. No OR, IN or BETWEEN. I think it's fixed in 13. We just went to 12 and it's still there.

There is a work-around if you're not actually using the time portion of the timestamp (which might be your case since the field is named xxx_DT): create a table based on sys_calendar.calendar with UPI of calendar_date and a new field based on CAST(calendar_date as timestamp). You can then join to this on the PPI timestamp and use BETWEEN/IN/OR criteria on calendar_date. I don't know of anything you can do if you have time portions other than 00:00:00.

So you would do something like

     SELECT COUNT(*)
             FROM "V_WOFDWVW"."F_VOL_DCSN" "Volume_Measures"
             INNER JOIN some_database.new_calendar cal  -- your new table here
               on "Volume_Measures"."DW_END_DT" = cal.calendar_date_timestamp
            WHERE cal.calendar_date  BETWEEN '2010-12-31' AND '9999-12-31'

Dave



     
  <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