CREATE TABLE - Sub-Partition definitions

Syntax

Below is the syntax for this command. Text within square brackets (i.e, [ and ] ) are optional. Choices are separated by bars (i.e, | ). Ellipses preceded by a comma indicates a repeating pattern. Ellipses before or after syntax are used to highlight a relevant except from the larger syntax. Text displayed in italic letters represent text that should be replaced with the specific names related to the database (e.g., column with the name of the column).

CREATE [TEMPORARY] TABLE [IF NOT EXISTS] table
    SUBPARTITION partition
        [[STORAGE] ENGINE [=] engine]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] '/path']
        [INDEX DIRECTORY [=] '/path']
        [MAX_ROWS [=] number]
        [MIN_ROWS [=] number]
        [TABLESPACE [=] (tablespace)]
        [NODEGROUP [=] number]

Explanation

Only partitions distributed by the RANGE or LIST methods can be subpartitioned. The subpartitions can use only the HASH or KEY methods. The definitions for subpartitions are the same as for partitions, described in the CREATE TABLE - Partitioning page.

Examples

CREATE TABLE ts (id INT, purchased DATE)
    PARTITION BY RANGE( YEAR(purchased) )
    SUBPARTITION BY HASH( TO_DAYS(purchased) )
    SUBPARTITIONS 2 (
        PARTITION p0 VALUES LESS THAN (1990),
        PARTITION p1 VALUES LESS THAN (2000),
        PARTITION p2 VALUES LESS THAN MAXVALUE
    );

CREATE TABLE sales_figures
(emp_id INT, sales_date DATE, amount INT)
PARTITION BY RANGE(YEAR(sales_date))
SUBPARTITION BY HASH(MONTH(sales_date))
SUBPARTITIONS 4 (
   PARTITION QTR1 VALUES LESS THAN (4),
   PARTITION QTR2 VALUES LESS THAN (7),
   PARTITION QTR3 VALUES LESS THAN (10),
   PARTITION QTR4 VALUES LESS THAN MAXVALUE);

Notice that although the subpartition uses HASH, the subpartions are specified in ranges of values because it's a subpartition of a partition that uses the RANGE method,.