Archives of the TeradataForum

Message Posted: Mon, 13 Sep 2010 @ 18:59:56 GMT

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

<-- Anonymously Posted: Monday, September 13, 2010 12:45 -->

Hello,

I am trying to create a partition on TimeStamp(3) column in a 2 Amp database.

Below is the DDL I am using ..

     COL_A TIMESTAMP(3),
     PRIMARY INDEX ( COL_A )
     PARTITION BY RANGE_N(COL_DTM (DATE) BETWEEN DATE '0001-01-01' AND DATE '1999-12-31' EACH
     INTERVAL '1000' YEAR , DATE '2000-01-01' AND DATE '2016-12-31' EACH INTERVAL '1' MONTH ,
     DATE '2017-01-01' AND DATE '8999-12-31' EACH INTERVAL '1000' YEAR , NO RANGE, UNKNOWN)
     UNIQUE INDEX PK217 ( COL_A ,COL_X);

But when I see the explains for the below queries, I do not see any partition elimination happening. Its always doing all AM Retrieve.

     -> EXPLAIN sel * from RPT_DEVELOPMENT_TABLES.USER_SESSION where CAST
     (COL_DTM AS DATE ) between '2004-01-01' AND '2005-01-01';

     -> EXPLAIN sel * from RPT_DEVELOPMENT_TABLES.USER_SESSION where CAST
     (COL_DTM AS DATE ) between DATE '2004-01-01' AND DATE '2005-01-01';

     -> EXPLAIN sel * from RPT_DEVELOPMENT_TABLES.USER_SESSION where
     -> CAST(COL_DTM AS DATE FORMAT 'YYYY-MM-DD') = '2004-01-01' AND DATE '2005-01-01';

When I partition on a DATE column, it works.

I am on TERADATA v2 R13 RELEASE 13.00.00.19

     VERSION 13.00.00.19
     LANGUAGE SUPPORT MODE Standard

Do any of you have any suggestions?

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