PL/SQL - Optional conditions in where-clause - without dynamic sql?

Question

I have a query where not all conditions are necessary. Here's an example of what it looks like when all conditions are used:

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

The parts marked as --this is variable are the parts that, well, vary! If a condition is NOT specified, then there is no default value. For example, if the input specifies "*" for q.type (but leaves everything else the same), then the query should match everything for type, and execute as:

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

I know it is possible to use dynamic sql to build this query on the fly, but I am wondering what sort of performance problems this could cause, and if there is a better way to do this.

Answer 1

While you could do this...

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

... the performance using dynamic SQL will usually be better, as it will generate a more targeted query plan. In the above query, Oracle cannot tell whether to use an index on bcode or lb or type or edate, and will probably perform a full table scan every time.

Of course, you must use bind variables in your dynamic query, not concatenate the literal values into the string, otherwise performance (and scalability, and security) will be very bad.

To be clear, the dynamic version I have in mind would work like this:

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;

This means that the result query will be "sargable" (a new word to me I must admit!) since the resulting query run will be (for example):

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

However, I accept that this could require up to 16 hard parses in this example. The "and :bv is null" clauses are required when using native dynamic SQL, but could be avoided by using DBMS_SQL.

Note: the use of (1=1 or :bindvar is null) when the bind variable is null was suggested in a comment by Michal Pravda, as it allows the optimizer to eliminate the clause.