Handle stray values in a list- or range-partitioned table - PolarDB

This topic describes how to handle stray values in a list- or range-partitioned table.

A DEFAULT or a MAXVALUE partition or subpartition captures the rows that do not match the other partitioning rules defined for a table.

Define a DEFAULT partition

A DEFAULT partition captures the rows that do not fit into other partitions in a list-partitioned or list-subpartitioned table. If you do not include a DEFAULT rule, a row that contains a value that does not match the partitioning rules causes an error. Each partition in a list-partitioned table or a subpartition in a list-subpartitioned table may have its own DEFAULT rule.

The DEFAULT rule has the following syntax:

PARTITION partition_name VALUES (DEFAULT)

Where partition_name specifies the name of the partition or subpartition used to store the rows that do not match the partitioning rules specified for other partitions.

In the last example, a list-partitioned table is created. The server determines which partition in this partitioned table to store the data based on the value of the country column. If you attempt to add a row in which the value for the country column is not listed in the rules, PolarDB reports an error:

acctg=# INSERT INTO sales VALUES
acctg-#   (40, '3000x', 'IRELAND', '01-Mar-2012', '45000');
ERROR:  inserted partition key does not map to any partition

In the following example, the same table is created and a DEFAULT partition is added to the table. Partitioning rules are used to specify the values that are stored in europe, asia, or americas partition. If the value in a row is not specified in the rules, the server stores the row in the others partition.

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'),
  PARTITION others VALUES (DEFAULT)
);

To test the DEFAULT partition, add a row in which the value for the country column does not match the countries specified in the partitioning rules:

INSERT INTO sales VALUES
  (40, '3000x', 'IRELAND', '01-Mar-2012', '45000');

You can query the sales table to check whether the previously rejected row is now stored in the sales_others partition:

acctg=# SELECT tableoid::regclass, * FROM sales;
    tableoid    | dept_no | part_no | country  |        date        |  amount 
----------------+---------+---------+----------+--------------------+--------
 sales_others   |      40 | 3000x   | IRELAND  | 01-MAR-12 00:00:00 |   45000
(18 rows)

Take note of the fact that PolarDB cannot reassign the content of a DEFAULT partition or subpartition.

You cannot run the ALTER TABLE...ADD PARTITION command to add a partition to a table that has a DEFAULT rule. However, you can run the ALTER TABLE...SPLIT PARTITION command to split existing partitions.
You cannot run the ALTER TABLE...ADD SUBPARTITION command to add a subpartition to a table that has a DEFAULT rule. However, you can run the ALTER TABLE...SPLIT SUBPARTITION command to split existing subpartitions.

Define a MAXVALUE partition

A MAXVALUE partition or subpartition captures the rows that do not fit into other partitions in a range-partitioned or range-subpartitioned table. If you do not include a MAXVALUE rule, an error occurs if a row contains a value that exceeds the upper limit specified by the partitioning rules. Each partition or subpartition may have its own MAXVALUE partition.

Take note of the fact that PolarDB cannot reassign the content of a MAXVALUE partition or subpartition.

You cannot run the ALTER TABLE ... ADD PARTITION statement to add a partition to a table that has a MAXVALUE rule. However, you can run the ALTER TABLE ... SPLIT PARTITION statement to split existing partitions.
You cannot run the ALTER TABLE... ADD SUBPARTITION statement to add a subpartition to a table that has a MAXVALUE rule. However, you can run the ALTER TABLE... SPLIT SUBPARTITION statement to split existing subpartitions.

The MAXVALUE rule has the following syntax:

PARTITION partition_name VALUES LESS THAN (MAXVALUE)

In the syntax, partition_name specifies the name of the partition that is used to store the rows that do not match the partitioning rules specified for other partitions.

In the last example, a range-partitioned table is created. The data in this table is partitioned based on the values for the date column. If you attempt to add a row in which the date value is later than the date value specified in the partitioning rules, PolarDB reports an error:

acctg=# INSERT INTO sales VALUES
acctg-#   (40, '3000x', 'IRELAND', '01-Mar-2013', '45000');
ERROR:  inserted partition key does not map to any partition

The following CREATE TABLE command creates a same table, but this table has a MAXVALUE partition. Instead of reporting an error, the server stores all the rows that do not match the previous partitioning rules of the others partition.

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'),
  PARTITION others VALUES LESS THAN (MAXVALUE)
);

To test the MAXVALUE partition, add a row in which the value for the date column is later than the last date value listed in each partitioning rule. The server stores this row in the others partition:

INSERT INTO sales VALUES
                    (40, '3000x', 'IRELAND', '2015-Oct-01', '45000');

You can query the sales table to check whether the previously rejected row is now stored in the sales_others partition:

acctg=# SELECT tableoid::regclass, * FROM sales;  
   tableoid    | dept_no | part_no | country  |        date        | amount 
---------------+---------+---------+----------+--------------------+---------
 sales_q1_2012 |      10 | 4519b   | FRANCE   | 17-JAN-12 00:00:00 |    45000
 sales_q1_2012 |      20 | 3788a   | INDIA    | 01-MAR-12 00:00:00 |    75000
 sales_q1_2012 |      30 | 9519b   | CANADA   | 01-FEB-12 00:00:00 |    75000
 sales_q2_2012 |      40 | 9519b   | US       | 12-APR-12 00:00:00 |   145000
 sales_q2_2012 |      20 | 3788a   | PAKISTAN | 04-JUN-12 00:00:00 |    37500
 sales_q2_2012 |      30 | 4519b   | CANADA   | 08-APR-12 00:00:00 |   120000
 sales_q2_2012 |      40 | 3788a   | US       | 12-MAY-12 00:00:00 |     4950
 sales_q3_2012 |      10 | 9519b   | ITALY    | 07-JUL-12 00:00:00 |    15000
 sales_q3_2012 |      10 | 9519a   | FRANCE   | 18-AUG-12 00:00:00 |   650000
 sales_q3_2012 |      10 | 9519b   | FRANCE   | 18-AUG-12 00:00:00 |   650000
 sales_q3_2012 |      20 | 3788b   | INDIA    | 21-SEP-12 00:00:00 |     5090
 sales_q3_2012 |      40 | 4788a   | US       | 23-SEP-12 00:00:00 |     4950
 sales_q4_2012 |      40 | 4577b   | US       | 11-NOV-12 00:00:00 |    25000
 sales_q4_2012 |      30 | 7588b   | CANADA   | 14-DEC-12 00:00:00 |    50000
 sales_q4_2012 |      40 | 4788b   | US       | 09-OCT-12 00:00:00 |    15000
 sales_q4_2012 |      20 | 4519a   | INDIA    | 18-OCT-12 00:00:00 |   650000
 sales_q4_2012 |      20 | 4519b   | INDIA    | 02-DEC-12 00:00:00 |     5090
 sales_others  |      40 | 3000x   | IRELAND  | 01-MAR-13 00:00:00 |    45000
(18 rows)