Oracle Blogs: The latest in Oracle Partitioning

The latest in Oracle Partitioning - Part 2: Multi Column List Partitioning

By Hermann Baer-Oracle on Mar 20, 2017

This is the second blog about new partitioning functionality in Oracle Database 12c Release 2, available on-premise for Linux x86-64, Solaris Sparc64, and Solaris x86-64 and for everybody else in the Oracle Cloud .

This one will talk about multi column list partitioning, a new partitioning methodology in the family of list partitioning. There will be more for this method, coming in a future blog post (how about that for a teaser?).

Just like read only partitions, this functionality is rather self-explaining. Unlike in earlier releases, we now can specify more than one column as partition key columns for list partitioned tables, enabling you to model even more business use cases natively with Oracle Partitioning.

So let's start off with a very simple example:

CREATE TABLE mc
PARTITION BY LIST (col1, col2)
(PARTITION p1 VALUES ((1,2),(3,4)),
 PARTITION p2 VALUES ((4,5)),
 PARTITION p3 VALUES (DEFAULT))
AS SELECT rownum col1, rownum+1 col2
FROM DUAL CONNECT BY LEVEL <= 10;

Yes, you can have a partitioned table with ten records, although I highly recommend NOT to assume this as best practice for real world environments. Just because you can create partitions - and many of them - you should always bear in mind that partitions come with a "cost" in terms of additional metadata in the data dictionary (and row cache, library cache), with additional work for parsing statements and so forth. Ten records per partition don't cut it. You should always consider having a reasonable amount of data per partition, but that's a topic for another day.

When we now look at the metadata of this newly created table you will see the partition value pairs listed as HIGH VALUE in the partitioning metadata:

SQL> SELECT partition_name, high_value FROM user_tab_partitions WHERE table_name='MC';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ------------------------------
P1                             ( 1, 2 ), ( 3, 4 )
P2                             ( 4, 4 )
P3                             DEFAULT
Now, while I talked about a "new partitioning strategy" a bit earlier, from a metadata perspective it isn't one. For the database metadata it is "only" a functional enhancement for list partitioning: the number of partition key columns is greater than one:

SQL> SELECT table_name, partitioning_type, partitioning_key_count FROM user_part_tables WHERE table_name='MC';

TABLE_NAME                     PARTITION PARTITIONING_KEY_COUNT
------------------------------ --------- ----------------------
MC                             LIST                           2

Let's now look into the data placement using the partition extended syntax and query our newly created table. Using the extended partition syntax is equivalent to specifying a filter predicate that exactly matches the partitioning criteria and an easy way to safe some typing. Note that both variants of the partition extended syntax - specifying a partition by name or by pointing to a specific record within a partition - can be used for any partition maintenance operation and also in conjunction with DML.

SQL> SELECT * FROM mc PARTITION (p1);

      COL1       COL2
---------- ----------
         1          2
         3          4
I can get exactly the same result when I am using the other variant of the partition extended syntax:

SQL> SELECT * FROM mc PARTITION FOR (1,2);
      COL1       COL2
---------- ----------
         1          2
         3          4
After having built a simple multi column list partitioned table with some data, let's just do one basic partition maintenance operation, namely a split operation on partition P1 that we just looked at. You might remember that this partition has two sets of key pairs as partition key definition, namely (1,2) and (3,4). We use the new functionality of doing this split in an online mode:

SQL> ALTER TABLE mc SPLIT PARTITION p1 INTO (PARTITION p1a VALUES (1,2), PARTITION p1b) ONLINE;

Table MC altered.
Unlike offline partition maintenance operations (PMOP) that take an exclusive DML lock on the partitions the database is working on (which prohibits any DML change while the PMOP is in flight), an online PMOP does not take any exclusive locks and allows not only queries (like offline operations) but also continuous DML operations while the operation is ongoing.

After we have now done this split, let's check the data containment in our newly created partition P3:

SQL> SELECT * FROM mc PARTITION (p1a);

      COL1       COL2
---------- ----------
         1          2
That's about it for now for multi column list partitioned tables. I am sure I have forgotten some little details here and there and I am sure that this short blog post is probably not answering all questions you are having. So please, stay tuned and if you have any comments about this specific one or suggestions for future blog posts, then please let me know. You can always reach me at hermann.baer@oracle.com.

Another one down, many more to go.