*서브쿼리 unnesting한 결과가 항상 더 나은 성능을 보장하지 않음.

서브쿼리 unnesting과 관련된 힌트(오라클)

unnest : 서브쿼리를 unnesting함으로 조인방식으로 최적화 유도

no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화

상황에 따른 서브쿼리 Unnesting

1.메인쿼리와 서브쿼리가 M:1관계이고 서브쿼리 컬럼이 PK/Unique 컬럼일때

일반 조인문으로 바꿔도 결과가 보장됨.

2.서브쿼리가 M쪽 집합이거나 서브쿼리 컬럼이 Nonunique인덱스일 때

select *

 from dept

where deptno in (select deptno

 from emp)

select *

 from (select deptno from emp) a, dept b

where a.deptno = b.deptno

->일반 조인문 형태로 변환할경우 M쪽 집합인 emp 테이블 단위의 결과집합이 생성되므로 오류.

--서브쿼리 컬럼(deptno)이 PK/Unique제약이나 Unique인덱스가 없을경우

select *

 from emp

where deptno in (select deptno

  from dept) 

-->emp와 dept간의 관계를 알수없기 때문에 일반 조인문으로 쿼리 변환을 시도 하지 않음.

    이럴경우 옵티마이저는 다음 두가지 방식중 하나를 선택.

1. 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙 될경우 sort unique오퍼레이션을 수행 후 조인
2. 메인쿼리 쪽 테이블이 드라이빙될경우 세미 조인 방식으로 조인.

3.뷰 Merging

사용자 관점의 편의성

<쿼리1>

select *

 from (select * from emp where job ='SALESMAN') a

        (select * from dept where loc = 'CHICAGO')b

where a.deptno = b.deptno

-->사람의 눈으로 볼때 쿼리를 블록화하는 것이 더 편하지만 옵티마이저 입장에서는 더 불편

    옵티마이저 입장에서는 가급적 쿼리 블록을 풀어 내려는 습성을 갖음.

<쿼리2>

select *

 from emp a, dept b

where a.job = 'SALESMAN'

  and b.loc ='CHICAGO'

뷰머징 : <쿼리1>의 뷰쿼리 블록은 액세스 쿼리 블록과 머지과정을 거쳐 <쿼리2>와 같은 형태로 변환되는데 이를 뷰 머징이라고 함

             (뷰머징을 해야 옵티마이저가 더 다양항 엑세스 경로를 조사 대상으로 삼을수 있음)

create or replace view emp_salesman 

as 

select empno, ename, job, mgr, hiredate, sal, comm, deptno 

 from emp 

where job = 'SALESMAN' ;

emp_salesman view와 조인

select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname 

  from emp_salesman e, dept d

 where d.deptno = e.deptno 

    and e.sal >= 1500 ;

위 쿼리를 뷰 Merging 하지 않고 그대로 최적화한다면 아래와 같은 실행계획이 만들어진다.

Execution Plan ------------------------------------------------------------- 

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=156) 

1 0 NESTED LOOPS (Cost=3 Card=2 Bytes=156) 

2 1   VIEW OF 'EMP_SALESMAN' (VIEW) (Cost=2 Card=2 Bytes=130) 

3 2      TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2 ) 

4 3          INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX) (Cost=1 Card=7)

5 1      TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13) 

6 5          INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)

뷰 Merging이 작동한다면 변환된 쿼리는 아래와 같은 모습일 것이다.

select e.empno, e.ename, e.job, e.mgr, e.sal, d.dname 

  from emp e, dept d 

 where d.deptno = e.deptno 

    and e.job = 'SALESMAN' 

    and e.sal >= 1500

Execution Plan ------------------------------------------------------------- 

0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=2 Bytes=84) 

1 0 NESTED LOOPS (Cost=3 Card=2 Bytes=84) 

2 1  TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (TABLE) (Cost=2 Card=2 Bytes=58) 

3 2  INDEX (RANGE SCAN) OF 'EMP_SAL_IDX' (INDEX) (Cost=1 Card=7) 

4 1  TABLE ACCESS (BY INDEX ROWID) OF 'DEPT' (TABLE) (Cost=1 Card=1 Bytes=13) 

5 4  INDEX (UNIQUE SCAN) OF 'DEPT_PK' (INDEX (UNIQUE)) (Cost=0 Card=1)

예시로든 단순한 뷰는 merging하더라도 성능이 나빠지지 않으나

아래와 같이 복잡한 연산을 포함한경우 뷰를 merging하면 오히려 성능이 더 나빠질 수도 있음.

• group by 절
• select-list에 distinct 연산자 포함

조건절 이행이 되고(a.deptno=30) 해당 조건이 뷰안으로 pushing됨.

select b.deptno, b.dname, a.avg_sal 

  from (select deptno, avg(sal) avg_sal 

      from emp 

            group by deptno) a , dept b 

  where a.deptno = b.deptno 

     and b.deptno = 30 

     and a.deptno = 30

나.조건절(Predicate) Pullup

뷰 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내어 해당 조건을 다른 쿼리 블록에 Pushdown하는데 사용

select * 

  from (select deptno, avg(sal) 

          from emp 

      where deptno = 10 

   group by deptno) e1 ,

 (select deptno, min(sal), max(sal) 

    from emp 

   group by deptno) e2 

  where e1.deptno = e2.deptno 

--------------------------------------------------------------------------------------- 

| Id | Operation                                | Name           |     Rows | Bytes | 

--------------------------------------------------------------------------------------- 

| 0 | SELECT STATEMENT                          |                |        1 |    65 | 

|* 1 | HASH JOIN                                |    |        1 |    65 |

| 2 |     VIEW                                |                |        1 |    26 | 

| 3 |        HASH GROUP BY                      |                |        1 |     5 | 

| 4 |    TABLE ACCESS BY INDEX ROWID           | EMP            |        5 |    25 | 

|* 5 |        INDEX RANGE SCAN                  | EMP_DEPTNO_IDX  |        5 |       | 

| 6 |      VIEW                                 |                |        1 |    39 | 

| 7 |  HASH GROUP BY                           |                |        1 |     5 | 

| 8 |    TABLE ACCESS BY INDEX ROWID           | EMP            |        5 |    25 | 

|* 9 |        INDEX RANGE SCAN                  | EMP_DEPTNO_IDX |        5 |       |

--------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id):

------------------------------------------------------------ 

1 - access("E1"."DEPTNO"="E2"."DEPTNO") 

5 - access("DEPTNO"=10) 

9 - access("DEPTNO"=10)

인라인 뷰 e2에는 deptno = 10 조건이 없지만 Predicate 정보를 보면 양쪽 모두 이 조건이 emp_deptno_idx 인덱스의 액세스 조건으로사용됨. 

아래와 같은 형태로 쿼리 변환이 일어남.

select * 

  from (select deptno, avg(sal) 

            from emp 

          where deptno = 10 

          group by deptno) e1 ,

       (select deptno, min(sal), max(sal) 

            from emp

          where deptno = 10 

          group by deptno) e2 

 where e1.deptno = e2.deptno

다.조인조건(Join Predicate) Pushdown

 NL Join 수행 중에 드라이빙 테이블에서 읽은 조인 컬럼 값을 Inner 쪽 뷰 쿼리 블록 내에서 참조 할수 있도록 하는 기능

*오라클 11g에서만 가능

select d.deptno, d.dname, e.avg_sal 

  from dept d ,(select deptno, avg(sal) avg_sal 

       from emp 

     group by deptno) e 

  where e.deptno(+) = d.deptno

-----------------------------------------------------------------------------------

| Id | Operation                                 | Name           | Rows | Bytes | 

----------------------------------------------------------------------------------

| 0 | SELECT STATEMENT                           |                |    4 |   116 | 

| 1 |   NESTED LOOPS OUTER                       |                |    4 |   116 | 

| 2 |     TABLE ACCESS FULL                      | DEPT           |    4 |    64 | 

| 3 |     VIEW PUSHED PREDICATE                  |                |    1 |    13 | 

|* 4 |      FILTER                               |                |      |       | 

| 5 |          SORT AGGREGATE                    |                |    1 |     7 | 

| 6 |            TABLE ACCESS BY INDEX ROWID     | EMP            |    5 |    35 | 

|* 7 |              INDEX RANGE SCAN             | EMP_DEPTNO_IDX |    5 |       | 

--------------------------------------------------------------------------------

Predicate Information (identified by operation id):

--------------------------------------------------------------------------------

4 - filter(COUNT(*)>0) 

7 - access("DEPTNO"="D"."DEPTNO")

oracle 10g에서 실행시

조인 조건 Pushdown이 작동하지 않아 아래와 같이 emp 쪽 인덱스를 Full Scan하는 실행계획. 

dept 테이블에서 읽히는 deptno마다 emp 테이블 전체를 group by 하므로 성능상 불리.

-------------------------------------------------------------------------------

| Id | Operation                                | Name         | Rows | Bytes | 

--------------------------------------------------------------------------------

| 0 | SELECT STATEMENT                          |              |    4 |    148 | 

| 1 |    NESTED LOOPS OUTER                     |                         |    4 |    148 | 

| 2 |      TABLE ACCESS FULL                    | DEPT                |    4 |     44 | 

|* 3 |     VIEW                                 |               |   1 |     26 | 

|4 |          SORT GROUP BY                     |               |   3 |     21 | 

| 5 |            TABLE ACCESS BY INDEX ROWID    | EMP           |  14 |     98 | 

| 6 |               INDEX FULL SCAN             | EMP_DEPTNO_IDX|  14 |        |

--------------------------------------------------------------------------------

Predicate Information (identified by operation id): 

--------------------------------------------------------------------------------

3 - filter("E"."DEPTNO"(+)="D"."DEPTNO")

집계함수가 하나이며 10g버전 이하일경우 다음처럼 스칼라 서브 쿼리를 통해 부분범위 처리가 가능

select d.deptno, d.dname ,(select avg(sal) from emp where deptno = d.deptno) 

  from dept d

집계함수가 여러 개일 때가 문제인데, 만약 아래와 같이 쿼리하면 emp에서 같은 범위를 반복적으로 액세스하는 비효율이 발생

select d.deptno, d.dname ,(select avg(sal) from emp where deptno = d.deptno) avg_sal ,

       (select min(sal) from emp where deptno = d.deptno) min_sal ,

     (select max(sal) from emp where deptno = d.deptno) max_sal 

  from dept d

이럴 때는 아래 처럼 구하고자 하는 값들을 모두 결합하고서 바깥쪽 액세스 쿼리에서 substr 함수로 분리하는 방법이 유용.

select deptno, dname , to_number(substr(sal, 1, 7)) avg_sal , 

        to_number(substr(sal, 8, 7)) min_sal , 

to_number(substr(sal, 15)) max_sal

    from ( select /*+ no_merge */ d.deptno, d.dname ,

       (select lpad(avg(sal), 7) || lpad(min(sal), 7) || max(sal) 

                    from emp 

           where deptno = d.deptno) sal 

  from dept d )

5.조건절 이행

「(A = B)이고 (B = C)이면 (A = C)이다」 라는 추론을 통해 새로운 조건절을 내부적으로 생성해 주는 쿼리변환.

「(A > B)이고 (B > C)이면 (A > C)이다」와 같은 추론도 가능. 

select * 

 from dept d, emp e 

where e.job = 'MANAGER' 

   and e.deptno = 10 

    and d.deptno = e.deptno

위 쿼리에서 deptno = 10은 emp 테이블에 대한 필터 조건. 

하지만 아래 실행계획에 나타나는 Predicate 정보를 확인해 보면, dept 테이블에도 같은 필터 조건이 추가됨.

------------------------------------------------------------------------------------ 

| Id | Operation                  |        Name |     Rows |   Bytes | Cost (%CPU) | 

------------------------------------------------------------------------------------ 

|0 | SELECT STATEMENT             |             |        1 |      57 |       2 (0) | 

| 1 | NESTED LOOPS                |             |        1 |      57 |       2 (0) | 

| 2 | TABLE ACCESS BY INDEX ROWID |        DEPT |        1 |      20 |       1 (0) | 

|* 3 | INDEX UNIQUE SCAN          |     DEPT_PK |        1 |         |       0 (0) | 

| 4 | TABLE ACCESS BY INDEX ROWID |         EMP |        1 |      37 |       1 (0) | 

|* 5 | INDEX RANGE SCAN           |     EMP_IDX |        1 |         |       0 (0) |

------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 

--------------------------------------------------- 

3 - access("D"."DEPTNO"=10) 

5 - access("E"."DEPTNO"=10 AND "E"."JOB"='MANAGER')

[e.deptno = 10]이고 [e.deptno = d.deptno]이므로 [d.deptno = 10]으로 추론되었고,

이런 조건절 이행(transitive)을 통해 쿼리가 아래와 같은 형태로 변환.

select * 

 from dept d, emp e 

where e.job = 'MANAGER'

   and d.deptno = e.deptno

   and e.deptno = 10 

   and d.deptno = 10

--> 조인되는 데이터량을줄일 수 있으며, 인덱스 사용을 추가로 고려할 수 있게 돼 더 나은 실행계획을 수립할 가능성이 커짐.

6.불필요한 조인 제거

1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않는다면, 

쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. (결과집합에 영향을 미치지 않기 때문) 

옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 ‘조인 제거(Join Elimination)’ 또는 ‘테이블 제거(Table Elimination)’라고 한다.

select e.empno, e.ename, e.deptno, e.sal, e.hiredate 

 from dept d, emp e 

where d.deptno = e.deptno 

Rows Row Source Operation 

---- --------------------------------------------------- 

14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=58 us)

위 쿼리에서 조인 조건식을 제외하면 1쪽 집합인 dept에 대한 참조가 전혀 없다.

따라서 emp 테이블만 액세스한 것을 볼 수 있다.

(오라클:10g부터 동작 / sql server : 과거부터 적용됨)

조인 제거 기능이 작동하려면 아래와 같이 PK와 FK 제약이 설정돼 있어야만 한다.

만약 PK가 없으면 두 테이블 간 조인 카디널리티를 파악할 수 없고, FK가 없으면 조인에 실패하는 레코드가 존재할 수도 있어 옵티마이저가 함부로 쿼리 변환을 수행할 수가 없다.

SQL> alter table dept add 2 constraint deptno_pk primary key(deptno); 

SQL> alter table emp add 2 constraint fk_deptno foreign key(deptno) 3 references dept(deptno);

FK가 설정돼 있더라도 emp의 deptno 칼럼이 Null 허용 칼럼이면 결과가 틀리게 될 수 있다. 

조인 칼럼 값이 Null인 레코드는 조인에 실패해야 정상인데, 옵티마이저가 조인문을 함부로 제거하면 그 레코드들이 결과집합에 포함되기때문이다. 

이런 오류를 방지하기 위해 옵티마이저가 내부적으로 e.deptno is not null 조건을 추가해 준다. 

Outer 조인일 때는 not null 제약이나 is not null 조건은 물론, FK 제약이 없어도 논리적으로 조인 제거가 가능하지만,

Oracle 10g까지는 아래에서 보듯 조인 제거가 일어나지 않았다.

select e.empno, e.ename, e.sal, e.hiredate 

  from emp e, dept d 

 where d.deptno(+) = e.deptno -- Outer 조인 

Rows Row Source Operation 

---- --------------------------------------------------- 

15 NESTED LOOPS OUTER (cr=10 pr=0 pw=0 time=119 us) 

15 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=255 us) 

14 INDEX UNIQUE SCAN DEPT_PK (cr=2 pr=0 pw=0 time=265 us)(Object ID 58557)

11g에서는 아래와 같이 불필요한 Inner 쪽 테이블 제거 기능이 구현된 것을 볼 수 있다.

select e.empno, e.ename, e.sal, e.hiredate 

 from emp e, dept d 

where d.deptno(+) = e.deptno -- Outer 조인 

Rows Row Source Operation

---- --------------------------------------------------- 

14 TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=0 us cost=3 size=770 card=14)

아래는 SQL Server에서 테스트한 것인데, 마찬가지로 Inner 쪽 테이블이 제거된 것을 볼 수 있다.

select e.empno, e.ename, e.sal, e.hiredate 

 from dbo.emp e left outer join dbo.dept d 

    on d.deptno = e.deptno 'Emp' 

테이블. 스캔 수 1, 논리적 읽기 수 2, 물리적 읽기 수 0, 미리 읽기 수 0. 

SQL Server 실행 시간 : CPU 시간 = 0ms, 경과 시간 = 0ms. 

Rows Executes StmtText -- ----- -------------------------------------- 

14 1 select e.empno, e.ename, e.sal, e.hiredate 

14 1 |--Clustered Index Scan(OBJECT:([MyDB].[dbo].[Emp].[PK_Emp] AS [e]))

7.OR 조건을 Union으로 변환

아래 쿼리가 그대로 수행된다면 OR 조건이므로 Full Table Scan으로 처리될 것이다.

select * 

 from emp 

 where job = 'CLERK' or deptno = 20

만약 job과 deptno에 각각 생성된 인덱스를 사용하고 싶다면 아래와 같이 union all 형태로 바꿔주면 된다.

select * 

 from emp  

where job = 'CLERK' 

union all 

select * 

 from emp 

where deptno = 20 

   and LNNVL(job='CLERK')

사용자가 쿼리를 직접 바꿔주지 않아도 옵티마이저가 이런 작업을 대신해 주는 경우가 있는데, 이를 ‘OR-Expansion’이라고 한다. 아래는OR-Expansion 쿼리 변환이 일어났을 때의 실행계획과 Predicate 정보다.

------------------------------------------------------------------------------------------- 

| Id | Operation                              | Name             | Rows |   Bytes | 

-------------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT                        |                  |    7 |     224 | 

| 1 |   CONCATENATION                         |                  |      |         | 

| 2 |     TABLE ACCESS BY INDEX ROWID         | EMP              |    3 |      96 | 

|* 3 |       INDEX RANGE SCAN                 | EMP_JOB_IDX      |    3 |         | 

|* 4 |    TABLE ACCESS BY INDEX ROWID         | EMP              |    4 |     128 | 

|* 5 |       INDEX RANGE SCAN                 | EMP_DEPTNO_IDX   |    5 |         | 

------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 

------------------------------------------------------------- 

3 - access("JOB"='CLERK') 

4 - filter(LNNVL("JOB"='CLERK')) 

5 - access("DEPTNO"=20)

분기된 두 쿼리가 각각 다른 인덱스를 사용하긴 하지만, emp 테이블 액세스가 두 번 일어난다. 

--> 중복 액세스되는 영역(deptno=20이면서 job=‘CLERK’)의 데이터 비중이 작을수록 효과적이고, 

그 반대의 경우라면 오히려 쿼리 수행 비용이 증가한다.(OR-Expansion 쿼리 변환이 처음부터 비용기반으로 작동한 것도 이 때문)

중복 액세스되더라도 결과집합에는 중복이 없게 하려고 union all 아래쪽에 Oracle이 내부적으로 LNNVL 함수를 사용

(job < > ‘CLERK’ 이거나 job is null인 집합만을 읽으려는 것이며, 이 함수는 조건식이 false이거나 알 수 없는(Unknown) 값일 때 true를리턴)

Oracle에서 OR-Expansion을 제어

use_concat : OR-Expansion을 유도

no_expand :OR-Expansion 방지

select /*+ USE_CONCAT */ * 

 from emp 

 where job = 'CLERK' or deptno = 20;

select /*+ NO_EXPAND */ * 

  from emp 

 where job = 'CLERK' or deptno = 20;

8.기타 쿼리 변환

가.집합 연산을 조인으로 변환

Intersect나 Minus 같은 집합(Set) 연산을 조인 형태로 변환하는 것.

아래는 deptno = 10에 속한 사원들의 job, mgr을 제외시키고 나머지 job, mgr 집합만을 찾는 쿼리인데, 각각 Sort Unique 연산을 수행한후에 Minus 연산을 수행하는 것을 볼 수 있다.

SQL> select job, mgr from emp 

     2 minus 

     3 select job, mgr from emp 

     4 where deptno = 10 ; 

--------------------------------------------------------------------------------------

| Id | Operation              | Name  | Rows | Bytes | Cost (%CPU) |    Time | 

--------------------------------------------------------------------------------------

| 0 | SELECT STATEMENT        |         |   14 |    362 |     8 (63) | 00:00:01 | 

| 1 | MINUS                   |         |      |        |            |          | 

| 2 |   SORT UNIQUE           |         |   14 |    266 |     4 (25) | 00:00:01 | 

| 3 |      TABLE ACCESS FULL  | EMP     |   14 |    266 |      3 (0) | 00:00:01 | 

| 4 |   SORT UNIQUE           |         |    3 |     96 |     4 (25) | 00:00:01 | 
|* 5 |    TABLE ACCESS FULL   | EMP     |    3 |     96 |      3 (0) | 00:00:01 |

--------------------------------------------------------------------------------------

Predicate Information (identified by operation id): 

--------------------------------------------------------------------------------------

5 - filter("DEPTNO"=10)

아래는 옵티마이저가 Minus 연산을 조인 형태로 변환했을 때의 실행계획.

---------------------------------------------------------------------------------

| Id | Operation             | Name     | Rows | Bytes | Cost (%CPU) |     Time | 

---------------------------------------------------------------------------------

| 0 | SELECT STATEMENT       |  |   13 |   663 |      8 (25) | 00:00:01 | 

| 1 | HASH UNIQUE            |          |   13 |   663 |      8 (25) | 00:00:01 | 

|* 2 |   HASH JOIN ANTI      |          |   13 |   663 |      7 (15) | 00:00:01 | 

| 3 |      TABLE ACCESS FULL | EMP  |   14 |   266 |       3 (0) | 00:00:01 | 

|* 4 |     TABLE ACCESS FULL | EMP  |    3 |    96 |       3 (0) | 00:00:01 | 

---------------------------------------------------------------------------------

Predicate Information (identified by operation id): 

---------------------------------------------------------------------------------

2 - access(SYS_OP_MAP_NONNULL("JOB")=SYS_OP_MAP_NONNULL("JOB") AND SYS_OP_MAP_NONNULL("MGR")=SYS_OP_MAP_NONNULL("MGR")) 

4 - filter("DEPTNO"=10)

해시 Anti 조인을 수행하고 나서 중복 값을 제거하기 위한 Hash Unique 연산을 수행.

아래와 같은 형태로 쿼리 변환이 일어난 것이다.

SQL> select distinct job, mgr from emp e 

    2 where not exists ( 

    3 select 'x' from emp 

    4 where deptno = 10 

    5 and sys_op_map_nonnull(job) = sys_op_map_nonnull(e.job) 

    6 and sys_op_map_nonnull(mgr) = sys_op_map_nonnull(e.mgr) 

    7 ) ;

sys_ p_map_nonnull :오라클의 비공식적인 함. null 값끼리 ‘=’ 비교(null = null)하면 false이지만 true가 되도록 처리. 

      위에서는 job과 mgr이 null 허용 칼럼이기 때문에 위와 같은 처리가 일어났다.

나.조인 컬럼에 IS NOT NULL 조건 추가

select count(e.empno), count(d.dname) 

 from emp e, dept d  

where d.deptno = e.deptno 

   and sal <= 2900

위와 같은 조인문을 처리할 때 조인 칼럼 deptno가 null인 데이터는 조인 액세스가 불필요.

(어차피 조인에 실패)

 따라서 아래와 같이 필터 조건을 추가해 주면 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있어 쿼리 성능 향상에 도움.

select count(e.empno), count(d.dname) 

 from emp e, dept d 

where d.deptno = e.deptno 

   and sal <= 2900 

   and e.deptno is not null 

   and d.deptno is not null

is not null 조건을 사용자가 직접 기술하지 않더라도, 옵티마이저가 필요하다고 판단되면(Oracle의 경우, null 값 비중이 5% 이상일 때) 내부적으로 추가해 준다.

다.필터 조건 추가

between 쿼리를 수행할 때 사용자가 :mx보다 :mn 변수에 더 큰 값을 입력한다면 쿼리 결과는 공집합이다.

select * 

  from emp 

 where sal between :mn and :mx

사전에 두 값을 비교해 알 수 있음에도 쿼리를 실제 수행하고서야 공집합을 출력한다면 매우 비합리적이다. 

----------------------------------------------------------- 

| Id | Operation             | Name | Rows | Bytes | Cost | 

----------------------------------------------------------- 

| 0 | SELECT STATEMENT       |      |    1 |    32 |    2 | 

|* 1 | FILTER                |      |      |       |      | 

|* 2 |   TABLE ACCESS FULL   |  EMP |    1 |    32 |    2 |

-----------------------------------------------------------

Predicate Information (identified by operation id): 

------------------------------------------------------------ 

1 - filter(TO_NUMBER(:MN)<=TO_NUMBER(:MX)) 

2 - filter("EMP"."SAL">=TO_NUMBER(:MN) AND "EMP"."SAL"<=TO_NUMBER(:MX))

아래는 :mn에 5000, :mx에 100을 입력하고 실제 수행했을 때의 결과인데, 

블록 I/O가 전혀 발생하지 않은 것을 볼 수 있다. 

실행계획 상으로는 Table Full Scan을 수행하고 나서 필터 처리가 일어나는 것 같지만 실제로는 Table Full Scan 자체를 생략한 것이다.

Statistics 

---------------------------------------------------------- 

0 recursive calls 

0 db block gets 

0 consistent gets 

0 physical reads .. .....

라.조건절 비교 순서