Managing Table Partitions with MariaDB Xpand
This page is part of MariaDB's Documentation.
The parent of this page is: Table Partitioning DDL for MariaDB Xpand
Topics on this page:
Overview
This page describes how to create, modify and manage partitioned tables. Xpand only supports RANGE partitioning. For additional information, see "Table Partitioning Limitations with MariaDB Xpand".
Partitioning Syntax
CREATE TABLE table_definition PARTITION BY RANGE partition_function (partition_definition)
ALTER TABLE table_name ADD | DROP PARTITION partition_name
ALTER TABLE table_name TRUNCATE PARTITION [partition_name | ALL]
ALTER TABLE table_name EXCHANGE PARTITION [partition_name | ALL]
ALTER TABLE table_name REORGANIZE PARTITION partition_name into (partition_definition)
ALTER TABLE table_name PARTITION BY RANGE partition_function (partition_definition)
ALTER TABLE table_name REMOVE PARTITIONING
Partition Name
Names for partitions must be unique. Xpand recommends using meaningful partition names.
Partition Definition
Partitions are defined by their uppermost value. Data must qualify for one and only one partition. It is advisable to define a final MAXVALUE
partition so that the table may store any remaining row values not explicitly covered by the prior partition definitions.
PARTITION partition_name VALUES LESS THAN (integer),
[PARTITION partition_name VALUES LESS THAN (integer), ...]
PARTITION partition_name VALUES LESS THAN MAXVALUE
Partition Function
A subset of functions can be used in the expression given to RANGE
. Using a function allows the relevant table column values to be transformed into a representation that is suitable for comparison with the endpoint values in the partition definitions. Note that the expression given to RANGE
must ultimately return an integer. This is necessary in order to provide a valid comparison with each partition definition's endpoint value. The columns used by the the partition function must be part of the primary key. The functions supported include:
CEILING()
FLOOR()
TO_DAYS()
UNIX_TIMESTAMP()
YEAR()
YEARWEEK()
User Privileges Needed for Partitioned Tables
A user must have CREATE, ALTER, and DROP TABLE privileges to manage partitions.
Defining Partitions for a Table
Use the following SQL to display the partitioning characteristics defined for a table. Use the FULL
parameter to view more extensive information. Additional database specifics regarding partitions can also be seen by querying information_schema.partitions
.
sql> SHOW [FULL] CREATE TABLE table_name;
CREATE TABLE with Partitions
sql> CREATE TABLE sample (
col1 INT NOT NULL,
col2 DATE NOT NULL,
col3 INT NOT NULL,
col4 INT NOT NULL,
PRIMARY KEY(col1, col2),
UNIQUE KEY(col2, col4)
)
PARTITION BY RANGE (YEAR(col2)) (
PARTITION p2000 VALUES LESS THAN (2000),
PARTITION p2010 VALUES LESS THAN (2010),
PARTITION pmax VALUES LESS THAN MAXVALUE);
Add More Partitions
Ranges specified for table partitions cannot overlap. If your table specifies MAXVALUE
, you must reorganize the top level table partition before adding a new partition before it.
sql> ALTER TABLE sample REORGANIZE PARTITION pmax INTO (
PARTITION p2015 VALUES LESS THAN (2015),
PARTITION pmax VALUES LESS THAN MAXVALUE);
Attempting to add a table partition that conflicts with another partition will result in this error:
sql> ALTER TABLE sample ADD PARTITION (PARTITION p2015 VALUES LESS THAN (2015));
sql> ERROR 1 (HY000): [12314] Invalid partitioning scheme: New partition "p2015" (2015) overlaps with existing partition "pmax" (MAXVALUE)
If MAXVALUE
hasn't been specified, you can just add a new partition:
sql> ALTER TABLE diff_sample ADD PARTITION (PARTITION p2015 VALUES LESS THAN (2015));
Add Partitioning to an Unpartitioned Table
sql> ALTER TABLE sample_unpartitioned
PARTITION BY RANGE (YEAR(col2)) (
PARTITION p2000 VALUES LESS THAN (2000),
PARTITION p2010 VALUES LESS THAN (2010),
PARTITION p2015 VALUES LESS THAN (2015),
PARTITION pmax VALUES LESS THAN MAXVALUE);
DROP PARTITION
DROP PARTITION
removes both the data and the partition definition. Multiple table partitions can be dropped at one time.
sql> ALTER TABLE sample DROP PARTITION p2000, p2010;
TRUNCATE PARTITION
TRUNCATE PARTITION
removes all data from a partition, but leaves the partition in place for reuse. TRUNCATE
is a DDL command and cannot be rolled back. DELETE
can be used to remove the same data, but as a DML command, DELETE
involves both row locking and logging for rollback and is slower as a result. TRUNCATE
and DROP PARTITION
are more efficient than DELETE
.
Xpand supports truncating a single table partition at a time or ALL partitions.
sql> ALTER TABLE sample TRUNCATE PARTITION P2000;
sql> ALTER TABLE sample TRUNCATE PARTITION ALL;
REORGANIZE
REORGANIZE
allows you to re-specify partition definitions. To increase the number of partitions used for a partition range:
sql> ALTER TABLE sample REORGANIZE PARTITION p2015 INTO (
PARTITION p2011 VALUES LESS THAN (2011),
PARTITION p2012 VALUES LESS THAN (2012),
PARTITION p2013 VALUES LESS THAN (2013),
PARTITION p2014 VALUES LESS THAN (2014),
PARTITION p2015 VALUES LESS THAN (2015));
REORGANIZE
can also be used to consolidated partitions:
sql> ALTER TABLE sample REORGANIZE PARTITION
p2011, p2012, p2013, p2014, p2015 INTO (
PARTITION p2015 VALUES LESS THAN (2015));
REORGANIZE
can also be used to rename a partition:
sql> ALTER TABLE sample REORGANIZE PARTITION pmax INTO (
PARTITION new_name VALUES LESS THAN MAXVALUE);
EXCHANGE PARTITION
EXCHANGE PARTITION
swaps all rows in a partition with the contents of a table. For example, this statement causes all rows found in the partition p2011 of sample
to be swapped with data found in table p2011_backup
. Data present in p2011_backup
is partitioned properly when moved to sample
. Xpand requires using the WITHOUT VALIDATION
option with EXCHANGE PARTITION
.
sql> ALTER TABLE sample EXCHANGE PARTITION p2011 WITH TABLE p2011_backup WITHOUT VALIDATION;
When exchanging partitions.
The destination table cannot be partitioned.
The destination table cannot be a temporary table.
The structures of two tables are otherwise identical.
The destination table contains no foreign keys, nor is it referenced by any foreign keys.
Since no validation is performed prior to making the exchange, it is possible that rows from the target table will be inserted into a partition in which they are not a valid member according to the partition definition. It is therefore highly recommended that EXCHANGE PARTITION
is only performed with a target table that is empty.
If you do suspect that rows are now in partitions that they shouldn't be, you can run REPAIR TABLE
in order to remove the invalid rows from the table.
Remove Table Partitioning Completely
This modifies the table to no longer have partitioning, but does not remove any data.
sql> ALTER TABLE sample REMOVE PARTITIONING;
Managing Slicing for Partitioned Tables
If you expect the partitions in your table to vary greatly by size, may need to configure separate slicing options for each partition. The default behavior is that slicing for a partitioned table is defined at the representation level and applied individually to each partition (e.g. a partitioned table with SLICES = 3 will result in 3 slices per partition).
In this example, partition p0 is configured with more slices.
sql> CREATE TABLE foo (
x INT,
y INT,
z INT,
PRIMARY KEY (x),
KEY z_idx (z)
) SLICES=3 PARTITION BY RANGE(x) (
PARTITION p0 VALUES LESS THAN (100) SLICES=6,
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300));
The other partitions derive their slicing from the table-level SLICES configuration:
SHOW CREATE TABLE
for the table above displays these results:
sql> SHOW CREATE TABLE foo;
Table | Create Table | +-------+----------------------------------------------------------------------------------------------+
| foo | CREATE TABLE `foo` (
`x` int(11) not null,
`y` int(11),
`z` int(11),
PRIMARY KEY (`x`) /*$ DISTRIBUTE=1 */,
KEY `z_idx` (`z`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ */
PARTITION BY RANGE (x) (
PARTITION p0 VALUES LESS THAN (100) /*$ SLICES=6 */,
PARTITION p1 VALUES LESS THAN (200) /*$ SLICES=3 */,
PARTITION p2 VALUES LESS THAN (300) /*$ SLICES=3 */)
By default, indexes used by partitions will inherit table-level (index) slicing. You can also configure separate slicing options for indexes used by partitions:
sql> CREATE TABLE `foo` (
`x` int(11) not null,
`y` int(11),
`z` int(11),
PRIMARY KEY (`x`) /*$ DISTRIBUTE=1 */,
KEY `z_idx` (`z`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ */
PARTITION BY RANGE (x) (
PARTITION p0 VALUES LESS THAN (100) SLICES=6 INDEX y_idx SLICES=6,
PARTITION p1 VALUES LESS THAN (200),
PARTITION p2 VALUES LESS THAN (300));
DEFAULT_SLICES
The DEFAULT_SLICES
option allows you to specify a slicing configuration for new partitions only. This is useful if you want all newly-created partitions to have a specific slicing, but don't want to immediately re-slice the existing partitions. The value for DEFAULT_SLICES
will be used by future ALTER TABLE
commands that rewrite row data or modify partitions. Using DEFAULT_SLICES
is a way to defer the potentially expensive work of re-slicing a table so that future modifications to the table will use the value for DEFAULT_SLICES
when a values for SLICES
is determined.
sql> ALTER TABLE `foo` default_slices = 16;
sql> ALTER TABLE `foo` REORGANIZE PARTITION p2 INTO (
PARTITION p25 VALUES LESS THAN (250),
PARTITION p30 VALUES LESS THAN (300));
New partitions will have the new default number of slices:
sql> SHOW CREATE TABLE foo;
+-------+---------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+---------------------------------------------------------------------------------------------------+
| foo | CREATE TABLE `foo` (
`x` int(11) not null,
`y` int(11),
`z` int(11),
PRIMARY KEY (`x`) /*$ DISTRIBUTE=1 */,
KEY `z_idx` (`z`) /*$ DISTRIBUTE=1 */
) CHARACTER SET utf8 /*$ DEFAULT_SLICES=16 */
PARTITION BY RANGE (x) (
PARTITION p0 VALUES LESS THAN (100) /*$ SLICES=6 */,
PARTITION p1 VALUES LESS THAN (200) /*$ SLICES=4 */,
PARTITION p25 VALUES LESS THAN (250) /*$ SLICES=16 */,
PARTITION p30 VALUES LESS THAN (300) /*$ SLICES=16 */)
|
+-------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)