Changing Netezza Table Distribution key and Example

Choosing right distribution key is one of the important factor to improve the performance of Netezza server. If you have created the table with RANDOM distribution or with different column with lots of duplicate records then you should immediately change the distribution key otherwise that will reduces the performance. Changing Netezza table distribution key is process of redistributing the Netezza table using Netezza nzsql.

Changing Netezza Table Distribution key and Example

You can achieve the redistribution in couple of ways:

Redistribute using CTAS

Creating new table and loading data at same time is one of the best way to redistribute table. You can use CTAS ( Create table AS) and is one of fastest way to re-create Netezza table.

Syntax:

CREATE TABLE <new_table> AS 
SELECT * FROM <old_table> DISTRIBUTE ON (new_colume(s));

For example;

TRAINING.ADMIN(ADMIN)=> CREATE TABLE new_sample_sales AS SELECT * FROM sample_sales DISTRIBUTE ON (MONTH_ID); 
INSERT 0 7 
TRAINING.ADMIN(ADMIN)=> DROP TABLE sample_sales; 
DROP TABLE 
TRAINING.ADMIN(ADMIN)=> ALTER TABLE new_sample_sales RENAME TO sample_sales; 
ALTER TABLE 
TRAINING.ADMIN(ADMIN)=>

Redistribute using Intermediate Table

The main drawback of using only CTAS is that you have to drop the original table and rename newly created table to original table. This process will invalidate the any views built on top of base table. You have to explicitly re-compile all views which are built on top of old table.

To overcome above mentioned issue, you can follow tow steps procedure to re-distribute table. First, use CTAS to create new intermediate table from old table, then, second, truncate original table and reload data from intermediate table.

For example;

TRAINING.ADMIN(ADMIN)=> CREATE TABLE int_sample_sales AS SELECT * FROM sample_sales DISTRIBUTE ON (MONTH_ID); 
INSERT 0 7 
TRAINING.ADMIN(ADMIN)=> TRUNCATE TABLE sample_sales; 
TRUNCATE TABLE 
TRAINING.ADMIN(ADMIN)=> INSERT INTO sample_sales SELECT * FROM int_sample_sales; 
INSERT 0 7 
TRAINING.ADMIN(ADMIN)=>

You can also create the scripts to re-distribute the Netezza table using aginity workbench. Here is the example of scripts that aginity generates for redistribution of Netezza table:

CREATE TABLE USER1."RENAME_DEMO_
(
 ID INTEGER,
 NAME CHARACTER(10)
) DISTRIBUTE ON (ID);

INSERT INTO USER1."RENAME_DEMO_
SELECT * FROM USER1.RENAME_DEMO;
ALTER TABLE USER1."RENAME_DEMO_ SET PRIVILEGES TO USER1.RENAME_DEMO;
ALTER TABLE USER1.RENAME_DEMO RENAME TO USER1."RENAME_DEMO_
ALTER TABLE USER1."RENAME_DEMO_ RENAME TO USER1.RENAME_DEMO;
ALTER TABLE USER1.RENAME_DEMO OWNER TO USER1;
DROP TABLE USER1."RENAME_DEMO_
GENERATE EXPRESS STATISTICS ON USER1.RENAME_DEMO;

Changing Netezza Table Default Distribution key

By default, Netezza will distribute on the first column if you do not specify the distribute on hash(column) command.

To change this default distribution property, you can update the /nz/data/postgresql.conf configuration file to add the below entry:

enable_random_table_distribute = 1

You need to perform nzstop/nzstart the database to use updated properties or you can issue command “pkill -HUP -f postmaster”.

Read:

• Importance of Right Distribution Key
• Netezza nzsql commands and its usage
• nzload command and its Usage
• Netezza Primary Key Constraint and Syntax