Oracle composite partitioning: subpartition templates

When creating composite partitoned tables, a subpartition template simplifies the partition-specification for its subpartitions.

Composite partitioned tables with a interval-partition require a subpartition template.

create table tq84_interval_list (
   num  number,
   txt  varchar2(3),
   val  number
)
partition    by range (num) interval (100)
subpartition by list  (txt)
subpartition template (
   subpartition sp_foo values ('foo'),
   subpartition sp_bar values ('bar'),
   subpartition sp_baz values ('baz')
)
(
   partition p_init values less than (100)
);

insert into tq84_interval_list values ( 50, 'bar', 1.1);

column    partition_name format a30
column subpartition_name format a30
column high_value        format a30

select
   subpar.partition_name,
   subpar.subpartition_name,
   tabpar.high_value
from
  user_tab_partitions    tabpar join
  user_tab_subpartitions subpar on   tabpar.table_name = subpar.table_name
where
  tabpar.table_name = 'TQ84_INTERVAL_LIST';

insert into tq84_interval_list values (350, 'baz', 7.7);

select
   subpar.partition_name,
   subpar.subpartition_name,
   tabpar.high_value
from
  user_tab_partitions    tabpar join
  user_tab_subpartitions subpar on   tabpar.table_name = subpar.table_name
where
  tabpar.table_name = 'TQ84_INTERVAL_LIST';

drop table tq84_interval_list;