Table Options with MariaDB Xpand

This page is part of MariaDB's Documentation.

The parent of this page is: Table DDL for MariaDB Xpand

Topics on this page:

Overview
Compatibility
Table Options with CREATE TABLE
Table Options with ALTER TABLE
Accepted Table Options
Ignored Table Options

Overview

MariaDB Xpand's table options are used in CREATE TABLE and ALTER TABLE statements to define table characteristics:

Xpand ignores some InnoDB-related table options in table definitions to allow easy migrations from InnoDB
Xpand accepts the REPLICAS table option to provide an increased level of fault tolerance for a given table

Compatibility

MariaDB Xpand 5.3
MariaDB Xpand 6.0
MariaDB Xpand 6.1

Table Options with `CREATE TABLE`

To set table options for a new table with MariaDB Xpand, set the table options in the CREATE TABLE statement:

CREATE TABLE hq_sales.invoices (
   invoice_id BIGINT UNSIGNED AUTO_INCREMENT NOT NULL,
   branch_id INT NOT NULL,
   customer_id INT,
   invoice_date DATETIME(6),
   invoice_total DECIMAL(13, 2),
   payment_method ENUM('NONE', 'CASH', 'WIRE_TRANSFER', 'CREDIT_CARD', 'GIFT_CARD'),
   PRIMARY KEY(invoice_id)
) SLICES=6;

Table Options with `ALTER TABLE`

To set table options for an existing table with MariaDB Xpand, set the table options in the ALTER TABLE statement:

ALTER TABLE hq_sales.invoices
   REPLICAS=ALLNODES;

Accepted Table Options

MariaDB Xpand supports the following table options:

Table Option	Description
`AUTO_INCREMENT`	Specifies the next auto-increment value
`AUTO_STATISTICS`	Specifies which Probability Distributions (PDs) are collected by default: Set to `NONE` for no PDs Set to `BASIC` for cardinality, NDV, and hotlist statistics Set to `EXTENDED` for GH quantiles in addition to `BASIC` statistics For additional information about Probability Distributions (PDs), see "Collect Static PDs with `ANALYZE FULL TABLE` for MariaDB Xpand".
`BUILDING`	This table option is for internal use Specifies whether the table is being rebuilt as part of a schema change process
`COMMENT`	Specifies an informational comment string for the table
`CONTAINER`	Specifies whether the table's representations are in-memory or on-disk: Set to `SKIPLIST` to create in-memory representations Set to `LAYERED` (default) to create on-disk representations The current container type can be viewed with `SHOW FULL CREATE TABLE`
`DISTRIBUTE`	Specifies the distribution of a composite primary key or the distribution of other indexes (non-primary key) after a table has been created
`ENGINE` / `TYPE`	Specifies the supported ENGINE types
`HASHES`	This table option is for internal use Specifies the hash value boundaries for each slice The current hash value boundaries can be viewed with `SHOW FULL CREATE TABLE`
`INSERT_LOCK`	When set, specifies that `INSERT` statements use `IX` table locks
`PAYLOAD`	This table option is for internal use Specifies the payload columns for the table, which are any columns that are not part of the primary key The current payload columns can be viewed with `SHOW FULL CREATE TABLE`
`REPLICAS`	Specifies the number of replicas Xpand maintains for the table's slices: Set to a number to maintain a specific number of replicas (defaults to 2) Set to `ALLNODES` to maintain a replica of each slice on every node
`SLICES`	Specifies the number of slices Xpand maintains for each of the table's representations
`SPLIT_THRESHOLD_KB`	Specifies a table-specific override for the `rebalancer_split_threshold_kb` system variable For additional information about slice splitting, see "Slice Split Example for MariaDB Xpand".

Ignored Table Options

MariaDB Xpand ignores the following table options to allow easy migrations from MariaDB Server and MySQL:

CHECKSUM
CONNECTION
DELAY_KEY_WRITE
INSERT_METHOD
KEY_BLOCK_SIZE
MAX_ROWS
MIN_ROWS
PACK_KEYS
PAGE_CHECKSUM
PASSWORD
ROW_FORMAT
TABLESPACE
UNION