Compute
Elastic Cloud Server
Huawei Cloud Flexus
Bare Metal Server
Auto Scaling
Image Management Service
Dedicated Host
FunctionGraph
Cloud Phone Host
Huawei Cloud EulerOS
Networking
Virtual Private Cloud
Elastic IP
Elastic Load Balance
NAT Gateway
Direct Connect
Virtual Private Network
VPC Endpoint
Cloud Connect
Enterprise Router
Enterprise Switch
Global Accelerator
Management & Governance
Cloud Eye
Identity and Access Management
Cloud Trace Service
Resource Formation Service
Tag Management Service
Log Tank Service
Config
OneAccess
Resource Access Manager
Simple Message Notification
Application Performance Management
Application Operations Management
Organizations
Optimization Advisor
IAM Identity Center
Cloud Operations Center
Resource Governance Center
Migration
Server Migration Service
Object Storage Migration Service
Cloud Data Migration
Migration Center
Cloud Ecosystem
KooGallery
Partner Center
User Support
My Account
Billing Center
Cost Center
Resource Center
Enterprise Management
Service Tickets
HUAWEI CLOUD (International) FAQs
ICP Filing
Support Plans
My Credentials
Customer Operation Capabilities
Partner Support Plans
Professional Services
Analytics
MapReduce Service
Data Lake Insight
CloudTable Service
Cloud Search Service
Data Lake Visualization
Data Ingestion Service
GaussDB(DWS)
DataArts Studio
Data Lake Factory
DataArts Lake Formation
IoT
IoT Device Access
Others
Product Pricing Details
System Permissions
Console Quick Start
Common FAQs
Instructions for Associating with a HUAWEI CLOUD Partner
Message Center
Security & Compliance
Security Technologies and Applications
Web Application Firewall
Host Security Service
Cloud Firewall
SecMaster
Anti-DDoS Service
Data Encryption Workshop
Database Security Service
Cloud Bastion Host
Data Security Center
Cloud Certificate Manager
Edge Security
Situation Awareness
Managed Threat Detection
Blockchain
Blockchain Service
Web3 Node Engine Service
Media Services
Media Processing Center
Video On Demand
Live
SparkRTC
MetaStudio
Storage
Object Storage Service
Elastic Volume Service
Cloud Backup and Recovery
Storage Disaster Recovery Service
Scalable File Service Turbo
Scalable File Service
Volume Backup Service
Cloud Server Backup Service
Data Express Service
Dedicated Distributed Storage Service
Containers
Cloud Container Engine
SoftWare Repository for Container
Application Service Mesh
Ubiquitous Cloud Native Service
Cloud Container Instance
Databases
Relational Database Service
Document Database Service
Data Admin Service
Data Replication Service
GeminiDB
GaussDB
Distributed Database Middleware
Database and Application Migration UGO
TaurusDB
Middleware
Distributed Cache Service
API Gateway
Distributed Message Service for Kafka
Distributed Message Service for RabbitMQ
Distributed Message Service for RocketMQ
Cloud Service Engine
Multi-Site High Availability Service
EventGrid
Dedicated Cloud
Dedicated Computing Cluster
Business Applications
Workspace
ROMA Connect
Message & SMS
Domain Name Service
Edge Data Center Management
Meeting
AI
Face Recognition Service
Graph Engine Service
Content Moderation
Image Recognition
Optical Character Recognition
ModelArts
ImageSearch
Conversational Bot Service
Speech Interaction Service
Huawei HiLens
Video Intelligent Analysis Service
Developer Tools
SDK Developer Guide
API Request Signing Guide
Terraform
Koo Command Line Interface
Content Delivery & Edge Computing
Content Delivery Network
Intelligent EdgeFabric
CloudPond
Intelligent EdgeCloud
Solutions
SAP Cloud
High Performance Computing
Developer Services
ServiceStage
CodeArts
CodeArts PerfTest
CodeArts Req
CodeArts Pipeline
CodeArts Build
CodeArts Deploy
CodeArts Artifact
CodeArts TestPlan
CodeArts Check
CodeArts Repo
Cloud Application Engine
MacroVerse aPaaS
KooMessage
KooPhone
KooDrive

INTERVAL RANGE

Updated on 2025-02-11 GMT+08:00

An INTERVAL RANGE partitioned table is an extension of a RANGE partitioned table. If data to be inserted into a RANGE partitioned table falls outside the range of an existing partition, it cannot be inserted and an error will be returned.

If there is an INTERVAL RANGE partitioned table in a database, the database can create a partition based on rules specified by the INTERVAL clause when data to be inserted exceeds the range of an existing partition.

Prerequisites

  • The kernel version of your TaurusDB instance 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 of the INTEGER, DATE, TIME, or DATETIME type.
    • If the partition key is of the INTEGER type, 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, expr must be an integer, and 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, automatic 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, automatic partition creation will not be triggered and data cannot 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

Table 1 Parameter description

Parameter

Level

Description

rds_interval_range_enabled

Global

Enables or disables INTERVAL RANGE.

Value:

  • ON: INTERVAL RANGE is enabled.
  • OFF: INTERVAL RANGE is disabled.

Creating an INTERVAL RANGE Partitioned Table

The definition format of an INTERVAL RANGE partitioned table is similar to that of a RANGE partitioned table. The only difference is that the INTERVAL clause is added.

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:

Table 2 Parameter description

Parameter

Description

INTERVAL(value)

The format of the INTERVAL clause when RANGE COLUMNS(column_list) with an integer column or RANGE(expr) is used. value indicates the interval value, which must be a positive integer.

expr

The expression of the partition. It is used in RANGE(expr) and must be of the integer type.

column_list

The list of partitions. It is used in RANGE COLUMNS(column_list). In an INTERVAL RANGE partitioned table, column_list can only be a single column.

INTERVAL(type, value)

The format of the INTERVAL clause when RANGE COLUMNS(column_list) is used and column_list is of the DATE, TIME, or DATETIME type. type indicates the interval type and its value can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. value indicates the interval value, which must be a positive integer. When type is set to SECOND, the interval value cannot be less than 60.

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

  • Interval values (expr)

    Add 1,000 consecutive numbers to a partition.

    Example:

    INTERVAL(1000)
  • Time types
    • YEAR

      Set the interval type to YEAR and add the data of one year to a partition.

      Example:

      INTERVAL(YEAR, 1)
    • QUARTER

      Set the interval type to QUARTER and add the data of one quarter to a partition.

      Example:
      INTERVAL(QUARTER, 1)
    • MONTH

      Set the interval type to MONTH and add the data of one month to a partition.

      Example:

      INTERVAL(MONTH, 1)
    • WEEK

      Set the interval type to WEEK and add the data of one week to a partition.

      Example:

      INTERVAL(WEEK, 1)
    • DAY

      Set the interval type to DAY and add the data of one day to a partition.

      Example:

      INTERVAL(DAY, 1)
    • HOUR

      Set the interval type to HOUR and add the data of one hour to a partition.

      Example:
      INTERVAL(HOUR, 1)
    • MINUTE

      Set the interval type to MINUTE and add the data of one minute to a partition.

      Example:
      INTERVAL(MINUTE, 1)
    • SECOND

      Set the interval type to SECOND and add the data of every 60 seconds to a partition.

      Example:
      INTERVAL(SECOND, 60)

The following example uses order_time as the partition key to partition the sales table by interval.

Create an INTERVAL RANGE partitioned table in a database and insert data into a table. Example:

CREATE TABLE sales
(
  id BIGINT,
  uid BIGINT,
  order_time DATETIME
)
PARTITION BY RANGE COLUMNS(order_time) INTERVAL(MONTH, 1)
(
  PARTITION p0 VALUES LESS THAN('2021-9-1')
);

Insert data into the INTERVAL RANGE partitioned table. Example:

INSERT INTO sales VALUES(1, 1010101010, '2021-11-11');

After data is inserted, execute the SHOW CREATE TABLE statement to query the sales table definition. The new table definition is as follows:

CREATE TABLE `sales` (
  `id` bigint DEFAULT NULL,
  `uid` bigint DEFAULT NULL,
  `order_time` datetime DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
/*!50500 PARTITION BY RANGE  COLUMNS(order_time) */ /*!99990 800220201 INTERVAL(MONTH, 1) */
/*!50500 (PARTITION p0 VALUES LESS THAN ('2021-9-1') ENGINE = InnoDB,
 PARTITION _p20211001000000 VALUES LESS THAN ('2021-10-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211101000000 VALUES LESS THAN ('2021-11-01 00:00:00') ENGINE = InnoDB,
 PARTITION _p20211201000000 VALUES LESS THAN ('2021-12-01 00:00:00') ENGINE = InnoDB) */

In the preceding example, three partitions _p20211001000000, _p20211101000000, and _p20211201000000 are automatically added to the INTERVAL RANGE partition. Note that partition names prefixed with _p are reserved by the system. Such partition names cannot be used when you create or rename partitions..

INTERVAL RANGE partitioned tables support HASH or KEY subpartitions. Example:

CREATE TABLE sales_ir_key
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE(month(date)) INTERVAL(1)
SUBPARTITION BY KEY(date) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN(4)
    (SUBPARTITION sp_001,
     SUBPARTITION sp_002),
  PARTITION q2_2012 VALUES LESS THAN(7)
    (SUBPARTITION sp_003,
     SUBPARTITION sp_004)
);
CREATE TABLE sales_ir_hash
(
  dept_no     INT,
  part_no     INT,
  country     varchar(20),
  date        DATE,
  amount      INT
)
PARTITION BY RANGE COLUMNS(date) INTERVAL(YEAR, 1)
SUBPARTITION BY HASH(TO_DAYS(date)) SUBPARTITIONS 2
(
  PARTITION q1_2012 VALUES LESS THAN('2021-01-01')
    (SUBPARTITION sp_001,
     SUBPARTITION sp_002),
  PARTITION q2_2012 VALUES LESS THAN('2022-01-01')
    (SUBPARTITION sp_003,
     SUBPARTITION sp_004)
);

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 is:
    PARTITION BY
    { RANGE{(expr) | COLUMNS(column_list)} }
    { INTERVAL(type, value) | INTERVAL(value) }
    [(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]

Description of parameters associated with the INTERVAL clause:

Table 3 Parameter description

Parameter

Description

INTERVAL(value)

The format of the INTERVAL clause when RANGE COLUMNS(column_list) with an integer column or RANGE(expr) is used. value indicates the interval value, which must be a positive integer.

expr

The expression of the partition. It is used in RANGE(expr) and must be of the integer type.

column_list

The list of partitions. It is used in RANGE COLUMNS(column_list). In an INTERVAL RANGE partitioned table, column_list can only be a single column.

INTERVAL(type, value)

The format of the INTERVAL clause when RANGE COLUMNS(column_list) is used and column_list is of the DATE, TIME, or DATETIME type. type indicates the interval type and its value can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. value indicates the interval value, which must be a positive integer. When type is set to SECOND, the interval value cannot be less than 60.

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)
);

SET INTERVAL Clause Usage

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)};
Table 4 Parameter description

Parameter

Description

type

The type of the interval. Its value can be YEAR, QUARTER, MONTH, WEEK, DAY, HOUR, MINUTE, or SECOND. If you do not specify this parameter, the numeric type is used by default.

value

The value of the interval. When type is set to SECOND, the interval value cannot be less than 60.

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);
CAUTION:

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.

We use cookies to improve our site and your experience. By continuing to browse our site you accept our cookie policy. Find out more

Feedback

Feedback

Feedback

0/500

Selected Content

Submit selected content with the feedback