ALTER MATERIALIZED VIEW の DISTSTYLE と SORTKEY の例 - Amazon Redshift

ALTER MATERIALIZED VIEW の DISTSTYLE と SORTKEY の例

このトピックの例では、ALTER MATERIALIZED VIEW を使用して DISTSTYLE および SORTKEY の変更を実行する方法を示します。

次のクエリ例は、サンプルベーステーブルを使用して DISTSTYLE KEY DISTKEY 列を変更する方法を示しています。

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'; DROP TABLE base_inventory CASCADE;

マテリアライズドビューを 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'; ALTER MATERIALIZED VIEW inventory ALTER diststyle ALL; SELECT "table", diststyle FROM svv_table_info WHERE "table" = 'inventory'; DROP TABLE base_inventory CASCADE;

次のコマンドは、サンプルベーステーブルを使用した ALTER MATERIALIZED VIEW SORTKEY の例を示しています。

CREATE TABLE base_inventory (c0 int, c1 int); INSERT INTO base_inventory VALUES(1,1); 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 inventory ALTER sortkey(c0, c1); SELECT "table", diststyle, sortkey_num FROM svv_table_info WHERE "table" = 'inventory'; ALTER materialized VIEW inventory ALTER sortkey NONE; SELECT "table", diststyle, sortkey_num FROM svv_table_info WHERE "table" = 'inventory'; ALTER materialized VIEW inventory ALTER sortkey(c0); SELECT "table", diststyle, sortkey_num FROM svv_table_info WHERE "table" = 'inventory'; DROP TABLE base_inventory CASCADE;