"WHERE"子句中的 Oracle 和可能的常量谓词

Question

我在以下示例代码中遇到了 ORACLE 的常见问题:

create or replace procedure usp_test
(
  p_customerId number,
  p_eventTypeId number,
  p_out OUT SYS_REFCURSOR
)
as
begin

  open p_out for
    select e.Id from eventstable e
    where 
      (p_customerId is null or e.CustomerId = p_customerId)
      and
      (p_eventTypeId is null or e.EventTypeId = p_eventTypeId)
    order by Id asc;

end usp_test;

“(p_customerId is null or e.CustomerId = p_customerId)”中的“OR”会破坏程序性能，因为优化器不会在“CustomerId”列上优化使用索引(我希望进行索引查找)，从而导致扫描而不是寻找。 “CustomerId”上的索引有很多不同的值。

在使用 MSSQL 2008 R2(最新的 SP)或 MSSQL 2012 时，我可以使用“选项(重新编译)”提示查询，这将:

1. 只重新编译这个查询
2. 解析所有变量的值(它们在调用存储过程后已知)
3. 用常量替换所有已解决的变量并消除常量谓词部分

例如:如果我传递 p_customerId = 1000，那么“1000 is null”表达式将始终为 false，因此优化器将忽略它。这会增加一些 CPU 开销，但它主要用于很少调用的大量报告程序，所以这里没有问题。

在 Oracle 中有什么方法可以做到这一点吗？动态 SQL 不是一个选项。

添加

没有“p_customerId is null”和“p_eventTypeId is null”的相同过程运行了约 0.041 秒，而上面的过程运行了约 0.448 秒(我有约 5.000.000 行)。

Answer 1

CREATE INDEX IX_YOURNAME1 ON eventstable (NVL(p_customerId, 'x'));
CREATE INDEX IX_YOURNAME2 ON eventstable (NVL(p_eventTypeId, 'x'));

create or replace procedure usp_test
(
  p_customerId number,
  p_eventTypeId number,
  p_out OUT SYS_REFCURSOR
)
as
begin

  open p_out for
    select e.Id from eventstable e
    where 
        (NVL(p_customerId, 'x') = e.CustomerId OR NVL(p_customerId, 'x') = 'x')
    AND (NVL(p_eventTypeId, 'x') = e.EventTypeId OR NVL(p_eventTypeId, 'x') = 'x')
    order by Id asc;
end usp_test;