Distributing Columns for Netezza

From ER/Studio Data Architect
Jump to: navigation, search

Go Up to Developing the Physical Model

The Distribute tab of the Table Editor for the Netezza platform allows you to create the DISTRIBUTE ON statement that specifies the distribution key. The distribution key is used by the SPUs ( Snippet Processing Units or intelligent storage nodes) to store data on the server. If you do not specify the columns of the distribution key and do not activate the random distribution option, the Netezza server will assign to the distribution key by default the first column created in the table.

  • Available Columns: Displays all the columns available to add to the distribution key. Select the column you want to add to the distribution key and move it to the Selected Columns box. Use the left and right arrows to move columns to and from the Selected Columns box. You can select a maximum of four columns to use in the distribution key.
  • Selected Columns: Displays the columns that make up the distribution key.
  • Up / Down buttons: Let you reorder the columns in the distribution key. The column order can affect the access speed. The most frequently accessed columns should be at the top of the Selected Columns list.
  • Random: If selected, the table is created using a random or round-robin distribution key.

Completing this tab with the Random option selected will produce SQL code similar to the following:

CREATE TABLE Entity1(

PK1 CHAR(10) NOT NULL,

A1 CHAR(10),

A2 CHAR(10),

CONSTRAINT PK1 PRIMARY KEY (PK1)

)

DISTRIBUTE ON RANDOM

Selecting PK1 to be the only column in the distribution key and not selecting Random will produce SQL code similar to the following:

CREATE TABLE Entity1(

PK1 CHAR(10) NOT NULL,

A1 CHAR(10),

A2 CHAR(10),

CONSTRAINT PK1 PRIMARY KEY (PK1)

)

DISTRIBUTE ON (PK1)

See Also