No partition pruning when Partitioning using date expression on timestamp column

Database

No partition pruning when Partitioning using date expression on timestamp column

TABLE DDL where partiting prunning occur:

CREATE MULTISET TABLE db1.tbl1 ,

     (

      Activity_Id INTEGER NOT NULL,

.....

      Order_Dttm TIMESTAMP(0),

.....................

)

PRIMARY INDEX ( Activity_Id )

PARTITION BY RANGE_N(CAST((Order_Dttm ) AS DATE  AT TIME ZONE 0 ) 

BETWEEN DATE '2010-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY , NO RANGE OR UNKNOWN);

TABLE DDL where partiting prunning DO NOT occur:

CREATE MULTISET TABLE db1.tbl1 ,

     (

      Activity_Id INTEGER NOT NULL,

.....

      Order_Dttm TIMESTAMP(0),

.....................

)

PRIMARY INDEX ( Activity_Id )

PARTITION BY RANGE_N(CAST((Order_Dttm ) AS DATE  ) 

BETWEEN DATE '2010-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' DAY , NO RANGE OR UNKNOWN);


I Can't understand why adding [AT TIME ZONE 0] enabled partition pruning, the column type is timestamp not (TS wz TZ) ! 

2 REPLIES

Re: No partition pruning when Partitioning using date expression on timestamp column

I too have faced this problem sometimes. Evenif there is a column on which a partition is defined, is used in a range query, the partition elimination doesn't occur.

Is there any specific guidelines that should be followed while coding a query that should use the partition elimination ?

Teradata Employee

Re: No partition pruning when Partitioning using date expression on timestamp column

Do you get hte issue with pruning only when using subqueries in the filtering criteria or it's happening even using constant values for the filtering in the query?

For me it's happening in the first case and not in the second.

No timezone specification in my DDL.