南大通用GBase 8a数据库Range分区表null值保存分区位置测试

本文测试在GBase 8a数据库集群里，Range分区表里对null值的处理方式，其默认保存位置是所有分区里最小的一个(也就是第一个)。

参考

GBase 8a集群支持分区表功能使用样例

GBase 8a分区表指定特定分区的查询方法

测试表

如下是根据int类型做range分区的表，按照10，100，其它做成3个分区。

CREATE TABLE t_range (
  id INT
  ,name VARCHAR(100)
  ) PARTITION BY range (id) (
  PARTITION p_10 VALUES less than(100)
  ,PARTITION p_100 VALUES less than(10)
  ,PARTITION p_other VALUES less than MAXVALUE
  );

测试数据

插入普通数据

gbase> select * from t_range;
Empty set (Elapsed: 00:00:00.03)

gbase> insert into t_range values(1,'v=1');
Query OK, 1 row affected (Elapsed: 00:00:00.09)

gbase> insert into t_range values(50,'v=50');
Query OK, 1 row affected (Elapsed: 00:00:00.06)

gbase> insert into t_range values(500,'v=500');
Query OK, 1 row affected (Elapsed: 00:00:00.05)

查看数据

通过指定分区的查询，数据均正确的在各个分区里，

gbase> select * from t_range;
+------+-------+
| id   | name  |
+------+-------+
|    1 | v=1   |
|   50 | v=50  |
|  500 | v=500 |
+------+-------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t_range partition(p_10);
+------+------+
| id   | name |
+------+------+
|    1 | v=1  |
+------+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from t_range partition(p_100);
+------+------+
| id   | name |
+------+------+
|   50 | v=50 |
+------+------+
1 row in set (Elapsed: 00:00:00.00)

gbase> select * from t_range partition(p_other);
+------+-------+
| id   | name  |
+------+-------+
|  500 | v=500 |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)

插入NULL值

可以看到其保存在第一个p_10分区内。

gbase> insert into t_range values(null,'v=NULL');
Query OK, 1 row affected (Elapsed: 00:00:00.06)

gbase> select * from t_range partition(p_other);
+------+-------+
| id   | name  |
+------+-------+
|  500 | v=500 |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from t_range partition(p_100);
+------+------+
| id   | name |
+------+------+
|   50 | v=50 |
+------+------+
1 row in set (Elapsed: 00:00:00.01)

gbase> select * from t_range partition(p_10);
+------+--------+
| id   | name   |
+------+--------+
|    1 | v=1    |
| NULL | v=NULL |
+------+--------+
2 rows in set (Elapsed: 00:00:00.01)

删除第一个分区

null值已经消失。

gbase> alter table t_range drop partition p_10;
Query OK, 0 rows affected (Elapsed: 00:00:00.62)
Records: 0  Duplicates: 0  Warnings: 0

gbase> select * from t_range;
+------+-------+
| id   | name  |
+------+-------+
|   50 | v=50  |
|  500 | v=500 |
+------+-------+
2 rows in set (Elapsed: 00:00:00.01)

再次插入NULL值

null值被保存在剩下分区的第一个分区内，p_100。

gbase> insert into t_range values(null,'v=NULL2');
Query OK, 1 row affected (Elapsed: 00:00:00.05)

gbase> select * from t_range;
+------+---------+
| id   | name    |
+------+---------+
|   50 | v=50    |
| NULL | v=NULL2 |
|  500 | v=500   |
+------+---------+
3 rows in set (Elapsed: 00:00:00.01)

gbase> select * from t_range partition(p_10);
ERROR 1893 (HY000): Unknown partition 'p_10' in table 't_range'
gbase> select * from t_range partition(p_100);
+------+---------+
| id   | name    |
+------+---------+
|   50 | v=50    |
| NULL | v=NULL2 |
+------+---------+
2 rows in set (Elapsed: 00:00:00.02)

gbase> select * from t_range partition(p_other);
+------+-------+
| id   | name  |
+------+-------+
|  500 | v=500 |
+------+-------+
1 row in set (Elapsed: 00:00:00.01)

总结

GBase 8a的Range分区，对NULl值是保存在第一个分区内的，如果分区被删除，则新数据会被保存在残留分区的第一个分区内。