Apartment I am living, Suwon Korea

Weather is falling in fall.

An unknown tree bears many small, apple-shaped fruits.

I'v been living here for eight years.

So many memories with my family..

About me..

I'm living in Korea.
I love coffee, love flowers, love God, love stroll and love people.
As an Oracle performance tuning engineer, I'v enjoyed the tuning Oracle DBMS or SQL for over 20 years of my life.
Some day in the near future, I hope to live in other country working in same job area.
Oracle tuning is one of my great pleasures.
I'v been managing a blog in Korean version : inhim.blog.me
Now I here open another one in English version.
Writing a post in English is somewhat challenging, but I'll try and enjoy.

Today I write this at Pangyo office. Everything is fine in this new-built city.
Weather is good enough to tempt me to stroll around the city.
That'll be just Good!

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.