Table partitioning is a feature often used in large databases with very large tables as a means to help improve the performance of accessing data in those tables. Partitioning offers advantages such as being able to spread out and balance IO across multiple devices through placing partitions into separate tablespaces and partition pruning, where the cost based optimizer can read only the blocks in specific partitions into memory when the where clause criteria match the partitioning options that were specified when the table was created.
There are several partitioning methods available. Below is a list of the different partitioning methods that are available in the Oracle Database.
- Range – Range partitioning distributes the rows based on a specific range of values in a column (very frequently used to partition on date based data). Range partitioning was introduced back in Oracle 8i.
- Hash – Hash partitioning divides the rows based on an even but somewhat random distribution by specifying a particular column to be used for the hash key. Hash partitioning was the first type of supported partitioning and goes back to Oracle 8.
- List – List partitioning divides the rows based on a discreet list of values for a column (often used to partition, based on geography such as state names). List partitioning was added as a new partitioning method in Oracle 9i.
- Interval – Interval partitioning is an extension of range partitioning that instructs the database to automatically create a new range partition when data is added that does not belong to any of the existing partitions. Interval partitioning was a new feature introduced with Oracle 11g.
- System – System partitioning is somewhat similar to hash partitioning but without the need of specifying a column to use as the hash key column. Like Interval partitioning, system partitioning was introduced in 11g.
- Reference – Reference partitioning is a feature that partitions the rows in a child table to match how its parent table rows were partitioned without having to duplicate the partition key column in the child table. For example, if the orders table is partitioned by order date, the child table can have its rows partitioned to match, but without having to duplicate the order date in the child records. A formal foreign key constraint must be defined on the two tables in order to be able to configure reference partitioning on the child table. Reference partitioning is also an 11g feature.
In addition, composite partitioning is available where we can partition a table two ways, for example, first by range as the primary partition and then by list as sub-partitions within each primary partition. Over the different versions of Oracle from Oracle 9i through Oracle 11g, virtually every combination of composite partitioning is now supported.
In Oracle Database 12c, several new features have been added to enhance working with and maintaining partitioned tables.
Online Partition Move Enhancements
Prior to 12c, several partition maintenance commands would cause the partition to be locked against DML commands until the partition maintenance command completed. For example, issuing an ALTER PARTITION MOVE command would prevent DML from being able to be executed until the move was complete. If the table had very large partitions, this would impact the ability of users to do work while the partition maintenance operations were being done, which could be problematic in systems that needed to support 24×7 user interactivity.
In 12c, that same command can now be issued as an online command, which no longer prevents concurrent DML commands from being executed.
An often-used partition maintenance command is the ALTER TABLE MOVE command, which can be used to move older partitions to different storage devices by moving the partition to a different tablespace. One advantage of being able to move partitions like this, especially with range partitions, is that they can be moved to a lower cost and perhaps slower storage device once the data in the partition is old enough that it is no longer very frequently accessed. Additionally the move can incorporate compressing the data in the partition as it’s being moved. Here are a couple of examples of the new online move syntax for moving partitions:
ALTER TABLE our_sales
MOVE PARTITION our_sales_part_1
TABLESPACE old_data_ts
UPDATE INDEXES ONLINE;
ALTER TABLE our_sales
MOVE PARTITION our_sales_part_1
COMPRESS {BASIC/FOR OLTP}
UPDATE INDEXES ONLINE;
Reference Partitioning Enhancements
Reference partitioning is a very new partitioning style, and there were several limitations not only in setting up the child table, but also in doing partition maintenance operations on the parent table and ensuring that the child partition tables would be kept synchronized with the parent. In order to enhance working with reference partitioned tables, three new features have been added in Oracle Database 12c.
First, reference partitioning can now be specified on a child table where the parent table has been partitioned using interval partitioning. In 11g this was not supported.
Second, a new CASCADE option is available for TRUNCATE operations on the parent table, which will automatically also truncate the corresponding child table partitions. In 11g the TRUNCATE partition command would have be issued specifically on the child table.
Third, the CASCADE option has also been added to the partition EXCHANGE operation and will modify the child table partitions to match the parent table modifications. Like the TRUNCATE option, in 11g any EXCHANGE commands would have had to be specifically issued against the child table.
Multi-Partition Maintenance Operations
Prior to 12c, merge and split partition operations were limited to only two partitions at a time. This meant that if we wanted to take an existing partition and split it 12 times (for example take a year-long partition and split it into 12 monthly partitions) we would have to issue 11 separate SPLIT PARTITION DDL commands.
The same thing would pertain to trying to merge 12 separate partitions into one single partition would have required 11 MERGE PARTITION DDL commands.
ALTER TABLE my_part_tab SPLIT PARTITION part_1 INTO (part_one VALUES LESS THAN (50), part_two VALUES LESS THAN (100), part_three VALUES LESS THAN (150), part_four; ALTER TABLE my_part_tab MERGE PARTITIONS part_one, part_two, part_three, part_four INTO PARTITION part_1;
Not only have both MERGE and SPLIT commands been modified, but also the ALTER TABLE ADD PARTITION and ALTER TABLE TRUNCATE PARTITION and ALTER TABLE DROP PARTITION commands have been enhanced to allow multiple partitions to be added in the same command.
ALTER TABLE my_part ADD PARTITION (my_new_part_10) VALUES…… PARTITION (my_new_part_11) VALUES…… PARTITION (my_new_part_12) VALUES……; ALTER TABLE my_part TRUNCATE PARTITIONS (my_new_part_10, my_new_part_11); ALTER TABLE my_part DROP PARTITIONS (my_new_part_10, my_new_part_11, my_new_part_12);
Partitioned tables continue to be used in many systems as performance enhancing options on large tables, and with the new features that have been added, Oracle Database 12c has taken some interesting steps to make managing them more effective and efficient.
