人大金仓数据库KingbaseES Interval分区介绍

关键字：

Range分区，INTERVAL分区、人大金仓、KingbaseES

什么是INTERVAL分区

首先，简单铺垫分区的概念。在数据库中，表分区是将大型表或者索引拆分成较小、可管理的部分的技术。分区表的主要使用场景是针对单表的数据过大，需要将数据进行拆分。这样既可以提高查询性能、管理性能和维护数据库的效率。

INTERVAL分区也可以叫间隔分区，最早是oracle11.1中引入的新功能。INTERVAL是一种分区策略，基于时间或数值范围创建分区，每个分区包含特定的时间段或数值范围。可以在输入相应分区数据时自动创建相应的分区，作为范围分区(range partition)的扩展，间隔分区可以让数据库在插入表中的数据超过所有现有范围分区时自动创建指定间隔的分区。

1.1INTERVAL分区的特点

1. 由range分区派生而来。
2. 是基于数值范围或时间间隔的分区。
3. 在表中插入数据时，系统根据需要创建新的分区和本地索引。

INTERVAL分区创建

2.1语法

INTERVAL分区创建基于Range分区，其完整语法如下所示：

range_partitions::=

range_values_clause::=

2.2 说明

1.range_partition字句用于按照分区键值的范围创建表的分区，将表的数据分布在对应的分区中。

2.range_partition中指定INTERVAL关键字，可以创建间隔分区，分区由指定的间隔参数创建。间隔分区与常规的范围分区存在一些差异，具体如下：

（2.1）间隔分区会自动创建新的分区，而范围分区需手动创建完整的分区范围。

（2.2）间隔分区基于统一的间隔值，当新的分区范围超过了已定的范围，会自动生成新的分区。范围分区则是根据具体范围划分来划分数据。

（2.3）INTERVAL分区仅支持唯一的分区键列，分区键列的类型必须是NUMBER、DATE、FLOAT或TIMESTAMP。

3.range_values_clause子句用于为分区表指定一个或多个分区的开始或结束值，与range_partition一起使用，用于按值范围定义表的分区策略。

2.3关键字解读

例子

range_partitions子句主要是创建范围（range）分区或间隔（INTERVAL）分区的表。下面的例子是创建销售表，并根据sales_date列的值自动创建以月为间隔的分区，当表中插入新数据时，数据库会自动为新的时间段创建分区。VALUES LESS THEN子句用于确定分区边界。

CREATE TABLE sales

(

    sales_id NUMBER,

    sales_date TIMESTAMP,

    amount NUMBER

)

PARTITION BY RANGE (sales_date) INTERVAL ('1 MONTH')

(

    PARTITION sales_jan_2023 VALUES LESS THAN ('2023-02-01'),

    PARTITION sales_feb_2023 VALUES LESS THAN ('2023-03-01')

) ;

上述例子中，表可以根据sales_date列的值自动创建以月为间隔的分区，当新的数据插入到表中时，数据库会自动为新的时间段创建新的分区。

在表中插入数据：

INSERT INTO sales (sales_id, sales_date, amount)

VALUES (1, '2023-01-15',1000);

INSERT INTO sales (sales_id, sales_date, amount)

VALUES (1, '2023-02-15',800);

INTERVAL关键字支持设置数字和时间间隔：

2.3.1数字分区

数字分区中，可以将一定范围的数字划分成不同的区间或分组。

CREATE TABLE sales1

(

    sales_id NUMBER,

    quantity NUMBER,

    sales_date DATE

)

PARTITION BY RANGE (quantity) INTERVAL (10)

(

    PARTITION part1 VALUES LESS THAN (10)

) ;

--插入数据

INSERT INTO sales1(sales_id, quantity, sales_date)

VALUES(1, 5, TO_DATE('2022-01-01', 'YYYY-MM-DD'));

INSERT INTO sales1(sales_id, quantity, sales_date)

VALUES(2, 15, TO_DATE('2022-01-02', 'YYYY-MM-DD'));

--查询

select * from sales PARTITION(part1);

上述示例中，使用range关键字指定quantity列进行分区，分区键为10个数字的间隔。建表时只用初始分区part1，不需要为每个分区指定明确的值。插入数据时，会根据INTERVAL规则创建对应的分区。

2.3.2 时间间隔分区

INTERVAL后可以跟两个函数NUMTOYMINTERVAL(n,c)或NUMTODSINTERVAL(n,c)，如下图所示。

2.3.2.1 NUMTOYMINTERVAL(n,c)

作用：该函数作用是将n转化为INTERVAL YEAR TO MONTH类型的数据。参数c即语法中的interval_unit，可以是’YEAR’,’MONTH’，n为数值大小。

函数使用：

SELECT NUMTOYMINTERVAL(12, 'YEAR') as interval_year,

        NUMTOYMINTERVAL(6, 'MONTH') as interval_month

FROM dual;

输出结果:

使用range关键字指定sales_date列进行分区，分区键为1个月的间隔。建表时只用初始分区part1，不需要为每个分区指定明确的值。插入数据时，会根据INTERVAL规则创建对应的分区。

CREATE TABLE sales2

(

    sales_id NUMBER,

    quantity NUMBER,

    sales_date DATE

)

PARTITION BY RANGE (sales_date) INTERVAL (NUMTOYMINTERVAL(1,'month'))

(

    PARTITION part1 VALUES LESS THAN (TO_DATE('2023-02-05:12:14:25','yyyy-mm-dd hh24:mi:ss'))

) ;

表的结构查看：

2.3.2.1 NUMTODSINTERVAL (n,c)

作用：该函数作用是将n转化为INTERVAL DAY TO SECOND类型的数据。参数c即语法中的interval_unit，可以是’ DAY,’HOUR’,’MINUTE’,’ SECOND，n为数值大小。

例子

函数使用：

SELECT NUMTODSINTERVAL (1, 'DAY') as interval_day,

        NUMTODSINTERVAL (6, 'HOUR') as interval_hour,

        NUMTODSINTERVAL (30, 'MINUTE') as interval_minute,

        NUMTODSINTERVAL (60, 'SECOND') as interval_second 

FROM dual;

输出结果

使用range关键字指定sales_date列进行分区，分区键为2个小时的间隔。建表时只用初始分区part1。

CREATE TABLE sales3

(

    sales_id NUMBER,

    quantity NUMBER,

    sales_date DATE

)

PARTITION BY RANGE (sales_date) INTERVAL (NUMTODSINTERVAL(2,'hour'))

(

    PARTITION part1 VALUES LESS THAN (TO_DATE('2023-02-05:00:10:25','yyyy-mm-dd hh24:mi:ss'))

) ;

表的结构查看：

2.4 间隔分区使用限制

• INTERVAL分区仅支持唯一的分区键列，分区键列的类型必须是NUMBER、DATE、FLOAT或者TIMESTAMP。
• INTERVAL子句不支持索引组织表。
• 必须定义至少一个RANGE分区。
• 不能在分区键列中指定NULL、infinity值。
• 在自动创建分区时，系统会默认有一个分区名，系统默认创建的分区名与我们创建的分区命名规范存在一定的差距。

参考资料