For range-hash, interval-hash, and list-hash partitioned tables, the subpartition template can describe the subpartitions in detail, or it can specify just the number of hash subpartitions.
Example 4-17 creates a range-hash partitioned table using a subpartition template and displays the subpartition names and tablespaces.
The example produces a table with the following description.
Every partition has four subpartitions as described in the subpartition template.
Each subpartition has a tablespace specified. It is required that if a tablespace is specified for one subpartition in a subpartition template, then one must be specified for all.
The names of the subpartitions, unless you use interval-* subpartitioning, are generated by concatenating the partition name with the subpartition name in the form:
partition name_subpartition name
For interval-* subpartitioning, the subpartition names are system-generated in the form:
SYS_SUBPn
Example 4-17 Creating a range-hash partitioned table with a subpartition template
CREATE TABLE employees_sub_template (department_id NUMBER(4) NOT NULL,
last_name VARCHAR2(25), job_id VARCHAR2(10))
PARTITION BY RANGE(department_id) SUBPARTITION BY HASH(last_name)
SUBPARTITION TEMPLATE
(SUBPARTITION a TABLESPACE ts1,
SUBPARTITION b TABLESPACE ts2,
SUBPARTITION c TABLESPACE ts3,
SUBPARTITION d TABLESPACE ts4
)
(PARTITION p1 VALUES LESS THAN (1000),
PARTITION p2 VALUES LESS THAN (2000),
PARTITION p3 VALUES LESS THAN (MAXVALUE)
);
SQL> SELECT TABLESPACE_NAME, PARTITION_NAME, SUBPARTITION_NAME
2 FROM DBA_TAB_SUBPARTITIONS WHERE TABLE_NAME='EMPLOYEEES_SUB_TEMPLATE'
3 ORDER BY TABLESPACE_NAME;
TABLESPACE_NAME PARTITION_NAME SUBPARTITION_NAME
--------------- --------------- ------------------
TS1 P1 P1_A
TS1 P2 P2_A
TS1 P3 P3_A
TS2 P1 P1_B
TS2 P2 P2_B
TS2 P3 P3_B
TS3 P1 P1_C
TS3 P2 P2_C
TS3 P3 P3_C
TS4 P1 P1_D
TS4 P2 P2_D
TS4 P3 P3_D
12 rows selected.