Managing Table Partitions with MariaDB Xpand

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)