Adding Partitions

Database
Enthusiast

Adding Partitions

I am attempting to add partitions to a non-empty table.

 

Table is defined with a partition that ends on 2018-12-31 (but also has the no range and unknown options) with an interval of 1 day.

I am trying to add 2019-01-01 through 2019-12-31.

 

I received the following error:

 

The facility of altering the partitioning of a nonempty table to increase the number of partitions higher than the maximum has not been implemented yet. 

 

Does anyone have any experience with this?

 


Accepted Solutions
Supporter

Re: Adding Partitions

It used to be that you could only add partitions at the 1st level of a multi-level partitioned table.
You are adding to the 3rd level.
Or was this limitation lifted in TD16 ?

Teradata Frank, Certified Master
1 ACCEPTED SOLUTION
17 REPLIES 17
Ambassador

Re: Adding Partitions

Can't say I have, but it sounds like you're trying to exceed the maximum number of allowed partitions.

 

On current versions of TD that is @2**63.

 

Did you get an error number with the message?

What is the current partitioning scheme for the table?

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Adding Partitions

Error number and message:  Failed [3732 : 42000] The facility of altering the partitioning of a nonempty table to increase the number of partitions higher than the maximum has not been implemented yet. 

 

Current partitioning:  

RANGE_N(CAST((EFF_END_TS ) AS DATE AT TIME ZONE 'America Eastern') BETWEEN DATE '2011-01-31' AND DATE '2014-09-29' EACH INTERVAL '1' MONTH ,

DATE '2014-09-30' AND DATE '2018-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE OR UNKNOWN) );

Teradata Employee

Re: Adding Partitions

Is your ALTER using ADD RANGE, or are you using a PARTITION BY clause (which would be asking to create an additional partitioning level)?

ALTER TABLE mytable MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE'2019-01-01' AND DATE'2019-12-31' EACH INTERVAL '1' DAY;

The original partitioning appears to define 2-byte partition numbers rather than 8-byte, but even with that it doesn't appear you ought to be near the limit.

 

Ambassador

Re: Adding Partitions

I don't think that is the full partitioning scheme because there is an extra ")" at the end of the RANGE_N clause. If I'm correct, please send the entire partitioning scheme.

 

Having said that, I've just created a table with this partitioning scheme, inserted some data and then changed the partitioning as you're trying to do and it worked fine for me. That was on TD 16.0.

 

My ALTER TABLE command was:

ALTER TABLE t1
MODIFY PRIMARY INDEX
ADD RANGE BETWEEN DATE '2019-01-01' AND DATE '2019-12-31' EACH INTERVAL '1' DAY

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Adding Partitions

OOPS.... I didn't realize I cut it off...

PARTITION BY ( CASE_N(

ACCT_BAL_TYP_CD =  'AMD',

ACCT_BAL_TYP_CD  IN ('DEB','QIT','POA'),

ACCT_BAL_TYP_CD  IN ('MIT','LIT','YIT','THB'),

 NO CASE OR UNKNOWN),CASE_N(

SRC_SYS_IDN_CD =  'ALS',

SRC_SYS_IDN_CD  IN ('IMPACS','IMCLOC'),

 NO CASE OR UNKNOWN),RANGE_N(CAST((EFF_END_TS ) AS DATE AT TIME ZONE 'America Eastern') BETWEEN DATE '2011-01-31' AND DATE '2014-09-29' EACH INTERVAL '1' MONTH ,

DATE '2014-09-30' AND DATE '2018-12-31' EACH INTERVAL '1' DAY ,

 NO RANGE OR UNKNOWN) );

Enthusiast

Re: Adding Partitions

We're on 16.10

 

Thanks for delving into this with me.

 

The regular alter statement works on any other tables.... just not this one.

Enthusiast

Re: Adding Partitions

Fred,

 

Thanks for helping.

 

I submitted  the following statement

 

alter table pidwdetaildata.pty_acct_litm_bal_hist MODIFY ADD RANGE between DATE '2019-01-01' and date '2019-12-31' each interval '1' day;

Ambassador

Re: Adding Partitions

Did the different syntax allow the command to work?

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: Adding Partitions

Hi Dave,

 

ALTER TABLE PIDWDETAILDATA.PTY_ACCT_LITM_BAL_HIST
MODIFY PRIMARY INDEX
ADD RANGE BETWEEN DATE '2019-01-01' AND DATE '2019-12-31' EACH INTERVAL '1' DAY;

 

Executed as Single statement. Failed [3732 : 42000] The facility of altering the partitioning of a nonempty table to increase the number of partitions higher than the maximum has not been implemented yet.
Elapsed time = 00:00:00.788

STATEMENT 1: Alter Table failed.