All Forums Database
gskaushik 56 posts Joined 09/10
10 Jul 2015
Best way to add a partition to an existing table

Kindly do suggest me the best way to add a partition to an existing table
1 way :-
1. rename the existing table
2. create a new table with similiar structure
3. Insert data from renamed table to this table
4. Collect stats
2 Way :-
Use an alter table statement
As we are going to do in production , I feel that 1 st way would be the right thing to as it would result in much IO.
Kindly do suggest me on the same.
Thanks in advance for your help
Note :-
Database version level - 13.10

Regards Subramanian kaushik Gurumoorthy
venkylingutla 19 posts Joined 06/12
13 Jul 2015

Hi Kaushik,
1 way is the good one. Even we follow the same in our application.

dnoeth 4628 posts Joined 11/04
13 Jul 2015

Hi Kaushik,
#2 is always preferred, unless you actually can't use ALTER TABLE (because you're not adding a range to a RANGE_N).
If there's no NO RANGE partition it's a fast modification of the table header, otherwise it's additionally scanning the NO RANGE for rows fitting into the new partition. If the NO RANGE is small it's still quite fast.
In fact when you use RANGE_N you should try to define the partitioning schema in such a way that you never have to add partitions as empty partition have zero overhead, e.g. for dates far into the future like 2030.
The same for dropping partitions, it's usually better to simply DELETE with WHERE-condition matching the partitioning definition, i.e. all rows of a partition (The rules are similar to a fastpath-DELETE).


You must sign in to leave a comment.