This example creates a hash-partitioned table sales using the PARTITION BY HASH clause. The partitioning column is part_no. The example specifies the number of partitions to create.
The eight partitions are created and assigned system-generated names. The partitions are stored in the default tablespace of the table.
Example: PARTITION BY HASH...PARTITIONS num...STORE IN
This example creates a hash-partitioned table named sales. The example specifies the number of partitions to create and the tablespaces in which the partition resides.
The STORE IN clause evenly distributes the partitions across the tablespaces ts1, ts2, and ts3.
Example: HASH/RANGE PARTITIONS num...
The HASH partition clause allows you to define a partitioning strategy. You can extend the PARTITION BY HASH clause to include SUBPARTITION BY either [ RANGE | LIST | HASH ] to create subpartitions in a HASH partitioned table.
This example creates a table sales that's hash partitioned by part_no and subpartitioned using a range by dept_no. The example specifies the number of partitions when creating the table sales.
The five partitions are created with default subpartitions and assigned system-generated names:
Example: LIST/HASH SUBPARTITIONS num...
This example shows the table sales that's list-partitioned by country. It is subpartitioned using hash partitioning by the dept_no column. This example specifies the number of subpartitions when creating the table.
The three partitions p1, p2, and p3 each contain three subpartitions with system-generated names:
This example creates the sales table, hash partitioned by part_no and hash subpartitioned by dept_no:
The two partitions are created. Each partition includes three subpartitions with the system-generated name assigned to them.
Example: HASH/HASH SUBPARTITIONS num... STORE IN
This example creates a hash-partitioned table sales. This example specifies the number of partitions and subpartitions to create when creating a hash partitioned table. It also specifies the tablespaces in which the subpartitions reside when creating a hash-partitioned table.
The two partitions are created and assigned system-generated names. The partitions are stored in the default tablespace. Subpartitions are stored in tablespaces ts1 and ts2.
The STORE IN clause assigns the hash subpartitions to the tablespaces and stores them in the two named tablespaces ts1 and ts2:
Example: HASH/HASH PARTITIONS num ...STORE IN SUBPARTITIONS num... STORE IN
This example creates the hash-partitioned table sales. It specifies the number of partitions and subpartitions to create and the tablespaces in which the partitions and subpartitions reside.
The two partitions are created with system-generated names and stored in the default tablespace:
Each partition includes three subpartitions. The STORE IN clause stores the subpartitions in tablespaces ts1 and ts2:
Note
If you specify the STORE IN clause for partitions and subpartitions, then the subpartitions are stored in the tablespaces defined in the PARTITIONS...STORE IN clause. The SUBPARTITIONS...STORE IN clause is ignored.
Example: RANGE/HASH SUBPARTITIONS num...
This example creates a range-partitioned table sales, which is first partitioned by the transaction date. Two range partitions are created and then hash subpartitioned using the value of the country column.
This statement creates a table with two partitions. The subpartition explicitly named q1_europe is created for partition p1. Because subpartitions aren't named for partition p2, the subpartitions are created based on the subpartition number and are assigned a system-generated name.
Example: RANGE/HASH SUBPARTITIONS num... IN PARTITION DESCRIPTION
This example creates a range-partitioned table sales. The table is first partitioned by the transaction date. Two range partitions are created and then hash subpartitioned using the value of the country column.
The partition p1 explicitly defines the subpartition count in the partition description. By default, two subpartitions are created for partition p2. Since you don't name subpartitions, system-generated names are assigned.
Example: LIST/HASH SUBPARTITIONS num STORE IN... IN PARTITION DESCRIPTION
This example creates a list-partitioned table sales with two list partitions. Partition p1 consists of three subpartitions, and partition p2 consists of two subpartitions. Since you don't name subpartitions, system-generated names are assigned.
The partition p2 explicitly defines the subpartition count in the partition description. Based on the definition, two subpartitions are created and stored in the tablespace named ts2. The subpartitions for partition p1 are stored in the tablespace named ts1.
Example: LIST/HASH STORE IN...TABLESPACES
This example creates a list-partitioned table sales. Partition p1 consists of three subpartitions stored explicitly in the tablespace ts2.
The SELECT statement shows partition p1, consisting of three subpartitions stored in the tablespace ts2:
This command adds a partition p2 to the sales table. Five subpartitions are created and distributed across the tablespace listed by the STORE IN clause.
A query of the ALL_TAB_PARTITIONS view shows the sales table with a partition named p2. The partition has five subpartitions. The STORE IN clause distributes the subpartitions across a tablespace named ts1.