国产数据库---GBase8a按时间分区

GBase 8a MPP Cluster系列产品从V95版本开始支持分区表功能，通过使用分区表，可有效提升查询性能、降低数据维护成本。
GBase 8a支持普通分区和子分区。目前常用的分区方法有range分区、list分区、key分区、hash分区。
在实际生产环境中，经常会按照时间顺序将生产数据进行组织管理，常用年度，季度，日期作为数据切分的时间单位。此时可利用range分区或list分区对数据按照时间单位进行划分管理。
示例1：按年度进行分区管理
Create table pt (d date,i int) partition by range(year(d))
(partition p0 values less than(2000),
Partition p1 values less than(2001),
Partition p2 values less than(2002)
);
Insert into pt values('1999-1-1',1);
Insert into pt values('2000-1-1',2);
Insert into pt values('2001-1-1',3);

Select * from pt;
Select * from pt (partition p0);
Select * from pt (partition p1);
Select * from pt (partition p2);

gbase> select * from pt;
+----------------+-------+
| d | i |
+----------------+-------+
| 1999-01-01 | 1 |
| 2000-01-01 | 2 |
| 2001-01-01 | 3 |
+----------------+-------+
3 rows in set (Elapsed: 00:00:00.03)

gbase> Select * from pt partition (p0);
+----------------+-------+
| d | i |
+----------------+-------+
| 1999-01-01 | 1 |
+----------------+-------+
1 row in set (Elapsed: 00:00:00.02)

gbase> Select * from pt partition (p1);
+----------------+------+
| d | i |
+----------------+------+
| 2000-01-01 | 2 |
+----------------+------+
1 row in set (Elapsed: 00:00:00.02)

gbase> Select * from pt partition (p2);
+----------------+------+
| d | i |
+----------------+------+
| 2001-01-01 | 3 |
+----------------+------+
1 row in set (Elapsed: 00:00:00.03)

示例2： 按季度进行分区管理


Create table pt (d date,i int) partition by range(quarter(d))
(partition p0 values less than(2),
Partition p1 values less than(3),
Partition p2 values less than(4),
Partition p3 values less than(5)
);

或者使用list分区
Create table pt (d date,i int) partition by range(quarter(d))
(partition p0 values in(1),
Partition p1 values in(2),
Partition p2 values in(3)，
Partition p3 values in(4)
);

示例3：按周进行分区管理

Create table pt (d date,i int) partition by range(week(d))
(partition p0 values less than(2),
Partition p1 values less than(3),
Partition p2 values less than(4),
Partition p3 values less than(5),
……
Partition p52 values less than(52)
);

示例4：按日期进行分区管理
Create table pt1 (d date,i int) partition by range(d)
(partition p0 values less than(20000101),
Partition p1 values less than(20000102),
Partition p2 values less than(20000103)
）；

gbase> Insert into pt1 values('2000-1-1',1);
Query OK, 1 row affected (Elapsed: 00:00:00.20)

gbase> Insert into pt1 values('2000-1-2',1);
Query OK, 1 row affected (Elapsed: 00:00:00.21)

gbase> select * from pt1;
+------------+------+
| d | i |
+------------+------+
| 2000-01-01 | 1 |
| 2000-01-02 | 1 |
+------------+------+
2 rows in set (Elapsed: 00:00:00.07)

gbase> select * from pt1 partition (p0);
Empty set (Elapsed: 00:00:00.05)

gbase> select * from pt1 partition (p1);
+------------+------+
| d | i |
+------------+------+
| 2000-01-01 | 1 |
+------------+------+
1 row in set (Elapsed: 00:00:00.03)

gbase> select * from pt1 partition (p2);
+------------+------+
| d | i |
+------------+------+
| 2000-01-02 | 1 |
+------------+------+
1 row in set (Elapsed: 00:00:00.04)