GreenPlum改变表的分布策略

来源：互联网发布：ping ip加端口编辑：程序博客网时间：2024/04/27 04:30

创建一张表,在没有primary key 或者 unique key 的情况下，GreenPlum默认会把第一个column作为分布键

zwcdb=# create table tab01(id int,name varchar(20));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 TABLEzwcdb=# zwcdb=# \d+ tab01                        Table "public.tab01" Column |         Type          | Modifiers | Storage  | Description --------+-----------------------+-----------+----------+------------- id     | integer               |           | plain    |  name   | character varying(20) |           | extended | Has OIDs: noDistributed by: (id)

使用以下语句修改分布键

zwcdb=# alter table tab01 set distributed by(name);ALTER TABLEzwcdb=# \d+ tab01                        Table "public.tab01" Column |         Type          | Modifiers | Storage  | Description --------+-----------------------+-----------+----------+------------- id     | integer               |           | plain    |  name   | character varying(20) |           | extended | Has OIDs: noDistributed by: (name)

在不确定哪个column为分布键的情况下可以使用randomly策略

zwcdb=# alter table tab01 set distributed randomly;ALTER TABLEzwcdb=# alter table tab01 set with(reorganize=true);ALTER TABLEzwcdb=# \d+ tab01                        Table "public.tab01" Column |         Type          | Modifiers | Storage  | Description --------+-----------------------+-----------+----------+------------- id     | integer               |           | plain    |  name   | character varying(20) |           | extended | Has OIDs: noDistributed randomly

0 0