代码之家 › 专栏 › 技术社区 › aleroot

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

indexing oracle database

aleroot · 技术社区 · 7 年前

假设我们有一个EMPLOYEE表,我们想用三个过滤器来查询以下字段(我们在其上有索引):subsidiary_id,EMPLOYEE_id,last_name。

如果我们在where子句中使用带有简单筛选器和参数绑定的动态SQL构造查询,如: WHERE last_name = :name ,使用索引,响应速度快。

现在的问题是,如果我们在查询中使用智能逻辑用静态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)无法优化特定筛选器的执行计划(因为它们中的任何一个都可以在运行时取消),因此会导致反模式,它必须为执行 全表扫描 即使每个列都有用于筛选的索引。

问题是: 如果将具有智能逻辑的查询放入存储过程/函数中,会发生什么情况? 数据库是否足够智能以使用索引,或者像对使用绑定参数提交的查询那样执行完整表扫描?

甲骨文公司 存储过程 正文:

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;

如果在存储过程或函数中执行,查询是否使用索引?

2 回复 | 直到 7 年前

Gary Myers 7 年前

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

为每次执行进行优化绝对是低效的。Oracle用于对每条语句进行一次优化(如果会话更改了默认优化器、NLS、排序规则设置等,则有一些例外情况)

在11g中,它提出了自适应游标共享,在这里,它将尝试查看对于不同的查询参数,不同的计划是否更好。如果它最初选择了一个计划,但发现随后的查询与计划中的假设不匹配,它可以切换到另一个计划。

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;

Bob Jarvis - Ð¡Ð»Ð°Ð²Ð° Ð£ÐºÑÐ°ÑÐ½Ñ 7 年前

根据我的查询经验,您最好的选择是动态地构建查询,以避免 (:sub_id IS NULL OR subsidiary_id = :sub_id) 逻辑类型。你可以试着用 NVL(:sub_id, subsidiary_id) = subsidiary_id 但总的来说,我还没有发现这个能带来好的表现。我发现动态执行的游标的性能比您显示的逻辑要好得多,即使有适当的索引。

祝你好运。