메뉴 건너뛰기

bysql.net

제2절_쿼리변환

2012.05.14 00:21

ljw 조회 수:5613

1.쿼리변환(Query Transformation)

옵티마이저가 SQL을 분석해 의미적으로 동일(결과가 동일)하면서도 더 나은 성능이 기대되는 형태로 SQL을 재작성하는 것.

실행계획 생성/비용 산정 전에 SQL을 최적화에 유리한 형태로 재작성하며

옵티마이저의 sub엔진중에 하나인 Query Transformer가 담당.


쿼리변환의 종류

  • 휴리스틱(Heuristic) 쿼리 변환 : 결과만 보장된다면 무조건 쿼리 변환을 수행. 일종의 규칙 기반 최적화 기법
  • 비용기반(Cost-based) 쿼리 변환 : 변환된 쿼리의 비용이 더 낮을 때만 수행.


2.서브쿼리 Unnesting
중첩된 서브쿼리(Nested Subquery)를 풀어내는 것. 
서브쿼리를 메인쿼리와 같은 레벨로 풀어내어 다양한 액세스 경로와 조인 방식을 평가.
(옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아짐)

*서브쿼리의 예

select * 

  from emp a 

where exists ( select 'x' 

      from dept 

                   where deptno = a.deptno )

   and sal > (select avg(sal) 

                   from emp b 

                 where exists ( select 'x' 

         from salgrade 

     where b.sal between losal 

and hisal and grade = 4) )



우의 중첩된 서브쿼리는 관계가 종속적-계층적 이다.

논리적인 관점에서는 메인쿼리에서 읽히는 레코드마다 서브쿼리를 반복 수행해야하지만

이와 같은 필터방식이 속도를 보장하지 못하므로 옵티마이저는 아래 둘중 하나를 선택한다.


  1. 동일한 결과를 보장하는 조인문으로 변환 하고나서 최적화.(서브쿼리 unnesting)
  2. 서브쿼리를 unnesting하지 않고 원상태에서 최적화. 메인쿼리와 서브쿼리 각각 최적화. 이때 서브쿼리에 필터 오퍼레이션이 나타남


--no_unnest : filter로 실행계획이 풀리는 것을 확인 할 수 있다.

explain plan for

select *

 from employees

where department_id in ( select /*+ no_unnest */ department_id

 from departments);


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

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

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

|   0 | SELECT STATEMENT   |            |    10 |   680 |     3   (0)| 00:00:01

|*  1 |  FILTER            |            |       |       |            |

|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  7276 |     3   (0)| 00:00:01

|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     1 |     4 |     0   (0)| 00:00:01

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

Predicate Information (identified by operation id):

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

  1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPARTMENTS"

             "DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1))




--서브쿼리 unnesting

explain plan for

select *

 from employees

where department_id in ( select department_id

 from departments);


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

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

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

|   0 | SELECT STATEMENT |             |   106 |  7632 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS        |             |   106 |  7632 |     3   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  7276 |     3   (0)| 00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |      1 |      4 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  3 - access("DEPARTMENT_ID"="DEPARTMENT_ID")





*서브쿼리 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. 메인쿼리 쪽 테이블이 드라이빙될경우 세미 조인 방식으로 조인.

--서브쿼리가 먼저 읽히는 상황

--또한 서브쿼리에 department_id에 pk, 유니크 제약 조건을 걸지 않았기 때문에

--옵티마이저 입장에서는

--dept테이블이 unique하다는 사실을 모르기 때문에 SORT UNIQUE 오퍼레이션이 나타남


explain plan for

select /*+ leading(dept) */ *

 from emp

where department_id in ( select department_id

   from dept);

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

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

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

|   0 | SELECT STATEMENT    |       |   106 | 15476 |     8  (25)| 00:00:01 |

|*  1 |  HASH JOIN |       |   106 | 15476 |     8  (25)| 00:00:01 |

|   2 |   SORT UNIQUE |      |    27 |    351 |     3   (0)| 00:00:01 |

|   3 |    TABLE ACCESS FULL| DEPT |    27 |    351 |     3   (0)| 00:00:01 |

|   4 |   TABLE ACCESS FULL | EMP  |   107 | 14231 |     3   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  1 - access("DEPARTMENT_ID"="DEPARTMENT_ID")





--nested loop semi

--sort unique 오퍼레이션을 생략하고  nested loop 를 돌면서 만족하는 건이 나오면

--다음 outer row에 대해 연산을 시작

explain plan for

select *

 from employees a

where exists ( select department_id

 from departments

where department_id = a.department_id);


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

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

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

|   0 | SELECT STATEMENT   |             |   106 |  7632 |     3   (0)| 00:00:01 |

|   1 |  NESTED LOOPS SEMI |             |   106 |  7632 |     3   (0)| 00:00:01 |

|   2 |   TABLE ACCESS FULL| EMPLOYEES  |   107 |  7276 |     3   (0)| 00:00:01 |

|*  3 |   INDEX UNIQUE SCAN| DEPT_ID_PK |     27 |   108 |     0   (0)| 00:00:01 |

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

Predicate Information (identified by operation id):

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

  3 - access("DEPARTMENT_ID"="A"."DEPARTMENT_ID")



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 연산자 포함
뷰 merging이 불가능한 경우
  • 집합 연산자가 사용된 경우(union, union all, intersect, minus)
  • connect by 절
  • ROWNUM pseudo 컬럼
  • select-list에 집계 함수(avg, count, max, min, sum) 사용
  • 분석 함수

4.조건절 pushing
뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 밀어 넣는 기능.
--> 뷰 안에서의 처리 일량을 최소화 및 리턴되는 결과 건수를 줄임으로 일량을 줄인다.

  • 조건절(Predicate) Pushdown : 쿼리 블록 밖에 있는 조건절을 쿼리 블록 안쪽으로 밀어 넣는것
  • 조건절(Predicate) Pullup :쿼리 블록 안에 있는 조건절을 쿼리 블록 밖으로 내오는 것. 그 조건을 다시 다른 쿼리 블록에 pushdown하는데 사용
  • 조인조건(Join Predicate) Pushdown : NL Join 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것.

가.조건절(Predicate) Pushdown

select deptno, avg_sal 

from (select deptno, avg(sal) avg_sal 

    from emp group by deptno) a 

where deptno = 30


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

| Id | Operation                               | Name            | Rows  | Bytes | 

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

| 0 | SELECT STATEMENT                         |                 | 1     |     26| 

| 1 | VIEW                                     |                 | 1     |     26| 

| 2 |    SORT GROUP BY NOSORT                  |                 | 1     |      7| 

| 3 |      TABLE ACCESS BY INDEX ROWI D        | EMP             | 6     |     42|

|* 4 |        INDEX RANGE SCAN                 | EMP_DEPTNO_IDX  | 6     |       |

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

Predicate Information (identified by operation id): 

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

4 - access("DEPTNO"=30)


쿼리 변환이 작동하지 않을경우 : emp 테이블을 Full Scan 하고서 group by 이후에 deptno = 30 조건을 필터링.

쿼리 변환이 작동한 경우: 조건절 Pushing 작동함으로써 emp_deptno_idx 인덱스를 사용


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


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

| Id | Operation                               | Name           | Rows | Bytes | 

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

| 0 | SELECT STATEMENT                         |                | 1    |    39 | 

| 1 |   NESTED LOOPS                           |                | 1    |    39 | 

| 2 |     TABLE ACCESS BY INDEX ROWID          | DEPT           | 1    |    13 | 

|* 3 |       INDEX UNIQUE SCAN                 | DEPT_PK        | 1    |       | 

| 4 |      VIEW                                |                | 1    |    26 | 

| 5 |        SORT GROUP BY                     |                | 1    |     7 |

| 6 |          TABLE ACCESS BY INDEX ROWID     | EMP            | 6    |    42 | 

|* 7 |            INDEX RANGE SCAN             | EMP_DEPTNO_IDX | 6    |       |

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

Predicate Information (identified by operation id): 

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

3 - access("B"."DEPTNO"=30) 

7 - access("DEPTNO"=30)



조건절 이행이 되고(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 .. .....



라.조건절 비교 순서

 데이터를 아래 SQL문으로 검색하면 B 칼럼에 대한 조건식을 먼저 평가하는 것이 유리하다.

왜냐하면대부분 레코드가 B = 1000 조건을 만족하지 않아 A 칼럼에 대한 비교 연산을 수행하지 않아도 되기 때문이다.


SELECT * 

  FROM T 

 WHERE A = 1 

     AND B = 1000 ;


반대로 A = 1 조건식을 먼저 평가한다면, A 칼럼이 대부분 1이어서 B 칼럼에 대한 비교 연산까지 그만큼 수행해야 하므로 CPU 사용량이늘어날 것이다.


아래와 같은 조건절을 처리할 때도 부등호(>) 조건을 먼저 평가하느냐 like 조건을 먼저 평가하느냐에 따라 일량에 차이가 생긴다.


select /*+ full(도서) */ 도서번호도서명가격저자출판사, isbn 

 from 도서 

 where 도서명 like '데이터베이스%' -- 사용자가 입력한 검색 키워드 

   and 도서명 > '데이터베이스성능고도화' --  페이지 화면에서 출력한 가장 마지막 도서명


DBMS 또는 버전에 따라 다르지만예전 옵티마이저는 where절에 기술된 순서 또는 반대 순서로 처리하는 내부 규칙을 따름으로써 비효율을 야기하곤 했다


하지만 최신 옵티마이저는 비교 연산해야  일량을 고려해 선택도가 낮은 칼럼의 조건식부터 처리하도록 내부적으로 순서를 조정한다.

번호 제목 글쓴이 날짜 조회 수
58 Week1_박우창 [1] balto 2012.07.17 9892
57 Week1_이주영 suspace 2012.07.17 5025
56 Week1_승대수 보라빛고양이 2012.07.17 3053
55 Week1_이진우 [5] ljw 2012.07.16 46262
54 Week1_위충환 [1] 실천하자 2012.07.16 9272
53 문제작성 실천하자 2012.07.16 5794
52 제5절_배치_프로그램_튜닝 보라빛고양이 2012.07.04 6906
51 제4절_파티션_활용 오예스 2012.06.26 5753
50 제3절_DML_튜닝 balto 2012.06.13 9073
49 제1절_고급_SQL_활용 실천하자 2012.06.11 6285
48 제1절 고급 SQL 활용 실천하자 2012.06.11 8397
47 제4절_고급_조인_기법 suspace 2012.06.05 31231
46 제2절_소트_튜닝 file ljw 2012.06.04 12024
45 제3절 조인 기본 원리 보라빛고양이 2012.05.30 6980
44 제1절_인덱스_기본_원리 오예스 2012.05.22 7100
43 제2절_인덱스_튜닝 balto 2012.05.18 9904
» 제2절_쿼리변환 ljw 2012.05.14 5613
41 제1절_옵티마이저 실천하자 2012.05.12 7185
40 제3절_동시성_제어 운영자 2012.05.08 6036
39 제1절_Lock balto 2012.05.05 25258