GBase 8a分区筛选优化

说明

分区表查询时，根据查询条件，自动过滤掉未命中的分区，只在命中的分区中查询

使用优化方法

分区表查询 SQL 的 WHERE 条件或者经 GBase 8a MPP Cluster 整理后的 WHERE条件符合以下两种情况，即可应用分区筛选：

partition_name = constant

partition_name IN (constant1, constant2,……)

WHERE 条件可以涵盖：<、<=、 >、>=、 =、 <>、IN、BETWEEN...AND...等

示例

gbase> CREATE TABLE t1 (n INT) PARTITION BY RANGE(n) 
 -> ( 
 -> PARTITION p0 VALUES LESS THAN (100), 
 -> PARTITION p1 VALUES LESS THAN (200), 
 -> PARTITION p2 VALUES LESS THAN (300), 
 -> PARTITION p3 VALUES LESS THAN (400) 
 -> ); 
Query OK, 0 rows affected (Elapsed: 00:00:00.13)
gbase> INSERT INTO t1 VALUES(1),(2),(3),(4),(5); 
Query OK, 5 rows affected (Elapsed: 00:00:00.10)
Records: 5 Duplicates: 0 Warnings: 0
gbase> INSERT INTO t1 VALUES(101),(102),(103),(104),(105);
Query OK, 5 rows affected (Elapsed: 00:00:00.08)
Records: 5 Duplicates: 0 Warnings: 0
gbase> INSERT INTO t1 VALUES(201),(202),(203),(204),(205); 
Query OK, 5 rows affected (Elapsed: 00:00:00.10)
Records: 5 Duplicates: 0 Warnings: 0
gbase> INSERT INTO t1 VALUES(301),(302),(303),(304),(305); 
Query OK, 5 rows affected (Elapsed: 00:00:00.09)
Records: 5 Duplicates: 0 Warnings: 0
gbase> SELECT * from t1 WHERE n <4; +------+
| n    |
+------+
| 1    |
| 2    |   
| 3    |
+------+
3 rows in set (Elapsed: 00:00:00.05)
gbase> SELECT * from t1 WHERE n >300; +------+
| n    |
+------+
| 301  |
| 302  |
| 303  |
| 304  |
| 305  |
+------+
5 rows in set (Elapsed: 00:00:00.03)
gbase> SELECT * FROM t1 WHERE n IN (101,309); 
+------+
| n    |
+------+
| 101  |
+------+
1 row in set (Elapsed: 00:00:00.04)
gbase> SELECT * FROM t1 WHERE n BETWEEN 1 AND 199; +------+
| n    |
+------+
| 1    |
| 2    |
| 3    |
| 4    |
| 5    |
| 101  |
| 102  |
| 103  |
| 104  |
| 105  |
+------+
10 rows in set (Elapsed: 00:00:00.05)