Oracle Database 12c R2 Partitioning New Features – Automatic List Partitioning

Oracle Database 12.2 introduces new nice and powerful partitioning features. This post will introduce one of them “Automatic List Partitioning”. We may talk about other features in future posts.

Before 12.2, if you have a list partitioned table in specific discrete values, and there is a need to add a new list value, then you have to create a new partition manually for the new added value.

In 12.2, you don’t have to do that, Oracle will do it automatically on behave of you.

When you create the table, you just need to know one of the list values, not all of them and then let Oracle do the magic.

This is similar to the “Interval Partitioning” (introduced in Oracle 11g) relative to “Range Partitioning”. With “Interval Partitioning”, you don’t have to create new partitions for new periods manually, Oracle will do it for you automatically.

Let us take an example:

create table employees

(      empno                     number,

ename                      varchar2(100),

salary                        number,

marital_status       varchar2(2)

)

partition by list (marital_status) automatic

(      partition p1 values (‘MA’)                       — ‘MA’ = Married

)

/

Note the new keyword “automatic” introduced in 12.2. Also, note that when you create a table this way, you can’t create a default partition.

Let us insert some data in employees table:

insert into employees values (10,’Mohamed’,10000,’MA’);

==> Row was inserted successfully

Now let us insert another row:

insert into employees values (20,’Ahmed’,5000,’SI’);                       — ‘SI’ = Single

==>Row was inserted successfully

Although, we didn’t create a partition for the ‘SI’ value, but its partition was created automatically by Oracle.

Let us confirm this:

select partition_name, high_value

from user_tab_partitions

where table_name = ‘EMPLOYEES’;

So, Oracle automatically created a new partition called “SYS_P30192” at run time for the “SI” value.

Let us confirm also, the correct mapping between data and partitions:

select * from employees partition (SYS_P30192);

So, the new row was inserted correctly on the newly created partition.

So, with this new feature, you don’t have to know all the possible values for the list partitioning key. This feature is only available for list-partitions but is not available for list-subpartitions.

Additional points:

• You may use the following syntax “extended partition naming convention” to get the partition data without knowing the partition name:

select * from employees partition for (‘SI’);

• If you don’t like the partition’s system generated name, you can rename it as follows:

alter table employees rename partition for (‘SI’) to P_SI;

Remarks:

If the list-partitioned table is not configured for automatic partitioning, you can change this by:

ALTER TABLE employees SET PARTITIONING AUTOMATIC;

Also, if you want to check if a table is configured for automatic list partitioning or not:

SELECT TABLE_NAME, partitioning_type, autolist FROM user_part_tables;

Thanks
Ahmed