ALTER PPI - performance impact

Database

ALTER PPI - performance impact

Hi,

 We are planning to extend our partition range from 2015 to 2025 using ALTER statement. I heard that ALTER is not suggestable for huge tables. My question is for adding partitions to huge tables, can we use

ALTER TABLE <TABEL NAME> MODIFY PRIMARY INDEX ADD RANGE BETWEEN DATE '2016-01-01' AND DATE '2025-12-31' EACH INTERVAL '1' MONTH;

 " or do we need use ....back up---->create with new partition definition -->copy data etc.

 Any pointer to documentation is very helpful

My thought is there is no datamovement involved in adding new partitions so "ALTER" will work fine. I want to hear from you,do you see any risk with this approach?

In case minor data movement involved can we use "ALTER"? following is the scenario

partitions created from 01-01-2009 to 31-12-2025 and we are seeing data for Dec 2008 and we want extend lower end partition can we use "ALTER" in this case?

8 REPLIES
N/A

Re: ALTER PPI - performance impact

Altering a primary index to add ranges is very fast and inexpensive.</p>

Alter can take a while if your are altering existing or adding new columns to a large table and since an exclusive lock is used may want to be avoided.

Also you can only add empty partiions using alter so for the table with

'partitions created from 01-01-2009 to 31-12-2025 and we are seeing data for Dec 2008"

you must have no_range defined. To add this range you will create a new table and insert.

Rglass

N/A

Re: ALTER PPI - performance impact

In fact you can add partitions where data exists. ADD RANGE will result in a scan of the NO RANGE partition, moving matching rows into the new partitions. 

But it's not really efficient (SELECT the rows into a spool, then DELETE using that spool and then MERGE) using Transient Journal, but you shouldn't have a huge number of row in NO RANGE anyway :-)

Similar for a DROP RANGE, if the partitions are not empty, those rows will be moved to an existing NO RANGE.

Re: ALTER PPI - performance impact

Thank You Rglass for the quick update

Re: ALTER PPI - performance impact

Thanks Dieter for explaining in detail

Re: ALTER PPI - performance impact

If the table is under BLOCK LEVEL COMPRESSION(BLC) and there are some rows present in UNKNOWN partition,if we perform ALTER to add partitions will decompress the entire table first and then scan through the unknkown pertition for qualified rows and then make changes to system tables with extended range?

If the above is correct "ALTER" is not a suggestable option for HUGE tables with BLC ?

Teradata Employee

Re: ALTER PPI - performance impact

It will not uncompress the entire table, only what it needs to look at to perform the operation. In the example above, it would only uncompress the data in the unknown partition to see what if anything needs to move into the new partitions defined.

Re: ALTER PPI - performance impact

Thanks for the quick response

Re: ALTER PPI - performance impact

You may want to check your PPI column distribution first before using the Alter table method:

 

Our System: EDW - 36 Amps

Our Expereince:  We expanded the end range from 12/31/2015 to 12/31/2025

                           - very little other processing was going on

                           - Tablesize = 4.9 gig,     7.2 million rows

                           - TransientJournal table --- maxed at 2.7 terabytes

                           - processing took 14 hrs and 10 minutes

                           - Amp CPU Skew was above 80% most of the time, and often above 90%

The table:

                LineServiceDateFrom range:              01/01/0001 to 2/24/2026

                        Range 01/01/0001 to 12/31/1999    =  21,221 rows

                        Range 01/01/2016 to 2/24/2026      =   6,008,310 rows

Calendar Dates      1st of the month    - > 200,000 rows

     The Rest of the month   most were < 200 rows with a few between 200 rows and 2000 rows.  

 

Claimlines : Before Alter Table Statement)

dbc.PartitioningConstraintsV

CHECK ((RANGE_N(LineServiceDateFrom  BETWEEN DATE '0001-01-01' AND DATE '1999-12-31' EACH INTERVAL '500' YEAR ,DATE '2000-01-01' AND DATE '2005-12-31' EACH INTERVAL '1' YEAR ,DATE '2006-01-01' AND DATE '2015-12-31' EACH INTERVAL '1' MONTH , NO RANGE, UNKNOWN)) BETWEEN 1 AND 00132)

 

ClaimLines: Add PPI Range

ALTER TABLE MMAtst.ClaimLines

mODIFY PRIMARY INDEX ( TransactionControlNumber )

ADD RANGE BETWEEN DATE '2016-01-01' AND DATE '2020-12-31'

EACH INTERVAL '1' MONTH ;

 

ClaimLines: After Alter Table Statement

dbc.PartitioningConstraintsV

CHECK ((RANGE_N(LineServiceDateFrom BETWEEN DATE '0001-01-01' AND DATE '1999-12-31' EACH INTERVAL '500' YEAR ,

DATE '2000-01-01' AND DATE '2005-12-31' EACH INTERVAL '1' YEAR ,

DATE '2006-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' MONTH ,

 NO RANGE, UNKNOWN)) BETWEEN 1 AND 65535)