人大金仓数据库KingbaseES 分区表常用操作介绍

站长

2024年03月19日 21:29 · 阅读数 5

关键字：

KingbaseES、分区表、常用操作、人大金仓

1. 分区表基本概念

当表中的数据量不断增大，查询数据的速度就会变慢，应用程序的性能就会下降，这时就应该考虑对表进行分区。表进行分区后，逻辑上表仍然是一张完整的表，只是将表中的数据在物理上存放到多个表空间（物理文件上），这样查询数据时，不至于每次都扫描整张表而只是从当前的分区查到所要的数据大大提高了数据查询的速度。

2. 什么时候使用分区表

1）表数据量是否足够大：通常对于大的事实表，比如数据量有几千万或者过意，我们可以考虑使用分区表，但数据量大小并没有一个绝对的标准可以定义，一般根据经验，以及对目前性能是否满意。

2）表是否有合适的分区字段：如果数据量足够大了，这个时候我们就需要看下是否有合适的字段能够用来分区，通常如果数据有时间维度，比如按天，按月等，是比较理想的分区字段。

3）表内数据是否具有声明周期：通常数据库中的数据不会一直存放，一般都会有一定的生命周期，比如最近一年等，这里就涉及到对旧数据的管理，如果有分区表，就很容易删除旧数据，或者将旧的数据归档到对象存储等更为廉价的存储介质上。

4）表数据备份与恢复：在备份还原场景时，可以备份恢复独立的分区，在非常大的数据集场景下效果很好。

5）查询语句是否含有分区字段：如果对一个表做了分区，但是所有的查询都不带分区字段，这不仅无法提高性能反而会使性能下降，因为所有的查询都会扫描所有的分区表。

3. 分区表常用操作

3.1 Oracle模式

3.1.1.分区表创建语法

3.1.1.1 单层分区表

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT

EXISTS ] table_name ( [

{ column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ]

| table_constraint

| LIKE source_table [ like_option … ] }

[, … ]

] )

[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

( PARTITION partition [ partition_bound_spec ] [ TABLESPACE tablespace_name ]

[, …]

) | PARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …]) ]

]

[ USING method ]

[ WITH ( storage_parameter [= value] [, … ] ) | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

partition_bound_spec::=

VALUES LESS THEN ( { partition_bound_expr | MAXVALUE } [, …] )

VALUES ( partition_bound_expr [, …] | DEFAULT )

3.1.1.1.1 范围分区

范围分区将数据基于范围映射到每一个分区，这个范围是在创建分区的时指定的分区键决定的。这种分区方式是最为常用的，并且分区键常采用日期。常用场景例如：将销售数据进行按月区分。示例如下：

create table range_tab1(

    col1 date,

    col2 varchar(20)

)partition by range(col1)(

    partition p1 values less than (to_date('2023-03-01','yyyy-mm-dd')),

    partition p2 values less than (to_date('2023-06-01','yyyy-mm-dd')),

    partition p3 values less than (to_date('2023-09-01','yyyy-mm-dd')),

    partition p4 values less than (to_date('2023-12-01','yyyy-mm-dd')),

    partition max values less than(maxvalue)

);

\d+ range_tab1

当使用范围分区时，注意以下规则：

分区将自动继承分区表的全部列定义、主外键索引和约束、唯一索引和约束、NOT NULL约束和CHECK约束。
分区自动继承分区表的全局或者本地临时表属性。
分区名称不可省略，服务器自动在分区名称前加上“table_name_”前缀作为分区的表名。
partition_bound_spec不能与分区表的其他分区重叠。VALUE LESS THAN确定了当前分区的上边界（不包含边界值），使用分区键值与子句指定的有序值列表进行比较，满足条件的行将被存储在分区中。
partition_bound_expr是任何无变量表达式（不允许子查询、窗口函数、聚合函数和集返回函数）。它的数据类型必须与相应分区键列的数据类型相匹配。表达式在表创建时只计算一次，因此它甚至可以包含易失性表达式，如CURRENT_TIMESTAMP。
使用MAXVALUE可以定义分区键值的最大值，该最大值总是大于其他任何值（包括null）。
指定了maxvalue分区后，不能maxvalue分区之上再添加小于maxvalue值的分区；
使用TABLESPACE可以为分区单独指定表空间，如果省略会则使用分区表的表空间。

3.1.1.1.2 列表分区

列表分区的特点是分区键列的值是固定的几个，遵照这几个固定的值将数据进行分区归类，基于这样的特点我们可以采用列表分区。示例如下：

create table list_tab1(

    col1 int,

    col2 varchar(20)

)partition by list(col2)(

    partition p1 values ('db','hb','xb'),

    partition p2 values ('hn','xn'),

    partition p3 values ('dn'),

    partition p4 values (default)

);

\d+ list_tab1

当使用列表分区时，注意以下规则：

PARTITION BY LIST后带括号的列或表达式的列表构成表的分区键。列表分区的分区键必须由单个列或表达式组成。
为分区指定分区值时，可以是多个。
分区将自动继承分区表的全部列定义、主外键索引和约束、唯一索引和约束、NOT NULL约束和CHECK约束。
分区自动继承分区表的全局或者本地临时表属性。
分区名称不可省略，服务器自动在分区名称前加上“table_name_”前缀作为分区的表名。
partition_bound_spec不能与分区表的其他分区重叠。可以在VALUES后指定NULL来表示分区允许分区键列为空。但是在列表分区表中仅允许存在一个这样的分区。如果在VALUES后指定了DEFAULT，则表将创建为默认分区。
partition_bound_expr是任何无变量表达式（不允许子查询、窗口函数、聚合函数和集返回函数）。它的数据类型必须与相应分区键列的数据类型相匹配。表达式在表创建时只计算一次，因此它甚至可以包含易失性表达式，如CURRENT_TIMESTAMP。
使用TABLESPACE可以为分区单独指定表空间，如果省略会则使用分区表的表空间。

3.1.1.1.3 哈希分区

哈希分区又称散列分区，这类分区是在分区键值上使用了哈希算法，以确定将数据放入到哪个分区中。当列的值没有合适的条件时，建议使用哈希分区。

\echo `mkdir /tmp/ts1`

\echo `mkdir /tmp/ts2`

\echo `mkdir /tmp/ts3`

\echo `mkdir /tmp/ts4`

create tablespace ts1 location '/tmp/ts1';

create tablespace ts2 location '/tmp/ts2';

create tablespace ts3 location '/tmp/ts1';

create tablespace ts4 location '/tmp/ts2';

create table hash_tab1(

    col1 int,

    col2 varchar(20)

)partition by hash(col1,col2)(

    partition p1 tablespace ts1,

    partition p2 tablespace ts2,

    partition p3 tablespace ts3,

    partition p4

);

\d+ hash_tab1

哈希分区partitions创建方式

CREATE TABLE dept

( deptno NUMBER

, deptname VARCHAR(32)

)

PARTITION BY HASH(deptno)

PARTITIONS 4 STORE IN (ts1,ts2,ts3,ts4);

\d+ dept

当使用哈希分区时，注意以下规则：

PARTITION BY HASH后带括号的列或表达式的列表构成表的分区键。哈希分区的分区键可以包含最多32个列或表达式。
分区将自动继承分区表的全部列定义、主外键索引和约束、唯一索引和约束、NOT NULL约束和CHECK约束。
分区自动继承分区表的全局或者本地临时表属性。
分区名称不可省略，服务器自动在分区名称前加上“table_name_”前缀作为分区的表名。
使用PARTITION可以按名称指定各个分区，并为每个分区单独指定表空间。使用TABLESPACE可以为分区单独指定表空间，如果省略会则使用分区表的表空间。
使用PARTITIONS可以通过指定哈希分区数量定义多个分区，numeric_literal为正整数。此时，数据库分配table_name_pn的分区名称。STORE IN子句允许指定一个或多个表空间。表空间的数量不必等于分区的数量，表空间按顺序指定给各个哈希分区。如果分区数大于表空间数，余下的分区使用分区表的表空间。
建议分区的数量用2的n次方，可以使得各个分区间的数据分布更加均匀。
分区键列为多个时，判断时只判断第一列分区键计算的hash值，对数据进行分区。

3.1.1.1.4 间隔（interval）分区

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT

EXISTS ] table_name ( [

{ column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ]

| table_constraint

| LIKE source_table [ like_option … ] }

[, … ]

] )

[ PARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ INTERVAL ( interval_expr ) [ STORE IN ( tablespace_name [, …]) ]]

( PARTITION partition partition_bound_spec [ TABLESPACE tablespace_name ]

[, …]

) ]

[ USING method ]

[ WITH ( storage_parameter [= value] [, … ] ) | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

partition_bound_spec::=

VALUES LESS THEN ( { partition_bound_expr | MAXVALUE } [, …] )

当插入或者更新的值超过最高分区时，以最高分区上限为下边界，以最高分区上限+间隔值的倍数为下边界创建一个新的分区。创建INTERVAL分区时要求至少存在一个分区。

CREATE TABLE customers(

id int,

name VARCHAR2(20),

regdate date,

email VARCHAR2(40),

count int

)PARTITION BY RANGE (count) INTERVAL (100)(

PARTITION base VALUES LESS THAN (200)

);

\d+ customers

当使用interval分区时，注意以下规则：

指定INTERVAL子句可以创建间隔分区（INTERVAL分区），这是一种特殊的范围分区。当执行INSERT或者UPDATE时，由数据库根据定义的间隔参数自动创建新的分区。当插入或者更新记录的分区键值大于等于最高分区上限时，以最高分区上限为下边界，以最高分区上限+间隔值倍数为下边界创建一个新的分区。
创建INTERVAL分区时，仅支持指定唯一的分区键列，类型必须是数值型或日期型。interval_expr是任何无变量表达式（不允许子查询、窗口函数、聚合函数和集返回函数），它的数据类型必须与唯一分区键列的数据类型相匹配，否则会报错。
创建INTERVAL分区时，必须使用PARTITION子句指定至少一个范围分区。当创建表少于一个分区时，不会立即报错，而是将延后至插入数据时再报错。这一点与Oracle行为不一致，Oracle是立即报错。
创建INTERVAL分区时，不能在VALUES子句中指定MAXVALUE，也不能为分区键列指定NULL值。
使用STORE IN可以为INTERVAL分区指定表空间，新的INTERVAL分区以轮循方式使用指定的各个表空间，如果省略STORE IN时，则使用分区表的表空间。

关于录入数据自动创建的分区下界值，按照以下公式计算：

\echo `mkdir /tmp/tbs1`

\echo `mkdir /tmp/tbs2`

create tablespace tbs1 location '/tmp/tbs1';

create tablespace tbs2 location '/tmp/tbs2';

CREATE TABLE customers2(

    id              float,

    name            VARCHAR2(20), 

    regdate         date,

    email           VARCHAR2(40),

    count           float

)PARTITION BY RANGE (count) INTERVAL (1.1)store in(tbs1,tbs2)(

    PARTITION base VALUES LESS THAN (2)

); 

insert into customers2(count) values(4.7);

\d+ customers2

公式：{[(录入数值-最高分区下边界)/间隔值]+1}*间隔值+最高分区下边界

日期型作为分区键、间隔值使用示例：

CREATE TABLE customers3(

    id              float,

    name            VARCHAR2(20), 

    regdate         date,

    email           VARCHAR2(40),

    count           date

)PARTITION BY RANGE (count) interval('1 year,2 month,3 day'::interval)(

    PARTITION p1 VALUES LESS THAN ('2000-01-01')

); 

\d+ customers3

上述的日期型实例中，还可以单独设置间隔值为年、月、日等，interval关键可以省略。

3.1.1.2 复合分区

CREATE [ [ GLOBAL | LOCAL ] { TEMPORARY | TEMP } | UNLOGGED ] TABLE [ IF NOT

EXISTS ] table_name ( [

{ column_name data_type [ COLLATE collation ] [ column_constraint [ … ] ]

| table_constraint

| LIKE source_table [ like_option … ] }

[, … ]

] )

[ PARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ INTERVAL ( interval_expr ) [ STORE IN ( tablespace_name [, …]) ]]

SUBPARTITION BY { RANGE | LIST | HASH } ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ { hash_subparts_by_quantity | subpartition_template } ]

{ ( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) | SUBPARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …]) ]]

[, …]

) | PARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …] ) ] }

]

[ USING method ]

[ WITH ( storage_parameter [= value] [, … ] ) | WITHOUT OIDS ]

[ ON COMMIT { PRESERVE ROWS | DELETE ROWS | DROP } ]

[ TABLESPACE tablespace_name ]

hash_subparts_by_quantity::=

SUBPARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …] ) ]

subpartition_template::=

SUBPARTITION TEMPLATE

( SUBPARTITION [subpartition] [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

)

partition_bound_spec::=

VALUES LESS THEN ( { partition_bound_expr | MAXVALUE } [, …] ) |

VALUES ( partition_bound_expr [, …] | DEFAULT )

subpartition_bound_spec::=

VALUES LESS THEN ( { partition_bound_expr | MAXVALUE } [, …] ) |

VALUES ( partition_bound_expr [, …] | DEFAULT )

规则

partition_bound_spec不能与分区表的其他分区重叠。subpartition_bound_spec不能与其他子分区重叠。
partition_bound_expr是任何无变量表达式（不允许子查询、窗口函数、聚合函数和集返回函数）。它的数据类型必须与相应分区键列的数据类型相匹配。表达式在表创建时只计算一次，因此它甚至可以包含易失性表达式，如CURRENT_TIMESTAMP。
在partition_bound_spec 和sub partition_bound_spec中，VALUE LESS THAN用于范围分区或子分区，VALUES用于列表分区或子分区。
VALUE LESS THAN它确定了当前分区的上边界（不包含边界值），使用分区键值与子句指定的有序值列表进行比较，满足条件的行将被存储在分区中。
使用MAXVALUE可以定义分区键值的最大值，该最大值总是大于其他任何值（包括null）。
可以在VALUES后指定NULL来表示分区允许分区键列为空。但是在列表分区表中仅允许存在一个这样的分区。
如果在VALUES后指定了DEFAULT，则表将创建为默认分区。
PARTITIONS和SUBPARTITIONS可以通过指定数量方式定义多个哈希分区和子分区，numeric_literal为正整数。
STORE IN子句允许为哈希分区或子分区指定一个或多个表空间。表空间的数量不必等于分区的数量，表空间按顺序指定给各个分区。如果分区数大于表空间数，余下的分区使用分区表的表空间。
使用TABLESPACE可以为分区单独指定表空间，如果省略会则使用分区表的表空间。
分区名称不可省略，服务器自动在分区名称前加上“table_name_”前缀作为分区的表名。
子分区名称不可省略，服务器自动在子分区名称前加上“table_name_partition_”前缀作为子分区的表名。
分区和子分区自动继承分区表的全部列定义、主外键约束、唯一约束、NOT NULL约束和CHECK约束。
分区和子分区自动继承分区表的全局或者本地临时表属性。
子分区模板可以简化子分区定义，子分区模板的规则与单独定义子分区子句相同。
当同时省略子分区模板和子分区定义时，数据库会创建一个默认的子分区。其中默认哈希子分区为modulus=1并且remainder=0，默认列表子分区为DEFAULT分区，默认范围子分区为MAXVALUE分区。
同一分区表的不同分区下的子分区名称应保证唯一，当子分区名称重复时会报错。
指定INTERVAL子句可以创建间隔复合分区，这是一种特殊的范围复合分区。当执行INSERT或者UPDATE时，由数据库根据定义的间隔参数自动创建新的分区和子分区。间隔复合分区表中INTERVAL子句的规则与单层范围分区表相同。

3.1.1.2.1 创建范围-范围分区表（间隔-范围分区表）

通过CREATE TABLE语句一次创建范围-范围分区表、所有分区和子分区。

[ PARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ INTERVAL ( interval_expr ) [ STORE IN ( tablespace_name [, …]) ]]

SUBPARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ subpartition_template ]

( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

)

示例：

CREATE TABLE range_range_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY RANGE (col1) interval(5)

SUBPARTITION BY RANGE (col2)

(

    PARTITION p1 VALUES LESS THAN (10)(

        subpartition sub1 values less than('2000-01-01'),

        subpartition sub2 values less than('2000-04-01'),

        subpartition sub3 values less than('2000-07-01'),

        subpartition sub4 values less than('2000-10-01')

    ),

    PARTITION p2 VALUES LESS THAN (20)(

        subpartition subp1 values less than('2000-01-01'),

        subpartition subp2 values less than('2000-04-01'),

        subpartition subp3 values less than('2000-07-01'),

        subpartition subp4 values less than('2000-10-01')

    )

);

3.1.1.2.2 创建范围-列表分区表（间隔-列表分区表）

通过CREATE TABLE语句一次创建范围-列表分区表、所有分区和子分区。

[ PARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ INTERVAL ( interval_expr ) [ STORE IN ( tablespace_name [, …]) ]]

SUBPARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ subpartition_template ]

( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

)

示例：

CREATE TABLE range_list_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY RANGE (col1) interval(5)

SUBPARTITION BY list (col2)

(

    PARTITION p1 VALUES LESS THAN (10)(

        subpartition sub1 values ('2000-01-01'),

        subpartition sub2 values ('2000-04-01'),

        subpartition sub3 values ('2000-07-01'),

        subpartition sub4 values ('2000-10-01')

    ),

    PARTITION p2 VALUES LESS THAN (20)(

        subpartition subp1 values ('2000-01-01'),

        subpartition subp2 values ('2000-04-01'),

        subpartition subp3 values ('2000-07-01'),

        subpartition subp4 values ('2000-10-01')

    )

);

3.1.1.2.3 创建范围-哈希分区表（间隔-哈希分区表）

通过CREATE TABLE语句一次创建范围-哈希分区表、所有分区和子分区。

[ PARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ INTERVAL ( interval_expr ) [ STORE IN ( tablespace_name [, …]) ]]

SUBPARTITION BY HASH ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ { hash_subparts_by_quantity | subpartition_template } ]

( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [ TABLESPACE tablespace_name ]

[, …]

) | SUBPARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …]) ] ]

[, …]

)

示例1：

CREATE TABLE range_hash_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY RANGE (col1) interval(5)

SUBPARTITION BY hash (col2)

(

    PARTITION p1 VALUES LESS THAN (10)(

        subpartition sub1 ,

        subpartition sub2 ,

        subpartition sub3 ,

        subpartition sub4 

    ),

    PARTITION p2 VALUES LESS THAN (20)(

        subpartition subp1 ,

        subpartition subp2 ,

        subpartition subp3 ,

        subpartition subp4 

    )

);

示例2

CREATE TABLE range_hash_tab2(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY RANGE (col1) interval(5)

SUBPARTITION BY hash (col2)

subpartitions 4

(

    PARTITION p1 VALUES LESS THAN (10),

    PARTITION p2 VALUES LESS THAN (20)

);

3.1.1.2.4 创建列表-范围分区表

通过CREATE TABLE语句一次创建列表-范围分区表、所有分区和子分区。

[ PARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

SUBPARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ subpartition_template ]

( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

)

示例：

CREATE TABLE list_range_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY list (col1)

SUBPARTITION BY range (col2)

(

    PARTITION p1 VALUES (10)(

        subpartition sub1 values less than('2000-01-01'),

        subpartition sub2 values less than('2000-04-01'),

        subpartition sub3 values less than('2000-07-01'),

        subpartition sub4 values less than('2000-10-01')

    ),

    PARTITION p2 VALUES (20)(

        subpartition subp1 values less than('2000-01-01'),

        subpartition subp2 values less than('2000-04-01'),

        subpartition subp3 values less than('2000-07-01'),

        subpartition subp4 values less than('2000-10-01')

    )

);

3.1.1.2.5 创建列表-列表分区表

通过CREATE TABLE语句一次创建列表-列表分区表、所有分区和子分区。

[ PARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

SUBPARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ subpartition_template ]

( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

)

示例：

CREATE TABLE list_list_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY list (col1)

SUBPARTITION BY list (col2)

(

    PARTITION p1 VALUES (10)(

        subpartition sub1 values ('2000-01-01'),

        subpartition sub2 values ('2000-04-01'),

        subpartition sub3 values ('2000-07-01'),

        subpartition sub4 values ('2000-10-01')

    ),

    PARTITION p2 VALUES (20)(

        subpartition subp1 values ('2000-01-01'),

        subpartition subp2 values ('2000-04-01'),

        subpartition subp3 values ('2000-07-01'),

        subpartition subp4 values ('2000-10-01')

    )

);

3.1.1.2.6 创建列表-哈希分区表

通过CREATE TABLE语句一次创建列表-哈希分区表、所有分区和子分区。

[ PARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

SUBPARTITION BY HASH ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ { hash_subparts_by_quantity | subpartition_template } ]

( PARTITION partition [partition_bound_spec] [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [ TABLESPACE tablespace_name ]

[, …]

) | SUBPARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …]) ] ]

[, …]

)

示例1：

CREATE TABLE list_hash_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY list (col1)

SUBPARTITION BY list (col2)

(

    PARTITION p1 VALUES (10)(

        subpartition sub1 ,

        subpartition sub2 ,

        subpartition sub3 ,

        subpartition sub4 

    ),

    PARTITION p2 VALUES (20)(

        subpartition subp1 ,

        subpartition subp2 ,

        subpartition subp3 ,

        subpartition subp4 

    )

);

示例2：

CREATE TABLE list_hash_tab2(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY list (col1)

SUBPARTITION BY hash (col2)

subpartitions 4

(

    PARTITION p1 VALUES (10),

    PARTITION p2 VALUES (20)

);

3.1.1.2.7 创建哈希-范围分区表

通过CREATE TABLE语句一次创建哈希-范围分区表、所有分区和子分区。

[ PARTITION BY HASH ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

SUBPARTITION BY RANGE ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ subpartition_template ]

{ ( PARTITION partition [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

) | PARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …] ) ] }

示例1：

CREATE TABLE hash_range_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY range (col2)

(

    PARTITION p1 (

        subpartition sub1 values less than('2000-01-01'),

        subpartition sub2 values less than('2000-04-01'),

        subpartition sub3 values less than('2000-07-01'),

        subpartition sub4 values less than('2000-10-01')

    ),

    PARTITION p2 (

        subpartition subp1 values less than('2000-01-01'),

        subpartition subp2 values less than('2000-04-01'),

        subpartition subp3 values less than('2000-07-01'),

        subpartition subp4 values less than('2000-10-01')

    )

);

示例2：

CREATE TABLE hash_range_tab2(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY range (col2)(

    subpartition sub1 values less than('2000-01-01'),

    subpartition sub2 values less than('2000-04-01'),

    subpartition sub3 values less than('2000-07-01'),

    subpartition sub4 values less than('2000-10-01')

    )

    partitions 4;-- error

示例3：

CREATE TABLE hash_range_tab3(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY range (col2)

SUBPARTITION TEMPLATE(

    subpartition sub1 values less than('2000-01-01'),

    subpartition sub2 values less than('2000-04-01'),

    subpartition sub3 values less than('2000-07-01'),

    subpartition sub4 values less than('2000-10-01')

    )

    partitions 4;

3.1.1.2.8 创建哈希-列表分区表

通过CREATE TABLE语句一次创建哈希-列表分区表、所有分区和子分区。

[ PARTITION BY HASH ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

SUBPARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ subpartition_template ]

{ ( PARTITION partition [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [subpartition_bound_spec]

[ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

) | PARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …] ) ] }

示例1：

CREATE TABLE hash_list_tab1(

    col1 int,

    col2 date,

    col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY list (col2)

(

    PARTITION p1 (

        subpartition sub1 values ('2000-01-01'),

        subpartition sub2 values ('2000-04-01'),

        subpartition sub3 values ('2000-07-01'),

        subpartition sub4 values ('2000-10-01')

    ),

    PARTITION p2 (

        subpartition subp1 values ('2000-01-01'),

        subpartition subp2 values ('2000-04-01'),

        subpartition subp3 values ('2000-07-01'),

        subpartition subp4 values ('2000-10-01')

    )

);

示例2：

CREATE TABLE hash_list_tab2(

col1 int,

col2 date,

col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY list (col2)

SUBPARTITION TEMPLATE(

subpartition sub1 values ('2000-01-01'),

subpartition sub2 values ('2000-04-01'),

subpartition sub3 values ('2000-07-01'),

subpartition sub4 values ('2000-10-01')

)

partitions 4;

3.1.1.2.9 创建哈希-哈希分区表

通过CREATE TABLE语句一次创建哈希-哈希分区表、所有分区和子分区。

[ PARTITION BY LIST ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

SUBPARTITION BY HASH ( { column_name | ( expression ) }

[ COLLATE collation ] [ opclass ] [, … ] )

[ { hash_subparts_by_quantity | subpartition_template } ]

{ ( PARTITION partition [ TABLESPACE tablespace_name ]

[ ( SUBPARTITION subpartition [ TABLESPACE tablespace_name ]

[, …]

) ]

[, …]

) | PARTITIONS numeric_literal [ STORE IN ( tablespace_name [, …] ) ] }

示例1：

CREATE TABLE hash_hash_tab1(

col1 int,

col2 date,

col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY hash (col2)

(

PARTITION p1 (

subpartition sub1 ,

subpartition sub2 ,

subpartition sub3 ,

subpartition sub4

),

PARTITION p2 (

subpartition subp1 ,

subpartition subp2 ,

subpartition subp3 ,

subpartition subp4

)

);

示例2：

CREATE TABLE hash_hash_tab2(

col1 int,

col2 date,

col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY hash (col2)

subpartitions 4

(

PARTITION p1 (

subpartition sub1 ,

subpartition sub2 ,

subpartition sub3 ,

subpartition sub4

),

PARTITION p2 (

subpartition subp1 ,

subpartition subp2 ,

subpartition subp3 ,

subpartition subp4

)

);

示例3：

CREATE TABLE hash_hash_tab3(

col1 int,

col2 date,

col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY hash (col2)

subpartitions 4

partitions 2;

示例4：

CREATE TABLE hash_hash_tab4(

col1 int,

col2 date,

col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY hash (col2)

subpartitions 4

(

PARTITION p1 ,

PARTITION p2

);

示例5：

CREATE TABLE hash_hash_tab5(

col1 int,

col2 date,

col3 varchar(20)

)PARTITION BY hash (col1)

SUBPARTITION BY hash (col2)

SUBPARTITION TEMPLATE(

subpartition subp1,

subpartition subp2,

subpartition subp3,

subpartition subp4

)

partitions 4;

3.1.2 分区表操作

查询

支持通过partition\subpartition关键字指定分区名或者子分区名实现对对分区和子分区直接进行select操作

select partition/subpartition ([sub]partition_name)

select partition/subpartition for ([sub]partition_key_value)

规则：

查询分区适用于查询的所有单类型的分区包括范围分区、列表分区、散列分区。
可以查询多个表的多个分区，也可以查询一个表的多个分区。
支持分区别名。
对于partition for（partition_key_value）方式指定分区，只要给定的partition _key_value在分区的范围内即可找到正确的分区。
partition_key_value是一个列表。对应创建分区时指定的分区键，如果是多列分区，则分区键值需要指定多个，操作subpartition还需要指定子分区的分区键，分区键值个数必须和多列分区键值和自分区键值个数相匹配。

以range_range_1,range_list_1分区表为示例：

--partition/subpartition_name

select * from range_range_tab1 partition(p1);--查询分区

select * from range_range_tab1 subpartition(sub1); --查询子分区

select * from range_range_tab1 partition(p1),range_range_tab1 partition(p2);--查询多个分区

select * from range_range_tab1 partition(p1) t1,range_range_tab1 partition(p2) t2;--支持分区别名

select * from range_range_tab1 partition(p1) t1,range_range_tab1 subpartition(sub1) t2;--查询分区、子分区

select * from range_range_tab1 partition(p1) t1,range_list_tab1 subpartition(sub1) t2;--查询不同分区表中分区、子分区

--partition/subpartition_key_value

\d+ range_range_tab1

select * from range_range_tab1 partition for(0);--查询p1分区

\d+ range_range_tab1_p1

select * from range_range_tab1 subpartition for(1, '1999-01-01');--查询p1分区下sub1子分区

select * from range_range_tab1 partition for(2) t1,range_range_tab1 partition for(11) t2;--查询p1，p2分区

select * from range_range_tab1 partition for(3) t1,range_range_tab1 subpartition for(4, '1999-01-01');--查询p1分区、sub1子分区

select * from range_range_tab1 partition for(3) t1,range_list_tab1 subpartition for(3, '2000-01-01 00:00:00') t2;--查询不同分区表中分区、子分区

参考资料

《KingbaseES SQL语言参考手册》