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

记录 Database Designer 的投影数据

1: 启用 Database Designer 的日志记录
2: 查看 Database Designer 日志
3: Database Designer 日志示例数据

当您运行 Database Designer 时，优化器会根据您指定的选项提出一组理想的投影建议。当您部署设计时，Database Designer 会根据这些投影创建设计。但是，由于空间或预算约束，Database Designer 可能无法创建所有建议的投影。此外，Database Designer 可能无法使用理想条件实施投影。

要获取有关投影的信息，请先启用 Database Designer 日志记录功能。启用后，Database Designer 会将有关所建议投影的信息存储在两个数据收集器表中。当 Database Designer 部署设计后，这些日志便会包含实际创建的所建议投影的相关信息。部署后，日志包含下列项的相关信息：

优化器提出的投影建议
Database Designer 在部署设计时实际创建的投影
Database Designer 创建但不具备优化器所标识的理想条件的投影
用于创建所有投影的 DDL
列优化

如果您未立即部署设计，请查看日志以确定是否需要进行更改。如果已经部署设计，则仍可以手动创建某些 Database Designer 未创建的投影。

要启用 Database Designer 日志记录功能，请参阅启用 Database Designer 的日志记录。

要查看记录的信息，请参阅查看 Database Designer 日志。

1 - 启用 Database Designer 的日志记录

默认情况下，不会记录优化器提出并且 Database Designer 部署的投影的相关信息。

要启用 Database Designer 日志记录，请输入以下命令：

=> ALTER DATABASE DEFAULT SET DBDLogInternalDesignProcess = 1;

要禁用 Database Designer 日志记录，请输入以下命令：

=> ALTER DATABASE DEFAULT SET DBDLogInternalDesignProcess = 0;

另请参阅

2 - 查看 Database Designer 日志

您可以在两个数据收集器表中找到 Database Designer 考虑和部署的投影的相关数据：

DC_DESIGN_PROJECTION_CANDIDATES
DC_DESIGN_QUERY_PROJECTION_CANDIDATES

DC_DESIGN_PROJECTION_CANDIDATES

DC_DESIGN_PROJECTION_CANDIDATES 表包含有关优化器建议的所有投影的信息。该表还包括创建它们的 DDL。is_a_winner 字段指示此投影是否是实际部署的设计的一部分。要查看 DC_DESIGN_PROJECTION_CANDIDATES 表，请输入：

=> SELECT *  FROM DC_DESIGN_PROJECTION_CANDIDATES;

DC_DESIGN_QUERY_PROJECTION_CANDIDATES

DC_DESIGN_QUERY_PROJECTION_CANDIDATES 表列出所有设计查询的计划特征。

可能的特征包括：

FULLY DISTRIBUTED JOIN
MERGE JOIN
GROUPBY PIPE
FULLY DISTRIBUTED GROUPBY
RLE PREDICATE
VALUE INDEX PREDICATE
LATE MATERIALIZATION

对于所有设计查询，DC_DESIGN_QUERY_PROJECTION_CANDIDATES 表包括以下计划特征信息：

优化程序路径成本。
Database Designer 优势。
理想的计划特征及其描述，用于标识应如何优化引用的投影。
如果已部署设计，实际计划特征及其描述将包括在表中。此信息用于标识所引用投影的实际优化方式。

由于大多数投影都具有多个优化，因此每个投影通常具有多个行。要查看 DC_DESIGN_QUERY_PROJECTION_CANDIDATES 表，请输入：

=> SELECT *  FROM DC_DESIGN_QUERY_PROJECTION_CANDIDATES;

要查看这些表中的示例数据，请参阅 Database Designer 日志示例数据。

3 - Database Designer 日志示例数据

在以下示例中，Database Designer 在为 VMart 示例数据库创建全面设计之后，创建了日志。输出显示 DC_DESIGN_PROJECTION_CANDIDATES 表中的两个记录。

第一个记录包含有关 customer_dimension_dbd_1_sort_$customer_gender$__$annual_income$ projection 的信息。此记录包含 Database Designer 用于创建投影的 CREATE PROJECTION 语句。is_a_winner 列为 t，表示 Database Designer 在部署设计时创建了此投影。

第二个记录包含有关 product_dimension_dbd_2_sort_$product_version$__$product_key$ projection 的信息。对于此投影，is_a_winner 列为 f。优化器建议 Database Designer 在设计过程中创建此投影。但是，Database Designer 在部署设计时，没有创建该投影。日志包含 CREATE PROJECTION 语句的 DDL。如果要手动添加投影，您可以使用该 DDL。有关详细信息，请参阅手动创建设计。

=> SELECT * FROM dc_design_projection_candidates;
-[ RECORD 1 ]--------+---------------------------------------------------------------
time                 | 2014-04-11 06:30:17.918764-07
node_name            | v_vmart_node0001
session_id           | localhost.localdoma-931:0x1b7
user_id              | 45035996273704962
user_name            | dbadmin
design_id            | 45035996273705182
design_table_id      | 45035996273720620
projection_id        | 45035996273726626
iteration_number     | 1
projection_name      | customer_dimension_dbd_1_sort_$customer_gender$__$annual_income$
projection_statement | CREATE PROJECTION v_dbd_sarahtest_sarahtest."customer_dimension_dbd_1_
            sort_$customer_gender$__$annual_income$"
(
customer_key ENCODING AUTO,
customer_type ENCODING AUTO,
customer_name ENCODING AUTO,
customer_gender ENCODING RLE,
title ENCODING AUTO,
household_id ENCODING AUTO,
customer_address ENCODING AUTO,
customer_city ENCODING AUTO,
customer_state ENCODING AUTO,
customer_region ENCODING AUTO,
marital_status ENCODING AUTO,
customer_age ENCODING AUTO,
number_of_children ENCODING AUTO,
annual_income ENCODING AUTO,
occupation ENCODING AUTO,
largest_bill_amount ENCODING AUTO,
store_membership_card ENCODING AUTO,
customer_since ENCODING AUTO,
deal_stage ENCODING AUTO,
deal_size ENCODING AUTO,
last_deal_update ENCODING AUTO
)
AS
SELECT customer_key,
customer_type,
customer_name,
customer_gender,
title,
household_id,
customer_address,
customer_city,
customer_state,
customer_region,
marital_status,
customer_age,
number_of_children,
annual_income,
occupation,
largest_bill_amount,
store_membership_card,
customer_since,
deal_stage,
deal_size,
last_deal_update
FROM public.customer_dimension
ORDER BY customer_gender,
annual_income
UNSEGMENTED ALL NODES;
is_a_winner          | t
-[ RECORD 2 ]--------+-------------------------------------------------------------
time                 | 2014-04-11 06:30:17.961324-07
node_name            | v_vmart_node0001
session_id           | localhost.localdoma-931:0x1b7
user_id              | 45035996273704962
user_name            | dbadmin
design_id            | 45035996273705182
design_table_id      | 45035996273720624
projection_id        | 45035996273726714
iteration_number     | 1
projection_name      | product_dimension_dbd_2_sort_$product_version$__$product_key$
projection_statement | CREATE PROJECTION v_dbd_sarahtest_sarahtest."product_dimension_dbd_2_
        sort_$product_version$__$product_key$"
(
product_key ENCODING AUTO,
product_version ENCODING RLE,
product_description ENCODING AUTO,
sku_number ENCODING AUTO,
category_description ENCODING AUTO,
department_description ENCODING AUTO,
package_type_description ENCODING AUTO,
package_size ENCODING AUTO,
fat_content ENCODING AUTO,
diet_type ENCODING AUTO,
weight ENCODING AUTO,
weight_units_of_measure ENCODING AUTO,
shelf_width ENCODING AUTO,
shelf_height ENCODING AUTO,
shelf_depth ENCODING AUTO,
product_price ENCODING AUTO,
product_cost ENCODING AUTO,
lowest_competitor_price ENCODING AUTO,
highest_competitor_price ENCODING AUTO,
average_competitor_price ENCODING AUTO,
discontinued_flag ENCODING AUTO
)
AS
SELECT product_key,
product_version,
product_description,
sku_number,
category_description,
department_description,
package_type_description,
package_size,
fat_content,
diet_type,
weight,
weight_units_of_measure,
shelf_width,
shelf_height,
shelf_depth,
product_price,
product_cost,
lowest_competitor_price,
highest_competitor_price,
average_competitor_price,
discontinued_flag
FROM public.product_dimension
ORDER BY product_version,
product_key
UNSEGMENTED ALL NODES;
is_a_winner          | f
.
.
.

以下示例显示了 DC_DESIGN_QUERY_PROJECTION_CANDIDATES 中两个记录的内容。这两个行应用于投影 ID 45035996273726626。

在第一个记录中，优化器建议 Database Designer 为 GROUPBY PIPE 算法优化 customer_gender 列。

在第二个记录中，优化器建议 Database Designer 为后续执行的实体化优化 public.customer_dimension 表。后续执行的实体化可以提高可能会溢出到磁盘的联接的性能。

=> SELECT * FROM dc_design_query_projection_candidates;
-[ RECORD 1 ]-----------------+------------------------------------------------------------
time                           | 2014-04-11 06:30:17.482377-07
node_name                      | v_vmart_node0001
session_id                     | localhost.localdoma-931:0x1b7
user_id                        | 45035996273704962
user_name                      | dbadmin
design_id                      | 45035996273705182
design_query_id                | 3
iteration_number               | 1
design_table_id                | 45035996273720620
projection_id                  | 45035996273726626
ideal_plan_feature             | GROUP BY PIPE
ideal_plan_feature_description | Group-by pipelined on column(s) customer_gender
dbd_benefits                   | 5
opt_path_cost                  | 211
-[ RECORD 2 ]-----------------+------------------------------------------------------------
time                           | 2014-04-11 06:30:17.48276-07
node_name                      | v_vmart_node0001
session_id                     | localhost.localdoma-931:0x1b7
user_id                        | 45035996273704962
user_name                      | dbadmin
design_id                      | 45035996273705182
design_query_id                | 3
iteration_number               | 1
design_table_id                | 45035996273720620
projection_id                  | 45035996273726626
ideal_plan_feature             | LATE MATERIALIZATION
ideal_plan_feature_description | Late materialization on table public.customer_dimension
dbd_benefits                   | 4
opt_path_cost                  | 669
.
.
.

您可以查看 Database Designer 为其创建的投影而实施的实际计划功能。为此，请查询 V_INTERNAL.DC_DESIGN_QUERY_PROJECTIONS 表：

=> select * from v_internal.dc_design_query_projections;
-[ RECORD 1 ]-------------------+-------------------------------------------------------------
time                            | 2014-04-11 06:31:41.19199-07
node_name                       | v_vmart_node0001
session_id                      | localhost.localdoma-931:0x1b7
user_id                         | 45035996273704962
user_name                       | dbadmin
design_id                       | 45035996273705182
design_query_id                 | 1
projection_id                   | 2
design_table_id                 | 45035996273720624
actual_plan_feature             | RLE PREDICATE
actual_plan_feature_description | RLE on predicate column(s) department_description
dbd_benefits                    | 2
opt_path_cost                   | 141
-[ RECORD 2 ]-------------------+-------------------------------------------------------------
time                            | 2014-04-11 06:31:41.192292-07
node_name                       | v_vmart_node0001
session_id                      | localhost.localdoma-931:0x1b7
user_id                         | 45035996273704962
user_name                       | dbadmin
design_id                       | 45035996273705182
design_query_id                 | 1
projection_id                   | 2
design_table_id                 | 45035996273720624
actual_plan_feature             | GROUP BY PIPE
actual_plan_feature_description | Group-by pipelined on column(s) fat_content
dbd_benefits                    | 5
opt_path_cost                   | 155