I wrote a short note a little while ago demonstrating how flexible Oracle 12.2 can be about physically rebuilding a table online to introduce or change the partitioning while discarding data, and so on. But what do you do (as a recent question on ODC asked) if you want to upgrade a customer’s database to meet the requirements of a new release of your application by changing a partitioned table into a composite partitioned table and don’t have enough room to do an online rebuild. Which could require two copies of the data to exist at the same time.)
If you’ve got the down time (and not necessarily a lot is needed) you can fall back on “traditional methods” with some 12c enhancements. Let’s start with a range partitioned table:
rem
rem Script: pt_comp_from_pt.sql
rem Author: Jonathan Lewis
rem Dated: May 2019
rem
create table pt_range (
id number(8,0) not null,
grp varchar2(1) not null,
small_vc varchar2(10),
padding varchar2(100)
)
partition by range(id) (
partition p200 values less than (200),
partition p400 values less than (400),
partition p600 values less than (600)
)
;
insert into pt_range
select
rownum-1,
mod(rownum,2),
lpad(rownum,10,'0'),
rpad('x',100,'x')
from
all_objects
where
rownum <= 600
;
commit;
So we’ve got a range-partitioned table with three partitions and some data in each partition. Let’s pretend we want to change this to range/list with the grp column as the subpartition key, allowing explicit use of values 0,1,2 and a bucket subpartition for anything else. First we create an empty version of the table with a suitable subpartition template, and a simple heap table to be used as an exchange medium:
create table pt_range_list (
id number(8,0) not null,
grp varchar2(1) not null,
small_vc varchar2(10),
padding varchar2(100)
)
partition by range(id)
subpartition by list (grp)
subpartition template (
subpartition p_def values(default)
)
(
partition p200 values less than (200),
partition p400 values less than (400),
partition p600 values less than (600)
)
;
prompt ===============================================
prompt First nice 12.2 feature - "create for exchange"
prompt ===============================================
create table t for exchange with table pt_range;
You’ll notice that our subpartition template identifies just a single subpartition that takes default values – i.e. anything for which no explicit subpartition has been identified. This means we have a one to one correspondance between the data segments of the original table and the copy table. So now we go through a tedious loop (which we could code up with a PL/SQL “execute immediate” approach) to do a double-exchange for each partition in turn. (Any PL/SQL code is left as an exercise to the interested reader.)
alter table pt_range exchange partition p200 with table t;
alter table pt_range_list exchange subpartition p200_p_def with table t;
alter table pt_range exchange partition p400 with table t;
alter table pt_range_list exchange subpartition p400_p_def with table t;
alter table pt_range exchange partition p600 with table t;
alter table pt_range_list exchange subpartition p600_p_def with table t;
prompt =====================================
prompt Show that we've got the data in place
prompt =====================================
execute dbms_stats.gather_table_stats(user,'pt_range_list',granularity=>'ALL')
break on partition_name skip 1
select partition_name, subpartition_name, num_rows
from user_tab_subpartitions
where table_name = 'PT_RANGE_LIST'
order by
partition_name, subpartition_name
;
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
---------------------- ---------------------- ----------
P200 P200_P_DEF 200
P400 P400_P_DEF 200
P600 P600_P_DEF 200
3 rows selected.
We now have to split the newly arrived subpartitions into the 4 pieces we want – but before we do that let’s make sure that any new partitions automatically have the correct subpartitions by changing the subpartition template:
alter table pt_range_list
set subpartition template(
subpartition p_0 values (0),
subpartition p_1 values (1),
subpartition p_2 values (2),
subpartition p_def values (default)
)
;
prompt =========================================================
prompt Second nice 12.2 feature - multiple splits in one command
prompt We could do this online after allowing the users back on.
prompt =========================================================
alter table pt_range_list split subpartition p200_p_def
into (
subpartition p200_p_0 values(0),
subpartition p200_p_1 values(1),
subpartition p200_p_2 values(2),
subpartition p200_p_def
)
;
alter table pt_range_list split subpartition p400_p_def
into (
subpartition p400_p_0 values(0),
subpartition p400_p_1 values(1),
subpartition p400_p_2 values(2),
subpartition p400_p_def
)
;
alter table pt_range_list split subpartition p600_p_def
into (
subpartition p600_p_0 values(0),
subpartition p600_p_1 values(1),
subpartition p600_p_2 values(2),
subpartition p600_p_def
)
;
Now, just to check that everything is behaving, let’s add a new partition, and check to see what partitions and subpartitions we end up with:
alter table pt_range_list add partition p800 values less than (800);
execute dbms_stats.gather_table_stats(user,'pt_range_list',granularity=>'ALL')
select partition_name, subpartition_name, num_rows
from user_tab_subpartitions
where table_name = 'PT_RANGE_LIST'
order by
partition_name, subpartition_name
;
PARTITION_NAME SUBPARTITION_NAME NUM_ROWS
---------------------- ---------------------- ----------
P200 P200_P_0 100
P200_P_1 100
P200_P_2 0
P200_P_DEF 0
P400 P400_P_0 100
P400_P_1 100
P400_P_2 0
P400_P_DEF 0
P600 P600_P_0 100
P600_P_1 100
P600_P_2 0
P600_P_DEF 0
P800 P800_P_0 0
P800_P_1 0
P800_P_2 0
P800_P_DEF 0
16 rows selected.
And as a final note – if we decide we want to put it all back we could merge four subpartitions down to one subpartition with a single command – then loop through every partition in turn:
alter table pt_range_list
merge subpartitions p200_p_0, p200_p_1, p200_p_2, p200_p_def
into subpartition p200_p_def
;
And now I feel like I’m turning into Tim Hall – writing potentially useful demonstrations instead of trying to baffle people with rocket science. But I hope to get over that quite soon. Of course I have left out some important “real-world” details – particularly how you choose to handle indexes while doing the double-exchange. My view would be to take the local indexes with you on the exchange, bypass the global indexes on the exchange out, and be choosy about which global indexes to maintain on the exchange back in; but it all depends on how much downtime you have, how many indexes there are, and the state they’re likely to start or end in.
As ever it’s possible to start with a simple idea like this, then discover there are real-world complications that have to be dealt with. So there’s another article in the pipeline to handle a slightly more complex case. I’ll also be publishing a short note about the easy way of getting the job done from 18c onwards – if you’ve got the spare resources.
