Oracle 12cR2 : Partitioning improvements – multi-column list partitioning & read-only partitions

In my last blog post I presented auto-list partitioning, a new partitioning functionality coming with 12cR2.
In this one I will introduce two others : multi-column list partitioning and read-only partitions.

Multi-column list partitioning

Image : “Oracle Partitioning in Oracle Database 12c Release 2” – Hermann Bär

With the first release of 12c it wasn’t possible to create list partitioned tables based on multi-column partition key :
ORA-14304: List partitioning method expects a single partitioning column
But now you can easily implement this functionality :
SQL> CREATE TABLE cars(
car_make VARCHAR2(30),
car_model VARCHAR2(30)
)
PARTITION BY LIST (car_make, car_model)
(PARTITION P1 VALUES ('Ford','Focus'),
PARTITION P_DEFAULT VALUES (DEFAULT));

Table created.

Check partitions :
SQL> SELECT partition_name, high_value FROM dba_tab_partitions WHERE table_name = 'CARS';


PARTITION_NAME HIGH_VALUE
-------------------- ------------------------------
P1 ( 'Ford', 'Focus' )
P_DEFAULT DEFAULT


SQL> SELECT partitioning_type, partition_count, partitioning_key_count FROM dba_part_tables WHERE table_name = 'CARS';


PARTITION PARTITION_COUNT PARTITIONING_KEY_COUNT
--------- --------------- ----------------------
LIST 2 2

This functionality allows you to use up to 16 columns for the partition key and as you can see it in this example, it’s also possible to define a DEFAULT partition.
Multi-column list partitioning on subpartitions is also permitted.

Read-only partitions

Image : “Oracle Partitioning in Oracle Database 12c Release 2” – Hermann Bär

We all know that Oracle is able to define a tablespace or a table as read-only. But did you know that with 12cR2 it’s now possible to define this attribute at the partition level ? :
CREATE TABLE sales(
sales_product VARCHAR2(30),
sales_date DATE
) READ WRITE
PARTITION BY RANGE (sales_date)
(
PARTITION P_2013 VALUES LESS THAN (TO_DATE('01-JAN-2014','dd-MON-yyyy')) READ ONLY,
PARTITION P_2014 VALUES LESS THAN (TO_DATE('01-JAN-2015','dd-MON-yyyy')) READ ONLY,
PARTITION P_2015 VALUES LESS THAN (TO_DATE('01-JAN-2016','dd-MON-yyyy')) READ ONLY,
PARTITION P_2016 VALUES LESS THAN (TO_DATE('01-JAN-2017','dd-MON-yyyy')));
Data insertion :
SQL> INSERT INTO sales VALUES ('SHIRT', '02-MAY-2013');
INSERT INTO sales VALUES ('SHIRT', '02-MAY-2013')
*
ERROR at line 1:
ORA-14466: Data in a read-only partition or subpartition cannot be modified.
SQL> INSERT INTO sales VALUES ('SHOES', '29-MAR-2016');


1 row created.

Easy to implement and reliable way to protect data changes inside a table.