Adding Partitions

Database
Teradata Employee

Re: Adding Partitions

That's strange. Even with the multiple levels it doesn't seem like the new partitioning should exceed the 64K combined partition limit for 2-byte partitioning.

You may have to create a new table and copy the data.

Senior Apprentice

Re: Adding Partitions

Hi,

 

Thanks for that. I get the same error as you but I can't see why.

 

With your additional partitions you only need 23568 partitions, so still well inside the 2-byte partitioning limit.

 

I've tried creating a table with your new partitioning scheme and that works without issue.

 

Unless anyone else can spot the 'deliberate mistake' Smiley Happy then I think you'll have to raise an incident with TD.

 

To get your table working with the new date range, is it feasible to build a new table which has the required (new) partitioning?

 

Sorry I couldn't be more help.

 

Cheers,

Dave

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

Re: Adding Partitions

It's such a large table, that I was trying to avoid rebuilding if at all possible.  However, I'm running some tests on our other environment just to get some idea of the time that would take.

 

I do really appreciate both of you looking at it with me.   I think I will raise an incident with Teradata.

 

Thanks again!

Highlighted
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
Junior Contributor

Re: Adding Partitions

New ranges can be added (or dropped) only on the top level.

 

When you want ADD n rangen in any other level you must also DROP n ranges, e.g. add 12 new months and drop the 12 oldest months. The number of partitions must remain the same, otherwise internal partition numbers would change and all rows would be updated because PARTITION is part of the ROWID.

 

Since a few releases there's a new option to reserve a number of partitions for future use

PARTITION BY ( CASE_N(...), RANGE_N(...) ADD 1000 );

Now you could add 1000 partitions later.

 

For this existing PPI there's no way to ALTER the table, you must Insert/Select into a new table.

Of course, don't use the ADD feature, simply use DATE '2029-06-30' as range end (no overhead for empty partitions) to keep the 2-byte partitioning and avoid doing it again in the near future.

Enthusiast

Re: Adding Partitions

Thank you Dieter

Senior Apprentice

Re: Adding Partitions

Oops! @FGroenen is correct, the problem is as he said (embarrassing that I didn't spot it). @JustMe, sorry about that.

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

Re: Adding Partitions

Thank you ..... just seeing your reply.   I appreciate you taking the time to assist.