Distributing Data ​

When you are creating tables, it is important to define the data distribution scheme for the table, which determines how the rows are distributed across the worker nodes. Good data distribution is critical to optimize parallel processing for queries and other database operations.

Within a CREATE TABLE statement, you can define a single-column distribution key and distribute the data by hashing on the values in that column. Alternatively, you can set the distribution to RANDOM or REPLICATE.

DISTRIBUTE ON (column)

Hash distribution across the worker nodes based on the values in the specified column. This option is recommended for most tables. For example:

create table team
(teamid smallint, htid smallint, atid smallint, name varchar(30), nickname varchar(20), city varchar(20), stadium varchar(50), capacity int) 
distribute on (teamid);

DISTRIBUTE REPLICATE

Replication of the entire table across all blades. This option is intended for smaller tables. For example:

create table season
(seasonid smallint, season_name character(9), numteams smallint, winners varchar(30)) 
distribute replicate;

DISTRIBUTE RANDOM

Random distribution of table rows.

If you do not specify the DISTRIBUTE clause, the table is hash-distributed on the first named column in the table.

Data Distribution for CREATE TABLE AS Results ​

If you do not specify a distribution type for a CTAS table, the resulting data distribution depends on the nature of the query that creates the table.

• A table created from columns in one or more replicated tables is also replicated.
• A table created from a single hash-distributed table is typically hash-distributed on the same column when the distribution column is included in the select list. If the distribution column is not included in the select list, random distribution is used.
• Tables resulting from equality joins over distribution columns typically preserve the hash distribution.
• In general, hash distribution is preserved where possible, but tables created from complex joins or that contain complex select list expressions may produce randomly distributed result sets.

Data Skew with NULLs and NaNs in Distribution Columns ​

When the distribution column of a table contains a significant number of NULL values or NaN values, the data may not be distributed evenly. Extreme data skew on the worker nodes may occur. In general, it is best to avoid distributing a table on any column that is nullable or a floating-point column that it is likely to contain a large number of NaN values.

For more details about NaN, see DOUBLE PRECISION.