DISTYLE 和 SORTKEY 示例

本主题中的示例向您展示了如何使用 ALTER MATERIAD VIEW 对 DISTYLE 和 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';

将实体化视图更改为 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 SORTKEY 示例：

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';