Example: PARTITION BY HASH...PARTITIONS num... v17

Suggest edits

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY HASH (part_no) PARTITIONS 8;

The eight partitions are created and assigned system-generated names. The partitions are stored in the default tablespace of the table.

edb=# SELECT table_name, partition_name FROM ALL_TAB_PARTITIONS WHERE
table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name
------------+----------------
 SALES      | SYS0101
 SALES      | SYS0102
 SALES      | SYS0103
 SALES      | SYS0104
 SALES      | SYS0105
 SALES      | SYS0106
 SALES      | SYS0107
 SALES      | SYS0108
(8 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY HASH (part_no) PARTITIONS 5 STORE IN (ts1, ts2, ts3);

The STORE IN clause evenly distributes the partitions across the tablespaces ts1, ts2, and ts3.

edb=# SELECT table_name, partition_name, tablespace_name FROM
ALL_TAB_PARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | tablespace_name
------------+----------------+-----------------
 SALES      | SYS0101        | TS1
 SALES      | SYS0102        | TS2
 SALES      | SYS0103        | TS3
 SALES      | SYS0104        | TS1
 SALES      | SYS0105        | TS2
(5 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY HASH (part_no) SUBPARTITION BY RANGE (dept_no) PARTITIONS 5;

The five partitions are created with default subpartitions and assigned system-generated names:

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | SYS0101        | SYS0102
 SALES      | SYS0103        | SYS0104
 SALES      | SYS0105        | SYS0106
 SALES      | SYS0107        | SYS0108
 SALES      | SYS0109        | SYS0110
(5 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST (country) SUBPARTITION BY HASH (dept_no) SUBPARTITIONS 3
(
  PARTITION p1 VALUES('FRANCE', 'ITALY'),
  PARTITION p2 VALUES('INDIA', 'PAKISTAN'),
  PARTITION p3 VALUES('US', 'CANADA')
);

The three partitions p1, p2, and p3 each contain three subpartitions with system-generated names:

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | P1             | SYS0101
 SALES      | P1             | SYS0102
 SALES      | P1             | SYS0103
 SALES      | P2             | SYS0104
 SALES      | P2             | SYS0105
 SALES      | P2             | SYS0106
 SALES      | P3             | SYS0107
 SALES      | P3             | SYS0108
 SALES      | P3             | SYS0109
(9 rows)

Example: HASH/HASH PARTITIONS num... SUBPARTITIONS num...

This example creates the sales table, hash partitioned by part_no and hash subpartitioned by dept_no:

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY HASH (part_no) SUBPARTITION BY HASH (dept_no) SUBPARTITIONS 3
PARTITIONS 2;

The two partitions are created. Each partition includes three subpartitions with the system-generated name assigned to them.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | SYS0101        | SYS0102
 SALES      | SYS0101        | SYS0103
 SALES      | SYS0101        | SYS0104
 SALES      | SYS0105        | SYS0106
 SALES      | SYS0105        | SYS0107
 SALES      | SYS0105        | SYS0108
(6 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY HASH (part_no) SUBPARTITION BY HASH (dept_no) SUBPARTITIONS 3
PARTITIONS 2 STORE IN (ts1, ts2);

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.

edb=# SELECT table_name, partition_name, tablespace_name FROM
ALL_TAB_PARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | tablespace_name
------------+----------------+-----------------
 SALES      | SYS0101        |
 SALES      | SYS0105        |
(2 rows)

The STORE IN clause assigns the hash subpartitions to the tablespaces and stores them in the two named tablespaces ts1 and ts2:

edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name
FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name | tablespace_name
------------+----------------+-------------------+-----------------
 SALES      | SYS0101        | SYS0102           | TS1
 SALES      | SYS0101        | SYS0103           | TS2
 SALES      | SYS0101        | SYS0104           | TS1
 SALES      | SYS0105        | SYS0106           | TS1
 SALES      | SYS0105        | SYS0107           | TS2
 SALES      | SYS0105        | SYS0108           | TS1
(6 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY HASH (part_no) SUBPARTITION BY HASH (dept_no) SUBPARTITIONS 3
STORE IN (ts3) PARTITIONS 2 STORE IN (ts1, ts2);

The two partitions are created with system-generated names and stored in the default tablespace:

edb=# SELECT table_name, partition_name, tablespace_name FROM
ALL_TAB_PARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | tablespace_name
------------+----------------+-----------------
 SALES      | SYS0101        |
 SALES      | SYS0105        |
(2 rows)

Each partition includes three subpartitions. The STORE IN clause stores the subpartitions in tablespaces ts1 and ts2:

edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name
FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name | tablespace_name
------------+----------------+-------------------+-----------------
 SALES      | SYS0101        | SYS0102           | TS1
 SALES      | SYS0101        | SYS0103           | TS2
 SALES      | SYS0101        | SYS0104           | TS1
 SALES      | SYS0105        | SYS0106           | TS1
 SALES      | SYS0105        | SYS0107           | TS2
 SALES      | SYS0105        | SYS0108           | TS1
(6 rows)
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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE (date) SUBPARTITION BY HASH (country) SUBPARTITIONS 2
(
  PARTITION p1 VALUES LESS THAN ('2012-Apr-01') (SUBPARTITION q1_europe),
  PARTITION p2 VALUES LESS THAN ('2012-Jul-01')
);

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.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | P1             | Q1_EUROPE
 SALES      | P2             | SYS0101
 SALES      | P2             | SYS0102
(3 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE (date) SUBPARTITION BY HASH (country) SUBPARTITIONS 2
(
  PARTITION p1 VALUES LESS THAN ('2012-Apr-01') SUBPARTITIONS 3,
  PARTITION p2 VALUES LESS THAN ('2012-Jul-01')
);

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.

edb=# SELECT table_name, partition_name, subpartition_name FROM
ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name
------------+----------------+-------------------
 SALES      | P1             | SYS0101
 SALES      | P1             | SYS0102
 SALES      | P1             | SYS0103
 SALES      | P2             | SYS0104
 SALES      | P2             | SYS0105
(5 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST (country) SUBPARTITION BY HASH (part_no) SUBPARTITIONS 3
STORE IN (ts1)
(
  PARTITION p1 VALUES ('FRANCE', 'ITALY'),
  PARTITION p2 VALUES ('INDIA', 'PAKISTAN') SUBPARTITIONS 2 STORE IN
  (ts2)
);

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.

edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name
FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name | tablespace_name
------------+----------------+-------------------+-----------------
 SALES      | P1             | SYS0101           | TS1
 SALES      | P1             | SYS0102           | TS1
 SALES      | P1             | SYS0103           | TS1
 SALES      | P2             | SYS0104           | TS2
 SALES      | P2             | SYS0105           | TS2
(5 rows)

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.

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST (country) SUBPARTITION BY HASH (part_no) SUBPARTITIONS 3
STORE IN (ts1)
(
  PARTITION p1 VALUES ('FRANCE', 'ITALY') TABLESPACE ts2
);

The SELECT statement shows partition p1, consisting of three subpartitions stored in the tablespace ts2:

edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name
FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name | tablespace_name
------------+----------------+-------------------+-----------------
 SALES      | P1             | SYS0101           | TS2
 SALES      | P1             | SYS0102           | TS2
 SALES      | P1             | SYS0103           | TS2
(3 rows)

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.

ALTER TABLE sales ADD PARTITION p2 VALUES ('US', 'CANADA') SUBPARTITIONS 5
STORE IN (ts1);

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.

edb=# SELECT table_name, partition_name, subpartition_name, tablespace_name
FROM ALL_TAB_SUBPARTITIONS WHERE table_name = 'SALES' and partition_name =
'P2' ORDER BY 1,2;
Output
 table_name | partition_name | subpartition_name | tablespace_name
------------+----------------+-------------------+-----------------
 SALES      | P2             | SYS0105           | TS1
 SALES      | P2             | SYS0106           | TS1
 SALES      | P2             | SYS0107           | TS1
 SALES      | P2             | SYS0108           | TS1
 SALES      | P2             | SYS0109           | TS1
(5 rows)
← Prev

Example: PARTITION BY HASH

↑ Up

CREATE TABLE...PARTITION BY

Next →

Example: PARTITION BY RANGE, SUBPARTITION BY LIST