Partition support and limitations

Supported scope

Instances with partitioning enabled support creating partitioned tables with KEY, HASH, RANGE, RANGE COLUMNS, LIST, and LIST COLUMNS. RANGE and LIST partitions check incoming data. An insert fails if no partition matches a value.

  • RANGE: Use VALUES LESS THAN to define an exclusive upper bound for each partition. To accept values greater than or equal to the final finite upper bound, define the last partition with VALUES LESS THAN (MAXVALUE).

  • LIST: Use VALUES IN to specify the values accepted by each partition. Values that match no partition cannot be inserted.

  • Subpartitioning and foreign keys on partitioned tables are not supported.

Partitioning capabilities may vary by instance version and configuration. Before using partitioning, verify table creation, valid inserts, and out-of-range inserts on the target instance. See System Tables for how to view partition definitions.

MatrixOne 4.2.5 uses a single partition-key column for RANGE COLUMNS and LIST COLUMNS. The examples below use one column. Check column-count and type limits for other instance versions.

2. Description of partition keys

The relationship between Partition Keys, Primary Keys and Unique Keys

The relationship rules of Partition Keys, Primary Keys and Unique Keys can be summarized as:

All columns used in the partition expression of a partition table must be part of each unique key that the table may have.

Note

The only keys include PrimaryKey and Unique KEY.

That is, each unique key on the table must use each column in the table’s partition expression. The unique key also includes the primary key of the table, because by definition, the primary key of the table is also a unique key.

Example Description

For example, each of the following table creation statements is invalid:

CREATE TABLE t1 (
      col1 INT NOT NULL,
      col2 DATE NOT NULL,
      col3 INT NOT NULL,
      col4 INT NOT NULL,
      UNIQUE KEY (col1, col2)
  )
  PARTITION BY HASH(col3)
  PARTITIONS 4;
ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

CREATE TABLE t2 (
     col1 INT NOT NULL,
     col2 DATE NOT NULL,
     col3 INT NOT NULL,
     col4 INT NOT NULL,
     UNIQUE KEY (col1),
     UNIQUE KEY (col3)
 )
  PARTITION BY HASH(col1 + col3)
  PARTITIONS 4;

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

About KEY Partition Key is NULL

  1. KEY accepts only lists of zero or more column names. In the case where the table has a primary key, any column used as a partition key must contain part or all of the table primary key.

    If no column name is specified as partition key, the primary key of the table is used (if any). For example, the following CREATE TABLE statement is valid in MySQL.

  2. If there is no primary key, but UNIQUE KEY, then UNIQUE KEY is used for partition keys.

For example, in the following table creation statement, the KEY partition partition key is NULL, and no primary key is defined, but it contains a unique key. When building a partition expression, a unique key is used as the partition key:

CREATE TABLE t1 (
	col1 INT NOT NULL,
	col2 DATE NOT NULL,
	col3 INT NOT NULL,
	col4 INT NOT NULL,
	UNIQUE KEY (col1, col2)
)
PARTITION BY KEY()
PARTITIONS 4;

Note

Partition syntax and limitations depend on the MatrixOne engine version of the instance.

3. Description of MatrixOne partition expressions

When a DDL statement builds a partition table, a partition expression is generated for each partition definition, which can be used to calculate the partition to which the data belongs.

During the planning construction phase, the partition information data structure in the DDL statement is plan.PartitionInfo:

type PartitionInfo struct {
	Type PartitionType
	Expr *Expr
	PartitionExpression *Expr
	Columns []*Expr
	PartitionColumns []string
	PartitionNum uint64
	Partitions []*PartitionItem
	Algorithm int64
	IsSubPartition bool
	PartitionMsg string
}

Where PartitionExpression is a partition expression. The partition expression is MatrixOne. The method of converting the partition clause into an expression for processing. The construction method of each partition expression is as follows:

KEY Partitioning

The KEY partition will construct a partition expression based on the partition key and the number of partitions. The calculation result of the partition expression is an integer greater than or equal to 0, representing the partition number, and incrementing from zero.

SQL examples are as follows:

CREATE TABLE t1 (
    col1 INT NOT NULL,
    col2 DATE NOT NULL,
    col3 INT NOT NULL,
    col4 INT NOT NULL,
    PRIMARY KEY (col1, col2)
)
PARTITION BY KEY(col1)
PARTITIONS 4;

HASH Partitioning

Similar to KEY partition, the HASH partition will build a partition expression based on the partition function and the number of partitions. The calculation result of the partition expression is an integer greater than or equal to 0, representing the partition number, and incrementing from zero.

SQL examples are as follows:

CREATE TABLE t1 (
    col1 INT,
    col2 CHAR(5),
    col3 DATE
)
PARTITION BY LINEAR HASH( YEAR(col3))
PARTITIONS 6;

RANGE Partitioning

RANGE partitioning is a method of dividing table data into different partitions based on the range of column values. This partition type is very suitable for data that can be segmented based on the value range of a particular column.

SQL examples are as follows:

CREATE TABLE employees (
	id INT NOT NULL,
	fname VARCHAR(30),
	lname VARCHAR(30),
	hired DATE NOT NULL DEFAULT '1970-01-01',
	separate DATE NOT NULL DEFAULT '9999-12-31',
	job_code INT NOT NULL,
	store_id INT NOT NULL
)
PARTITION BY RANGE (store_id) (
	PARTITION p0 VALUES LESS THAN (6),
	PARTITION p1 VALUES LESS THAN (11),
	PARTITION p2 VALUES LESS THAN (16),
	PARTITION p3 VALUES LESS THAN MAXVALUE
);

RANGE COLUMNS

RANGE COLUMNS selects a partition by a column-value range. Define exclusive upper bounds with VALUES LESS THAN and use a final MAXVALUE partition for remaining values.

CREATE TABLE rc (a INT NOT NULL, b INT)
PARTITION BY RANGE COLUMNS(a) (
    PARTITION p0 VALUES LESS THAN (10),
    PARTITION p1 VALUES LESS THAN (20),
    PARTITION pmax VALUES LESS THAN (MAXVALUE)
);

LIST Partitioning

LIST partitions divide data based on discrete values of a single column. Each partition contains a specific list of column values. When data is inserted, rows are assigned to the corresponding partition according to the value of the column.

SQL examples are as follows:

CREATE TABLE client_firms (
	id INT,
	name VARCHAR(35)
)
PARTITION BY LIST (id) (
	PARTITION r0 VALUES IN (1, 5, 9, 13, 17, 21),
	PARTITION r1 VALUES IN (2, 6, 10, 14, 18, 22),
	PARTITION r2 VALUES IN (3, 7, 11, 15, 19, 23),
	PARTITION r3 VALUES IN (4, 8, 12, 16, 20, 24)
);

LIST COLUMNS

LIST COLUMNS selects a partition from enumerated column values. VALUES IN lists accepted values; an insert fails if none of the partitions matches.

CREATE TABLE lc (a INT, b INT)
PARTITION BY LIST COLUMNS(a) (
    PARTITION p0 VALUES IN (1, 2),
    PARTITION p1 VALUES IN (3, 4)
);
Last updated on