greenplum partition table-白红宇

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

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]

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

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

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