索引分区

语法格式

语法格式如下：

各项参数参考CREATE INDEX、PARTITION BY和LOCATE IN章节。

语法示例

示例1：索引分区的创建与查询。

为表t6创建带分区的索引。

> CREATE TABLE t6 ( c1 INT, c2 STRING, PRIMARY KEY ( c1 ) );

CREATE TABLE

> CREATE INDEX id ON t6(c2)

​ PARTITION BY LIST(c2) (

​ PARTITION p1 VALUES IN(‘tianjin’) LOCATE IN tianjin,

​ PARTITION p2 VALUES IN(‘beijing’) LOCATE IN beijing,

​ PARTITION p3 VALUES IN(‘guangzhou’) LOCATE IN guangzhou

);

CREATE INDEX

-- 等价于

>CREATE TABLE t6(c1 INT,c2 STRING,PRIMARY KEY(c1),INDEX id(c2) PARTITION BY LIST(c2) (

​ PARTITION p1 VALUES IN(‘tianjin’) LOCATE IN tianjin,

​ PARTITION p2 VALUES IN(‘beijing’) LOCATE IN beijing,

​ PARTITION p3 VALUES IN(‘guangzhou’) LOCATE IN Guangzhou

));

CREATE TABLE

插入落入分区p1的数据，并查询p1下的数据。

> INSERT INTO t6 VALUES (1, ‘tianjin’);

INSERT 1

> SELECT * FROM [PARTITION p1] OF t6;

c1 | c2

+——+————-+

1 | tianjin

(1 row)

插入不属于现有分区p1、p2的数据，并查询默认分区下的数据。

> INSERT INTO t6 VALUES (2, ‘jinan’);

INSERT 1

> SELECT * FROM [PARTITION id default] OF t6;

c1 | c2

+——+———-+

2 | jinan |

示例2：创建多个索引分区。

> CREATE TABLE t7 ( c1 INT, c2 INT, c3 STRING, PRIMARY KEY ( c1 ) );

CREATE TABLE

CREATE INDEX id1 ON t7(c2) PARTITION BY RANGE(c2) (

​ PARTITION p1 VALUES FROM (0) TO (100),

​ PARTITION p2 VALUES FROM (100) TO (200)

);

CREATE INDEX

> CREATE INDEX id2 ON t7(c3)

​ PARTITION BY LIST(c3) (

​ PARTITION n1 VALUES IN (‘男’),

​ PARTITION n2 VALUES IN (‘女’));

CREATE INDEX

-- 等价于

>CREATE TABLE t7 ( c1 INT, c2 INT, c3 STRING, PRIMARY KEY ( c1 ), INDEX id1 ( c2 ) PARTITION BY RANGE ( c2 ) ( PARTITION p1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p2 VALUES FROM ( 100 ) TO ( 200 ) ), INDEX id2 ( c3 ) PARTITION BY LIST ( c3 ) ( PARTITION n1 VALUES IN ( ‘男’ ), PARTITION n2 VALUES IN ( ‘女’ ) ) );

CREATE TABLE

插入同时落入分区p1和n1的数据，并查询两个分区下的数据。

> INSERT INTO t7 VALUES (1, 22, ‘男’);

INSERT 1

> SELECT * FROM [PARTITION p1] OF t7;

c1 | c2 | c3

+——+——+——+

1 | 22 | 男

> SELECT * FROM [PARTITION n1] OF t7;

c1 | c2 | c3

+——+——+——+

1 | 22 | 男

插入不属于现有分区的数据，并查询两个索引id1、id2默认分区下的数据。

> INSERT INTO t7 VALUES (2, 200, null);

INSERT 1

> SELECT * FROM [PARTITION id1 default] OF t7;

c1 | c2 | c3

+——+——-+———+

2 | 200 | NULL

> SELECT * FROM [PARTITION id2 default] OF t7;

c1 | c2 | c3

+——+——-+———+

2 | 200 | NULL |

示例3：创建嵌套分区。

> CREATE TABLE t8( c1 INT,c2 INT,c3 STRING,PRIMARY KEY(c1));

CREATE TABLE

> CREATE INDEX id ON t8 ( c3, c2 ) PARTITION BY LIST ( c3 ) ( PARTITION p1 VALUES IN ( ‘男’ ) PARTITION BY RANGE ( c2 ) ( PARTITION p1_1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p1_2 VALUES FROM ( 100 ) TO ( 200 ) ), PARTITION p2 VALUES IN ( ‘女’ ) PARTITION BY RANGE ( c2 ) ( PARTITION p2_1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p2_2 VALUES FROM ( 100 ) TO ( 200 ) ) );

CREATE INDEX

-- 等价于

> CREATE TABLE t8 ( c1 INT, c2 INT, c3 STRING, PRIMARY KEY ( c1 ), INDEX id ( c3, c2 ) PARTITION BY LIST ( c3 ) ( PARTITION p1 VALUES IN ( ‘男’ ) PARTITION BY RANGE ( c2 ) ( PARTITION p1_1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p1_2 VALUES FROM ( 100 ) TO ( 200 ) ), PARTITION p2 VALUES IN ( ‘女’ ) PARTITION BY RANGE ( c2 ) ( PARTITION p2_1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p2_2 VALUES FROM ( 100 ) TO ( 200 ) ) ) );

CREATE TABLE

需要注意的是，由于嵌套分区具有顺序性，想要在c3列分区下对c2列进行进一步划分，需要在指定分区索引时将c3置于c2之前，如id (c3, c2),插入落于p1子分区p1_1的数据，会在p1和p1_1中同时查询到这条数据。

> INSERT INTO t8 VALUES (1, 22, ‘男’);

INSERT 1

> SELECT * FROM [PARTITION p1] OF t8;

c1 | c2 | c3

+——+——+——+

1 | 22 | 男

(1 row)

> SELECT * FROM [PARTITION p1_1] OF t8;

c1 | c2 | c3

+——+——+——+

1 | 22 | 男

插入不属于现有分区的数据，并在索引id默认分区查询。

> INSERT INTO t8 VALUES (2, 22, null);

INSERT 1

> SELECT * FROM [PARTITION id default] OF t8;

c1 | c2 | c3

+——+——+———+

2 | 22 | NULL (

插入不属于p1分区下现有分区的数据，并在p1分区的默认分区查询。

> INSERT INTO t8 VALUES (3, 200, ‘男’);

INSERT 1

> SELECT * FROM [PARTITION p1 default] OF t8;

c1 | c2 | c3

+——+——-+——+

3 | 200 | 男 (1 row)

> SELECT * FROM [PARTITION p1] OF t8;

c1 | c2 | c3

+——+——-+——+

1 | 22 | 男

3 | 200 | 男

(2 rows) |

可以看到，位于p1下默认分区的数据，也同样位于p1分区内，这也实现了对分区数据的完全划分。

示例4：表既对主键分区也对索引分区。

> CREATE TABLE t ( c1 INT, c2 INT, c3 STRING, c4 INT, PRIMARY KEY ( c1, c2 ), INDEX id ( c3, c4 ) PARTITION BY LIST ( c3 ) ( PARTITION p1 VALUES IN ( ‘男’ ) PARTITION BY RANGE ( c4 ) ( PARTITION p1_1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p1_2 VALUES FROM ( 100 ) TO ( 200 ) ), PARTITION p2 VALUES IN ( ‘女’ ) PARTITION BY RANGE ( c4 ) ( PARTITION p2_1 VALUES FROM ( 0 ) TO ( 100 ), PARTITION p2_2 VALUES FROM ( 100 ) TO ( 200 ) ) ) ) PARTITION BY LIST ( c1 ) ( PARTITION n1 VALUES IN ( 1, 2, 3 ) PARTITION BY RANGE ( c2 ) ( PARTITION n11 VALUES FROM ( minvalue ) TO ( 666 ), PARTITION n12 VALUES FROM ( 666 ) TO ( MAXVALUE ) ), PARTITION n2 VALUES IN ( DEFAULT ) );

CREATE TABLE

分区的修改

语法格式如下：

该语法项可用于将未分区表进行分区，也可以用于修改已分区表的分区信息。各项参数说明参考ALTER INDEX和LOCATE IN章节。

分区修改示例

创建带索引列表，并对索引列分区。

>CREATE TABLE t9 (c1 INT, c2 INT, c3 STRING, PRIMARY KEY(c1));

CREATE TABLE

> CREATE INDEX id ON t9(c2);

CREATE INDEX

> ALTER INDEX t9@id PARTITION BY RANGE (c2) (

​ PARTITION p1 VALUES FROM (0) TO (100),

​ PARTITION p2 VALUES FROM (100) TO (200)

);

ALTER INDEX

修改索引列分区信息。

> ALTER INDEX t9@id PARTITION BY LIST (c2) (

PARTITION p1 VALUES IN (1, 2, 3, 4),

PARTITION p2 VALUES IN (5, 6, 7, 8));

ALTER INDEX

为表上分区p1添加物理归属地信息。

> ALTER PARTITION p1 OF TABLE t9 LOCATE IN “guangzhou”;

ALTER TABLE

删除分区p1上的物理归属地信息。

> ALTER PARTITION p1 OF TABLE t9 LOCATE IN “”;

ALTER TABLE

删除索引列上的分区信息。

> ALTER INDEX t9@id PARTITION BY NOTHING;

ALTER INDEX

删除建立的索引项。

> DROP INDEX t9@id;

DROP INDEX