12.GaussDB范围分区表的管理

1.范围分区表相关技术包括如下

分区表:
(1)按照数字范围分区
(2)包含START,END关键字的按照数字范围分区
(3)分区表的分区指定特定的表空间。
(4)删除包含某个数字值的分区。
(5)添加分区
(6)重命名分区
(7)移动分区到某个指定表空间
(8)在某个值处对某个分区进行拆分
(9)将两个分区合并成一个分区。
(10)分区交换技术

2.范围分区表的详细演练

--范围分区表。
CREATE TABLE part_tbl1 (a int, b int)
PARTITION BY RANGE(a)
(
PARTITION part1 VALUES LESS THAN (10),
PARTITION part2 VALUES LESS THAN (100),
PARTITION part3 VALUES LESS THAN (MAXVALUE)
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
sjzt=> 

--包含START 和END 关键字的分区表。
sjzt=>
CREATE TABLE part_tbl2 (a int, b int)
PARTITION BY RANGE(a)
(
partition part1 START(1) END(100) EVERY(50),
partition part2 END(200),
partition part3 START(200) END(300),
partition part4 start(300)
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'a' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE

--创建范围分区表：
-- 按照属性范围划分分区,指定分区。
CREATE TABLE pt1
(
id INTEGER,
name Varchar(20),
score DECIMAL(5,2) 
)
PARTITION BY RANGE(score)
(
PARTITION P1 VALUES LESS THAN(60) tablespace tbs3, 
PARTITION P2 VALUES LESS THAN(85) tablespace tbs3,
PARTITION P3 VALUES LESS THAN(MAXVALUE) tablespace tbs3
);
NOTICE:  The 'DISTRIBUTE BY' clause is not specified. Using 'id' as the distribution column by default.
HINT:  Please use 'DISTRIBUTE BY' clause to specify suitable data distribution column.
CREATE TABLE
sjzt=> 

--插入数据：
sjzt=> insert into pt1 values(1,'Jerry',99);
INSERT 0 1
sjzt=> insert into pt1 values(2,'Tom',80);
INSERT 0 1
sjzt=> insert into pt1 values(3,'Jack',55);
INSERT 0 1


--查询分区表：
sjzt=> select * from pt1 partition (p3);
 id | name  | score 
----+-------+-------
  1 | Jerry | 99.00
(1 row)

--查询分区表的分区 
sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;
  relname  |  relname  
-----------+-----------
 part3     | part_tbl1
 part2     | part_tbl1
 part1     | part_tbl1
 part_tbl1 | part_tbl1
 part4     | part_tbl2
 part3     | part_tbl2
 part2     | part_tbl2
 part1_2   | part_tbl2
 part1_1   | part_tbl2
 part1_0   | part_tbl2
 part_tbl2 | part_tbl2
 p3        | pt1
 p2        | pt1
 p1        | pt1
 pt1       | pt1
(15 rows)

-- 删除分区：
--删除90所在的分区,看到分区P3已经没有了。
sjzt=> alter table pt1 drop partition for(90);
ALTER TABLE
sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;
  relname  |  relname  
-----------+-----------
 part3     | part_tbl1
 part2     | part_tbl1
 part1     | part_tbl1
 part_tbl1 | part_tbl1
 part4     | part_tbl2
 part3     | part_tbl2
 part2     | part_tbl2
 part1_2   | part_tbl2
 part1_1   | part_tbl2
 part1_0   | part_tbl2
 part_tbl2 | part_tbl2
 p2        | pt1
 p1        | pt1
 pt1       | pt1
(14 rows)

-- 增加分区：
sjzt=> alter table pt1 add partition p3 values less than (95);
ALTER TABLE
sjzt=> alter table pt1 add partition p4 values less than (MAXVALUE);
ALTER TABLE
--
sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;
  relname  |  relname  
-----------+-----------
 part3     | part_tbl1
 part2     | part_tbl1
 part1     | part_tbl1
 part_tbl1 | part_tbl1
 part4     | part_tbl2
 part3     | part_tbl2
 part2     | part_tbl2
 part1_2   | part_tbl2
 part1_1   | part_tbl2
 part1_0   | part_tbl2
 part_tbl2 | part_tbl2
 pt1       | pt1
 p4        | pt1
 p3        | pt1
 p2        | pt1
 p1        | pt1
(16 rows)

--分区表修改：
--重命名分区
sjzt=> alter table pt1 rename partition p4 to pmax;
ALTER TABLE
--移动对象到别的表空间。
sjzt=> alter table pt1 move partition pmax tablespace tbs2;
ALTER TABLE
--在90处分割分区。
sjzt=> alter table pt1 split partition p3 at (90) into (partition p4,partition p5);
ALTER TABLE
--分离完成后没有P3了。
sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;
  relname  |  relname  
-----------+-----------
 part3     | part_tbl1
 part2     | part_tbl1
 part1     | part_tbl1
 part_tbl1 | part_tbl1
 part4     | part_tbl2
 part3     | part_tbl2
 part2     | part_tbl2
 part1_2   | part_tbl2
 part1_1   | part_tbl2
 part1_0   | part_tbl2
 part_tbl2 | part_tbl2
 p4        | pt1
 pt1       | pt1
 pmax      | pt1
 p5        | pt1
 p2        | pt1
 p1        | pt1
(17 rows)
--合并完成后没有P4,P5了,最后剩下P3;
sjzt=> alter table pt1 merge partitions p4,p5 into partition p3;
ALTER TABLE
sjzt=> select p.relname,c.relname from pg_partition p,pg_class c where p.parentid=c.oid;
  relname  |  relname  
-----------+-----------
 part3     | part_tbl1
 part2     | part_tbl1
 part1     | part_tbl1
 part_tbl1 | part_tbl1
 part4     | part_tbl2
 part3     | part_tbl2
 part2     | part_tbl2
 part1_2   | part_tbl2
 part1_1   | part_tbl2
 part1_0   | part_tbl2
 part_tbl2 | part_tbl2
 p3        | pt1
 pt1       | pt1
 pmax      | pt1
 p2        | pt1
 p1        | pt1
(16 rows)


CREATE TABLE T1
(
id INTEGER,
name Varchar(20),
score DECIMAL(5,2) 
);
sjzt=> insert into t1 values(3,'Jack',85);
INSERT 0 1
sjzt=> select * from t1;
 id | name | score 
----+------+-------
  3 | Jack | 85.00
(1 row)



sjzt=> select * from pt1 partition(p3);
 id | name | score 
----+------+-------
(0 rows)


-- 普通表与分区表数据交换
--分区交换技术是Oracle特有的,现在GaussDB也实现了。
sjzt=> alter table pt1 exchange partition (p3) with table t1; 
ALTER TABLE
sjzt=> select * from pt1 partition(p3);
 id | name | score 
----+------+-------
  3 | Jack | 85.00
(1 row)

-- 查询分区表属性信息

SELECT t1.oid, t1.relname, partstrategy, boundaries, t1.reltablespace, t1.parentid 
FROM pg_partition t1, pg_class t2 
WHERE t1.parentid = t2.oid AND t2.relname = 'pt1' AND t1.parttype = 'p'; 

  oid  | relname | partstrategy | boundaries | reltablespace | parentid 
-------+---------+--------------+------------+---------------+----------
 26455 | p3      | r            | {95.00}    |             0 |    26419
 26427 | pmax    | r            | {NULL}     |         18203 |    26419
 26424 | p2      | r            | {85}       |         18236 |    26419
 26423 | p1      | r            | {60}       |         18236 |    26419
(4 rows)

3.总结

GaussDB分区表里面有两个比较有特色的内容:

（1）分区交换技术，这个只在oracle里面出现过，现在GaussDB也实现了这个功能。

（2）保护START,END 关键字创建分区表。这个也是第一次见，比较新鲜。