Example: PARTITION BY RANGE, SUBPARTITION BY LIST v17

Suggest edits

This example creates a partitioned table sales that's first partitioned by the transaction date. The range partitions q1_2012, q2_2012, q3_2012 and q4_2012 are then list 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 LIST(country)
  (
    PARTITION q1_2012
      VALUES LESS THAN('2012-Apr-01')
      (
        SUBPARTITION q1_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q1_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q1_americas VALUES ('US', 'CANADA')
      ),
  PARTITION q2_2012
    VALUES LESS THAN('2012-Jul-01')
      (
        SUBPARTITION q2_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q2_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q2_americas VALUES ('US', 'CANADA')
      ),
  PARTITION q3_2012
    VALUES LESS THAN('2012-Oct-01')
      (
        SUBPARTITION q3_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q3_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q3_americas VALUES ('US', 'CANADA')
      ),
  PARTITION q4_2012
    VALUES LESS THAN('2013-Jan-01')
      (
        SUBPARTITION q4_europe VALUES ('FRANCE', 'ITALY'),
        SUBPARTITION q4_asia VALUES ('INDIA', 'PAKISTAN'),
        SUBPARTITION q4_americas VALUES ('US', 'CANADA')
      )
);

This statement creates a table with four partitions. Each partition has three subpartitions.

edb=# SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;

Output

 subpartition_name |     high_value      | partition_name
-------------------+---------------------+----------------
 Q1_EUROPE         | 'FRANCE', 'ITALY'   | Q1_2012
 Q1_ASIA           | 'INDIA', 'PAKISTAN' | Q1_2012
 Q1_AMERICAS       | 'US', 'CANADA'      | Q1_2012
 Q2_EUROPE         | 'FRANCE', 'ITALY'   | Q2_2012
 Q2_ASIA           | 'INDIA', 'PAKISTAN' | Q2_2012
 Q2_AMERICAS       | 'US', 'CANADA'      | Q2_2012
 Q3_EUROPE         | 'FRANCE', 'ITALY'   | Q3_2012
 Q3_ASIA           | 'INDIA', 'PAKISTAN' | Q3_2012
 Q3_AMERICAS       | 'US', 'CANADA'      | Q3_2012
 Q4_EUROPE         | 'FRANCE', 'ITALY'   | Q4_2012
 Q4_ASIA           | 'INDIA', 'PAKISTAN' | Q4_2012
 Q4_AMERICAS       | 'US', 'CANADA'      | Q4_2012
(12 rows)

When a row is added to this table, the value in the date column is compared to the values specified in the range-partitioning rules. The server selects the partition for the row to reside in. The value in the country column is then compared to the values specified in the list subpartitioning rules. When the server locates a match for the value, the row is stored in the corresponding subpartition.

Any row added to the table is stored in a subpartition, so the partitions contain no data.

The server evaluates the following statement against the partitioning and subpartitioning rules. It stores the row in the q3_europe partition.

INSERT INTO sales VALUES (10, '9519a', 'FRANCE', '18-Aug-2012', '650000');

← Prev

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

↑ Up

CREATE TABLE...PARTITION BY

Example: CREATING UNIQUE INDEX on PARTITION TABLE

Example: PARTITION BY RANGE, SUBPARTITION BY LIST v17

← Prev

↑ Up

Next →