sql - JOIN 性能与循环选择

Question

我正在尝试解释一个令我困惑的性能问题......

我有 2 个表，A 和 B。

表A定义对象:

+----+--------------+
+ ID + other_things +
+----+--------------+
+ 1  + ~~~~~~~~~~~~ +
+ 2  + ~~~~~~~~~~~~ +
+ 3  + ~~~~~~~~~~~~ +
+----+--------------+

此表中有 575 行。

表 B 为所述对象定义了一些属性。

+----+-------------+-------------+
+ ID + prop_type   +  prop_value +
+----+-------------+-------------+
+ 1  +    prop1    +   foo       +
+----+-------------+-------------+
+ 1  +    prop2    +   toto      +
+----+-------------+-------------+
+ 3  +    prop2    +  lorem      +
+----+-------------+-------------+  

此表中有 20254 行。

目标是获取表 A 中项目的所有“prop2”值。

这里的一些用户非常友好地帮助我设置了“好的”解决方案(请参阅问题 41331902):

SELECT A.ID, B.prop_value
FROM A LEFT JOIN
     B
     ON A.ID = B.ID AND B.prop_type = 'prop2';

此查询将在大约 20 秒内执行。

然而，目前使用的是另一个版本，我一开始就在努力改进:

FOR (
SELECT ID as id_a FROM A
)
DO
prop = (select prop_value from B where prop_type = 'prop2' and id = id_a);
write id_a || '   ' || prop;
END

这给出了相同的结果，但查询在 1.5 秒内完成...

根据我在网上阅读的内容，我的理解是 JOIN 应该比循环好得多，但实际结果却相反......

我曾尝试更改表的顺序(即大连接小)，但这只会让事情变得更糟(最多 1 分钟)。

你能帮我理解这里发生了什么吗？

旁注:我无法获得执行计划，因为数据库引擎不允许(AspenTech 的 SQLPlus)

非常感谢您的帮助

Answer 1

如果您有这样的疑问:

SELECT A.ID, B.prop_value
FROM A LEFT JOIN
     B
     ON A.ID = B.ID AND B.prop_type = 'prop2';

如果您想加快速度，请在 B 上创建索引:

CREATE INDEX idx_b_id_type_value ON B(id, prop_type, prop_value);

这应该会大大提高性能。

如果 B 有一个索引，其中 prop_type 是索引中的第一个键，那么您的双查询版本会更快。我应该补充:我没有使用 Aspen SQL 的经验。另一种可能性是它只是有一个糟糕的优化器。