greenplum partition table

  1. 云栖社区>
  2. 博客>
  3. 正文

greenplum partition table

樱桃味 2016-11-25 17:01:25 浏览818
展开阅读全文
greenplum 分区表,索引


greenplum 分区按照类型可以分为


#列表分表


create table gh_par_list(
id1 integer,
id2 varchar(10))
distributed by (id1)
partition by list(id2)
(
partition p1 values ('1','2') tablespace ts_gh,
partition p2 values ('3','0')
tablespace ts_gh,
default partition pd
tablespace ts_gh
);






#范围分区


create table gh_par_range
(id1 integer,
 id2 varchar(10),
 id3 date)
 distributed by (id1)
 partition by range(id3)
 (partition p1 start ('2011-01-01'::date) end ('2012-01-01'::date) tablespace ts_gh,
 partition p2 start('2020-01-01'::date) end ('2021-01-01'::date) tablespace ts_gh);
 
##简便分区


create table gh_par_range_every
(id1 integer,
 id2 varchar(10),
 id3 date)
 distributed by (id1)
partition by range(id3)
(partition p2011 start ('2011-01-01'::date) end ('2030-01-01'::date) every ('1 year'::interval) tablespace ts_gh);


#表和分区的联系




tutorial=> \d pg_partition;
    Table "pg_catalog.pg_partition"
    Column     |    Type    | Modifiers 
---------------+------------+-----------
 parrelid      | oid        | not null
 parkind       | "char"     | not null
 parlevel      | smallint   | not null
 paristemplate | boolean    | not null
 parnatts      | smallint   | not null
 paratts       | int2vector | not null
 parclass      | oidvector  | not null
Indexes:
    "pg_partition_oid_index" UNIQUE, btree (oid)
    "pg_partition_parrelid_index" btree (parrelid)
    "pg_partition_parrelid_parlevel_istemplate_index" btree (parrelid, parlevel, paristemplate)




tutorial=> 


**pg_partition中的parrelid关联pg_class的oid**








tutorial=> \d pg_partition_rule;
   Table "pg_catalog.pg_partition_rule"
      Column       |   Type   | Modifiers 
-------------------+----------+-----------
 paroid            | oid      | not null
 parchildrelid     | oid      | not null
 parparentrule     | oid      | not null
 parname           | name     | not null
 parisdefault      | boolean  | not null
 parruleord        | smallint | not null
 parrangestartincl | boolean  | not null
 parrangeendincl   | boolean  | not null
 parrangestart     | text     | 
 parrangeend       | text     | 
 parrangeevery     | text     | 
 parlistvalues     | text     | 
 parreloptions     | text[]   | 
 partemplatespace  | oid      | 
Indexes:
    "pg_partition_rule_oid_index" UNIQUE, btree (oid)
    "pg_partition_rule_parchildrelid_index" btree (parchildrelid)
    "pg_partition_rule_parchildrelid_parparentrule_parruleord_index" btree (parchildrelid, parp
arentrule, parruleord)
    "pg_partition_rule_paroid_parentrule_ruleord_index" btree (paroid, parparentrule, parruleor
d)




tutorial=> 




**pg_partition_rule中的paroid关联pg_partition的oid**




##创建视图直接查询
create view vw_partition as 
select pp.parrelid tableoid ,prl.parchildrelid,prl.parname as partitionname 
from pg_partition pp,pg_partition_rule prl
where pp.paristemplate=false and prl.paroid=pp.oid;


select * from vw_partition t where tableoid='public.gh_par_range'::regclass;






#分区操作


##增加


alter table gh_par_range 
add partition p3 start ('1991-01-01'::date) end ('1992-01-01'::date);


##删除




 alter table gh_par_range drop partition p3;


##清空


alter table gh_par_range truncate partition p3;
##分离


alter table gh_par_range
split partition p3
at ('1991-07-01'::date)
into (partition p4,partition p5);


##交换




2016-12-01

greenplum 支持对表进行分区

分区表

逻辑上的一个大表分割为物理上的几块


greenplum  来自于 postgresql


postgresql 创建分区表 步骤

1)创建主表,所有分区都从它继承

2)创建几个子表,每个都从主表上继承

3)为分区表增加约束,定义每个分区允许的键值

4)对于每个分区,在关键字字段上创建一个索引,以及其他想创建的索引。

5)定义一个规则或触发器,把对主表的修改重定向到合适的分区表


greenplum 来说,分区表的实现原理与上面介绍的一样

分区表目前支持范围分区和列表分区

范围分区


创建范围分区
tutorial=> create table gh_partition_range(id int,name varchar(32),dw_end_date date)
tutorial-> distributed by (id)
tutorial-> partition by range (dw_end_date)
tutorial-> (
tutorial(> partition p2015 start ('2015-01-01'::date) end ('2016-01-01'::date),
tutorial(> partition p2016 start ('2016-01-01'::date) end ('2017-01-01'::date)
tutorial(> );
NOTICE:  CREATE TABLE will create partition "gh_partition_range_1_prt_p2015" for table "gh_partitio
n_range"NOTICE:  CREATE TABLE will create partition "gh_partition_range_1_prt_p2016" for table "gh_partitio
n_range"CREATE TABLE

插入数据
tutorial=> insert into gh_partition_range values(1,23,'2015-01-01'::date);
INSERT 0 1
tutorial=> select * from gh_partition_range;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2015-01-01
(1 row)

tutorial=> insert into gh_partition_range values(1,23,'2016-01-01'::date);
INSERT 0 1

插入数值大于分区范围报错
tutorial=> insert into gh_partition_range values(1,23,'2018-01-01'::date);
ERROR:  no partition for partitioning key  (seg0 slave1:40000 pid=1875)


查询数据
tutorial=> select * from gh_partition_range;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2015-01-01
  1 | 23   | 2016-01-01
(2 rows)

查询分区数据
tutorial=> select * from gh_partition_range_1_prt_p2015;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2015-01-01
(1 row)
tutorial=> select * from gh_partition_range_1_prt_p2016;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2016-01-01
(1 row)

添加分区
tutorial=> alter table gh_partition_range add partition p2017 start ('2017-01-01'::date) end ('2018-01-01'::date);
NOTICE:  CREATE TABLE will create partition "gh_partition_range_1_prt_p2017" for table "gh_partitio
n_range"ALTER TABLE
tutorial=> insert into gh_partition_range values(1,23,'2017-01-01'::date);
INSERT 0 1
tutorial=>
tutorial=> select * from gh_partition_range_1_prt_p2017;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2017-01-01
(1 row)

删除分区
tutorial=> alter table gh_partition_range drop partition p2017;
ALTER TABLE
tutorial=>

清空分区
tutorial=> alter table gh_partition_range truncate partition p2015;
ALTER TABLE
tutorial=> select * from gh_partition_range;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2016-01-01
(1 row)


分离分区

tutorial=> alter table gh_partition_range split partition p2016  at (('2016-06-01'::date)) into (partition p2016s, partition p2016x);
NOTICE:  exchanged partition "p2016" of relation "gh_partition_range" with relation "pg_temp_85849"
NOTICE:  dropped partition "p2016" for relation "gh_partition_range"
NOTICE:  CREATE TABLE will create partition "gh_partition_range_1_prt_p2016s" for table "gh_partiti
on_range"NOTICE:  CREATE TABLE will create partition "gh_partition_range_1_prt_p2016x" for table "gh_partiti
on_range"ALTER TABLE
tutorial=> select * from gh_partition_range;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2016-01-01
  1 | 25   | 2016-01-01
  1 | 25   | 2016-02-01
  1 | 25   | 2016-06-01
  1 | 25   | 2016-07-01
(5 rows)

tutorial=> select * from gh_partition_range_1_prt_p2016s;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2016-01-01
  1 | 25   | 2016-01-01
  1 | 25   | 2016-02-01
(3 rows)

tutorial=> select * from gh_partition_range_1_prt_p2016x;
id | name | dw_end_date
----+------+-------------
  1 | 25   | 2016-06-01
  1 | 25   | 2016-07-01
(2 rows)


交换分区(表字段名称,顺序,类型,字段长度必须一致,就像一张另外自己的表,且交换分区,一张表不能为分区表)

tutorial=> create table gh_one_partition (id int,name varchar(35),dw_date date)                   
partition by range (dw_date)                                                                       (partition  p2015 start ('2015-01-01'::date) end ('2016-01-01'::date),                              partition  p2016 start ('2016-01-01'::date) end ('2017-01-01'::date));NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column named 'id' as the Greenplum Dat
abase data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen
are the optimal data distribution key to minimize skew.NOTICE:  CREATE TABLE will create partition "gh_one_partition_1_prt_p2015" for table "gh_one_partit
ion"NOTICE:  CREATE TABLE will create partition "gh_one_partition_1_prt_p2016" for table "gh_one_partit
ion"CREATE TABLE

tutorial=> insert into gh_one_partition values(1,26,'2015-01-01'::date);
INSERT 0 1
tutorial=> insert into gh_one_partition values(1,26,'2015-02-01'::date);
INSERT 0 1
tutorial=> select * from gh_one_partition;
id | name |  dw_date  
----+------+------------
  1 | 26   | 2015-01-01
  1 | 26   | 2015-02-01
(2 rows)


tutorial=> select * from gh_partition_range_1_prt_p2015;
id | name | dw_end_date
----+------+-------------
(0 rows)

tutorial=> select * from gh_partition_range;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2016-01-01
  1 | 25   | 2016-01-01
  1 | 25   | 2016-02-01
  1 | 25   | 2016-06-01
  1 | 25   | 2016-07-01
(5 rows)

tutorial=> insert into gh_partition_range_1_prt_p2015 values(1,21,'2015-01-01'::date);
INSERT 0 1
tutorial=> select * from gh_partition_range_1_prt_p2015;
id | name | dw_end_date
----+------+-------------
  1 | 21   | 2015-01-01
(1 row)

tutorial=> select * from gh_one_partition_1_prt_p2015;
id | name |  dw_date  
----+------+------------
  1 | 26   | 2015-01-01
  1 | 26   | 2015-02-01
(2 rows)


tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition;
ERROR:  relation "gh_one_partition" must have the same column names and column order as "gh_partition_range"
tutorial=> ALTER TABLE public.gh_one_partition RENAME dw_date  TO dw_end_date;
ALTER TABLE
tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition;
ERROR:  cannot EXCHANGE table "gh_one_partition" as it has child table(s)
tutorial=> alter table gh_one_partition drop partition p2016;
ALTER TABLE
tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition;
ERROR:  cannot EXCHANGE table "gh_one_partition" as it has child table(s)
tutorial=> create table gh_one_partition2 as select * from gh_one_partition;
NOTICE:  Table doesn't have 'DISTRIBUTED BY' clause -- Using column(s) named 'id' as the Greenplum
Database data distribution key for this table.HINT:  The 'DISTRIBUTED BY' clause determines the distribution of data. Make sure column(s) chosen
are the optimal data distribution key to minimize skew.SELECT 2
tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition;
ERROR:  cannot EXCHANGE table "gh_one_partition" as it has child table(s)
tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition2;
ERROR:  child table "gh_one_partition2" has different type for column "name"
tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition2;
ALTER TABLE
tutorial=> select * from gh_one_partition2;
id | name | dw_end_date
----+------+-------------
  1 | 21   | 2015-01-01
(1 row)

tutorial=> select * from gh_partition_range_1_prt_p2015;
id | name | dw_end_date
----+------+-------------
  1 | 26   | 2015-01-01
  1 | 26   | 2015-02-01
(2 rows)

tutorial=> truncate tabel gh_one_partition2;
ERROR:  syntax error at or near "gh_one_partition2"
LINE 1: truncate tabel gh_one_partition2;
                       ^
tutorial=> truncate table gh_one_partition2;
TRUNCATE TABLE
tutorial=> alter table gh_partition_range exchange partition p2015 with table gh_one_partition2;
ALTER TABLE
tutorial=> select * from gh_one_partition2;
id | name | dw_end_date
----+------+-------------
  1 | 26   | 2015-01-01
  1 | 26   | 2015-02-01
(2 rows)

tutorial=> select * from gh_partition_range;
id | name | dw_end_date
----+------+-------------
  1 | 23   | 2016-01-01
  1 | 25   | 2016-01-01
  1 | 25   | 2016-02-01
  1 | 25   | 2016-06-01
  1 | 25   | 2016-07-01
(5 rows)

tutorial=>


范围分区每个间隔都要写,可以简化写法
every

tutorial=> create table gh_partition_every (id int,name varchar(32),dw_end_date date)             
distributed by (id)                                                                                partition by range (dw_end_date)                                                                   (                                                                                                  partition p2015 start ('2015-01-01'::date) end ('2019-01-01'::date)                                every ('1 years'::interval)                                                                        );
NOTICE:  CREATE TABLE will create partition "gh_partition_every_1_prt_p2015_1" for table "gh_partit
ion_every"NOTICE:  CREATE TABLE will create partition "gh_partition_every_1_prt_p2015_2" for table "gh_partit
ion_every"NOTICE:  CREATE TABLE will create partition "gh_partition_every_1_prt_p2015_3" for table "gh_partit
ion_every"NOTICE:  CREATE TABLE will create partition "gh_partition_every_1_prt_p2015_4" for table "gh_partit
ion_every"CREATE TABLE
tutorial=>


tutorial=> insert into gh_partition_every values(1,1,'2018-01-01'::date);
INSERT 0 1
tutorial=> insert into gh_partition_every values(1,1,'2017-01-01'::date);
INSERT 0 1
tutorial=> insert into gh_partition_every values(1,1,'2016-01-01'::date);
INSERT 0 1
tutorial=> insert into gh_partition_every values(1,1,'2015-01-01'::date);
INSERT 0 1
tutorial=> insert into gh_partition_every values(1,1,'2014-01-01'::date);
ERROR:  no partition for partitioning key  (seg0 slave1:40000 pid=2464)
tutorial=> insert into gh_partition_every values(1,1,'2019-01-01'::date);
ERROR:  no partition for partitioning key  (seg0 slave1:40000 pid=2464)
tutorial=>


tutorial=> select * from gh_partition_every;
id | name | dw_end_date
----+------+-------------
  1 | 1    | 2015-01-01
  1 | 1    | 2016-01-01
  1 | 1    | 2017-01-01
  1 | 1    | 2018-01-01
(4 rows)

tutorial=>


list分区
tutorial=> create table gh_partition_list(id int,name varchar(32),status varchar(10))
tutorial-> distributed by (id)
tutorial-> partition by list (status)
tutorial-> (
tutorial(> partition p_list_1 values ('weifang','qingdao'),
tutorial(> partition p_list_2 values ('taiyuan','datong'),
tutorial(> default partition other_city);
NOTICE:  CREATE TABLE will create partition "gh_partition_list_1_prt_p_list_1" for table "gh_partit
ion_list"NOTICE:  CREATE TABLE will create partition "gh_partition_list_1_prt_p_list_2" for table "gh_partit
ion_list"NOTICE:  CREATE TABLE will create partition "gh_partition_list_1_prt_other_city" for table "gh_part
ition_list"CREATE TABLE
tutorial=> insert into gh_partition_list values (1,1,'shanghai'),(2,2,'weifang');
INSERT 0 2
tutorial=> select * from gh_partition_list_1_prt_p_list_1;
id | name | status 
----+------+---------
  2 | 2    | weifang
(1 row)

tutorial=> select * from gh_partition_list_1_prt_p_list_2;
id | name | status
----+------+--------
(0 rows)

tutorial=> select * from gh_partition_list_1_prt_other_city ;
id | name |  status 
----+------+----------
  1 | 1    | shanghai
(1 row)

tutorial=>



分区表参数

pg_partition:分区主表

tutorial=> \d pg_partition;
    Table "pg_catalog.pg_partition"
    Column     |    Type    | Modifiers
---------------+------------+-----------
parrelid      | oid        | not null
parkind       | "char"     | not null
parlevel      | smallint   | not null
paristemplate | boolean    | not null
parnatts      | smallint   | not null
paratts       | int2vector | not null
parclass      | oidvector  | not null
Indexes:
    "pg_partition_oid_index" UNIQUE, btree (oid)
    "pg_partition_parrelid_index" btree (parrelid)
    "pg_partition_parrelid_parlevel_istemplate_index" btree (parrelid, parlevel, paristemplate)

tutorial=> select parrelid,parkind from pg_partition;
parrelid | parkind
----------+---------
    85849 | r
    86189 | r
    86445 | r
    86613 | l
(4 rows)

tutorial=> select relname from pg_class where oid ='public.gh_partition_range'::regclass;
      relname      
--------------------
gh_partition_range
(1 row)

tutorial=> select relname from pg_class where oid =85849;
      relname      
--------------------
gh_partition_range
(1 row)


tutorial=> \d pg_partition_rule;
   Table "pg_catalog.pg_partition_rule"
      Column       |   Type   | Modifiers
-------------------+----------+-----------
paroid            | oid      | not null
parchildrelid     | oid      | not null
parparentrule     | oid      | not null
parname           | name     | not null
parisdefault      | boolean  | not null
parruleord        | smallint | not null
parrangestartincl | boolean  | not null
parrangeendincl   | boolean  | not null
parrangestart     | text     |
parrangeend       | text     |
parrangeevery     | text     |
parlistvalues     | text     |
parreloptions     | text[]   |
partemplatespace  | oid      |
Indexes:
    "pg_partition_rule_oid_index" UNIQUE, btree (oid)
    "pg_partition_rule_parchildrelid_index" btree (parchildrelid)
    "pg_partition_rule_parchildrelid_parparentrule_parruleord_index" btree (parchildrelid, parparen
trule, parruleord)    "pg_partition_rule_paroid_parentrule_ruleord_index" btree (paroid, parparentrule, parruleord)


tutorial=> select paroid, parchildrelid , parname from pg_partition_rule ;
paroid | parchildrelid |  parname  
--------+---------------+------------
  85920 |         86106 | p2016s
  85920 |         86153 | p2016x
  86260 |         86212 | p2015
  85920 |         85872 | p2015
  86564 |         86468 | p2015_1
  86564 |         86492 | p2015_2
  86564 |         86516 | p2015_3
  86564 |         86540 | p2015_4
  86707 |         86636 | p_list_1
  86707 |         86660 | p_list_2
  86707 |         86684 | other_city
(11 rows)

tutorial=> select relname from pg_class where oid=86106;
             relname            
---------------------------------
gh_partition_range_1_prt_p2016s







范围分区支持max吗?
目前没有sql语法,无法测试,不过按照语句start end 应该不行




范围分区支持split吗?
范围分区支持split

范围分区各个分区索引可以不同吗?

Command:     CREATE INDEX
Description: define a new index
Syntax:
CREATE [UNIQUE] INDEX name ON table
       [USING btree|bitmap|gist]
       ( {column | (expression)} [opclass] [, ...] )
       [ WITH ( FILLFACTOR = value ) ]
       [TABLESPACE tablespace]
       [WHERE predicate]

查询创建索引语句没有所谓全局索引,本地索引的创建语句 

删除分区会对其他分区的索引有影响吗?

既然为本地索引,那么就可以认为删除分区对其他分区索引无影响









网友评论

登录后评论
0/500
评论
樱桃味
+ 关注