避免动态SQL和索引使用的智能逻辑

Question

假设我们有一个EMPLOYEE表，我们希望在以下字段上使用三个过滤器来查询它(在这些字段上我们有索引)：subsidiary_id、employee_id、last_name。

如果在where子句(如：WHERE last_name = :name )中使用带有简单过滤器和参数绑定的动态SQL来构造查询，则使用索引，并且响应速度快。

现在的问题是，如果我们在查询中使用智能逻辑来使用静态SQL构造查询，那么：

SELECT subsidiary_id, employee_id, last_name
FROM EMPLOYEE 
WHERE (:sub_id IS NULL OR subsidiary_id = :sub_id)
AND   (:emp_id IS NULL OR employee_id = :emp_id)
AND   (:name IS NULL OR last_name = :name)

即使执行查询，并避免使用动态SQL，因为所有可能的筛选表达式都是静态地编码在语句中的，它也会导致反模式，因为数据库(Oracle)无法优化特定筛选器的执行计划(因为其中任何一个都可能在运行时被取消)，它必须为执行全表扫描的最坏情况(所有已禁用的筛选器)做好准备，即使为过滤器使用的每一列都有索引。

问题是:如果智能逻辑查询放在存储过程/函数中会发生什么？数据库是否足够聪明，可以使用索引，还是像使用绑定参数提交的查询一样执行全表扫描？

Oracle 存储过程主体：

create procedure myproc (employee_id IN NUMBER, subsidiary_id IN NUMBER, name IN VARCHAR2, prc out sys_refcursor)
is
begin
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE (subsidiary_id IS NULL OR e.subsidiary_id = subsidiary_id)
                    AND   (employee_id IS NULL OR e.employee_id = employee_id)
                    AND   (name IS NULL OR e.last_name = name)
                );
end;

如果在存储过程或函数中执行索引，查询是否使用这些索引？

Answer 1

退一步说，如果查询要运行几十次/数百次/数千次，那么查询优化器应该确定计划多少次。

对每一次执行进行优化肯定是效率低下的。Oracle用于每条语句优化一次(如果会话更改了默认优化器、NLS、排序规则设置等，则例外)

在11g中，它提出了Adaptive共享，它将尝试查看不同的计划对于不同的查询参数是否更好。如果它最初选择了一个计划，但发现随后的查询与计划中的假设不匹配，那么它可以切换到另一个方案。

https://oracle-base.com/articles/11g/adaptive-cursor-sharing-11gr1

我的建议是不要依赖这一点。显式编码查询最“预期”的路径，您可以确信会有合适的索引。您正在构建一个应用程序，该应用程序超出了提供临时查询的期望。

并且始终使用命名约定来确保您的PL/SQL变量/参数名称不会与列名混淆。

create procedure myproc (p_employee_id IN NUMBER, p_subsidiary_id IN NUMBER, p_name IN VARCHAR2, prc out sys_refcursor)
is
begin
    if p_employee_id is not null then
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE (p_subsidiary_id IS NULL OR e.subsidiary_id = p_subsidiary_id)
                    AND   e.employee_id = p_employee_id
                    AND   (p_name IS NULL OR e.last_name = p_name)
                );
    elsif p_name is not null
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE (p_subsidiary_id IS NULL OR e.subsidiary_id = p_subsidiary_id)
                    AND   (p_employee_id IS NULL OR e.employee_id = p_employee_id)
                    AND   e.last_name = p_name
                );
    elsif p_subsidiary_id is not null
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                    WHERE e.subsidiary_id = p_subsidiary_id
                    AND   (p_employee_id IS NULL OR e.employee_id = p_employee_id)
                    AND   (p_name IS NULL OR e.last_name = p_name)
                );
    else
     open prc for (SELECT e.subsidiary_id, e.employee_id, e.last_name
                    FROM EMPLOYEE e
                );
    end if;
end;

Answer 2

根据我在查询方面的经验，您最好的选择是动态构建查询，以避免使用(:sub_id IS NULL OR subsidiary_id = :sub_id)类型的逻辑。您可以尝试使用NVL(:sub_id, subsidiary_id) = subsidiary_id，但是总的来说，我还没有发现这能提供良好的性能。我发现动态执行游标的性能比您展示的逻辑要好得多，即使有适当的索引也是如此。

祝你好运。