Interval Partitions in Oracle 11g

Among many other enhancements in Oracle 11g, interval partition is definitely a good one for DBAs. There is a good article here discussing it (and other partition enhancements).

In one of our projects, we are looking for sub-partitions within an interval partition, and it's well able to handle. Here's an extended discussion of the link above.

This time we have a interval partition, and list subpartitions inside.

CREATE TABLE interval_tab (
  id           NUMBER,
  code         VARCHAR2(10),
  description  VARCHAR2(50),
  created_date DATE
)
PARTITION BY RANGE (created_date)
INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY LIST( code )
SUBPARTITION TEMPLATE
  ( SUBPARTITION CD_01 VALUES ('ONE'),
    SUBPARTITION CD_02 VALUES ('TWO'),
    SUBPARTITION CD_03 VALUES ('THREE')
  )
(
   PARTITION part_01 values LESS THAN (TO_DATE('01-NOV-2007','DD-MON-YYYY'))
);

INSERT INTO interval_tab VALUES (1, 'ONE', 'One', TO_DATE('16-OCT-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (2, 'TWO', 'Two', TO_DATE('31-OCT-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

COLUMN partition_name FORMAT A20
COLUMN subpartition_name FORMAT A20
COLUMN high_value FORMAT A10

SELECT partition_name, subpartition_name, high_value, num_rows
FROM   user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;

PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE   NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01              PART_01_CD_01        'ONE'               1
PART_01              PART_01_CD_02        'TWO'               1
PART_01              PART_01_CD_03        'THREE'             0

By adding more data that expands the partitions, you'll see the subpartitions are generated as well.

INSERT INTO interval_tab VALUES (3, 'THREE', 'Three', TO_DATE('01-NOV-2007', 'DD-MON-YYYY'));
INSERT INTO interval_tab VALUES (4, 'TWO', 'TWO', TO_DATE('30-NOV-2007', 'DD-MON-YYYY'));
COMMIT;
EXEC DBMS_STATS.gather_table_stats(USER, 'INTERVAL_TAB');

SELECT partition_name, subpartition_name, high_value, num_rows
FROM   user_tab_subpartitions
where table_name = 'INTERVAL_TAB'
ORDER BY table_name, partition_name, subpartition_name;

PARTITION_NAME       SUBPARTITION_NAME    HIGH_VALUE   NUM_ROWS
-------------------- -------------------- ---------- ----------
PART_01              PART_01_CD_01        'ONE'               1
PART_01              PART_01_CD_02        'TWO'               1
PART_01              PART_01_CD_03        'THREE'             0
SYS_P40              SYS_SUBP37           'ONE'               0
SYS_P40              SYS_SUBP38           'TWO'               1
SYS_P40              SYS_SUBP39           'THREE'             1

Subpartition names are automatically with system name, which is not I'm expecting: it's better to named as partition + subpartition_template, ie, SYS_P40_CD_01.