Oracle Tuning: Sql Query tuning

 Weigh JOIN versus EXISTS Sub-Query

Use table JOIN instead of EXISTS sub-query
when the percentage of rows returned from the outer sub-query is high

select  e.name, e.phone, e.mailstop
from   employee e, department d
where e.deptno = d.deptno
    and d.status = ‘ACTIVE’

Use EXISTS sub-query instead of table JOIN
when the percentage of rows returned from the outer sub-query is low

select e.name, e.phone, e.mailstop
from employee e
where e.deptno in (select  d.deptno
                               from   department d
                               where d.status != ‘ACTIVE’)

• Consider EXISTS in place of DISTINCT

Avoid joins that use DISTINCT, use EXISTS sub-query instead

Bad:

select distinct deptno, deptname from emp, dept where
                      emp.deptno = dept.deptno


Good: select deptno, deptname from dept where
                         exists (select ‘X’ from emp where
                                                   emp.deptno = dept.deptno)
Note – only has to find one match

• Consider NOT EXISTS in place of NOT IN

avoid sub-queries that use NOT IN, use NOT EXISTS instead

Bad: select * from emp where
                         deptno not in (select deptno from dept where
                                                   deptstatus = ‘A’)

Good: select * from emp where
                         not exists (select ‘X’ from dept where
                                                   deptstatus = ‘A’ and
                                                   dept.deptno = emp.deptno)

Note – only has to find one non-match