如何在PL/SQL中内联变量？

Question

形势

对于Oracle 11.2.0.2.0中大量数据的中型查询，我的查询执行计划有一些问题。为了加快速度，我引入了一个范围过滤器，它做的大概是这样的：

PROCEDURE DO_STUFF(
    org_from VARCHAR2 := NULL,
    org_to   VARCHAR2 := NULL)

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((org_from IS NULL) OR (org_from <= org.no))
   AND ((org_to   IS NULL) OR (org_to   >= org.no)))
  -- [...]

如您所见，我希望使用可选的组织编号范围来限制JOIN of organisations。客户端代码可以使用(应该是快速的)或没有(非常慢的)限制来调用DO_STUFF。

麻烦

问题是，PL/SQL将为上述org_from和org_to参数创建绑定变量，这在大多数情况下都是我所期望的：

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((:B1 IS NULL) OR (:B1 <= org.no))
   AND ((:B2 IS NULL) OR (:B2 >= org.no)))
  -- [...]

解决办法

只有在这种情况下，当我只是内联这些值时，即当Oracle执行的查询实际上类似于

  -- [...]
  JOIN organisations org
    ON (cust.org_id = org.id
   AND ((10 IS NULL) OR (10 <= org.no))
   AND ((20 IS NULL) OR (20 >= org.no)))
  -- [...]

所谓“很多”，我的意思是快5-10倍。请注意，查询很少执行，即每月一次。所以我不需要缓存执行计划。

我的问题

• 如何在PL/SQL中内联值？我知道立即执行，但我更愿意让PL/SQL编译我的查询，而不是进行字符串连接。
• 我只是在测量偶然发生的事情，还是可以假设内联变量确实更好(在这种情况下)？我之所以问这个问题，是因为我认为绑定变量迫使Oracle设计一个通用的执行计划，而内联的值则允许分析非常具体的列和索引统计信息。所以我可以想象这不仅仅是巧合。
• 我是不是遗漏了什么？也许有一种完全不同的方法来实现查询执行计划的改进，而不是变量内联(注意，我也尝试过一些提示，但我不是这个领域的专家)？

Answer 1

在你的评论中，你说：

“此外，我还检查了各种绑定值。使用绑定变量，我得到了一些完整的表扫描，而使用硬编码的值，计划看起来要好得多。”

有两条路。如果输入参数为NULL，则选择所有记录。在这种情况下，全表扫描是检索数据的最有效方法。如果您传递值，那么索引读取可能会更高效，因为您只选择一小部分信息。

当您使用bind变量编写查询时，优化器必须做出决定:是假定大多数时间您将传递值还是传递空值？很难。因此，从另一种角度来看:当您只需要选择一组记录，或者在需要选择所有记录时执行索引读取时，执行完整的表扫描是否效率更低？

似乎优化器已经选择了全表扫描，认为它是覆盖所有可能发生的事件的效率最低的操作。

然而，当硬编码时，优化器立即知道10 IS NULL值为FALSE，因此它可以权衡使用索引读取查找所需子集记录的优点。

那么，该怎么办？正如您所说，这个查询每月只运行一次，我认为只需要对业务流程进行一次小小的更改，就可以进行单独的查询:一次针对所有组织，一次针对一组组织。

“顺便说一句，删除:R1 IS NULL子句不会改变执行计划，这给我留下了OR条件的另一面，即:R1 <= org.no，其中NULL无论如何都没有意义，因为org.no不是NULL。”

好的，问题是你有一对绑定变量，它指定了一个范围。根据值的分布，不同的范围可能适合不同的执行计划。也就是说，这个范围可能适合索引范围扫描.

WHERE org.id BETWEEN 10 AND 11

...whereas这可能更适合于全表扫描.

WHERE org.id BETWEEN 10 AND 1199999

这就是绑定变量窥视的作用所在。

(当然，这取决于价值的分布)。

Answer 2

由于查询计划实际上始终是不同的，这意味着优化器的基数估计因某种原因而关闭。您能否从查询计划中确认，当使用绑定变量时，优化器期望条件不够有选择性？由于您使用的是11.2，所以Oracle应该使用自适应游标共享，所以它不应该是一个绑定变量窥视问题(假设您在测试中使用不同的NO值多次使用绑定变量调用版本。

关于好计划的基数估计实际上是正确的吗？我知道你说过NO列上的统计数据是准确的，但是我会怀疑一个散乱的直方图，它可能不会被你的定期统计数据收集过程更新。

您总是可以在查询中使用提示来强制使用特定的索引(虽然从长期维护的角度来看，使用存储大纲或优化器计划稳定性更好)。这些选项中的任何一个都比诉诸动态SQL更可取。

不过，要尝试的另一个测试是用Oracle的旧语法替换SQL 99联接语法，即

SELECT <<something>>
  FROM <<some other table>> cust,
       organization org
 WHERE cust.org_id = org.id
   AND (    ((org_from IS NULL) OR (org_from <= org.no)) 
        AND ((org_to   IS NULL) OR (org_to   >= org.no)))

这显然不应该改变任何东西，但是SQL 99语法存在解析器问题，所以需要检查。

Answer 3

它闻起来像捆绑窥视，但是我只使用Oracle10，所以我不能声称在11中也存在同样的问题。