虽然你可以这样做...
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 的性能通常是better,因为它将生成更有针对性的查询计划。在上面的查询中,Oracle无法判断是在bcode还是lb上使用索引,还是在type或edate上使用索引,并且可能每次都会执行全表扫描。
当然,你must在动态查询中使用绑定变量,而不是将文字值连接到字符串中,否则性能(以及可扩展性和安全性)将受到影响very bad.
需要明确的是,我想到的动态版本将像这样工作:
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;
这意味着查询结果will是“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 次硬解析。使用本机动态 SQL 时需要“and :bv is null”子句,但可以通过使用 DBMS_SQL 来避免。
注意:使用(1=1 or :bindvar is null)
Michal Pravda 在评论中建议当绑定变量为 null 时,因为它允许优化器消除该子句。