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.
