Partition on yearmonth colum

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Enthusiast

Partition on yearmonth colum

Looking for how i can create a partition on a column containing date in YYYYMM format. 200001,..201402....201711,201712

 

CREATE TABLE customer
(
yearmonth INTEGER,

customername CHAR(50), 

...

...

)
PRIMARY INDEX ( yearmonth );

 

i could have created a partition if month and year were seperate but not sure how to using YYYYMM

PARTITION BY (
RANGE_N(cYEAR BETWEEN 1980 AND 2017 EACH 1),
RANGE_N(cMONTH BETWEEN 1 AND 12 EACH 1,
NO RANGE OR UNKNOWN ));

1 REPLY
Highlighted
Teradata Employee

Re: Partition on yearmonth colum

It's generally OK to have "extra" partitions in the range that will always be empty, provided you have stats on the system-derived PARTITION column. And with expanded partition limits since TD14.0 we don't have to be so careful about running out of partition numbers (though it will take a few extra bytes per row).

 

So this would probably be fine:

PARTITION BY (RANGE_N(YearMonth BETWEEN 200001 AND 201712 EACH 1))

Alternatively you can explicitly list multiple discrete ranges:

PARTITION BY (RANGE_N(YearMonth BETWEEN 200001 AND 200012 EACH 1, 200101 AND 200112 EACH 1, ...))