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 关键字创建分区表。这个也是第一次见,比较新鲜。