oracle - PL/SQL - where 子句中的可选条件 - 没有动态 sql？

Question

我有一个查询，并非所有条件都是必需的。以下是使用所有条件时的外观示例:

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             and q.type = 'privt' --this is variable
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

标记为 --this is variable 的部分是不同的部分!如果未指定条件，则没有默认值。例如，如果输入为 q.type 指定“*”(但保持其他所有内容相同)，则查询应匹配所有类型，并执行如下:

select num
from (select distinct q.num
       from cqqv q
       where q.bcode = '1234567' --this is variable
             and q.lb = 'AXCT' --this is variable
             --and q.type = 'privt' --this condition ignored because of "type=*" in input
             and q.edate > sysdate - 30 --this is variable
       order by dbms_random.value()) subq
where rownum <= 10; --this is variable

我知道可以使用动态 sql 即时构建此查询，但我想知道这可能会导致什么样的性能问题，以及是否有更好的方法来做到这一点。

Answer 1

虽然你可以这样做...

select num
from (select distinct q.num
       from cqqv q
       where 1=1
             and (:bcode is null or q.bcode = :bcode)
             and (:lb is null or q.lb = :lb)
             and (:type is null or q.type = :type)
             and (:edate is null or q.edate > :edate - 30)
       order by dbms_random.value()) subq
where rownum <= :numrows

... 使用动态 SQL 的性能通常为 更好 ，因为它将生成更有针对性的查询计划。在上述查询中，Oracle 无法判断是在 bcode 还是 lb 或 type 或 edate 上使用索引，并且可能每次都会执行全表扫描。

当然，你 必须在动态查询中使用绑定(bind)变量，不要将文字值连接到字符串中，否则性能(以及可伸缩性和安全性)将为 非常糟糕 .

需要明确的是，我想到的动态版本将像这样工作:

declare
    rc sys_refcursor;
    q long;
begin
    q := 'select num
    from (select distinct q.num
           from cqqv q
           where 1=1';

    if p_bcode is not null then
        q := q || 'and q.bcode = :bcode';
    else
        q := q || 'and (1=1 or :bcode is null)';
    end if;

    if p_lb is not null then
        q := q || 'and q.lb = :lb';
    else
        q := q || 'and (1=1 or :lb is null)';
    end if;

    if p_type is not null then
        q := q || 'and q.type = :type';
    else
        q := q || 'and (1=1 or :type is null)';
    end if;

    if p_edate is not null then
        q := q || 'and q.edate = :edate';
    else
        q := q || 'and (1=1 or :edate is null)';
    end if;

    q := q || ' order by dbms_random.value()) subq
    where rownum <= :numrows';

    open rc for q using p_bcode, p_lb, p_type, p_edate, p_numrows;
    return rc;
end;

这意味着结果查询 将是“sargable”(我必须承认的一个新词!)，因为生成的查询运行将是(例如):

select num
from (select distinct q.num
       from cqqv q
       where 1=1
             and q.bcode = :bcode
             and q.lb = :lb
             and (1=1 or :type is null)
             and (1=1 or :edate is null)
       order by dbms_random.value()) subq
where rownum <= :numrows

但是，我接受在此示例中这可能需要多达 16 次硬解析。使用 native 动态 SQL 时需要“and :bv is null”子句，但可以通过使用 DBMS_SQL 来避免。

注:使用 (1=1 or :bindvar is null)当绑定(bind)变量为 null 时，Michal Pravda 在评论中提出了建议，因为它允许优化器消除该子句。