INTERVAL RANGE

Prerequisites

• The kernel version of GaussDB(for MySQL) must be 2.0.54.240600 or later.
• rds_interval_range_enabled has been set to ON.

Constraints

• INTERVAL RANGE partitioned tables support only HASH or KEY subpartitions.
• If an INTERVAL RANGE rule is in RANGE COLUMNS(column_list) INTERVAL([type], value) format:
  • column_list specifies only a single partition key, which must be an integer or of the DATE/TIME/DATETIME type.
  • If the partition key is an integer, the interval type (type) can be left blank.
  • If the partition key is of the DATE type,the interval type (type) can only be YEAR, QUARTER, MONTH, WEEK, or DAY.
  • If the partition key is of the TIME type, the interval type (type) can only be HOUR, MINUTE, or SECOND.
  • If the partition key is of the DATETIME type, the interval type (type) can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND.
  • The interval value (value) must be a positive integer.
  • If the interval type (type) is SECOND, the interval cannot be less than 60.
• If an INTERVAL RANGE rule is in RANGE(expr) INTERVAL(value) format, the result of the expr expression must be an integer, and the interval value must be a positive integer.
• You cannot execute the INSERT ... SELECT, INSERT ... ON DUPLICATE KEY UPDATE, and UPDATE statements to add partitions.
• When you execute the LOAD DATA statement to import data, partition creation will not be triggered. (If the range of the partition covers all data, data can be imported. If the range of the partition does not cover all data, partition creation is not triggered, and data fails to be imported.)
• Once partitions are automatically created, they cannot be rolled back.
• Prefix _p is reserved for automatically created partitions. If you use this prefix for custom partitions, automatic partition creation may fail.
• The SET INTERVAL([type], value) clause applies only to INTERVAL RANGE and RANGE partitioned tables. If these tables have subpartitions, the subpartitions must be of the HASH or KEY type.
• The values of type and value in the SET INTERVAL([type], value) clause must be restricted by the partition expression expr or the partition key column_list of the original table.

Parameters

Creating an INTERVAL RANGE Partitioned Table

The definition format of an INTERVAL RANGE partitioned table is similar to that of a RANGE partitioned table, with the addition of an INTERVAL clause.

Syntax:

CREATE TABLE [IF NOT EXISTS] [schema.]table_name
table_definition
partition_options;

partition_options is:

PARTITION BY
    RANGE {(expr) | COLUMNS(column_list)}
    {INTERVAL(value) | INTERVAL(type, expr)}
    (partition_definition [, partition_definition] ...)

partition_definition is:

PARTITION partition_name
    [VALUES LESS THAN {expr | MAXVALUE}]
    [[STORAGE] ENGINE [=] engine_name]
    [COMMENT [=] 'string' ]
    [DATA DIRECTORY [=] 'data_dir']
    [INDEX DIRECTORY [=] 'index_dir']
    [MAX_ROWS [=] max_number_of_rows]
    [MIN_ROWS [=] min_number_of_rows]
    [TABLESPACE [=] tablespace_name]

The INTERVAL clause supports only the interval value (value) and interval type (type).

Description of parameters associated with the INTERVAL clause:

Further description of interval values (value) and interval types (type):

Conversion Between INTERVAL RANGE Partitioned Tables and Other Types of Tables

Syntax:

Convert other types of tables to INTERVAL RANGE partitioned tables.

ALTER TABLE table_name table_definition
partition_options;
​
partition_options:
    PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, value) | INTERVAL(value) }
    [(partition_definition [, partition_definition] ...)]
​
partition_definition:
    PARTITION partition_name
        [VALUES LESS THAN {expr | MAXVALUE}]
        [[STORAGE] ENGINE [=] engine_name]
        [COMMENT [=] 'string' ]
        [DATA DIRECTORY [=] 'data_dir']
        [INDEX DIRECTORY [=] 'index_dir']
        [MAX_ROWS [=] max_number_of_rows]
        [MIN_ROWS [=] min_number_of_rows]
        [TABLESPACE [=] tablespace_name]

Description of parameters associated with the INTERVAL clause:

Convert an INTERVAL RANGE partitioned table to any other type of table. partition_options is optional.

ALTER TABLE table_name table_definition
[partition_options];

Examples:

Convert other types of tables to INTERVAL RANGE partitioned tables.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
);
​
ALTER TABLE orders
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);

Convert an INTERVAL RANGE partitioned table to another type of table.

CREATE TABLE orders (a INT, b DATETIME)
PARTITION BY RANGE (a) INTERVAL(10)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
ALTER TABLE orders PARTITION BY LIST COLUMNS (a)
(
    PARTITION p0 VALUES IN (1, 11, 25)
);

Modify the INTERVAL clause in the INTERVAL RANGE partitioned table.

CREATE TABLE orders (a INT, b DATETIME)
PARTITION BY RANGE (a) INTERVAL(10)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
ALTER TABLE orders PARTITION BY RANGE (a) INTERVAL(20)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
# Delete the INTERVAL clause.
ALTER TABLE orders PARTITION BY RANGE (a)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);
​
# Add the INTERVAL clause.
ALTER TABLE orders PARTITION BY RANGE (a) INTERVAL(100)
(
    PARTITION p0 VALUES LESS THAN(10),
    PARTITION p2 VALUES LESS THAN(20)
);

Support for the SET INTERVAL Clause

You can use the SET INTERVAL clause to modify the interval type and value of the INTERVAL clause defined in the INTERVAL RANGE partitioned table, or eliminate or add the INTERVAL clause.

Syntax:

ALTER TABLE table_name SET INTERVAL {() | (type, value) | (value)};

Example:

Modify the interval type and value in the INTERVAL RANGE partitioned table.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
​
ALTER TABLE orders SET INTERVAL(YEAR, 1);

Convert a RANGE partitioned table to an INTERVAL RANGE partitioned table.

CREATE TABLE orders(
  orderkey BIGINT NOT NULL,
  custkey BIGINT NOT NULL,
  orderdate DATE NOT NULL
)
PARTITION BY RANGE COLUMNS(orderdate) INTERVAL(MONTH, 1) (
  PARTITION p0 VALUES LESS THAN('2021-10-01')
);
​
# Delete the INTERVAL clause.
ALTER TABLE sales SET INTERVAL();
​
# Add the INTERVAL clause.
ALTER TABLE sales SET INTERVAL(DAY, 60);

The ALTER TABLE table_name SET INTERVAL() statement can be used even if rds_interval_range_enabled is disabled. This statement is used to eliminate the definition of the INTERVAL clause in an INTERVAL RANGE partitioned table and convert the partitioned table to a RANGE partitioned table.