Extremely useful Partitioning enhancements in Oracle Database 12c

Back in January, I promised I would write a series of blogs on some of my favorite new 12c enhancements, designed to make your life easier. I’m finally getting around to keeping that promise with this weeks blog post on my favorite partitioning enhancements.

Imagine you have a large SALES table that contains information on all of the sales we have had in our chain of department stores.

DESC sales
 Name	          NULL?    TYPE
 ----------     -------- ------------------------
 PROD_ID        NOT NULL NUMBER(6)
 CUST_ID        NOT NULL NUMBER
 TIME_ID        NOT NULL DATE
 CHANNEL_ID     NOT NULL CHAR(1)
 PROMO_ID	NOT NULL NUMBER(6)
 QUANTITY_SOLD	NOT NULL NUMBER(3)
 AMOUNT_SOLD	NOT NULL NUMBER(10,2)
 REGION 	NOT NULL CHAR(3)

There are also 4 indexes on our SALES table to help with our analytic queries.

SELECT index_name, index_type
FROM   user_indexes 
WHERE  TABLE_NAME='SALES';
 
INDEX_NAME		       		INDEX_TYPE
------------------------------ ---------------------------
SALES_PROD_ID_BIX	       		BITMAP
SALES_CUST_ID_BIX	       		BITMAP
SALES_REGION_TIME_IDX	       	        NORMAL
SALES_PROMO__QUANITY_IDX       	        NORMAL

As our stores become more popular, the volume of data in our sales table starts to become unmanageable and impact the performance of our BI dashboards. The obvious solution is to partition the SALES table. The only problem is we have stores all over the world, so the schema needs to be available 24X7. Therefore we need to partition the SALES table online.

Prior to 12c, I would have to do a CREATE TABLE AS SELECT command to create a complete copy of the SALES table that is partitioned and then manually create all of the necessary constraints and indexes on the new partitioned table. Finally I would need to rename the tables, to make the new partitioned table appear as SALES. So, not an impossible tasks but a very manual and potentially error prone process.

Starting in Oracle Database 12c Release 2 (12.2) you can now convert a non-partitioned table to a partitioned table online with a simple ALTER TABLE command.

Let’s assume we will want to LIST partition the SALES table by region. The ALTER TABLE COMMAND would look as follows:

ALTER TABLE sales MODIFY
PARTITION BY LIST (region)
  (partition p1 VALUES ('USA'),
   partition p2 VALUES ('UK'),
   partition p3 VALUES ('IRE'),
   partition p4 VALUES ('OZ'))
UPDATE INDEXES ONLINE;
 
TABLE altered.
 
SELECT partition_name, high_value 
FROM   user_tab_partitions 
WHERE  TABLE_NAME='SALES';
 
PARTITION_NAME	HIGH_VALUE
-------------- -------------
P1		     'USA'
P2		     'UK'
P3		     'IRE'
P4		     'OZ'

You will notice that I have specified two additional clauses with this ALTER TABLE command:

• UPDATE INDEXES
• ONLINE

UPDATE INDEXES ensures that all Indexes on the SALES table will be converted and kept online throughout the conversion process. By default indexes will be handled as followed:

1. Global partitioned indexes will retain their original partitioning shape
2. Indexes that are not pre-fixed with the partitioning key will become global non-partitioned indexes
3. Indexes that are pre-fixed with the partitioning key will be converted to local partitioned indexes
4. Bitmap indexes will become local partitioned indexes

Let’s check what happened to our 4 original indexes.

SELECT index_name, index_type, partitioned 
FROM   user_indexes 
WHERE  TABLE_NAME='SALES';
 
INDEX_NAME		       	INDEX_TYPE	PAR
--------------------------- ----------------    ---
SALES_PROD_ID_BIX	       	BITMAP		YES
SALES_CUST_ID_BIX	       	BITMAP		YES
SALES_REGION_TIME_IDX	        NORMAL		YES
SALES_PROMO_QUANITY_IDX         NORMAL		NO

Our two bitmap indexes have became local partitioned indexes, as has our composite index (SALES_REGION_TIME_IDX) that is prefixed with our partitioning key column, REGION.

However, our composite index (SALES_PROMO_QUANITY_IDX) that is not prefixed with the partitioning key column remains a non-partitioned global index.

The ONLINE keyword ensures that DML operations will be allowed while the table is being partitioned.

Now that we have a partitioned table, we may want to load data into the table via a partition exchange load, which brings me to the second partitioning enhancement, a new create table syntax to help with partition exchange loads.

In case you are not familiar with a partition exchange load let me quickly recap what it is. A partition exchange load swaps the data of a standalone non-partitioned table into a particular partition in a partitioned table. Unlike loading data directly into a partitioned table, a partition exchange offers a greater degree of flexibility in terms of index maintenance and when the data becomes visible to the end-users.

However, no data is physically moved during a partition exchange load.

The Oracle data dictionary is simply updated to reset the pointer from the partition to being exchanged with the pointer of the standalone non-partitioned table to being loaded and vice versa. Because there is no physical movement of data, a partition exchange does not generate any redo and undo, making it a sub-second operation and far less likely to impact performance compared with any traditional data-movement approaches such as INSERT.

The most common problem encountered with a partition exchange load is ensuring the standalone non-partitioned table is both semantically and physically identical to the partitioned table.

With Oracle Database 12c, this potential wrinkle is eliminated with a new CREATE TABLE FOR EXCHANGE syntax. The new syntax creates an empty table that is identical both in terms of semantic and internal table shape with the partitioned table, so a partition exchange command will always succeed.

CREATE TABLE sales_xch 
FOR EXCHANGE WITH TABLE sales;
 
TABLE created.
 
DESC sales_xch
 Name		    NULL?    TYPE
 ---------------- -------- --------------
 PROD_ID	   NOT NULL 	NUMBER(6)
 CUST_ID	   NOT NULL 	NUMBER
 TIME_ID	   NOT NULL 	DATE
 CHANNEL_ID	   NOT NULL 	CHAR(1)
 PROMO_ID          NOT NULL 	NUMBER(6)
 QUANTITY_SOLD	   NOT NULL 	NUMBER(3)
 AMOUNT_SOLD	   NOT NULL 	NUMBER(10,2)
 REGION 	   NOT NULL 	CHAR(3)

Note, local indexes are not automatically created but they must be in place before a successful exchange can occur.

Finally, let’s assume we want to add more stores but we don’t know which country they will be in yet. Rather than having to add a new list partition each time we open a store, let’s convert the SALES table to be interval partitioned by list.

Starting in Oracle Database 12c Release 2 it’s now possible to use interval partitioning with list. Auto-List partitioning will automatically create a new list partition for each new value that is encountered.

We can evolve our list partition table into an auto-list partition table using a simple alter table command.

ALTER TABLE sales_xch SET PARTITIONING AUTOMATIC;
 
TABLE altered.
 
SELECT TABLE_NAME, partitioning_type, autolist 
FROM   user_part_tables;
 
TABLE_NAME		       PARTITION AUT
------------------------------ --------- ---
COSTS			       RANGE	 NO
SALES			       LIST	 YES

When we insert a record for a new region that doesn’t currently exist in the SALES table, Oracle will automatically create a new list partition, with a system generated name.

INSERT INTO sales VALUES
(11160, 40315, '15-MAY-17', 'I', 9999, 42, 1242, 'GER');
 
1 ROW created.
 
COMMIT;
 
Commit complete.
 
SELECT partition_name, high_value 
FROM   user_tab_partitions 
WHERE  TABLE_NAME='SALES';
 
PARTITION_NAME	 HIGH_VALUE
--------------- --------------------------------------
P1		       'USA'
P2		       'UK'
P3		       'IRE'
P4		       'OZ'
SYS_P3288	       :1

You can use the PARTITION FOR syntax to access a specific partition if you don’t know the system generated partition name.

SELECT * 
FROM sales PARTITION FOR ('GER');
 
   PROD_ID    CUST_ID TIME_ID	C   PROMO_ID QUANTITY_SOLD AMOUNT_SOLD REG
---------- ---------- --------- - ---------- ------------- ----------- ---
     11160	40315 15-MAY-17 I	9999		42	  1242 GER

For more details on partitioning enhancements in 12c, check out the awesome partitioning tutorial on Oracle Live SQL that Hermann Baer, the product manager and Oracle Partitioning expert, has put together!