Handle outliers in a list- or range-partitioned table - PolarDB

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

A DEFAULT or MAXVALUE partition in a partitioned table captures rows that do not fit into the other partitions. A DEFAULT or MAXVALUE subpartition in a partition captures rows that do not fit into the other subpartitions. This topic primarily focuses on the DEFAULT or MAXVALUE partition. The same principles apply to the DEFAULT or MAXVALUE subpartition.

Define a DEFAULT partition

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

The following limits apply when a DEFAULT partition or subpartition exists:

You cannot use the ALTER TABLE…ADD PARTITION statement to add a partition to a table that has a DEFAULT partition. However, you can use the ALTER TABLE…SPLIT PARTITION statement to divide an existing partition.
You cannot use the ALTER TABLE…ADD SUBPARTITION statement to add a subpartition to a partition that has a DEFAULT subpartition. However, you can use the ALTER TABLE…SPLIT SUBPARTITION statement to divide an existing subpartition.

To create a DEFAULT partition, execute the following statement:

PARTITION partition_name VALUES (DEFAULT)

In the statement, partition_name specifies the name of the DEFAULT partition that stores rows that do not fit into the other partitions.

Example 1

In this example, a list-partitioned table named sales is created. The table is partitioned based on a list of values of the country column. If you attempt to insert a row in which the value of the country column does not fit into the defined partitions, an error is reported.

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'),
);

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

Example 2

In this example, a list-partitioned table the same as that in example 1 is created. A DEFAULT partition named others is added to the table to store the rows that do not fit into the other partitions.

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 of the country column does not match the countries specified in the partitioning rules:

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

Query the sales table to check whether the row is stored in the DEFAULT partition.

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)

Define a MAXVALUE partition

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

The following limits apply when a MAXVALUE partition or subpartition exists:

You cannot use the ALTER TABLE...ADD PARTITION statement to add a partition to a table that has a MAXVALUE partition. However, you can use an ALTER TABLE...SPLIT PARTITION statement to divide an existing partition.
You cannot use the ALTER TABLE...ADD SUBPARTITION statement to add a subpartition to a partition that has a MAXVALUE subpartition. However, you can use the ALTER TABLE...SPLIT SUBPARTITION statement to divide an existing subpartition.

To create a MAXVALUE partition, execute the following statement:

PARTITION partition_name VALUES LESS THAN (MAXVALUE)

In the statement, partition_name specifies the name of the MAXVALUE partition that is used to store the rows that do not fit into other partitions.

Example 3

In this example, a range-partitioned table named sales is created. The table is partitioned based on the value ranges of the date column. If you attempt to insert a row in which the value of the date column is later than the date ranges specified in the partitioning rules, an error is reported:

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'),
);

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

Example 4

In this example, a range-partitioned table the same as that in example 3 is created. A MAXVALUE partition named others is added to the table to store all the rows that do not match the existing partitioning rules in the MAXVALUE 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, insert a row in which the value of the date column is later than the date ranges specified in the partitioning rules.

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

Query the sales table to check whether the row is stored in the MAXVALUE 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)