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;
标签:hash,8c,partition,list,id,range,oracle,subpartition,GBase
From: https://blog.51cto.com/u_17026136/12058060