GBase 8c 兼容性-oracle分区语法

原文链接：
https://www.gbase.cn/community/post/4055
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。

GBase 8c 分布式版本支持 兼容oracle 分区表功能，支持二级分区，9种分区组合，interval 分区。

分区明显如下：

hash分区sql 示例，与oracle写法一致。

-- 一级 hash 分区
drop table if exists mea_hash cascade;
create table mea_hash ( city_id int,logdate timestamp,id int )  partition by hash(id) ( partition p1 , partition p2 );
--二级分区 hash-list,hash-hash, hash-range
drop table if exists mea_hash_list cascade;
create table mea_hash_list ( city_id int,logdate timestamp,id int)  partition by hash(id) subpartition by list(city_id) ( partition p1  (subpartition p12 values (10),subpartition p13 values (20) ));
drop table if exists mea_hash_hash cascade;
create table mea_hash_hash ( city_id int,logdate timestamp,id int)  partition by hash(id) subpartition by hash(city_id) ( partition id_1  (subpartition p12 ,subpartition p13) );
drop table if exists mea_hash_range cascade;
create table mea_hash_range ( city_id int,logdate timestamp,id int)  partition by hash(id) subpartition by range(logdate) ( partition meas_y2021  (subpartition p12 values less than ('2021-02-04 12:00:00'),subpartition p13 values less than ('2021-02-04 20:00:00') ));

range分区sql 示例，与oracle写法一致。

drop table if exists mea_range cascade;
create table mea_range ( city_id int,logdate timestamp)  partition by range(logdate) ( partition meas_y2021 values less than  ('2021-01-01') );
--二级 range-range, range-hash,range-list
drop table if exists mea_range_range cascade;
create table mea_range_range ( city_id int,logdate timestamp,id int)  partition by range(logdate) subpartition  by range(id) ( partition meas_y2021 values less than ('2021-02-04 21:00:00') (subpartition p12 values less than (1),subpartition p13 values less than (10) ));
drop table if exists mea_range_hash cascade;
create table mea_range_hash ( city_id int,logdate timestamp,id int)  partition by range(logdate) subpartition by hash(city_id) ( partition id_1 values less than ('2021-02-01 01:00:00') (subpartition p12,subpartition p13) );
drop table if exists mea_range_list cascade;
create table mea_range_list ( city_id int,logdate timestamp,id int)  partition by range(logdate) subpartition by list(city_id) ( partition p1 values less than  ('2021-02-01 01:00:00') (subpartition p12 values (1),subpartition p13 values (20) ));

list 分区sql 示例，与oracle写法一致。

drop table if exists mea_list cascade;
create table mea_list ( city_id int,logdate timestamp,id int )  partition by list(id) ( partition p1 values (1), partition p2 values (2) );
--期望支持.成功执行
-- 二级 list-list,list-range,list-hash 分区
drop table if exists mea_list_list cascade;
create table mea_list_list ( city_id int,logdate timestamp,id int)  partition by  list(id) subpartition by list(city_id) ( partition p1 values (1) (subpartition p12 values (10),subpartition p13 values (20) ));
drop table if exists mea_list_range cascade;
create table mea_list_range ( city_id int,logdate timestamp,id int)  partition by  list(id) subpartition by range(logdate) ( partition meas_y2021 values ('202102') (subpartition p12 values less than ('2021-02-04 12:00:00'),subpartition p13 values less than ('2021-02-04 20:00:00') ));
drop table if exists mea_list_hash cascade;
create table mea_list_hash ( city_id int,logdate timestamp,id int)  partition by  list(id) subpartition by hash(city_id) ( partition id_1 values (2021) (subpartition p12,subpartition p13) );

其他分区语法，drop子分区，rename，拆分子分区等兼容新oracle语法。

-- 创建分区表，分区键是integer类型
CREATE TABLE tpcds.startend_pt (c1 INT, c2 INT) 
TABLESPACE startend_tbs1 
PARTITION BY RANGE (c2) (
   PARTITION p1 START(1) END(1000) EVERY(200) TABLESPACE startend_tbs2,
   PARTITION p2 END(2000),
   PARTITION p3 START(2000) END(2500) TABLESPACE startend_tbs3,
   PARTITION p4 START(2500),
   PARTITION p5 START(3000) END(5000) EVERY(1000) TABLESPACE startend_tbs4
)
ENABLE ROW MOVEMENT;
-- 查看分区表信息
SELECT relname, boundaries, spcname FROM pg_partition p JOIN pg_tablespace t ON p.reltablespace=t.oid and p.parentid='tpcds.startend_pt'::regclass ORDER BY 1;

-- 导入数据，查看分区数据量
INSERT INTO tpcds.startend_pt VALUES (GENERATE_SERIES(0, 4999), GENERATE_SERIES(0, 4999));
SELECT COUNT(*) FROM tpcds.startend_pt PARTITION FOR (0);

SELECT COUNT(*) FROM tpcds.startend_pt PARTITION (p3);
-- 增加分区: [5000, 5300), [5300, 5600), [5600, 5900), [5900, 6000)
ALTER TABLE tpcds.startend_pt ADD PARTITION p6 START(5000) END(6000) EVERY(300) TABLESPACE startend_tbs4;
-- 增加MAXVALUE分区: p7
ALTER TABLE tpcds.startend_pt ADD PARTITION p7 END(MAXVALUE);
-- 重命名分区p7为p8
ALTER TABLE tpcds.startend_pt RENAME PARTITION p7 TO p8;
-- 删除分区p8
ALTER TABLE tpcds.startend_pt DROP PARTITION p8;
-- 重命名5950所在的分区为：p71
ALTER TABLE tpcds.startend_pt RENAME PARTITION FOR(5950) TO p71;
-- 分裂4500所在的分区[4000, 5000)
ALTER TABLE tpcds.startend_pt SPLIT PARTITION FOR(4500) INTO(PARTITION q1 START(4000) END(5000) EVERY(250) TABLESPACE startend_tbs3);
-- 修改分区p2的表空间为startend_tbs4
ALTER TABLE tpcds.startend_pt MOVE PARTITION p2 TABLESPACE startend_tbs4;


原文链接：
https://www.gbase.cn/community/post/4055
更多精彩内容尽在南大通用GBase技术社区，南大通用致力于成为用户最信赖的数据库产品供应商。