说明
分区表查询时,根据查询条件,自动过滤掉未命中的分区,只在命中的分区中查询
使用优化方法
分区表查询 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)