南大通用GBase 8a分区表指定特定分区的查询方法

GBase 8a数据库集群从V95开始支持分区，本文介绍用户在SQL里对指定分区的查询方法。

有关创建分区表的介绍，请参考：

GBase 8a集群支持分区表功能使用样例
GBase 8a 元数据表介绍

语法

TABLENAME partition(PARTITION_NAME[,P2...])

说明

在表名字后面，通过partition关键字，指定分区名字,多个分区用逗号分割。

样例

表结构

CREATE TABLE "t_key_int" (
  "id" int(11) DEFAULT NULL,
  "name" varchar(100) DEFAULT NULL,
  "birth" date DEFAULT NULL
) 
 PARTITION BY KEY (id)
(PARTITION p01 TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS,
 PARTITION p02 TABLESPACE = 'sys_tablespace' ENGINE = EXPRESS) 

数据

gbase> select * from t_key_int;
+------+--------------------+------------+
| id   | name               | birth      |
+------+--------------------+------------+
|    7 | 444444444444444444 | 2021-03-08 |
|    2 | 444444444444444444 | 2021-03-08 |
|    4 | 444444444444444444 | 2021-03-08 |
+------+--------------------+------------+
3 rows in set (Elapsed: 00:00:00.01)

查询指定一个分区

gbase> select * from t_key_int partition(p01);
+------+--------------------+------------+
| id   | name               | birth      |
+------+--------------------+------------+
|    7 | 444444444444444444 | 2021-03-08 |
+------+--------------------+------------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from t_key_int partition(p01) where id<10;
+------+--------------------+------------+
| id   | name               | birth      |
+------+--------------------+------------+
|    7 | 444444444444444444 | 2021-03-08 |
+------+--------------------+------------+
1 row in set (Elapsed: 00:00:00.00)

查询指定多个分区

gbase> select * from t_key_int partition(p01,p02) where id<10;
+------+--------------------+------------+
| id   | name               | birth      |
+------+--------------------+------------+
|    7 | 444444444444444444 | 2021-03-08 |
|    2 | 444444444444444444 | 2021-03-08 |
|    4 | 444444444444444444 | 2021-03-08 |
+------+--------------------+------------+
3 rows in set (Elapsed: 00:00:00.01)