Example - PARTITION BY RANGE, SUBPARTITION BY LIST v11

Suggest edits

The following example creates a partitioned table (sales) that is 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;
 subpartition_name  |             high_value              | partition_name
--------------------+-------------------------------------+----------------
 Q1_AMERICAS        | FOR VALUES IN ('US', 'CANADA')      | Q1_2012  
 Q1_ASIA            | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q1_2012
 Q1_EUROPE          | FOR VALUES IN ('FRANCE', 'ITALY')   | Q1_2012
 Q2_AMERICAS        | FOR VALUES IN ('US', 'CANADA')      | Q2_2012
 Q2_ASIA            | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q2_2012
 Q2_EUROPE          | FOR VALUES IN ('FRANCE', 'ITALY')   | Q2_2012
 Q3_AMERICAS        | FOR VALUES IN ('US', 'CANADA')      | Q3_2012
 Q3_ASIA            | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q3_2012
 Q3_EUROPE          | FOR VALUES IN ('FRANCE', 'ITALY')   | Q3_2012
 Q4_AMERICAS        | FOR VALUES IN ('US', 'CANADA')      | Q4_2012
 Q4_ASIA            | FOR VALUES IN ('INDIA', 'PAKISTAN') | Q4_2012
 Q4_EUROPE          | FOR VALUES IN ('FRANCE', 'ITALY')   | 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, and the server selects the partition in which the row should reside. 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 will be stored in a subpartition, so the partitions will contain no data.

The server would evaluate the following statement against the partitioning and subpartitioning rules and store the row in the q3_europe partition.

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

← Prev

Example - PARTITION BY HASH

ALTER TABLE...ADD PARTITION

Example - PARTITION BY RANGE, SUBPARTITION BY LIST v11

← Prev

Next →