All Forums Database
MgajsK 9 posts Joined 11/09
10 Sep 2010
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

Cyberness 2 posts Joined 01/10
10 Sep 2010

Hi,

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

MgajsK 9 posts Joined 11/09
10 Sep 2010

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?

robpaller 159 posts Joined 05/09
15 Sep 2010

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.

MgajsK 9 posts Joined 11/09
23 Sep 2010

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.

robpaller 159 posts Joined 05/09
24 Sep 2010

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.

SANJI 12 posts Joined 08/10
01 Mar 2013

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

Adeel Chaudhry 773 posts Joined 04/08
02 Mar 2013

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!

-- If you are stuck at something .... consider it an opportunity to think anew.

dnoeth 4628 posts Joined 11/04
02 Mar 2013

Lets's recap :-)
TD13: not possible
TD13.10: Sanjeev's version
TD14: Adeel's version
Dieter

Dieter

Adeel Chaudhry 773 posts Joined 04/08
03 Mar 2013

Perfect .... Dieter :)

-- If you are stuck at something .... consider it an opportunity to think anew.

mahatikrishna 1 post Joined 03/13
03 Mar 2013

Hi,i am new to teradata, can you please explain the primary index mechanism of data distribution

KS42982 137 posts Joined 12/12
04 Mar 2013

There are lot of documents available online to understand the primary index mechanism of data distribution. If you are new then you can start with this very basic document.

http://teradata.uark.edu/research/wang/indexes.html

AshishPatil 24 posts Joined 05/12
30 May 2013

Hi,
 
We are on TD 13.10.
We implemented PPI on timestamp column with below syntax,
CREATE SET TABLE .....
.
.
.
 

UNIQUE PRIMARY INDEX ( DATASOURCE_NUM_ID ,INTEGRATION_ID ,X_ORDER_TYPE ,X_INVC_DT )

PARTITION BY(RANGE_N(CAST((X_INVC_DT) AS DATE AT LOCAL) BETWEEN '2000-01-01' AND '2020-12-31' EACH INTERVAL '1' MONTH,

 NO RANGE OR UNKNOWN),

CASE_N(

X_ORDER_TYPE =  'Order1',

X_ORDER_TYPE =  'Order2',

X_ORDER_TYPE =  'Order_T,

 NO CASE, UNKNOWN) )

 

So, in Informatica jobs in order to use MLOAD on this table, we have to make all PPI columns i.e. ( DATASOURCE_NUM_ID ,INTEGRATION_ID ,X_ORDER_TYPE ,X_INVC_DT ) as primary key(Also PPI must be Unique).

 

 

But while running job with MLOAD Update, received TD 6760 error i.e. "Invalid Timestamp".

 

I think there is some issue with date column on which we created partition.

 

Kindly suggest how to overcome this error?

 

Regards,

Ashish

You must sign in to leave a comment.