The only way to define list subpartitions for future interval partitions is with the subpartition template. If you do not use the subpartitioning template, then the only subpartition that are created for every interval partition is a DEFAULT subpartition. To learn more about how to use a subpartition template, see "Specifying Subpartition Templates to Describe Composite Partitioned Tables".
Example 4-15 shows the sales table, interval partitioned using daily intervals on time_id, with list subpartitions by channel_id.
Example 4-15 Creating a composite interval-list partitioned table
CREATE TABLE sales
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) INTERVAL (NUMTODSINTERVAL(1,'DAY'))
SUBPARTITION BY LIST (channel_id)
SUBPARTITION TEMPLATE
( SUBPARTITION p_catalog VALUES ('C')
, SUBPARTITION p_internet VALUES ('I')
, SUBPARTITION p_partners VALUES ('P')
, SUBPARTITION p_direct_sales VALUES ('S')
, SUBPARTITION p_tele_sales VALUES ('T')
)
(PARTITION before_2000 VALUES LESS THAN (TO_DATE('01-JAN-2000','dd-MON-yyyy'))
)
PARALLEL;