These examples show how to create subpartitions in a partitioned table using a subpartition template.
This example creates a table sales list partitioned by country and subpartitioned using list by date. The sales table uses a subpartition template and displays the subpartition and tablespace name.
The SELECT statement shows partition q1_2021 consisting of three subpartitions stored in tablespaces ts1, ts2, and ts3:
Example: Creating a subpartition template for LIST/RANGE partitioned table
This example creates a table sales list partitioned by country and subpartitioned using range partitioning by the date column:
The sales table creates a partition named q1_2021 that includes three subpartitions stored in tablespaces ts1, ts2, and ts3.
Example: Creating a subpartition template for LIST/HASH partitioned table
This example creates a list-partitioned table sales that's first partitioned by country and then hash subpartitioned using the value of the dept_no column:
The sales table creates a q1_2021 partition that includes three subpartitions stored in tablespaces ts1, ts2, and ts3.
Example: Creating a subpartition template for INTERVAL/HASH partitioned table
This example creates a sales table, interval partitioned using monthly intervals on the date column and hash subpartitioned using the value of the dept_no column:
The sales table creates a partition q2_2021 consisting of three subpartitions stored in tablespaces ts1, ts2, and ts3:
Insert values into the sales table:
The SELECT statement shows a system-generated name of partitions and subpartitions stored in tablespaces ts1, ts2, and ts3.