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?
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.
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 ?
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.
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%
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)
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
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)