为什么只有当我使用 where 子句查询子查询时，Oracle 10g SQL 才运行缓慢？

Question

由于各种原因，我无法粘贴整个 SQL，因此请考虑以下示例：

select * 
from
    (select nvl(get_quantity(1), 10) available_qty 
     from dual)
where available_qty > 30;

get_quantity 是一个函数，它根据通过它的记录的 ID 进行计算。如果它返回 null，我会使用 nvl() 将其强制为 10。

当我在父查询中使用 WHERE 子句时，查询运行速度非常慢。然而，当我注释掉 WHERE 子句时，它运行得非常快。我不明白的是为什么它可以非常快地显示数据，但不能同样快地查询数据。我也在查询子查询的结果。我的印象是子查询返回“渲染”数据集。这几乎就像查询 available_qty 标识符导致它引用子查询中的某些内容一样。

这就是为什么我认为 get_quantity 函数的内容与这里无关，所以我没有费心发布它。相反，我认为这是我对 Oracle 如何处理子查询等的误解。

你们中的 Oracle 专家知道我做错了什么吗？

事后思考：当我为这个问题输入标签时，出现了“相关子查询”标签。在进行一些快速研究时，似乎这种类型的子查询在某种程度上取决于外部查询。这可能与我的问题有关吗？

Answer 1

我们来做个实验吧。首先，我们将运行以下查询：

select lvl, rnd 
from (select level as lvl from dual connect by level <= 5) a,
     (select dbms_random.value() rnd from dual) b;

“a”子查询将返回 5 行，其值从 1 到 5。“b”子查询将返回具有随机值的一行。如果该函数在两个表连接（通过笛卡尔）之前运行，则每行将返回相同的随机值。实际结果：

       LVL        RND
---------- ----------
         1 .417932089
         2 .963531718
         3 .617016889
         4 .128395638
         5 .069405568

5 rows selected.

显然，该函数是针对每个连接的行运行的，而不是针对连接之前的子查询运行的。这是 Oracle 优化器决定查询的最佳路径是按该顺序执行操作的结果。为了防止这种情况，我们必须在第二个子查询中添加一些内容，使 Oracle 在执行联接之前完整地运行子查询。我们将把 rownum 添加到子查询中，因为 Oracle 知道如果在连接之后运行 rownum 将会改变。以下查询演示了这一点：

select lvl, rnd from (
select level as lvl from dual connect by level <= 5) a,
(select dbms_random.value() rnd, rownum from dual) b;

正如您从结果中看到的，在这种情况下该函数仅运行一次：

       LVL        RND
---------- ----------
         1 .028513902
         2 .028513902
         3 .028513902
         4 .028513902
         5 .028513902

5 rows selected.

在您的情况下， where 子句提供的过滤器似乎正在使优化器采用不同的路径，重复运行该函数，而不是一次。通过让 Oracle 按照编写的方式运行子查询，您应该获得更一致的运行时间。