这是本节的多页打印视图。点击此处打印.

修整表

1: 修整表示例
2: 创建修整表
3: 必需权限
4: DEFAULT 与 SET USING
5: 修改 SET USING 和 DEFAULT 列
6: 重写 SET USING 查询
7: SET USING 列对许可证限制的影响

高度标准化的数据库设计通常使用星形或雪花架构模型，包含多个大型事实表和多个较小的维度表。查询通常涉及大型事实表和多个维度表之间的联接。根据联接的表数和数据量，这些查询可能会产生大量开销。

为避免此问题，一些用户创建了对查询所需的所有事实表和维度表列进行组合的宽表。这些表可以显著加快查询执行速度。但是，维护冗余的标准化和非标准化数据集有其自身的管理成本。

非标准化或修整表可以最大限度地减少这些问题。修整表可以包含通过查询其他表来获取其值的列。对源表和修整表的操作是解耦的；一个表的更改不会自动传播到另一个表。这最大限度地减少了非标准化表的典型开销。

修整表使用以下一个或两个列约束子句来定义派生列：

使用 `CREATE TABLE` 或 `ALTER TABLE...ADD COLUMN` 创建列时，DEFAULT query-expression 设置列值。
调用函数 REFRESH_COLUMNS 时，SET USING query-expression 设置列值。

在这两种情况下，query-expression 必须只返回一个行和列值，或者不返回。如果查询未返回任何行，则列值设置为 NULL。

与 Vertica 中定义的其他表一样，您可以随时在修整表中添加和移除 DEFAULT 和 SET USING 列。Vertica 强制实施修整表与其查询的表之间的依赖关系。有关详细信息，请参阅修改 SET USING 和 DEFAULT 列。

1 - 修整表示例

在以下示例中，列 orderFact.cust_name 和 orderFact.cust_gender 分别定义为 SET USING 和 DEFAULT 列。两列都通过查询表 custDim 来获取它们的值：

=> CREATE TABLE public.custDim(
       cid int PRIMARY KEY NOT NULL,
       name varchar(20),
       age int,
      gender varchar(1)
);


=> CREATE TABLE public.orderFact(
       order_id int PRIMARY KEY NOT NULL,
       order_date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL,
       cid int REFERENCES public.custDim(cid),
       cust_name varchar(20) SET USING (SELECT name FROM public.custDim WHERE (custDim.cid = orderFact.cid)),
       cust_gender varchar(1) DEFAULT (SELECT gender FROM public.custDim WHERE (custDim.cid = orderFact.cid)),
       amount numeric(12,2)
)
PARTITION BY order_date::DATE GROUP BY CALENDAR_HIERARCHY_DAY(order_date::DATE, 2, 2);

以下 INSERT 命令将数据加载到两个表中：

=> INSERT INTO custDim VALUES(1, 'Alice', 25, 'F');
=> INSERT INTO custDim VALUES(2, 'Boz', 30, 'M');
=> INSERT INTO custDim VALUES(3, 'Eva', 32, 'F');
=>
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(100, 1, 15);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(200, 1, 1000);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(300, 2, -50);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(400, 3, 100);
=> INSERT INTO orderFact (order_id, cid, amount) VALUES(500, 2, 200);
=> COMMIT;

查询此表时，Vertica 返回以下结果集：

=> SELECT * FROM custDim;
 cid | name  | age | gender
-----+-------+-----+--------
   1 | Alice |  25 | F
   2 | Boz   |  30 | M
   3 | Eva   |  32 | F
(3 rows)

=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
 order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
      100 | 2018-12-31 |   1 |           | F           |   15.00
      200 | 2018-12-31 |   1 |           | F           | 1000.00
      300 | 2018-12-31 |   2 |           | M           |  -50.00
      500 | 2018-12-31 |   2 |           | M           |  200.00
      400 | 2018-12-31 |   3 |           | F           |  100.00
(5 rows)

Vertica 自动填充DEFAULT 列 orderFact.cust_gender，但 SET USING 列 orderFact.cust_name 保持为 NULL。您可以通过在修整表 orderFact 上调用函数 REFRESH_COLUMNS 来自动填充此列。此函数调用列 orderFact.cust_name 的 SET USING 查询并从结果集中填充该列：

=> SELECT REFRESH_COLUMNS('orderFact', 'cust_name', 'REBUILD');

    REFRESH_COLUMNS
-------------------------------
refresh_columns completed
(1 row)

=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
 order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
      100 | 2018-12-31 |   1 | Alice     | F           |   15.00
      200 | 2018-12-31 |   1 | Alice     | F           | 1000.00
      300 | 2018-12-31 |   2 | Boz       | M           |  -50.00
      500 | 2018-12-31 |   2 | Boz       | M           |  200.00
      400 | 2018-12-31 |   3 | Eva       | F           |  100.00
(5 rows)

2 - 创建修整表

修整表通常是一个事实表，其中一个或多个列通过 DEFAULT 或 SET USING 约束查询其他表的值。DEFAULT 和 SET USING 约束可用于所有数据类型的列。与其他列一样，您可以在创建修整表时设置这些约束，或者在之后的任何时间通过修改表 DDL 设置这些约束：

CREATE TABLE... (column-name data-type { DEFAULT | SET USING } expression)
ALTER TABLE...ADD COLUMN column-name { DEFAULT | SET USING } expression
ALTER TABLE...ALTER COLUMN column-name { SET DEFAULT | SET USING } expression

在所有情况下，您为这些约束设置的表达式都存储在系统表 COLUMNS 的 COLUMN_DEFAULT 和 COLUMN_SET_USING 列中。

通常，DEFAULT 和 SET USING 支持相同的表达式。这些包括：

查询
同一个表的其他列
字面量（常数）
Vertica 支持的所有运算符
以下函数类别：

有关 DEFAULT 和 SET USING 表达式的详细信息（包括限制），请参阅定义列值。

3 - 必需权限

修整表上的以下操作需要权限，如下所示：

4 - DEFAULT 与 SET USING

修整表中的列可以查询具有 DEFAULT 和 SET USING 约束的其他表。在这两种情况下，查询表中的更改都不会自动传播到修整表。这两个约束的区别如下：

DEFAULT 列

Vertica 通常在将新行添加到修整表时通过 INSERT 和 COPY 等加载操作，或者当您使用指定 DEFAULT 表达式的新列创建或更改表时，对新行执行 DEFAULT 查询。在所有情况下，现有行中的值（包括具有 DEFAULT 表达式的其他列）保持不变。

要刷新列的默认值，您必须在该列上显式调用 UPDATE，如下所示：

=> UPDATE table-name SET column-name=DEFAULT;

SET USING 列

仅当您调用函数 REFRESH_COLUMNS 时，Vertica 才会执行 SET USING 查询。加载操作将新行中的 SET USING 列设置为 NULL。加载后，您必须调用 REFRESH_COLUMNS 从查询表中填充这些列。这在两个方面很有用：您可以将更新修整表的开销推迟到任何方便的时间；您可以重复查询源表以获取新数据。

SET USING 对于引用多个维度表中的数据的大型修整表特别有用。通常，只有一小部分 SET USING 列会发生更改，并且对修整表的查询并不总是需要最新数据。在这种情况下，您可以定期或仅在非高峰时段刷新表的内容。这些策略中的一种或两种可以最大限度地减少开销，并在查询大型数据集时提高性能。

您可以通过在以下两种模式之一中调用 REFRESH _COLUMNS 来控制刷新操作的范围：

UPDATE ：将原始行标记为已删除并用新行替换它们。为了保存这些更新，您必须发出 COMMIT 语句。
REBUILD：替换指定列中的所有数据。重建操作是自动提交的。

如果对修整表进行了分区，则可以通过指定一个或多个分区键来减少在 REBUILD 模式下调用 REFRESH_COLUMNS 的开销。这样做会将重建操作限制到指定的分区。有关详细信息，请参阅基于分区的 REBUILD 操作。

示例

注意

示例使用修整表示例中描述的 custDim 和 orderFact 表。

以下 UPDATE 语句更新 custDim 表：

=> UPDATE custDim SET name='Roz', gender='F' WHERE cid=2;
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT

更改不会传播到修整表 orderFact（其中分别包含 SET USING 和 DEFAULT 列 cust_name 和 cust_gender）：


=> SELECT * FROM custDim ORDER BY cid;
 cid | name  | age | gender
-----+-------+-----+--------
   1 | Alice |  25 | F
   2 | Roz   |  30 | F
   3 | Eva   |  32 | F
(3 rows)

=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
 order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
      100 | 2018-12-31 |   1 | Alice     | F           |   15.00
      200 | 2018-12-31 |   1 | Alice     | F           | 1000.00
      300 | 2018-12-31 |   2 | Boz       | M           |  -50.00
      500 | 2018-12-31 |   2 | Boz       | M           |  200.00
      400 | 2018-12-31 |   3 | Eva       | F           |  100.00
(5 rows)

以下 INSERT 语句调用 cust_gender 列的 DEFAULT 查询并将该列设置为 F。加载操作不会调用 cust_name 列的 SET USING 查询，因此 cust_name 设置为 null：

=> INSERT INTO orderFact(order_id, cid, amount)  VALUES(500, 3, 750);
 OUTPUT
--------
      1
(1 row)

=> COMMIT;
COMMIT
=>  SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
 order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
      100 | 2018-12-31 |   1 | Alice     | F           |   15.00
      200 | 2018-12-31 |   1 | Alice     | F           | 1000.00
      300 | 2018-12-31 |   2 | Boz       | M           |  -50.00
      500 | 2018-12-31 |   2 | Boz       | M           |  200.00
      400 | 2018-12-31 |   3 | Eva       | F           |  100.00
      500 | 2018-12-31 |   3 |           | F           |  750.00
(6 rows)

要更新 cust_name 中的值，请通过调用 REFRESH_COLUMNS 来调用其 SET USING 查询。REFRESH_COLUMNS 执行 cust_name 的 SET USING 查询：它查询表 custDim 中的 name 列并使用以下值更新 cust_name：

将新行中的 cust_name 设置为 Eva。
返回 cid=2 的更新值，并将 Boz 更改为 Roz。

=> SELECT REFRESH_COLUMNS ('orderFact','');
      REFRESH_COLUMNS
---------------------------
 refresh_columns completed
(1 row)

=> COMMIT;
COMMIT
=>  SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
 order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
      100 | 2018-12-31 |   1 | Alice     | F           |   15.00
      200 | 2018-12-31 |   1 | Alice     | F           | 1000.00
      300 | 2018-12-31 |   2 | Roz       | M           |  -50.00
      500 | 2018-12-31 |   2 | Roz       | M           |  200.00
      400 | 2018-12-31 |   3 | Eva       | F           |  100.00
      500 | 2018-12-31 |   3 | Eva       | F           |  750.00
(6 rows)

REFRESH_COLUMNS 仅影响列 cust_name 中的值。gender 列中的值未更改，因此 cid=2 (Roz) 的行设置仍设置为 M。要使用 custDim.gender 中的默认值重新填充 orderFact.cust_gender，请在 orderFact 上调用 UPDATE：

=> UPDATE orderFact SET cust_gender=DEFAULT WHERE cust_name='Roz';
 OUTPUT
--------
      2
(1 row)

=> COMMIT;
COMMIT
=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
 order_id | order_date | cid | cust_name | cust_gender | amount
----------+------------+-----+-----------+-------------+---------
      100 | 2018-12-31 |   1 | Alice     | F           |   15.00
      200 | 2018-12-31 |   1 | Alice     | F           | 1000.00
      300 | 2018-12-31 |   2 | Roz       | F           |  -50.00
      500 | 2018-12-31 |   2 | Roz       | F           |  200.00
      400 | 2018-12-31 |   3 | Eva       | F           |  100.00
      500 | 2018-12-31 |   3 | Eva       | F           |  750.00
(6 rows)

5 - 修改 SET USING 和 DEFAULT 列

注意

示例使用修整表示例中描述的 custDim 和 orderFact 表。

修改 SET USING 和 DEFAULT 表达式

ALTER TABLE...ALTER COLUMN 可以将现有列设置为 SET USING 或 DEFAULT，或者更改现有 SET USING 或 DEFAULT 列的查询表达式。在这两种情况下，现有值都保持不变。Vertica 在以下情况下会刷新列值：

DEFAULT 列：仅在加载新行或调用 UPDATE 将列值设置为 DEFAULT 时刷新。
SET USING 列：仅当您在表上调用 REFRESH_COLUMNS 时才刷新。

例如，可以将整个列设置为 NULL，如下所示：

=> ALTER TABLE orderFact ALTER COLUMN cust_name SET USING NULL;
ALTER TABLE
=> SELECT REFRESH_COLUMNS('orderFact', 'cust_name', 'REBUILD');
REFRESH_COLUMNS
---------------------------
refresh_columns completed
(1 row)

=> SELECT order_id, order_date::date, cid, cust_name, cust_gender, amount FROM orderFact ORDER BY cid;
  order_id | order_date | cid | cust_name | cust_gender | amount
 ----------+------------+-----+-----------+-------------+---------
    100 | 2018-12-31 |   1 |           | F           |   15.00
    200 | 2018-12-31 |   1 |           | F           | 1000.00
    300 | 2018-12-31 |   2 |           | M           |  -50.00
    500 | 2018-12-31 |   2 |           | M           |  200.00
    400 | 2018-12-31 |   3 |           | F           |  100.00
(5 rows)

有关详细信息，请参阅定义列值

移除 SET USING 和 DEFAULT 约束

使用 ALTER TABLE...ALTER COLUMN 移除列的 SET USING 或 DEFAULT 约束，如下所示：

ALTER TABLE table-name ALTER COLUMN column-name DROP { SET USING | DEFAULT };

Vertica 会从指定列中移除约束，但该列及其数据不受影响。例如：

=> ALTER TABLE orderFact ALTER COLUMN cust_name DROP SET USING;
ALTER TABLE

删除由 SET USING 或 DEFAULT 查询的列

Vertica 强制实施修整表与其查询的表之间的依赖关系。除非删除操作还包含 CASCADE 选项，否则尝试删除查询的列或其表会返回错误。Vertica 通过从修整表中移除 SET USING 或 DEFAULT 约束来实施 CASCADE。表列及其数据不受影响。

例如，尝试删除表 custDim 中的列 name 会返回回退错误，因为该列由 SET USING 列 orderFact.cust_gender 引用：

=> ALTER TABLE custDim DROP COLUMN gender;
ROLLBACK 7301:  Cannot drop column "gender" since it is referenced in the default expression of table "public.orderFact", column "cust_gender"

要删除此列，请使用 CASCADE 选项：

=> ALTER TABLE custDim DROP COLUMN gender CASCADE;
ALTER TABLE

在执行操作过程中，Vertica 从 orderFact.cust_gender 中移除 DEFAULT 约束。不过，cust_gender 会保留先前从删除的 custDim.gender 列中查询的数据：

=> SELECT EXPORT_TABLES('','orderFact');
                                                EXPORT_TABLES
------------------------------------------------------------------------------------------------------------
CREATE TABLE public.orderFact
(
    order_id int NOT NULL,
    order_date timestamp NOT NULL DEFAULT (now())::timestamptz(6),
    cid int,
    cust_name varchar(20),
    cust_gender varchar(1) SET USING NULL,
    amount numeric(12,2),
    CONSTRAINT C_PRIMARY PRIMARY KEY (order_id) DISABLED
)
PARTITION BY ((orderFact.order_date)::date) GROUP BY (CASE WHEN ("datediff"('year', (orderFact.order_date)::date, ((now())::timestamptz(6))::date) >= 2) THEN (date_trunc('year', (orderFact.order_date)::date))::date WHEN ("datediff"('month', (orderFact.order_date)::date, ((now())::timestamptz(6))::date) >= 2) THEN (date_trunc('month', (orderFact.order_date)::date))::date ELSE (orderFact.order_date)::date END);
ALTER TABLE public.orderFact ADD CONSTRAINT C_FOREIGN FOREIGN KEY (cid) references public.custDim (cid);

(1 row)

=> SELECT * FROM orderFact;
 order_id |         order_date         | cid | cust_name | cust_gender | amount
----------+----------------------------+-----+-----------+-------------+---------
      400 | 2021-01-05 13:27:56.026115 |   3 |           | F           |  100.00
      300 | 2021-01-05 13:27:56.026115 |   2 |           | F           |  -50.00
      200 | 2021-01-05 13:27:56.026115 |   1 |           | F           | 1000.00
      500 | 2021-01-05 13:30:18.138386 |   3 |           | F           |  750.00
      100 | 2021-01-05 13:27:56.026115 |   1 |           | F           |   15.00
      500 | 2021-01-05 13:27:56.026115 |   2 |           | F           |  200.00
(6 rows)

6 - 重写 SET USING 查询

当您在平展表的 SET USING（或 DEFAULT USING）列中调用 REFRESH_COLUMNS 时，它将通过联接目标表和源表来执行 SET USING 查询。默认情况下，源表始终为联接的内表。大多数情况下，源表的基数小于目标表，因此 REFRESH_COLUMNS 有效地执行联接。

有时，特别是当您在分区表上调用 REFRESH_COLUMNS 时，源表有可能比目标表更大。在这种情况下，联接操作的性能可能欠佳。

您可以通过启用配置参数 RewriteQueryForLargeDim 来解决此问题。启用 (1) 后，Vertica 将反转目标表和源表之间的内部和外部联接，以此重写查询。

重要

仅当 SET USING 源数据所在的表大于目标表时，才启用此参数。如果源数据所在的表小于目标表，则启用 RewriteQueryForLargeDim 会对刷新性能产生不利影响。

7 - SET USING 列对许可证限制的影响

Vertica 不会将非标准化列中的数据计入原始数据许可证限制。SET USING 列通过查询其他表中的列来获取它们的数据。只有源表中的数据才计入原始数据许可证限制。

有关 SET USING 限制的列表，请参阅定义列值。

您可以使用以下命令移除 SET USING 列，使其计入许可证限制：

=> ALTER TABLE table1 ALTER COLUMN column1 DROP SET USING;