All Forums Database
syam1406 10 posts Joined 07/11
16 Jul 2015
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?

Glass 225 posts Joined 04/10
16 Jul 2015

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

dnoeth 4628 posts Joined 11/04
16 Jul 2015

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.

Dieter

syam1406 10 posts Joined 07/11
16 Jul 2015

Thank You Rglass for the quick update

syam1406 10 posts Joined 07/11
16 Jul 2015

Thanks Dieter for explaining in detail

syam1406 10 posts Joined 07/11
24 Jul 2015

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 ?

ToddAWalter 316 posts Joined 10/11
24 Jul 2015

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.

syam1406 10 posts Joined 07/11
24 Jul 2015

Thanks for the quick response

You must sign in to leave a comment.