Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Mon, 20 Sep 2010 @ 22:01:57 GMT


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


Subj:   Re: Table partitioning on TIMESTAMP(3) in Teradata 13
 
From:   Curley, David

Prior to TD12, you could use partition elimination on a timestamp column only for an equality, not a range:

     Select *
     From MyPPITable
     Where ppi_ts = '2009/12/01 08:35:27'

That would use partition elimination. It would not eliminate partitions if you cast the timestamp to a date - you have to let the database do that under the covers.

But

     Select *
     From MyPPITable
     Where ppi_ts between '2009/12/01 08:00:00' and '2009/12/01 08:59:59'

Would not use partition elimination.

In TD12, even the equality doesn't work.

However, you can trick it to use dynamic partition elimination. We have a date table similar to sys_calendar.calendar but with additional fields, including a 00:00:00 timestamp of calendar_date; UPI on calendar_date. With this table, we can do

     Select p.*
     From MyPPITable p
     Inner join MyCalendar c
     On p.ppi_ts = c.calendar_date_ts
     Where calendar date between '2009/12/01' and '2009/12/01'

This will use dynamic partition elimination, and also works with an equality. Once you have this, you can also add whatever timestamp restrictions you want.

As others have said, though, the best approach is not to use timestamps if you can at all help it.


Dave C



     
  <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: 15 Jun 2023