PolarDB:CREATE TABLE... PARTITION BY

Overview

The CREATE TABLE command syntax has the following three forms:

• List partitioning syntax
  The first form is to create a list-partitioned table:

  CREATE TABLE [ schema. ]table_name table_definition PARTITION BY 
      LIST(column)
      [SUBPARTITION BY {RANGE|LIST} (column[, column ]...)] 
      (list_partition_definition[, list_partition_definition]...) ;

  Where list_partition_definition is:

  PARTITION [partition_name] 
  VALUES (value[, value]...) [TABLESPACE tablespace_name] [(subpartition, ...)]

• Range partitioning syntax
  The second form is to create a range-partitioned table:

  CREATE TABLE [ schema. ]table_name 
     table_definition
     PARTITION BY RANGE(column[, column ]...)
     [SUBPARTITION BY {RANGE|LIST} (column[, column ]...)]
     (range_partition_definition[, range_partition_definition]...) ;

  Where range_partition_definition is:

  PARTITION [partition_name]
    VALUES LESS THAN (value[, value]...)
    [TABLESPACE tablespace_name]
    [(subpartition, ...)]

• Subpartitioning syntax
  subpartition may be one of the following two types:

  {list_subpartition | range_subpartition}

  Where list_subpartition is:

  SUBPARTITION [subpartition_name] VALUES (value[, value]...)
  [TABLESPACE tablespace_name]

  Where range_subpartition is:

  SUBPARTITION [subpartition_name]
  VALUES LESS THAN (value[, value]...) 
  [TABLESPACE tablespace_name] 

Description

The CREATE TABLE... PARTITION BY command creates a table that has one or multiple partitions. Each partition may have one or multiple subpartitions. The number of defined partitions is not limited. If you include the PARTITION BY clause, you must specify a minimum of one partitioning rule. The resulting table is owned by the user who creates the table.

Use the PARTITION BY LIST clause to divide a table into partitions based on the values entered in a specified column. Each partitioning rule must specify a minimum of one literal value. The number of values you may specify is not limited. Include a rule that specifies a matching value of DEFAULT to direct any un-qualified rows to the specified partition.

Use the PARTITION BY RANGE clause to specify boundary rules based on which partitions are created. Each partitioning rule must contain at least one column of a data type that has two operators (for example, a greater-than or equal to operator, and a less-than operator). Range boundaries are evaluated based on a LESS THAN clause and are non-inclusive. A date boundary of January 1, 2013 only includes the date values that fall on or before December 31, 2012.

Range partitioning rules must be specified in ascending order. If INSERT commands store rows with values that exceed the top boundary of a range-partitioned table, the commands will fail. However, commands will not fail if the partitioning rules include a boundary rule that specifies a value of MAXVALUE. If you do not include a MAXVALUE rule, any row that exceeds the maximum limit specified by the boundary rules will cause an error.

Use the TABLESPACE keyword to specify the name of a tablespace in which a partition or subpartition will reside. If you do not specify a tablespace, the partition or subpartition will be created in the default tablespace.

If you use the CREATE TABLE syntax to create an index on a partitioned table, the index will be created on each partition or subpartition.

If the table definition includes the SUBPARTITION BY clause, each partition in the table will have a minimum of one subpartition. Each subpartition can be explicitly defined or system-defined.

A subpartition name generated by the server is a combination of the partition name and a unique identifier. You can query the ALL_TAB_SUBPARTITIONS table to view a complete list of subpartition names.

Parameters

Example - PARTITION BY LIST

CREATE TABLE sales
(
  dept_no     number,   
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY LIST(country)
(
  PARTITION europe VALUES('FRANCE', 'ITALY'),
  PARTITION asia VALUES('INDIA', 'PAKISTAN'),
  PARTITION americas VALUES('US', 'CANADA')
);

acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
 partition_name |     high_value      
----------------+---------------------
 americas       | 'US', 'CANADA'      
 asia           | 'INDIA', 'PAKISTAN' 
 europe         | 'FRANCE', 'ITALY'   
(3 rows)

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

Example - PARTITION BY RANGE

CREATE TABLE sales
(
  dept_no     number,
  part_no     varchar2,
  country     varchar2(20),
  date        date,
  amount      number
)
PARTITION BY RANGE(date)
(
  PARTITION q1_2012 
    VALUES LESS THAN('2012-Apr-01'),
  PARTITION q2_2012 
    VALUES LESS THAN('2012-Jul-01'),
  PARTITION q3_2012 
    VALUES LESS THAN('2012-Oct-01'),
  PARTITION q4_2012 
    VALUES LESS THAN('2013-Jan-01')
);

acctg=# SELECT partition_name, high_value from ALL_TAB_PARTITIONS;
 partition_name |  high_value   
----------------+---------------
 q4_2012        | '2013-Jan-01' 
 q3_2012        | '2012-Oct-01' 
 q2_2012        | '2012-Jul-01' 
 q1_2012        | '2012-Apr-01' 
(4 rows)

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

Example - PARTITION BY RANGE, SUBPARTITION BY LIST

The following example creates a partitioned table (sales) that is first partitioned by using the transaction date. Then, the range partitions (q1_2012, q2_2012, q3_2012, and q4_2012) are list-partitioned by 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')
       )
);

acctg=# SELECT subpartition_name, high_value, partition_name FROM ALL_TAB_SUBPARTITIONS;
subpartition_name | high_value | partition_name     +    +    
q4_asia    | 'INDIA', 'PAKISTAN' | q4_2012
q4_europe    | 'FRANCE', 'ITALY' | q4_2012
SUBPARTITION q4_ SUBPARTITION q4_ SUBPARTITION q4_
q4_americas    | 'US', 'CANADA'    | q4_2012 
q3_americas    | 'US', 'CANADA'    | q3_2012 
q3_asia        | 'INDIA', 'PAKISTAN'     | q3_2012 
q3_europe    | 'FRANCE', 'ITALY'    | q3_2012
q2_americas    | 'US', 'CANADA'    | q2_2012
q2_asia        | 'INDIA','PAKISTAN'     | q2_2012
q2_europe    | 'FRANCE', 'ITALY'    | q2_2012
q1_americas    | 'US', 'CANADA'     | q1_2012
q1_asia        | 'INDIA', 'PAKISTAN'     | q1_2012
q1_europe    | 'FRANCE', 'ITALY'    | q1_2012
(12 rows)

When a row is added to this table, the value in the date column is compared with the values specified in the range partitioning rules. The server selects the partition in which the row will reside. The value in the country column is then compared with 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. Therefore, all partitions contain no data.

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