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,
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
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.
- 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;
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;
Donate to support our blogging work