How To Introduce Interval Partitioning into a Range Partitioned Table [ID 854332.1]

Goal

This note tells you how to introduce interval partitioning into a table that was originally created as a range-partitioned table, without having to recreate the table from scratch.

The example illustrates how to use the ALTER TABLE command and the INTERVAL partition definition to automatically create future partitions with a range of 1 day, while the existing partitions have a range of 1 month.

Fix

Follow the example below, which is based on the SH - Sales History demo schema.

Create the range-partitioned table and insert data

1. Create a range partitioned table with 2 partitions.

CREATE TABLE t_part (
col_date DATE,
col_text VARCHAR2(100),
col_number NUMBER(10))
PARTITION BY RANGE (col_date)
( 
  PARTITION P_200105 VALUES LESS THAN (TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN')), 
  PARTITION P_200106 VALUES LESS THAN (TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIAN'))
);

You can create the partition key with data type TIMESTAMP or TIMESTAMP WITH LOCAL TIMEZONE (but not with TIMESTAMP WITH TIMEZONE).

2. Create local index that is stored in DM tablespace.

CREATE INDEX t_part_idx on t_part(col_date) TABLESPACE DM LOCAL;

3. Insert 41625 rows of data.

INSERT INTO t_part SELECT time_id, channel_id, amount_sold FROM sales WHERE time_id BETWEEN to_date('01-MAY-2001') and  ('30-JUN-2001');
COMMIT;

4.  Notice that USER_TAB_PARTITIONS and USER_IND_PARTITIONS show the following:

Table           PARTITION_NAME  HIGH_VALUE                                                                                 TABLESPACE_NAME
--------------- --------------- ------------------------------------------------------------------------------------------ ---------------
T_PART          P_200105        TO_DATE(' 2001-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           USERS
T_PART          P_200106        TO_DATE(' 2001-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA           USERS


Index                PARTITION_NAME       STATUS
-------------------- -------------------- -------
T_PART_IDX           P_200105             DM
T_PART_IDX           P_200106             DM

5.  Notice that rows inserted outside the existing, range-partitioned boundaries fail, as we expect:

insert into t_part select time_id, CHANNEL_ID, amount_sold from sales where time_id between to_date('01-JUL-2001') and  ('02-JUL-2001')
            *
ERROR at line 1:
ORA-14400: inserted partition key does not map to any partition

Alter the table to use a partitioning interval

The following steps show how to change the interval for future partitions beyond the highest value allowed for the last range partition.

1.  Alter the table, set the interval, and specify tablespace(s) for the interval partitions, as shown.   If you specify one tablespace, then all the newly created interval partitions will be created in that tablespace.  If you specify a list of  tablespaces  (for example, TS1, TS2), then the newly created interval partitions will be created in the tablespaces in a circular manner (that is, the first interval partition will be created in TS1, the second one in TS2, the third interval partiton in TS1 again, and so forth).

alter table t_part set interval(numtodsinterval(1,'day'));
alter table t_part set store in (TS1,TS2);

2. Insert the next 4 days of data.

insert into t_part select time_id, CHANNEL_ID, amount_sold from sales where time_id between to_date('01-JUL-2001') and  ('04-JUL-2001');
commit;

3.  Notice that for each day a partition is created by Oracle, it has an internal name starting with SYS:

Table             PARTITION_NAME            HIGH_VALUE                        
----------------- ------------------------- --------------------------------
T_PART            P_200105                  TIMESTAMP' 2001-06-01 00:00:00'   
T_PART            P_200106                  TIMESTAMP' 2001-07-01 00:00:00'   
T_PART            SYS_P5793                 TIMESTAMP' 2001-07-03 00:00:00'   
T_PART            SYS_P5794                 TIMESTAMP' 2001-07-04 00:00:00'   
T_PART            SYS_P5795                 TIMESTAMP' 2001-07-02 00:00:00'   
T_PART            SYS_P5796                 TIMESTAMP' 2001-07-05 00:00:00'    

Note: Per the Oracle� Database SQL Language Reference 11g Release 1 (11.1) 

• To change an interval-partitioned table back to a range-partitioned table. Use SET INTERVAL () to disable interval partitioning. The database converts existing interval partitions to range partitions, using the higher boundaries of created interval partitions as upper boundaries for the range partitions to be created.

• You can also use SET INTERVAL(<numtodsinterval expression>) to change the interval of an existing interval-partitioned table. The database converts existing interval partitions to range partitions, and then automatically creates partitions of the specified numeric range or datetime interval as needed for data beyond the highest value allowed for the last range partition.

Refer to documentation for the NUMTODSINTERVAL function, also in the Oracle� DatabaseSQL Language Reference 11g Release 1 (11.1) documentation, for a list of valid value expressions.

Currently Interval Partitioning cannot be specified at sub-partition level. Corresponding enhancement exists.