Archives of the TeradataForum

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

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

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>>