Table partitioning on TIMESTAMP(3) in Teradata 13

Database
Enthusiast

Table partitioning on TIMESTAMP(3) in Teradata 13

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 (Initial_Page_View_DTM AS DATE ) between '2004-01-01' AND '2005-01-01';

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

-> EXPLAIN sel * from RPT_DEVELOPMENT_TABLES.USER_SESSION where CAST(Initial_Page_View_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?

MGajsk
12 REPLIES

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

Hi,

I think it will not and should not work at all. It's like applying a function to indexed column, do you see?
Enthusiast

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

I am sorry I did not get you. While it did create 45 partitions based on the timestamp casted as DATE column, I was hoping it will hit the right partition when queried using the date range. OR AM I totally missing something basic here ?

or TERADATA does not allow partitioning on TIMESTAMP?
Enthusiast

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

I don't have the manuals in front of me to confirm the partitioning on a TIMESTAMP or not.

But try this:

PARTITION BY RANGE_N(COL_DTM BETWEEN TIMESTAMP '0001-01-01 00:00:00.000' AND TIMESTAMP '1999-12-31 23:59:59.999' EACH INTERVAL '1000' YEAR ,
TIMESTAMP '2000-01-01 00:00:00.000' AND TIMESTAMP '2016-12-31 23:59:59.999' EACH INTERVAL '1' MONTH ,
TIMESTAMP '2017-01-01 00:00:00.000' AND TIMESTAMP '9999-12-31 23:59:59.999' EACH INTERVAL '1000' YEAR)

COLLECT STATS ON . INDEX (COLA);
COLLECT STATS ON .
COLUMN(COL_DTM);

I have dropped NO RANGE and UNKNOWN because the partitioning statement covers the acceptable range of TIMESTAMP. Double check my timestamp format for the 3 fractional seconds of precision. I think I should have it right. If you are not really going to store data with dates prior to 1900 I wouldn't define them as a RANGE and then define the NO RANGE option to catch your "bad" data.

Hope this helps.
Enthusiast

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

Looks like you cannot.
create table failed. 3732: the facility of a test value with a data type other than integer or DATE has not been implemented yet. I am surprised to see that 13 is not allowing to partition on character fields either.
Enthusiast

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

With character fields you may have to jump through some hoops to get it to work. I managed to do it on TD 12 but it required the use of HASHROW and HASHBUCKET if I recall correctly and it may have been in part due to the fact it was multi-level partitioned with a DATE (integer) field. You also have to consider that the combination of both can not exceed 65535 partitions.

Have you considered splitting the TIMESTAMP into a DATE and a TIME field? So that you can partition on the date and then possibly further qualify on the time range.
Enthusiast

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

CREATE TABLE SSURI.PPI_TEST (TRACKINGNUM INTEGER, AUD_TIME TIMESTAMP)

PRIMARY INDEX PPI_TEST_PR (TRACKINGNUM)

PARTITION BY RANGE_N(CAST((AUD_TIME) AS DATE AT LOCAL) BETWEEN '2009-12-31' AND '2013-12-31' EACH INTERVAL '1' DAY);

INSERT INTO SSURI.PPI_TEST

SELECT CDTRK#, AUD_TIME FROM SSURI.CUSLDSFL_SI SAMPLE 0.05

WHERE CAST(AUD_TIME AS DATE) BETWEEN '2009-12-31' AND '2013-12-31'

COLLECT STATS SSURI.PPI_TEST COLUMN PARTITION;

COLLECT STATS SSURI.PPI_TEST COLUMN AUD_TIME;

COLLECT STATS SSURI.PPI_TEST COLUMN TRACKINGNUM;

SELECT TOP 10 PARTITION, CAST(AUD_TIME AS DATE), COUNT(*) FROM SSURI.PPI_TEST GROUP BY 1,2 ORDER BY 1

PARTITION    AUD_TIME    Count(*)

----------------------------------------

3    1/2/2010    596

4    1/3/2010    3072

5    1/4/2010    45956

6    1/5/2010    32130

7    1/6/2010    32571

8    1/7/2010    28969

9    1/8/2010    26795

10    1/9/2010    2275

11    1/10/2010    1734

12    1/11/2010    38274

SELECT * FROM SSURI.PPI_TEST WHERE AUD_TIME BETWEEN CAST(DATE '2009-12-31' AS TIMESTAMP) AND CAST(DATE '2010-12-31'  AS TIMESTAMP);

  3) We DO an ALL-AMPs RETRIEVE step FROM 366 partitions OF

     SSURI.PPI_TEST WITH a CONDITION OF ("(SSURI.PPI_TEST.AUD_TIME <=

     TIMESTAMP '2010-12-31 00:00:00.000000') AND

     (SSURI.PPI_TEST.AUD_TIME >= TIMESTAMP '2009-12-31

     00:00:00.000000')") INTO SPOOL 1 (group_amps), which IS built

     locally ON the AMPs.  The SIZE OF SPOOL 1 IS estimated WITH HIGH

     confidence TO be 10,061,272 ROWS (513,124,872 BYTES).  The

     estimated TIME FOR this step IS 0.18 seconds.

The behaviour can be best described as weird.. but it works.

Sanjeev

Teradata Employee

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

Can be done using follows:

PARTITION BY RANGE_N(ts1 BETWEEN

TIMESTAMP '0001-01-01 00:00:00.000000+00:00' AND

TIMESTAMP '9999-12-31 23:23:59.999999+00:00'

EACH INTERVAL '1' DAY);

HTH!

Senior Apprentice

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

Lets's recap :-)

TD13: not possible

TD13.10: Sanjeev's version

TD14: Adeel's version

Dieter

Teradata Employee

Re: Table partitioning on TIMESTAMP(3) in Teradata 13

Perfect .... Dieter :)