The original post has been getting a little long in the tooth, and it’s no longer applicable to current Oracle versions. After a long debate with myself I decided to rewrite it; the old solution simply wasn’t tenable anymore.

WARNING: Partitioning is an extra cost option on top of Enterprise Edition. Do NOT use partitioning unless you are licensed appropriately to do so!

Background

Despite best planning efforts it might become necessary to introduce partitioning to a table. The example in this article is based the order entry schema created by Swingbench. For the sake of argument it has become necessary to partition the ORDERS table.

Properties of the ORDERS table

The ORDERS table features a primary key and a few auxiliary indexes,. There aren’t any triggers, no materialized views or other dependent objects affected by the introduction of partitions. In the real world you wouldn’t just partition ORDERS: the ORDER_ITEMS table should also be partitioned using the same partitioning model to allow for partition-wise joins.

Using ALTER TABLE … MODIFY

Oracle 12.2 introduced the ALTER TABLE … MODIFY command to convert a non-partitioned table into a partitioned one. It is pretty cool as it allows you to perform the operation online. It is an addition to the existing DBMS_REDEFINITION package. There are lots of posts about DBMS_REDEFINITION already, I’ll use the ALTER TABLE command instead. Please refer to the documentation for more information about DBMS_REDEFINITION.

Considerations before implementing the change:

• The primary key will become a global (non partitioned) index
• All auxiliary indexes will become local indexes
• The table will be partitioned by range using the ORDER_DATE column

The minimum ORDER_DATE found in the table was reported to be Jan 1st, 2007. I’ll use that as the lower limit. I’m using the interval partitioning method to assign rows to partitions, which will help with partitioning maintenance in the future. Interestingly ORDER_DATE was defined as TIMESTAMP WITH LOCAL TZ requiring some trickery and intimate knowledge of my timezone settings.

The following indexes are defined for the table:

SELECT
  index_name,
  uniqueness,
  partitioned
FROM
  user_indexes
WHERE
  table_name = 'ORDERS';

INDEX_NAME           UNIQUENESS    PARTITIONED    
____________________ _____________ ______________ 
ORDER_PK             UNIQUE        NO             
ORD_SALES_REP_IX     NONUNIQUE     NO             
ORD_CUSTOMER_IX      NONUNIQUE     NO             
ORD_ORDER_DATE_IX    NONUNIQUE     NO             
ORD_WAREHOUSE_IX     NONUNIQUE     NO             

Using the available information I used the following command to partition the ORDERS table online:

ALTER TABLE orders MODIFY
PARTITION BY RANGE (order_date) 
INTERVAL (numtoyminterval(1, 'MONTH'))
(
  PARTITION P1 VALUES LESS THAN (to_timestamp_tz('02.01.2007 europe/berlin', 'dd.mm.yyyy tzr'))
) 
ONLINE
  UPDATE INDEXES 
  (
    ORD_SALES_REP_IX LOCAL,
    ORD_CUSTOMER_IX LOCAL,
    ORD_ORDER_DATE_IX LOCAL,
    ORD_WAREHOUSE_IX LOCAL,
    ORDER_PK GLOBAL
  )
/

A little while later the table was partitioned, and so all its indexes with the exception of the primary key.