Transforming a SQL attached many optional conditions with UNION ALL - an IDEA

Note. This idea came from Lee Jong-Chan, my close friend. On his agreement I post this article on my blog.

When a SQL was attached many optional conditions on where clause, for purpose of tuning we usually divide the SQL in two mutual exclusive query blocks using 'UNION ALL' on the basis of performance-decisive optional condition clause, like this.

<original SQL>

select ...
from ...
where sal_date between :fr_date and :to_date --mandatory condition
and (cust_name like :cust_name or :cust_name is null) --optional condition
;

<divided SQL using union all>

select ...
from ...
where :cust_name is null
and sal_date between :fr_date and :to_date --mandatory condition
union all
select /*+index(t(cust_name)) */ ...
from ...
where :cust_name is not null
and sal_date between :fr_date and :to_date --mandatory condition
and cust_name like :cust_name --optional condition

If the number of performance-decisive optional condition clause is 2 then we can divide the SQL in 4 mutual exclusive query blocks using 'UNION ALL's. This is little bit complicate and the SQL became much longer.

How about if the number of performance-decisive optional condition clauses is 4?

Yes, we can divide the SQL in 8 blocks, but this way is surely not acceptable because the SQL would be too long.

In this case I recommend you consider this method, which you can easily divide the SQL in two main query blocks - the one is for all performance-decisive optional conditions are not evaluated and the other is for at least one of the all performance-decisive optional conditions are evaluated. Below is the example of this.

<premise>

mandatory condition:  hiredate
optional conditions of good selectivity:  empno, ename, sal
optional conditions of bad selectivity:  deptno 


<original SQL>

select /*+index(e(hiredate)) */ *
from emp e
where 1=1
and hiredate between to_date(:fr_date,'yyyymmdd') and to_date(:to_date,'yyyymmdd') --mandatory,index
and (empno = :empno or :empno is null) --optional,index
and (ename = :ename or :ename is null) --optional,index
and (sal >= :sal or :sal is null) --optional,index
and (deptno = :deptno or :deptno is null) --optional,full
;

<transformed SQL>

-- all optional conditions of good selectivity are not evaluated
select /*+index(e(hiredate)) */ *
from emp e
where 1=1
and hiredate between to_date(:fr_date,'yyyymmdd') and to_date(:to_date,'yyyymmdd') 
and (deptno = :deptno or :deptno is null) 
and (:empno is null and :ename is null and :sal is null)

union all

-- at least one of all optional conditions of good selectivity are evaluated
select /*+leading(a) use_nl(e) rowid(e) */ e.*
from (
      select rid,max(empno_fg), max(ename_fg), max(sal_fg)
      from (
            select /*+index(e) */ rowid rid, 1 empno_fg, 0 ename_fg, 0 sal_fg
            from emp e
            where :empno is not null
            and empno = :empno 
            union all
            select /*+index(e) */ rowid rid, 0 empno_fg, 1 ename_fg, 0 sal_fg
            from emp e
            where :ename is not null
            and ename = :ename 
            union all
            select /*+index(e) */ rowid rid, 0 empno_fg, 0 ename_fg, 1 sal_fg
            from emp e
            where :sal is not null
            and sal >= :sal 
            )
      group by rid
      having max(empno_fg)|| max(ename_fg)|| max(sal_fg)
      = nvl2(:empno,1,0)|| nvl2(:ename,1,0)|| nvl2(:sal,1,0)
      ) a, emp e
where e.rowid = a.rid
and hiredate between to_date(:fr_date,'yyyymmdd') and to_date(:to_date,'yyyymmdd') 
and (deptno = :deptno or :deptno is null) 
and not(:empno is null and :ename is null and :sal is null)
;

For the worst case, if :empno, :ename and :sal are all evaluated - accept values, then the table will be accessed three times. You should consider this before the transforming SQLs in performance view.