Greenplum常⽤SQL总结Greenplum常⽤SQL总结
查看表在各个segment的数据分布
=# SELECT gp_segment_id,count(*)FROM table_name GROUP BY gp_segment_id;
查看分区表在各个segment的数据分布
=# SELECT gp_segment_id,tableoid::regclass,count(*)FROM partition_tablename GROUP BY1,2ORDER BY1,2；查看磁盘可⽤空间
=# SELECT dfsegment, dfhostname, dfdevice,pg_size_pretty(dfspace)AS dfspace
FROM gp_toolkit.gp_disk_free ORDER BY dfsegment;
dfsegment | dfhostname  |        dfdevice          | dfspace
-----------+-------------+---------------------------+---------
0|  minion_187 |/dev/mapper/ncl-paasdata |842MB
1|  minion_187 |/dev/mapper/ncl-paasdata |842MB
2|  minion_187 |/dev/mapper/ncl-paasdata |842MB
3|  minion_187 |/dev/mapper/ncl-paasdata |842MB
4|  minion_188 |/dev/mapper/ncl-paasdata |844MB
5|  minion_188 |/dev/mapper/ncl-paasdata |844MB
6|  minion_188 |/dev/mapper/ncl-paasdata |844MB
7|  minion_188 |/dev/mapper/ncl-paasdata |844MB
(8 rows)
查看所有数据库占⽤空间⼤⼩
=# SELECT sodddatname AS db_name,pg_size_pretty(sodddatsize)AS db_size
FROM gp_toolkit.gp_size_of_database ORDER BY sodddatname;
db_name              | db_size
-
------------------------------------+---------
db_21c0944e79f2488bb610d58c89f1809b |51GB
db_30639a04dc4b4b7c891667d7031cc989 |46GB
db_9bd86a7457fc445c9b3dbcb39a4eed30 |148MB
db_e0ecc09cf5db4dcfb1460c3ff06d1b0d |146MB
dbplat                              |147MB
(5 rows)
查看表占⽤空间⼤⼩
=# SELECT relname AS name, sotdsize AS size, 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;
name            |  size  | toast  | other
---------------------------+----------+--------+-------
comm_ne_test_mac          |0|0|0
generationstrategytable  |262144|0|0
old_t_npa_ctn_moc_busi    |62816256|294912|0
old_t_npa_ctn_moc_card    |91947008|294912|0
old_t_npa_ctn_moc_ethserv |43483136|294912|0
(5 rows)
查看索引占⽤空间⼤⼩
=# SELECT soisize, relname as indexname
FROM pg_class, gp_toolkit.gp_size_of_index
WHERE pg_class.oid=gp_size_of_index.soioid AND lkind='i';
soisize |          indexname
---------+-------------------------------
557056| t_npa_mark_style_pkey
557056| t_npa_mark_style_content_pkey
294912| t_npa_operator_log_pkey
557056| t_npa_periodmark_pkey
557056|t_npa_drill_content_pkey
(5 rows)
建表模板
drop table if exists rpt.rpt_bill_m ;
create table rpt.rpt_bill_m(
user_id SERIAL,----⾃增序列
acct_month varchar(6),
bill_fee numeric(16,2),
user_info text
)
WITH(
appendonly=true,--对于压缩表跟列存储来说，前提必须是appendonly表orientation=column,--列存 row
compresstype=zlib,--压缩格式--QUICKLZ
COMPRESSLEVEL=5,--压缩等级0--9--1压缩低查询快
OIDS=FALSE
)
DISTRIBUTED BY(user_id)--分布键
PARTITION BY LIST("acct_month")--分区键
(
PARTITION p_201810 VALUES('201810'),greenplum数据库
PARTITION p_201811 VALUES('201811'),
PARTITION p_201812 VALUES('201812'),
default partition other --容错没有分区键在此表
/* PARTITION p_20170801 START('20170801'::DATE) END ('20170831'::DATE) EVERY ('1 month'::INTERVAL) */
);
comment on column rpt.rpt_bill_m.user_info is '员⼯备注信息';--注解

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。