Automatic List Partitioning: 12 Things Developers Will Love About Oracle Database 12c Release 2 Part 10

List partitions are great when you have a column with a specific set of values you want to carve into separate partitions. Things like states, countries and currencies are all good examples.

Reference data like these change rarely. But they do change. For example, South Sudan came into being in 2011.

If you list partitioned your data by country you need to keep your partitions up-to-date. Particularly if you let customers provide their own values. Or you could end up with embarrassing errors such as:

SQL Error: ORA-14400: inserted partition key does not map to any partition

Which of course will happen at 2am. A great way to incur the wrath of the on-call DBA!

To avoid this you could create a default partition. Any new values would then go into this. This prevented inserts throwing exceptions. But all new values go into the default partition. Over time this would fill up with all the new values.

So you need a regular maintenance task to split values out as needed.

12.2 resolves this with Automatic List Partitioning. Every time you insert new values, Oracle Database will create the new partition on the fly!

To use it, simply place the automatic keyword after the partition column:

create table orders ( 
  customer_id integer not null, 
  order_datetime date not null, 
  country_iso_code varchar2(2) not null 
) partition by list (country_iso_code) automatic ( 
  partition pUS values ('US'), 
  partition pGB values ('GB'), 
  partition pDE values ('DE'), 
  partition pFR values ('FR'), 
  partition pIT values ('IT') 
); 
insert into orders values (1, sysdate, 'ZA'); 
select partition_name 
from   user_tab_partitions 
where  table_name = 'ORDERS'; 
PARTITION_NAME 
-------------- 
PDE 
PFR 
PGB 
PIT 
PUS 
SYS_P1386

Note the new partition will have a system-generated name. So you may want to change them to meaningful names. You can do this with:

alter table orders rename partition SYS_P1386 to pZA;

Be aware. The default partition and automatic list partitioning are mutually exclusive options:

create table orders ( 
   customer_id integer not null, 
   order_datetime date not null, 
   country_iso_code varchar2(2) not null 
) partition by list (country_iso_code) automatic ( 
  partition pUS values ('US'), 
  partition pGB values ('GB'), 
  partition pDE values ('DE'), 
  partition pFR values ('FR'), 
  partition pIT values ('IT'), 
  partition pDEF values (default) 
); 
SQL Error: ORA-14851: DEFAULT [sub]partition cannot be specified for AUTOLIST [sub]partitioned objects.

Which makes sense when you think about it. But if you want to migrate list partitions with a default to automatic, you’ll need to go through a process. First split everything out of the default partition, then drop it:

create table orders ( 
  customer_id integer not null, 
  order_datetime date not null, 
  country_iso_code varchar2(2) not null 
) partition by list (country_iso_code) ( 
  partition pUS values ('US'), 
  partition pGB values ('GB'), 
  partition pDE values ('DE'), 
  partition pFR values ('FR'), 
  partition pIT values ('IT'),   
  partition pDEF values (default) 
); 
insert into orders values (1, sysdate, 'ZA'); 
insert into orders values (2, sysdate, 'JP'); 
alter table orders split partition pDEF into ( 
  partition pZA values ('ZA'), 
  partition pJP values ('JP'), 
  partition pDEF 
); 
alter table orders drop partition pDEF; 
alter table orders set partitioning automatic;

Note this does leave a brief time when there’s no default partition. And automatic partitioning isn’t ready. So you may want to take a short outage to do this!

Full article originally published at blogs.oracle.com on November 10, 2016.