Create a table. Without primarykey or uniquekey, GreenPlum uses the first column as the distribution key zwcdb # createtabletab01 (idint, namevarchar (20) by default. NOTICE: partition -- Partition
Create a table. Without primary key or unique key, GreenPlum uses the first column as the distribution key zwcdb = # create table tab01 (id int, name varchar (20); NOTICE: Table doesnt have distributed by clause -- Using column named id as the Greenplum Da
Create a table. Without the primary key or unique key, GreenPlum uses the first column as the distribution key by default.
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)
Use the following statement to modify the distribution key
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)
You can use the randomly policy when you are not sure which column is the distribution key.
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