4.3. 管理表
创建表你需要考虑的内容和因素有：字段类型、表或者字段的约束、分布策略、存储策略、表分区等等。
4.3.0. 字段类型
在gp中character类型的char、varchar、text之间在不考虑使用空格填充空白的情况下没有区别，建议使用varchar和text替代char。而数字类型要考虑下类型大小占用的空间问题了，BIGINT类型如果都是填充些INT或者SMALLINT类型的数据的话将会浪费空间。
4.3.1. 约束
字段上，和oracle类似拥有primary、unique、check、not null、foreign约束，只是primary约束有个特殊的就是必须使用hash策略来分布表数据存储，不能在同一个表同时使用主键和唯一约束，并且指定了primary和unique的列必须全部或者部分包含在distributed key中；foreign约束虽然可以定义，但是并不会生效，至少在目前为止没有支持。
4.3.2. 分布策略
gp采用的是分布存放数据的架构，有两种分布方式：distributed by (key)，采用的是hash算法，distributed randomly，采用的是随机算法；如果不指定分布策略默认使用hash。
为了尽可能的并行处理数据，需要选择能够最大化地将数据均匀分布到所有seg的策略，比如选择primary key；分布式处理中将会存在本地和分布式协作的操作，当不同的表使用相同的分部键的时候，大部分的排序、连接关联操作工作将会在本地完成，本地操作往往比分布式操作快上5倍，但是采用随机分布的策略无法享受到这个优势。
默认情况下，不指定分布策略将使用hash策略，并且选择primiary key或者第1个column，key不能是几何类型和用户自定义的类型的column，如果没有合适的列那么会采用randomly策略。
一个参数，可能不会经常用到的：block size介于8192和2097152之间，以byte为单位，即8k到2mb，默认32k。更大的块大小会消耗更多的内存。
4.3.4. 数据存储方式
gp提供了多种的数据存储和组织方式，包括行存储、列存储、行和列混合存储、压缩、只读表等等。
ao表只允许追加数据；列存储只能是ao表；压缩只使用与ao表；
4.3.4.0. heap堆表
最普通的表形式，适合于较小的、经常更新的数据存储方式。
4.3.4.1. append only表
在某些情况下使用ao表存放非结构化的数据可以提高数据查询性能，ao表不允许update和delete操作。
从以下方面考虑如何选择行或列存储：
如果表数据在载入后必须update，那么不能采用列存储的表，列存储只能适用于ao表；
如果表数据经常insert，还是建议采用行表，因为列表对写入并没有优势，一列的数据必须往磁盘上写入多个地方；
如果表的数据查询经常是选择多个列的查询，建议使用行表，列表还是比较适合聚合查询的场景，比如sum、avg等等；
如果一次查询选择了大部分的列或者行的数据相对小，采用行表，表的列数量如果非常多，那么选择行表是个不错的建议；
因为列表中列的数据都是同一类型的，使用压缩存储可以比行表压缩带来更大的空间节省，带来的问题是在读取数据解压缩的消耗，以及随机访问带来的性能下降，压缩只适用于ao表。
可以基于表和行级别对数据进行压缩，基于列级别的压缩可以指定列和选择不同的压缩算法。 压缩和解压数据需要消耗更多的cpu资源，同时不要将数据存储到压缩的文件系统中。压缩数据初衷是出于最小化存储空间， 但是cpu消耗和压缩速度同样是重要的考虑因素。
针对不同的组织方式的表有以下算法选择：
行表：可以选择zlib和quicklz算法，zlib有1-9个压缩级别，级别越高压缩比越高
列表：可以选择zlib和quicklz以及rle_type算法，rle_type是4.2.1之后版本新推出的称为运行长度编码的算法，适合于有大量重复的数据记录
quicklz通常比zlib更少的消耗cpu，更快的压缩速度；在zlib压缩级别1的情况之下，两者通常具有相同的压缩比，在级别大于6的情况下，会有更加显著的压缩比。
4.3.5.0. 列级别压缩
只能适用于列存储的表，可以在创建或修改表的语句中指定列压缩命令。
C1 char ENCODING (compresstype=quicklz, blocksize=65536)
COLUMN C1 ENCODING (compresstype=quicklz, blocksize=65536)
DEFAULT COLUMN ENCODING (compresstype=quicklz)
指定参数的生效优先级如下（从高到底）：分区表、分区、列，注意压缩属性不会被继承，如果使用like子句创建的表会忽略列和表级别的参数
注意，临时表是存放在一个特定的schema当中的，并不是当前默认的schema，而且可以创建2个临时表和常规表公用一个名称，但是默认访问的是临时表。临时表有3个选项用来定义数据存活周期，on commit={preserve rows|delete rows|drop}，默认是第1个。
4.3.7. 分区表
支持rang和list以及两者混合的分区选项，无限制子分区层级数量；rang分区键只能有一个，适用于时间、数字范围的分区，list可以有多个分区键，适用于字符值的分区。
使用时间分区的时候建议采用最细粒度的平面分区而不是多层级的分区，平面分区有更佳的查询速度，多级分区有更好的查询计划分析时间。
分区表使用限制，分区键必须包含主键或者唯一键;
RAND范围分区：
partiton by range(date) (start (date '2012-01-01') inclusive end (date '2015-01-01') exclusive,every(interval '1 month'))
LIST列表分区：
partiton by list(region) (partition prtusa values('usa'),partition prtchina values('chaina'),default prtother other)
分区的选择性，查询计划只能对稳定的比较运算符执行选择性扫描：= < <= > >= <>，不能选择不稳定的函数，比如时间，但是可以对日期选择比较。
4.3.8. 表使用汇总
--创建表 heap
gtlions=# create table t1(id int constraint pk_t1_id primary key using index tablespace gtlions_ts2,name text not null,age int constraint ck_t1_age check(age>=18),birth date not null,notes text default 'this is notes!') tablespace gtlions_ts1 distributed by (id);
NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index "t1_pkey" for table "t1"
CREATE TABLE
--创建临时表 temporary
gtlions=# create temp table tp(id int) on commit delete rows;
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column 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.
CREATE TABLE
--创建列表 column
gtlions=# create table t7 (id int) with(appendonly=true,orientation=column);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column 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.
CREATE TABLE
--创建只读表 appendonly
gtlions=# create table t2(id int) with (appendonly=true) distributed by (id);
CREATE TABLE
--创建压缩表 compression
gtlions=# create table t3(id int,name text) with (appendonly=true,compresstype=zlib,compresslevel=9) distributed by (id);
CREATE TABLE
gtlions=# create table t4(id int,name text,age date encoding(compresstype=quicklz),notes text ,column notes encoding(compresstype=zlib,compresslevel=9),default column encoding(compresstype=quicklz,blocksize=8192)) with (appendonly=true,orientation=column);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column 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.
CREATE TABLE
--创建表 like
gtlions=# create table t5(like t4);
NOTICE: Table doesn't have 'distributed by' clause, defaulting to distribution columns from LIKE table
CREATE TABLE
--创建表 as
gtlions=# create table t6 as select * from t4;
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 0
--创建按照数字分区，每个“1”一个分区，注意默认情况下start总是包含而end总是排除的
gtlions=# create table t9 (id int,name text) partition by range(id) (start (20) end (25) every(1),default partition prtdefault);
NOTICE: Table doesn't have 'DISTRIBUTED BY' clause -- Using column 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.
NOTICE: CREATE TABLE will create partition "t9_1_prt_prtdefault" for table "t9"
NOTICE: CREATE TABLE will create partition "t9_1_prt_2" for table "t9"
NOTICE: CREATE TABLE will create partition "t9_1_prt_3" for table "t9"
NOTICE: CREATE TABLE will create partition "t9_1_prt_4" for table "t9"
NOTICE: CREATE TABLE will create partition "t9_1_prt_5" for table "t9"
NOTICE: CREATE TABLE will create partition "t9_1_prt_6" for table "t9"
CREATE TABLE
--创建一个月份范围分区
CREATE TABLE t10 (
ID INT,
DATE DATE,
amt DECIMAL (10, 2)
) DISTRIBUTED BY (ID) PARTITION BY RANGE (DATE)(
PARTITION Jan08 START (DATE '2008-01-01') INCLUSIVE,
PARTITION Feb08 START (DATE '2008-02-01') INCLUSIVE,
PARTITION Mar08 START (DATE '2008-03-01') INCLUSIVE,
PARTITION Apr08 START (DATE '2008-04-01') INCLUSIVE,
PARTITION May08 START (DATE '2008-05-01') INCLUSIVE,
PARTITION Jun08 START (DATE '2008-06-01') INCLUSIVE,
PARTITION Jul08 START (DATE '2008-07-01') INCLUSIVE,
PARTITION Aug08 START (DATE '2008-08-01') INCLUSIVE,
PARTITION Sep08 START (DATE '2008-09-01') INCLUSIVE,
PARTITION Oct08 START (DATE '2008-10-01') INCLUSIVE,
PARTITION Nov08 START (DATE '2008-11-01') INCLUSIVE,
PARTITION Dec08 START (DATE '2008-12-01') INCLUSIVE END (DATE '2009-01-01') EXCLUSIVE
);
--创建一个list分区
gtlions=# create table t11 (id int,sex char) distributed by (id) partition by list (sex) (partition p_boy values('b'), partition p_girl values('g'),default partition uknow);
NOTICE: CREATE TABLE will create partition "t11_1_prt_p_boy" for table "t11"
NOTICE: CREATE TABLE will create partition "t11_1_prt_p_girl" for table "t11"
NOTICE: CREATE TABLE will create partition "t11_1_prt_uknow" for table "t11"
CREATE TABLE
创建多层分区，多层分区会创建很多的子表，我们看下是如何计算一个分区表创建的子表的数量的，以下创建一个二级分区表，首先安装月份分区，2013年一共12个月，然后按照地区分区：usa和chn、other3个分区：
create table t12 (id int,date date,region text) distributed by (id)
partition by range (date)
subpartition by list (region)
subpartition template
(
subpartition prtregionusa values ('usa'),
subpartition prtregionchn values ('china'),
default subpartition prtregionother
)
(start (date '2013-01-01') end (date '2014-01-01') every (interval '1 month'),default partition prtdataother);
首先sales本身会创建1父表，二级分区根据date会创建1*（12+1）个父表，同时也是region的父表，最后region一层不会子表，是根据上一层的date分区1*（12+1）*3个表，一共是1+1*(12+1)+1*(12+1)*3=53个表格。
--添加分区，只能在没有默认分区的情况下添加新的分区，如果有默认分区而需要增加新的分区需要拆分分区操作
gtlions=# alter table t10 add partition jan09 start (date '2009-01-01') end (date '2009-02-01');
NOTICE: CREATE TABLE will create partition "t10_1_prt_jan09" for table "t10"
ALTER TABLE
--添加默认分区
gtlions=# alter table t10 add default partition prtdef;
NOTICE: CREATE TABLE will create partition "t10_1_prt_prtdef" for table "t10"
ALTER TABLE
--重命名分区
gtlions=# alter table t10 rename partition for ('2008-01-01') to prt200801;
NOTICE: renamed partition "jan08" to "prt200801" for relation "t10"
ALTER TABLE
gtlions=# alter table t10 rename partition for (rank(1)) to prt20080101;
NOTICE: renamed partition "prt200801" to "prt20080101" for relation "t10"
ALTER TABLE
--拆分分区，将200901的数据当中的1-15日的数据拆分到新的分区，16-31的数据拆分到新的分区
gtlions=# alter table t10 split partition for ('2009-01-01') at ('2009-01-16') into (partition prt200901a,partition prt200901b);
NOTICE: exchanged partition "jan09" of relation "t10" with relation "pg_temp_100487"
NOTICE: dropped partition "jan09" for relation "t10"
NOTICE: CREATE TABLE will create partition "t10_1_prt_prt200901a" for table "t10"
NOTICE: CREATE TABLE will create partition "t10_1_prt_prt200901b" for table "t10"
ALTER TABLE
--拆分默认分区，将默认分区中的数据的200902的数据拆分出入形成一个新的分区，其余数据还是存放在默认分区
gtlions=# alter table t10 split default partition start (date '2009-02-01') end (date '2009-03-01') into (partition prt200902,default partition);
NOTICE: exchanged partition "prtdef" of relation "t10" with relation "pg_temp_100487"
NOTICE: dropped partition "prtdef" for relation "t10"
NOTICE: CREATE TABLE will create partition "t10_1_prt_prt200902" for table "t10"
NOTICE: CREATE TABLE will create partition "t10_1_prt_prtdef" for table "t10"
ALTER TABLE
--删除分区
gtlions=# alter table t10 drop partition for ('2009-01-01');
NOTICE: dropped partition "prt200901a" for relation "t10"
ALTER TABLE
--删除默认分区
gtlions=# alter table t10 drop default partition;
NOTICE: dropped partition "prtdef" for relation "t10"
ALTER TABLE
--删除分区表数据
loglap=# alter table t10 truncate partition for (rank(1));
ALTER TABLE
--交换分区数据
gtlions=# alter table t10 exchange partition for (rank(1)) with table t10a;
NOTICE: exchanged partition "prt20080101" of relation "t10" with relation "t10a"
ALTER TABLE
--查看分区数据分布
gtlions=# select gp_segment_id,tableoid::regclass,count(*) from t10 group by 1,2 order by 1,2;
gp_segment_id | tableoid | count
---------------+----------+-------
(0 rows)
4.3.8. 修改表
修改hash分布策略会重新分布数据，而randomly则不会重新分布数据；当然我们也可以手工要求表重新分布数据，这个操作在表数据分布验证倾斜或者增删节点的情况下有用，这个操作对两种分布策略都是有效的;表的存储模式是无法被修改的。
--修改分布键
gtlions=# alter table t1 set distributed by (name);
ALTER TABLE
--手工重新分布数据
gtlions=# alter table t1 set with (reorganize=true);
ALTER TABLE
--增改删列
gtlions=# alter table t1 add column others text;
ALTER TABLE
gtlions=# alter table t1 alter column others type char(20);
ALTER TABLE
gtlions=# alter table t1 alter column others set default 'xx';
ALTER TABLE
gtlions=# alter table t1 rename column others to o;
ALTER TABLE
gtlions=# alter table t1 drop column o;
ALTER TABLE
--修改约束
gtlions=# alter table t1 add constraint check_id check(id>=1);
ALTER TABLE
gtlions=# alter table t1 drop constraint check_id;
ALTER TABLE
-EOF-
参考: http://www.cnblogs.com/gobird/archive/2012/04/11/2442846.html
greenplum分区表查看所占空间大小
前者用来查看数据大小,后者是human readable的调整.方法如下:
select pg_size_pretty(pg_relation_size('relation_name')) ;
select pg_size_pretty(pg_relation_size(oid)) ;
但是,对于分区表而言,这个方法就没有用了,会发现使用后表的大小为0bytes.原因在于:GP的分区表的主表只是一个表定义,其实际数据内容存储在继承父表的分区子表里面了.上网查了一下,没发现有相应的函数,干脆写了个函数来实现.
-- Function: calc_partition_table(character varying, character varying)
-- DROP FUNCTION calc_partition_table(character varying, character varying);
CREATE OR REPLACE FUNCTION calc_partition_table(v_schemaname character varying, v_tablename character varying)
RETURNS bigint AS
$BODY$
DECLARE
v_calc BIGINT := 0;
v_total BIGINT := 0;
v_tbname VARCHAR(200);
cur_tbname cursor for select schemaname||'.'||partitiontablename as tb from pg_partitions
where schemaname=v_schemaname and tablename=v_tablename;
BEGIN
OPEN cur_tbname;
loop
FETCH cur_tbname into v_tbname;
if not found THEN
exit;
end if;
EXECUTE 'select pg_relation_size('''||v_tbname||''')' into v_calc;
v_total:=v_total+v_calc;
end loop;
CLOSE cur_tbname;
RETURN v_total;
end;
$BODY$
LANGUAGE plpgsql VOLATILE;
ALTER FUNCTION calc_partition_table(character varying, character varying) OWNER TO gpadmin;
--表占用空间
SELECT relname as name, sotdsize/1024/1024 as size_MB, sotdtoastsize as toast, sotdadditionalsize as other
FROM gp_toolkit.gp_size_of_table_disk as sotd, pg_class
WHERE sotd.sotdoid = pg_class.oid
ORDER BY relname;
--索引占用空间
SELECT soisize/1024/1024 as size_MB, relname as indexname
FROM pg_class, gp_toolkit.gp_size_of_index
WHERE pg_class.oid = gp_size_of_index.soioid
AND pg_class.relkind='i';
select pg_size_pretty(pg_database_size('xxx'))
select * from pg_tables where schemaname='xxxx'
select oid,* from pg_namespace
select sum(relpages) from pg_class where relkind = 'i' and relnamespace=441490
5704154
select sum(relpages) from pg_class where relkind = 'r' and relnamespace=441490
21831261
select * from pg_class where relkind = 'r' and relnamespace=441490 and relname like 'tb%' order by relpages desc
"tb201302_sd";441490;24698109;10;0;28619180;0;717191;7.27088e+07;0;0;0;0;t;f;"r";"h";57;0;0;0;0;0;f;t;f;f;9290437;"";""
select pg_relation_size('"9960".tb201302_sd')/717191*27535415
715366760448
select pg_relation_size('"9960".tb201302_sd')/717191*5704154
186913718272
select pg_size_pretty(902280478720)--"666 GB"
select pg_size_pretty(186913718272)--"174 GB"
select sum(relpages) from pg_class where relnamespace=441490
27535415
select pg_size_pretty(pg_relation_size('"9960".tb201302_sd')) ;