Best way to add a partition to an existing table

Database
Enthusiast

Best way to add a partition to an existing table

Hi,

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

2 REPLIES
Enthusiast

Re: Best way to add a partition to an existing table

Hi Kaushik,

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

Junior Contributor

Re: Best way to add a partition to an existing table

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