DISTSTYLE and SORTKEY examples
The examples in this topic show you how to perform DISTSTYLE and SORTKEY changes, using ALTER MATERIALIZED VIEW.
The following example queries show how to alter a DISTSTYLE KEY DISTKEY column using a sample base table:
CREATE TABLE base_inventory( inv_date_sk int4 not null, inv_item_sk int4 not null, inv_warehouse_sk int4 not null, inv_quantity_on_hand int4 ); INSERT INTO base_inventory VALUES(1,1,1,1); CREATE MATERIALIZED VIEW inventory DISTSTYLE EVEN as SELECT * FROM base_inventory; SELECT "table", DISTSTYLE FROM svv_table_info WHERE "table" = 'inventory'; ALTER MATERIALIZED VIEW inventory ALTER DISTSTYLE KEY DISTKEY inv_warehouse_sk; SELECT "table", DISTSTYLE FROM svv_table_info where "table" = 'inventory'; ALTER MATERIALIZED VIEW inventory ALTER DISTKEY inv_item_sk; SELECT "table", diststyle from svv_table_info where "table" = 'inventory';
Alter a materialized view to DISTSTYLE ALL:
CREATE TABLE base_inventory( inv_date_sk int4 not null, inv_item_sk int4 not null, inv_warehouse_sk int4 not null, inv_quantity_on_hand int4 ); INSERT INTO base_inventory values(1,1,1,1); CREATE MATERIALIZED VIEW inventory DISTSTYLE EVEN as SELECT * FROM base_inventory; SELECT "table", DISTSTYLE FROM svv_table_info WHERE "table" = 'inventory';
The following commands show ALTER MATERIALIZED VIEW SORTKEY examples, using a sample base table:
CREATE MATERIALIZED VIEW base_inventory (c0 int, c1 int); CREATE MATERIALIZED VIEW inventory interleaved sortkey(c0, c1) as SELECT * FROM base_inventory; SELECT "table", sortkey1 FROM svv_table_info WHERE "table" = 'inventory'; ALTER MATERIALIZED VIEW t1 alter sortkey(c0, c1); SELECT "table", diststyle, sortkey_num FROM svv_table_info WHERE "table" = 'inventory'; ALTER MATERIALIZED VIEW t1 alter sortkey none; SELECT "table", diststyle, sortkey_num FROM svv_table_info WHERE "table" = 'inventory'; ALTER MATERIALIZED VIEW t1 alter sortkey(c0); SELECT "table", diststyle, sortkey_num FROM svv_table_info WHERE "table" = 'inventory';