ORACLE 12C RELEASE 2 PARTITIONING NEW FEATURES

A number of enhancements to the Oracle database Partitioning option have been introduced in Oracle Database 12c Release 2.

These include:

• Automatic List Partitioning
• Multi-Column List Partitioning
• Read-only Partitions
• Filtered Partition maintenance operations
• Online conversion of non-partitioned to partitioned table
• Partitioned External Tables

Similar to the interval partitioning method which was introduced in Oracle Database 12c Release 1, one of the new partitioning features introduced in Oracle 12c Release 2 is the Automatic List Partitioning feature which makes the partitioned table easier to manage.

The ability to partition a table based on distinct values in a column has been around since quite some time, but what is new in Oracle database 12.2 is that we can initially partition the table based on values that are known and present in the column we would like to partition the table on. As data is loaded into the table, new partitions are automatically created by the database if the loaded partition key value does not correspond to any of the existing partitions.

In Oracle Database 12c Release 2, we can now partition a table based on list of values of multiple columns and not just a single column as in the case of previous versions. This is called Multi-Column List Partitioning.

So if we have a very large table containing sales data for the entire country, we can partition the table based on a state and as well as a city located in the same state.

Another new feature is Read-Only Partitions. So, the entire table is not made read-only, but just some partitions in the table can be made read-only. Maybe we have a requirement to store historical data and older partitions in the table can be made read-only to prevent any DML operations on such ‘old’ data.

As DBA’s, one of the common partition maintenance operations we had to carry out quite regularly was moving partitions – maybe older partitions of the table are moved to a different tablespace hosted on low cost storage because this data is infrequently accessed. Now in Oracle Database 12.2, we can combine partition maintenance operations like MOVE PARTITION, MERGE PARTITION and SPLIT PARTITION with data filtering. So for example, while we are moving data of a particular partition, we can also filter data which will be moved. By specifying a filtering predicate via the INCLUDING ROWS keyword, we can determine what data should be preserved when the partition maintenance operation is carried out and what data can be discarded.

But one of the best new 12.2 features is that we can now convert a non-partitioned table into a partitioned table very easily via the ALTER TABLE command with the MODIFY clause as well as while concurrent DML operations are being performed on the table. So the conversion of the table is an online process and with the UPDATE INDEXES clause we can also convert any indexes on the table into either local or global partitioned indexes as well.

We can also now create a Partitioned External Table. So basically, we are creating a table based on data hosted outside the Oracle database and we partition the table which contains this external data for better optimization of queries which would be really beneficial if we are dealing with very large volumes of these external data sets.

Let us have a look at all these 12c Release 2 Partitioning new features.