Supported partitioning functions data types and policies for automatic partitioning in PolarDB-X - PolarDB

PolarDB-X supports a fixed set of partitioning functions for databases in automatic partitioning mode. Only the functions listed in this topic are supported in partitioning expressions.

Key concepts

Partition key: One or more columns used to horizontally partition a table.

Partition key column: A single column that contributes to a partition key. A partition key consists of one or more partition key columns.

Single-column partition key: A partition key with exactly one column. Example: c1 in PARTITION BY HASH(c1).

Vector partition key: A partition key with one or more partition key columns. Example: (c1,c2,c3) in PARTITION BY HASH(c1,c2,c3).

Partitioning function: A function applied to partition key columns that returns a value PolarDB-X uses to route rows to the correct partition. Example: PARTITION BY HASH(TO_DAYS(c1)).

Monotonicity: The relationship between changes in the input value x and the output of func(x). Monotonicity determines whether PolarDB-X can apply partition pruning to range queries.

Monotonicity type	Definition	Range query partition pruning
Strict monotonicity	If `x > y`, then `func(x) > func(y)`. If `x < y`, then `func(x) < func(y)`.	Supported
Non-strict monotonicity	If `x > y`, then `func(x) >= func(y)`. If `x < y`, then `func(x) <= func(y)`.	Supported
No monotonicity	When `x > y`, `func(x)` may not be `>= func(y)`.	Not supported

Functions with no monotonicity (such as MONTH, DAYOFWEEK, DAYOFMONTH, DAYOFYEAR, SUBSTR/SUBSTRING, RIGHT, and LEFT) do not support partition pruning for range queries. Queries using range predicates on partitions keyed by these functions scan all partitions.

Function properties

The following table lists all supported partitioning functions, the minimum PolarDB-X version required, the monotonicity type, and the supported column data types.

Footnotes for the Supported data types column:

Integer types: BIGINT, BIGINT UNSIGNED, INT, INT UNSIGNED, MEDIUMINT, MEDIUMINT UNSIGNED, SMALLINT, SMALLINT UNSIGNED, TINYINT, TINYINT UNSIGNED
Date and time types: DATE, DATETIME, TIMESTAMP
String types: CHAR, VARCHAR

Function	Description	Minimum version	Monotonicity	Integer types	Date and time types	String types
YEAR(date)	Returns the year of the input date. See YEAR(date) in the MySQL documentation.	5.4.13	Non-strict	-	Yes	-
MONTH(date)	Returns the month of the input date. See MONTH(date) in the MySQL documentation.	5.4.13	None	-	Yes	-
TO_DAYS(date)	Returns the number of days from January 1, 0000 to the input date. See TO_DAYS(date) in the MySQL documentation.	5.4.13	Non-strict	-	Yes	-
TO_MONTHS(date)	Returns the number of months from January 1, 0000 to the input date. This is a PolarDB-X-specific function.	5.4.16-16717637	Non-strict	-	Yes	-
TO_WEEKS(date)	Returns the number of weeks from January 1, 0000 to the input date. This is a PolarDB-X-specific function.	5.4.16-16717637	Non-strict	-	Yes	-
TO_SECONDS(expr)	Returns the number of seconds from January 1, 0000, 00:00:00 to the input time. See TO_SECONDS(expr) in the MySQL documentation.	5.4.13	Non-strict	-	Yes	-
UNIX_TIMESTAMP([date])	Returns the number of seconds from January 1, 1970, 00:00:00 UTC to the input time. See UNIX_TIMESTAMP([date]) in the MySQL documentation.	5.4.13	Non-strict	-	Yes	-
DAYOFWEEK(date)	Returns the day of the week as an integer (1 = Sunday, 2 = Monday, ..., 7 = Saturday). See DAYOFWEEK(date) in the MySQL documentation.	5.4.16-16717637	None	-	Yes	-
DAYOFMONTH(date)	Returns the day of the month as an integer (1-31). See DAYOFMONTH(date) in the MySQL documentation.	5.4.16-16717637	None	-	Yes	-
DAYOFYEAR(date)	Returns the day of the year as an integer (1-366). See DAYOFYEAR(date) in the MySQL documentation.	5.4.16-16717637	None	-	Yes	-
SUBSTR/SUBSTRING	Returns a substring of the input string. See SUBSTRING() in the MySQL documentation.	5.4.16-16717637	None	-	-	Yes
RIGHT(str, len)	Returns the rightmost `len` characters of the input string. See RIGHT(str,len) in the MySQL documentation.	5.4.18-17034692	None	-	-	Yes
LEFT(str, len)	Returns the leftmost `len` characters of the input string. See LEFT(str,len) in the MySQL documentation.	5.4.18-17034692	None	-	-	Yes

Partitioning policy support

The following table shows which partitioning policies each function supports.

All functions support HASH (single-column), RANGE (single-column), and LIST (single-column). No function supports KEY, HASH (vector), RANGE COLUMNS, LIST COLUMNS, or LIST (vector). CO_HASH support is limited to the string functions.

Function	KEY	HASH (single-column)	HASH (vector)	RANGE (single-column)	RANGE (vector)	RANGE COLUMNS	LIST (single-column)	LIST (vector)	LIST COLUMNS	CO_HASH
YEAR	No	Yes	No	Yes	No	No	Yes	No	No	No
MONTH	No	Yes	No	Yes	No	No	Yes	No	No	No
TO_DAYS	No	Yes	No	Yes	No	No	Yes	No	No	No
TO_MONTHS	No	Yes	No	Yes	No	No	Yes	No	No	No
TO_WEEKS	No	Yes	No	Yes	No	No	Yes	No	No	No
TO_SECONDS	No	Yes	No	Yes	No	No	Yes	No	No	No
UNIX_TIMESTAMP	No	Yes	No	Yes	No	No	Yes	No	No	No
DAYOFWEEK	No	Yes	No	Yes	No	No	Yes	No	No	No
DAYOFMONTH	No	Yes	No	Yes	No	No	Yes	No	No	No
DAYOFYEAR	No	Yes	No	Yes	No	No	Yes	No	No	No
SUBSTR/SUBSTRING	No	Yes	No	Yes	No	No	Yes	No	No	Yes
RIGHT	No	Yes	No	Yes	No	No	Yes	No	No	Yes
LEFT	No	Yes	No	Yes	No	No	Yes	No	No	Yes