oracle - 如何在 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。客户端代码可以在有(应该很快)或没有(非常慢)限制的情况下调用 DO_STUFF。

麻烦

问题是，PL/SQL 将为上面的 org_from 和 org_to 参数创建绑定(bind)变量，这正是我在大多数情况下所期望的:

  -- [...]
  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 中内联值？我知道EXECUTE IMMEDIATE ，但我更愿意让 PL/SQL 编译我的查询，而不是进行字符串连接。

• 我只是测量了巧合发生的事情还是我可以假设内联变量确实更好(在这种情况下)？我之所以问这个问题，是因为我认为绑定(bind)变量迫使 Oracle 设计一个通用执行计划，而内联值则允许分析非常具体的列和索引统计信息。所以我可以想象这不仅仅是巧合。

• 我错过了什么吗？除了变量内联之外，也许还有一种完全不同的方法来实现查询执行计划的改进(请注意，我也尝试了很多提示，但我不是该领域的专家)？

Answer 1

在您的评论中您说:

"Also I checked various bind values. With bind variables I get some FULL TABLE SCANS, whereas with hard-coded values, the plan looks a lot better."

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

当您使用绑定(bind)变量制定查询时，优化器必须做出决定:是否应该假定大多数情况下您将传入值，或者您将传入空值？难的。那么换个角度来看:当您只需要选择记录的子集时进行全表扫描，还是当您需要选择所有记录时进行索引读取效率较低？

优化器似乎已经将全表扫描视为覆盖所有可能性的效率最低的操作。

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

<小时/>

那么，该怎么办呢？正如您所说，此查询每月仅运行一次，我认为只需要对业务流程进行少量更改即可进行单独的查询:一个针对所有组织，一个针对组织的子集。

<小时/>

"Btw, removing the :R1 IS NULL clause doesn't change the execution plan much, which leaves me with the other side of the OR condition, :R1 <= org.no where NULL wouldn't make sense anyway, as org.no is NOT NULL"

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

WHERE org.id BETWEEN 10 AND 11

...而这可能更适合全表扫描...

WHERE org.id BETWEEN 10 AND 1199999

这就是绑定(bind)变量查看发挥作用的地方。

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