这看起来很简单，因为 sel 中只有 1 列，但我没有得到调谐方法。
sel distinct (ColID ) from 1Billiontb btb
Join smalltb1 stb1 on btb.col_1=stb1.col_1
Join stb2 on btb.col_2=stb2.col_2
join stb3 on btb.col_3=stb3.col_3
etc till 12
Join (some medium size tables )
where stb1 colx in ( value-list1 )
OR stb2 coly in ( same value-list1 as above )
OR stb3 colz in ( same value-list1 as above )
OR stb4 cola in ( same value-list1 as above )
continued till...stb12
where <some more filters on medium size tables>
我做了
什么根据 ColID 值的基数将非重复值更改为分组依据，我将中等大小表上的内部联接更改为存在条件，这使 CPU 下降了大约 20K。
它仍然像旧车
一样运行现在有一些我需要一些帮助来理解
- stb1 stb2 stb3 都是视图，在打开它们时 - 它们都有相同的的定义（谁创建了它们 - 认为是 IBM 的人）
SEL * 来自 STB1 减去 SEL * 来自 STB2 减去 sel * 来自 SBT3等0 行
因此，它实际上将事实表的各个列连接到维度表的 SAME 列，并且一个或 2 个想法会让人意识到从同一个表一次又一次地调用同一个 col 与再次别名同一个表并调用同一个 col
不同
sel distinct （ColID ） from 1Billiontb btb 加入 smalltb1 stb1 on btb.col_1=stb1.col_1 在 btb.col_2=stb2.col_2 加入 stb2 在BTB上加入STB3，col_3=stb3.col_3 等直到 9
与
sel distinct （ColID ） from 1Billiontb btb 加入 （smalltb1 stb1 on btb.col_1=stb1.col_1 和btb.col_2=stb1.col_1 和btb.col_3=stb1.col_1 等直到 9 ）
即使 STB1 和 STB2 .等与前一种情况相同，实际上涉及更多表。在这里，我需要帮助可视化正在发生的事情。我知道上述 2 个是不同的，因为涉及的表格更多，但究竟是什么使它们在 o/p 方面有所不同
- 此条件
sel （ColID ） 来自 1Billiontb btb 加入 smalltb1 stb1 on btb.col_1=stb1.col_1 联接（一些中等大小的表） 其中 stb1 colx 在 （ 值列表 1 ） 哪里 联盟 sel （ColID ） 来自 1Billiontb btb 加入 smalltb2 stb2 on btb.col_2=stb2.col_2联接（一些中等大小的表） 哪里 STB2 Coly In （ 同上值列表1 ） 联盟 直到。。。9 分组依据 1# 与原始查询相比，我正在用 UNION 替换 OR 部分# stb1.col1 与 stb2.col2 别名相同，但视图中的 sel 相同
它们在测试数据上给出不同的结果。我的理解是OR可以被UNION取代，但这里的结果是不同的。有人可以解释一下吗
- 也因为过滤器中的OR条件，我无法进一步重写查询以提高性能，此 OR 条件会导致 PJ。所以我正在寻找改进建议。如果这种查询模式看起来很熟悉，并且是否有一些替换的最佳做法
以下是原始报告的解释计划：
Explain
CREATE MULTISET VOLATILE TABLE VT AS (
SELECT DISTINCT d.in_ID FROM
DB.FACT_BODtl d
JOIN cd_vw1 dc1 ON (d.col_CD1=dc1.col_CD1)
JOIN cd_vw2 dc2 ON (d.col_CD2=dc2.col_CD2)
JOIN cd_vw3 dc3 ON (d.col_CD3=dc3.col_CD3)
JOIN cd_vw4 dc4 ON (d.col_CD4=dc4.col_CD4)
JOIN cd_vw5 dc5 ON (d.col_CD5=dc5.col_CD5)
JOIN cd_vw6 dc6 ON (d.col_CD6=dc6.col_CD6)
JOIN cd_vw7 dc7 ON (d.col_CD7=dc7.col_CD7)
JOIN cd_vw8 dc8 ON (d.col_CD8=dc8.col_CD8)
JOIN cd_vw9 dc9 ON (d.col_CD9=dc9.col_CD9)
JOIN DM_NBR NBR ON (d.fact_SysID = NBR.fact_SysID)
JOIN PRICE PRC ON (d.in_ID=PRC.in_ID)
JOIN Dm_LabDt ENDDT ON (d.dt_tb_sysID=ENDDT.DT_SYS_ID )
JOIN CC_sg CSG ON (PRC.client_sysID=CSG.client_sysID)
JOIN COMPONENT PROD ON (CSG.prdt_cd=PROD.prdt_cd)
WHERE
NBR.COLX_CD IN ('163000')
AND EXTRACT (YEAR FROM ENDDT.Fdt) = '2015'
AND PROD.Prd_CD1 IN ('COM')
AND dc1.d_cd1 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc2.d_cd2 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc3.d_cd3 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc4.d_cd4 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc5.d_cd5 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc6.d_cd6 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc7.d_cd7 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc8.d_cd8 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
OR dc9.d_cd9 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')
)
WITH DATA
ON COMMIT PRESERVE ROWS;
This query is optimized using type 2 profile insert-sel, profileid
10001.
1) First, we lock DB.DM_CODE_TABLE in view
DB.cd_vw4 for access, we lock
DB.FACT_FACT_BODtl in view DB.FACT_BODtl for access,
we lock DB.DIM_CC_sg in view
DB.CC_sg for access, we lock
DB.DIM_COMPONENT_2 in view DB.COMPONENT for access, we
lock DB.DIM_PRICE in view DB.PRICE for
access, we lock DB.DIM_DM_NBR in view
DB.DM_NBR for access, and we lock DB.DIM_DATE in
view DB.Dm_LabDt for access.
2) Next, we create the table header.
3) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from DB.DIM_COMPONENT_2
in view DB.COMPONENT by way of an all-rows scan with no
residual conditions into Spool 4 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. The size
of Spool 4 is estimated with high confidence to be 2,550,240
rows (68,856,480 bytes). The estimated time for this step is
0.06 seconds.
2) We do an all-AMPs RETRIEVE step from DB.DIM_DATE in
view DB.Dm_LabDt by way of an all-rows scan with no
residual conditions into Spool 5 (all_amps) (compressed
columns allowed), which is duplicated on all AMPs. The size
of Spool 5 is estimated with high confidence to be 32,399,136
rows (615,583,584 bytes). The estimated time for this step
is 0.19 seconds.
3) We do an all-AMPs RETRIEVE step from
DB.DM_CODE_TABLE in view DB.cd_vw4
by way of an all-rows scan with no residual conditions split
into Spool 6 (all_amps) with a condition of ("DIAG_CD_SYS_ID
IN (:*)") to qualify rows matching skewed rows of the skewed
relation (compressed columns allowed) and Spool 7 (all_amps)
with remaining rows (compressed columns allowed) fanned out
into 4 hash join partitions. Spool 6 is built locally on the
AMPs. The size of Spool 6 is estimated with high confidence
to be 3 rows. Spool 7 is duplicated on all AMPs. The size
of Spool 7 is estimated with high confidence to be
132,346,872 rows. The estimated time for this step is 0.85
seconds.
4) We do an all-AMPs JOIN step from Spool 5 (Last Use) by way of an
all-rows scan, which is joined to DB.FACT_FACT_BODtl in view
DB.FACT_BODtl by way of an all-rows scan with no residual
conditions. Spool 5 and DB.FACT_FACT_BODtl are joined using
a dynamic hash join, with a join condition of (
"DB.FACT_FACT_BODtl.dt_tb_sysID = DT_SYS_ID"). The
result is split into Spool 8 (all_amps) with a condition of (
"col_CD4 IN (:*)") to qualify skewed rows fanned out into
50 hash join partitions and Spool 9 (all_amps) with remaining rows
fanned out into 4 hash join partitions. Spool 8 is built locally
on the AMPs. The size of Spool 8 is estimated with low confidence
to be 2,097,544,557 rows (123,755,128,863 bytes). Spool 9 is
built locally on the AMPs. The size of Spool 9 is estimated with
low confidence to be 505,740,248 rows (29,838,674,632 bytes). The
estimated time for this step is 54.89 seconds.
5) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 6 (Last Use) by
way of an all-rows scan into Spool 10 (all_amps) (compressed
columns allowed) fanned out into 50 hash join partitions,
which is duplicated on all AMPs. The size of Spool 10 is
estimated with high confidence to be 1,512 rows (37,800
bytes). The estimated time for this step is 0.01 seconds.
2) We do an all-AMPs RETRIEVE step from
DB.DM_CODE_TABLE in view DB.cd_vw5
by way of an all-rows scan with no residual conditions
locking for access split into Spool 11 (all_amps) with a
condition of ("DG_CD_ID IN (:*)") to qualify rows
matching skewed rows of the skewed relation (compressed
columns allowed) and Spool 12 (all_amps) with remaining rows
(compressed columns allowed) fanned out into 4 hash join
partitions. Spool 11 is built locally on the AMPs. The size
of Spool 11 is estimated with high confidence to be 2 rows.
Spool 12 is duplicated on all AMPs. The size of Spool 12 is
estimated with high confidence to be 132,347,376 rows. The
estimated time for this step is 0.85 seconds.
6) We do an all-AMPs JOIN step from Spool 10 (Last Use) by way of an
all-rows scan, which is joined to Spool 8 (Last Use) by way of an
all-rows scan. Spool 10 and Spool 8 are joined using a hash join
of 50 partitions, with a join condition of ("col_CD4 =
DG_CD_ID"). The result goes into Spool 13 (all_amps), which
is built locally on the AMPs. The size of Spool 13 is estimated
with low confidence to be 2,097,544,557 rows (132,145,307,091
bytes). The estimated time for this step is 3.04 seconds.
7) We do an all-AMPs JOIN step from Spool 7 (Last Use) by way of an
all-rows scan, which is joined to Spool 9 (Last Use) by way of an
all-rows scan. Spool 7 and Spool 9 are joined using a hash join
of 4 partitions, with a join condition of ("col_CD4 =
DG_CD_ID"). The result goes into Spool 13 (all_amps), which
is built locally on the AMPs. The size of Spool 13 is estimated
with low confidence to be 505,740,248 rows (31,861,635,624 bytes).
The estimated time for this step is 36.84 seconds.
8) We execute the following steps in parallel.
1) We do an all-AMPs RETRIEVE step from Spool 13 (Last Use) by
way of an all-rows scan split into Spool 14 (all_amps) with a
condition of ("col_CD5 IN (:*)") to qualify skewed
rows (compressed columns allowed) fanned out into 50 hash
join partitions and Spool 15 (all_amps) with remaining rows
(compressed columns allowed) fanned out into 4 hash join
partitions. Spool 14 is built locally on the AMPs. The size
of Spool 14 is estimated with low confidence to be
2,355,693,166 rows. Spool 15 is built locally on the AMPs.
The size of Spool 15 is estimated with low confidence to be
247,591,639 rows. The estimated time for this step is 36.84
seconds.
2) We do an all-AMPs RETRIEVE step from Spool 11 (Last Use) by
way of an all-rows scan into Spool 16 (all_amps) (compressed
columns allowed) fanned out into 50 hash join partitions,
which is duplicated on all AMPs. The size of Spool 16 is
estimated with high confidence to be 1,008 rows (25,200
bytes). The estimated time for this step is 0.01 seconds.
3) We do an all-AMPs RETRIEVE step from
DB.DM_CODE_TABLE in view DB.cd_vw6
by way of an all-rows scan with no residual conditions
locking for access into Spool 17 (all_amps) (compressed
columns allowed) fanned out into 4 hash join partitions,
which is duplicated on all AMPs. The size of Spool 17 is
estimated with high confidence to be 132,348,384 rows (
3,308,709,600 bytes). The estimated time for this step is
0.84 seconds.
9) We do an all-AMPs JOIN step from Spool 16 (Last Use) by way of an
all-rows scan, which is joined to Spool 14 (Last Use) by way of an
all-rows scan. Spool 16 and Spool 14 are joined using a hash join
of 50 partitions, with a join condition of ("col_CD5 =
DG_CD_ID"). The result goes into Spool 18 (all_amps) fanned
out into 4 hash join partitions, which is built locally on the
AMPs. The size of Spool 18 is estimated with low confidence to be
2,355,693,166 rows (157,831,442,122 bytes). The estimated time
for this step is 3.30 seconds.
10) We do an all-AMPs JOIN step from Spool 12 (Last Use) by way of an
all-rows scan, which is joined to Spool 15 (Last Use) by way of an
all-rows scan. Spool 12 and Spool 15 are joined using a hash join
of 4 partitions, with a join condition of ("col_CD5 =
DG_CD_ID"). The result goes into Spool 18 (all_amps) fanned
out into 4 hash join partitions, which is built locally on the
AMPs. The size of Spool 18 is estimated with low confidence to be
247,591,639 rows (16,588,639,813 bytes). The estimated time for
this step is 36.63 seconds.
11) We do an all-AMPs JOIN step from Spool 17 by way of an all-rows
scan, which is joined to Spool 18 (Last Use) by way of an all-rows
scan. Spool 17 and Spool 18 are joined using a hash join of 4
partitions, with a join condition of ("col_CD6 =
DG_CD_ID"). The result goes into Spool 20 (all_amps)
(compressed columns allowed) fanned out into 4 hash join
partitions, which is built locally on the AMPs. The size of Spool
20 is estimated with index join confidence to be 2,603,284,805
rows (184,833,221,155 bytes). The estimated time for this step is
45.65 seconds.
12) We do an all-AMPs JOIN step from Spool 17 by way of an all-rows
scan, which is joined to Spool 20 (Last Use) by way of an all-rows
scan. Spool 17 and Spool 20 are joined using a hash join of 4
partitions, with a join condition of ("col_CD7 =
DG_CD_ID"). The result goes into Spool 22 (all_amps)
(compressed columns allowed) fanned out into 4 hash join
partitions, which is built locally on the AMPs. The size of Spool
22 is estimated with index join confidence to be 2,603,284,805
rows (195,246,360,375 bytes). The estimated time for this step is
46.96 seconds.
13) We do an all-AMPs JOIN step from Spool 17 by way of an all-rows
scan, which is joined to Spool 22 (Last Use) by way of an all-rows
scan. Spool 17 and Spool 22 are joined using a hash join of 4
partitions, with a join condition of ("col_CD8 =
DG_CD_ID"). The result goes into Spool 24 (all_amps)
(compressed columns allowed) fanned out into 4 hash join
partitions, which is built locally on the AMPs. The size of Spool
24 is estimated with index join confidence to be 2,603,284,805
rows (205,659,499,595 bytes). The estimated time for this step is
48.26 seconds.
14) We do an all-AMPs RETRIEVE step from DB.DIM_DM_NBR in
view DB.DM_NBR by way of an all-rows scan with no
residual conditions into Spool 25 (all_amps) (compressed columns
allowed), which is duplicated on all AMPs. The size of Spool 25
is estimated with high confidence to be 4,421,592 rows (
92,853,432 bytes). The estimated time for this step is 0.06
seconds.
15) We do an all-AMPs JOIN step from Spool 17 by way of an all-rows
scan, which is joined to Spool 24 (Last Use) by way of an all-rows
scan. Spool 17 and Spool 24 are joined using a hash join of 4
partitions, with a join condition of ("col_CD9 =
DG_CD_ID"). The result goes into Spool 26 (all_amps)
(compressed columns allowed), which is built locally on the AMPs.
The size of Spool 26 is estimated with index join confidence to be
2,603,284,805 rows (216,072,638,815 bytes). The estimated time
for this step is 49.57 seconds.
16) We do an all-AMPs JOIN step from Spool 25 (Last Use) by way of an
all-rows scan, which is joined to Spool 26 (Last Use) by way of an
all-rows scan. Spool 25 and Spool 26 are joined using a single
partition hash join, with a join condition of ("fact_SysID =
fact_SysID"). The result goes into Spool 28 (all_amps)
(compressed columns allowed) fanned out into 4 hash join
partitions, which is built locally on the AMPs. The size of Spool
28 is estimated with index join confidence to be 2,603,284,805
rows (226,485,778,035 bytes). The estimated time for this step is
52.96 seconds.
17) We do an all-AMPs JOIN step from Spool 17 by way of an all-rows
scan, which is joined to Spool 28 (Last Use) by way of an all-rows
scan. Spool 17 and Spool 28 are joined using a hash join of 4
partitions, with a join condition of ("col_CD1 =
DG_CD_ID"). The result goes into Spool 30 (all_amps)
(compressed columns allowed) fanned out into 4 hash join
partitions, which is built locally on the AMPs. The size of Spool
30 is estimated with index join confidence to be 2,603,284,805
rows (236,898,917,255 bytes). The estimated time for this step is
54.66 seconds.
18) We do an all-AMPs JOIN step from Spool 4 (Last Use) by way of an
all-rows scan, which is joined to
DB.DIM_CC_sg in view
DB.CC_sg by way of an all-rows scan with
no residual conditions. Spool 4 and
DB.DIM_CC_sg are joined using a dynamic
hash join, with a join condition of (
"DB.DIM_CC_sg.prdt_cd = prdt_cd").
The result is split into Spool 31 (all_amps) with a condition of (
"client_sysID IN (:*)") to qualify rows matching skewed
rows of the skewed relation and Spool 32 (all_amps) with remaining
rows. Spool 31 is duplicated on all AMPs. The size of Spool 31
is estimated with low confidence to be 25,200 rows (730,800 bytes).
Spool 32 is built locally on the AMPs. The size of Spool 32 is
estimated with low confidence to be 1,902,852 rows (55,182,708
bytes). The estimated time for this step is 0.04 seconds.
19) We do an all-AMPs RETRIEVE step from DB.DIM_PRICE in
view DB.PRICE by way of an all-rows scan with no
residual conditions split into Spool 33 (all_amps) with a
condition of ("client_sysID IN (:*)") to qualify skewed
rows (compressed columns allowed) and Spool 34 (all_amps) with
remaining rows (compressed columns allowed). Spool 33 is built
locally on the AMPs. The size of Spool 33 is estimated with high
confidence to be 8,061,965 rows. Spool 34 is redistributed by
hash code to all AMPs. The size of Spool 34 is estimated with
high confidence to be 34,903,738 rows. The estimated time for
this step is 1.44 seconds.
20) We do an all-AMPs JOIN step from Spool 31 (Last Use) by way of an
all-rows scan, which is joined to Spool 33 (Last Use) by way of an
all-rows scan. Spool 31 and Spool 33 are joined using a single
partition hash join, with a join condition of (
"client_sysID = client_sysID"). The result
goes into Spool 35 (all_amps) fanned out into 5 hash join
partitions, which is redistributed by the hash code of (
DB.DIM_PRICE.in_ID) to all AMPs. The size of
Spool 35 is estimated with low confidence to be 8,061,965 rows (
233,796,985 bytes). The estimated time for this step is 0.03
seconds.
21) We do an all-AMPs JOIN step from Spool 32 (Last Use) by way of an
all-rows scan, which is joined to Spool 34 (Last Use) by way of an
all-rows scan. Spool 32 and Spool 34 are joined using a single
partition hash join, with a join condition of (
"client_sysID = client_sysID"). The result
goes into Spool 35 (all_amps) fanned out into 5 hash join
partitions, which is redistributed by the hash code of (
DB.DIM_PRICE.in_ID) to all AMPs. The size of
Spool 35 is estimated with low confidence to be 34,903,738 rows (
1,012,208,402 bytes). The estimated time for this step is 1.20
seconds.
22) We do an all-AMPs JOIN step from Spool 17 by way of an all-rows
scan, which is joined to Spool 30 (Last Use) by way of an all-rows
scan. Spool 17 and Spool 30 are joined using a hash join of 4
partitions, with a join condition of ("col_CD2 =
DG_CD_ID"). The result goes into Spool 36 (all_amps)
(compressed columns allowed) fanned out into 5 hash join
partitions, which is redistributed by the hash code of (
{RightTable}.in_ID) to all AMPs. The size of Spool 36 is
estimated with index join confidence to be 2,603,284,805 rows (
247,312,056,475 bytes). The estimated time for this step is 3
minutes and 10 seconds.
23) We do an all-AMPs JOIN step from Spool 35 (Last Use) by way of an
all-rows scan, which is joined to Spool 36 (Last Use) by way of an
all-rows scan. Spool 35 and Spool 36 are joined using a hash join
of 5 partitions, with a join condition of ("in_ID =
in_ID"). The result goes into Spool 38 (all_amps)
(compressed columns allowed) fanned out into 4 hash join
partitions, which is built locally on the AMPs. The size of Spool
38 is estimated with index join confidence to be 2,603,284,805
rows (278,551,474,135 bytes). The estimated time for this step is
1 minute.
24) We do an all-AMPs JOIN step from Spool 17 (Last Use) by way of an
all-rows scan, which is joined to Spool 38 (Last Use) by way of an
all-rows scan. Spool 17 and Spool 38 are joined using a hash join
of 4 partitions, with a join condition of ("(col_CD3 =
DG_CD_ID) AND (((DG_GEN_CD = '1701.02 ') OR ((DG_GEN_CD
= '1701 ') OR ((DG_GEN_CD = '1701.1 ') OR ((DG_GEN_CD =
'1701.12 ') OR ((DG_GEN_CD = '1701.2 ') OR ((DG_GEN_CD =
'1701.22 ') OR ((DG_GEN_CD = '1701.3 ') OR ((DG_GEN_CD =
'1701.32 ') OR ((DG_GEN_CD = '1701.4 ') OR ((DG_GEN_CD =
'1701.42 ') OR ((DG_GEN_CD = '1701.5 ') OR ((DG_GEN_CD =
'1701.52 ') OR ((DG_GEN_CD = '1701.6 ') OR ((DG_GEN_CD =
'1701.62 ') OR ((DG_GEN_CD = '1701.7 ') OR ((DG_GEN_CD =
'1701.72 ') OR ((DG_GEN_CD = '1701.8 ') OR ((DG_GEN_CD =
'1701.82 ') OR ((DG_GEN_CD = '1701.9 ') OR (DG_GEN_CD =
'1701.92 ')))))))))))))))))))) OR (((DG_GEN_CD = '1701.02 ') OR
((DG_GEN_CD = '1701 ') OR ((DG_GEN_CD = '1701.1 ') OR
((DG_GEN_CD = '1701.12 ') OR ((DG_GEN_CD = '1701.2 ') OR
((DG_GEN_CD = '1701.22 ') OR ((DG_GEN_CD = '1701.3 ') OR
((DG_GEN_CD= '1701.32 ') OR ((DG_GEN_CD = '1701.4 ') OR
((DG_GEN_CD = '1701.42 ') OR ((DG_GEN_CD = '1701.5 ') OR
((DG_GEN_CD = '1701.52 ') OR ((DG_GEN_CD = '1701.6 ') OR
((DG_GEN_CD = '1701.62 ') OR ((DG_GEN_CD = '1701.7 ') OR
((DG_GEN_CD = '1701.72 ') OR ((DG_GEN_CD = '1701.8 ') OR
((DG_GEN_CD = '1701.82 ') OR ((DG_GEN_CD = '1701.9 ') OR
(DG_GEN_CD = '1701.92 ')))))))))))))))))))) OR (((DG_GEN_CD =
'1701.02 ') OR ((DG_GEN_CD = '1701 ') OR ((DG_GEN_CD = '1701.1
') OR ((DG_GEN_CD = '1701.12 ') OR ((DG_GEN_CD = '1701.2 ') OR
((DG_GEN_CD = '1701.22 ') OR ((DG_GEN_CD = '1701.3 ') OR
((DG_GEN_CD = '1701.32 ') OR ((DG_GEN_CD = '1701.4 ') OR
((DG_GEN_CD = '1701.42 ') OR ((DG_GEN_CD = '1701.5 ') OR
((DG_GEN_CD = '1701.52 ') OR ((DG_GEN_CD = '1701.6 ') OR
((DG_GEN_CD = '1701.62 ') OR ((DG_GEN_CD = '1701.7 ') OR
((DG_GEN_CD = '1701.72 ') OR ((DG_GEN_CD = '1701.8 ') OR
((DG_GEN_CD = '1701.82 ') OR ((DG_GEN_CD = '1701.9 ') OR
(DG_GEN_CD = '1701.92 ')))))))))))))))))))) OR (((DG_GEN_CD =
'1701.02 ') OR ((DG_GEN_CD = '1701 ') OR ((DG_GEN_CD =
'1701.1') OR ((DG_GEN_CD = '1701.12 ') OR ((DG_GEN_CD = '1701.2
') OR ((DG_GEN_CD = '1701.22 ') OR ((DG_GEN_CD = '1701.3 ') OR
((DG_GEN_CD = '1701.32 ') OR ((DG_GEN_CD = '1701.4 ') OR
((DG_GEN_CD = '1701.42 ') OR ((DG_GEN_CD = '1701.5 ') OR
((DG_GEN_CD = '1701.52 ') OR ((DG_GEN_CD = '1701.6 ') OR
((DG_GEN_CD = '1701.62 ') OR ((DG_GEN_CD = '1701.7 ') OR
((DG_GEN_CD = '1701.72 ') OR ((DG_GEN_CD = '1701.8 ') OR
((DG_GEN_CD = '1701.82 ') OR ((DG_GEN_CD = '1701.9 ') OR
(DG_GEN_CD = '1701.92 ')))))))))))))))))))) OR (((DG_GEN_CD =
'1701.02 ') OR ((DG_GEN_CD = '1701 ') OR ((DG_GEN_CD = '1701.1
') OR ((DG_GEN_CD = '1701.12 ') OR ((DG_GEN_CD = '1701.2 ') OR
((DG_GEN_CD = '1701.22 ') OR ((DG_GEN_CD = '1701.3 ') OR
((DG_GEN_CD = '1701.32 ') OR ((DG_GEN_CD = '1701.4 ') OR
((DG_GEN_CD = '1701.42 ') OR ((DG_GEN_CD = '1701.5 ') OR
((DG_GEN_CD = '1701.52 ') OR ((DG_GEN_CD = '1701.6 ') OR
((DG_GEN_CD = '1701.62 ') OR ((DG_GEN_CD = '1701.7 ') OR
((DG_GEN_CD = '1701.72 ') OR ((DG_GEN_CD = '1701.8 ') OR
((DG_GEN_CD = '1701.82 ') OR ((** additional conditions
notlisted **) OR (** additional conditions not listed
**)))))))))))))))))))) OR (** additional conditions not listed
**))))))"). The result goes into Spool 3 (all_amps) (compressed
columns allowed), which is built locally on the AMPs. The size of
Spool 3 is estimated with index join confidence to be
2,603,284,805 rows (49,462,411,295 bytes). The estimated time for
this step is 25.72 seconds.
25) We do an all-AMPs SUM step to aggregate from Spool 3 (Last Use) by
way of an all-rows scan, and the grouping identifier in field 2.
Aggregate Intermediate Results are computed globally, then placed
in Spool 39. The size of Spool 39 is estimated with low
confidence to be 34,391,711 rows (722,225,931 bytes). The
estimated time for this step is 51.94 seconds.
26) We do an all-AMPs RETRIEVE step from Spool 39 (Last Use) by way of
an all-rows scan into Spool 1 (all_amps) (compressed columns
allowed), which is redistributed by hash code to all AMPs. Then
we do a SORT to order Spool 1 by row hash. The size of Spool 1 is
estimated with low confidence to be 34,391,711 rows (584,659,087
bytes). The estimated time for this step is 0.87 seconds.
27) We do an all-AMPs MERGE into scarp3.VT from Spool 1
(Last Use). The size is estimated with low confidence to be
34,391,711 rows. The estimated time for this step is 3 minutes
and 6 seconds.
当您非规范化时，您需要多次加入同一个表 ：-）
但是该计划看起来还不错，优化器知道偏斜并使用PRPD（部分重新分发部分复制）。
关于重写，如果视图实际上共享相同的定义（以及缺少的括号），则可以将其简化为：
CREATE MULTISET VOLATILE TABLE VT AS (
WITH cd_vw AS
( SELECT * FROM cd_vw1
WHERE dc1.d_cd1 IN ('1701','1701.02','1701.1','1701.12','1701.2'
,'1701.22','1701.3','1701.32','1701.4','1701.42'
,'1701.5','1701.52','1701.6','1701.62','1701.7'
,'1701.72','1701.8','1701.82','1701.9','1701.92')
)
SELECT DISTINCT d.in_ID
FROM DB.FACT_BODtl d
JOIN cd_vw dc1 ON (d.col_CD1=dc1.col_CD1)
JOIN cd_vw dc2 ON (d.col_CD2=dc2.col_CD2)
JOIN cd_vw dc3 ON (d.col_CD3=dc3.col_CD3)
JOIN cd_vw dc4 ON (d.col_CD4=dc4.col_CD4)
JOIN cd_vw dc5 ON (d.col_CD5=dc5.col_CD5)
JOIN cd_vw dc6 ON (d.col_CD6=dc6.col_CD6)
JOIN cd_vw dc7 ON (d.col_CD7=dc7.col_CD7)
JOIN cd_vw dc8 ON (d.col_CD8=dc8.col_CD8)
JOIN cd_vw dc9 ON (d.col_CD9=dc9.col_CD9)
JOIN DM_NBR NBR ON (d.fact_SysID = NBR.fact_SysID)
JOIN PRICE PRC ON (d.in_ID=PRC.in_ID)
JOIN Dm_LabDt ENDDT ON (d.dt_tb_sysID=ENDDT.DT_SYS_ID )
JOIN CC_sg CSG ON (PRC.client_sysID=CSG.client_sysID)
JOIN COMPONENT PROD ON (CSG.prdt_cd=PROD.prdt_cd)
WHERE
NBR.COLX_CD IN ('163000')
AND EXTRACT (YEAR FROM ENDDT.Fdt) = '2015'
AND PROD.Prd_CD1 IN ('COM')
)
WITH DATA
ON COMMIT PRESERVE ROWS;
我不知道这是否也会改变计划。
这些多个联接可能取代了 OR 联接，这总是会导致产品联接，并且对于大量行执行非常糟糕：
SELECT DISTINCT d.in_ID
FROM DB.FACT_BODtl d
JOIN cd_vw dc1
ON ((d.col_CD1=dc1.col_CD1) OR
(d.col_CD2=dc1.col_CD2) OR
(d.col_CD3=dc1.col_CD3) OR
(d.col_CD4=dc1.col_CD4) OR
(d.col_CD5=dc1.col_CD5) OR
(d.col_CD6=dc1.col_CD6) OR
(d.col_CD7=dc1.col_CD7) OR
(d.col_CD8=dc1.col_CD8) OR
(d.col_CD9=dc1.col_CD9)
JOIN DM_NBR NBR ON (d.fact_SysID = NBR.fact_SysID)
JOIN PRICE PRC ON (d.in_ID=PRC.in_ID)
JOIN Dm_LabDt ENDDT ON (d.dt_tb_sysID=ENDDT.DT_SYS_ID )
JOIN CC_sg CSG ON (PRC.client_sysID=CSG.client_sysID)
JOIN COMPONENT PROD ON (CSG.prdt_cd=PROD.prdt_cd)
WHERE
NBR.COLX_CD IN ('163000')
AND EXTRACT (YEAR FROM ENDDT.Fdt) = '2015'
AND PROD.Prd_CD1 IN ('COM')
AND dc1.d_cd1 IN ('1701','1701.02','1701.1','1701.12','1701.2','1701.22','1701.3','1701.32','1701.4','1701.42','1701.5','1701.52','1701.6','1701.62','1701.7','1701.72','1701.8','1701.82','1701.9','1701.92')