This example creates a table sales that's range partitioned by date and hash-subpartitioned by country.
This command creates the sales table:
The table definition creates four partitions: q1_2012, q2_2012, q3_2012, and q4_2012. Each partition consists of two subpartitions with system-generated names.
Set the subpartition template on the sales table:
The sales table is modified, with the subpartition template set to eight. If you try to add a partition q1_2013, a new partition is created and consists of eight subpartitions.
Query the ALL_TAB_PARTITIONS view. The q1_2013 partition is successfully added. It has eight subpartitions that have system-generated names.
Example: Adding a subpartition template for LIST/LIST partitioned table
This example creates a table sales that's list-partitioned by country. It is subpartitioned using the list by the date column.
The SELECT statement shows partition q1_2021 consisting of three subpartitions stored in tablespaces ts1, ts2, and ts3.
This command adds a partition named q2_2021 to the sales table:
This command shows that the sales table includes the q2_2021 partition:
Example: Adding 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.
This command adds a partition named q2_2021 to the sales table:
This command shows that the sales table includes the q2_2021 partition:
Example: Adding 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:
This command adds a partition named q2_2021 to the sales table:
This command shows that the sales table includes the q2_2021 partition:
Examples: Resetting a SUBPARTITION TEMPLATE
This example creates a list-partitioned table sales that's list partitioned by country and hash subpartitioned by part_no.
This command creates the sales table:
The table contains three partitions: americas, asia, and europe. Each partition consists of three subpartitions with system-generated names.
This command resets the subpartition template on the sales table:
The sales table is modified with the subpartition template reset to default 1. Try to add a new partition east_asia using this command:
Query the ALL_TAB_PARTITIONS view. A new partition east_asia is created consisting of one subpartition with a system-generated name.
This example creates a table sales list-partitioned by country and subpartitioned using range partitioning by the date column:
The sales table contains a partition named q1_2021 that includes three subpartitions stored in tablespaces ts1, ts2, and ts3.
This command adds a partition named q2_2021 to the sales table:
This command shows that the sales table includes the q2_2021 partition:
Use the ALTER TABLE... SET SUBPARTITION TEMPLATE command to specify a new subpartition template:
This command adds a partition named q3_2021 to the sales table:
This command shows that the sales table includes the q3_2021 partition:
This command resets or drops the subpartition template on the sales table:
This command adds a partition named q4_2021 to the sales table:
The SELECT statement shows partition q4_2021 consists of a subpartition with a system-generated name: