꿈꾸는 개발자, DBA 커뮤니티 구루비

5.1 개괄설명
5.2 바람직하지 않은 쿼리문
5.3 바람직한 쿼리문

5.1 개괄설명

1) 인라인뷰의 적절한 이용을 통해 실행계획을 효과적으로 제어할 수 있다.

2) 예시 테이블(3-14 페이지 참조)

사원테이블
급여테이블
가족테이블

사원:급여=1:M
사원:가족=1:M

3) 가정

급여테이블에는 모든 사원의 사번이 있다고 가정한다.(즉,회사에 적을 두고 있으면서 급여를 받지 못한 경우는 없다)
급여는 한달에 한번 지급된다.
사원중에는 가족이 없는 사원이 있을 수 있다.

5)문제제시

1998년 1월에 급여가 3,500,000 이상을 받은 사원들에 대해 1월 급여와 70세 이상의 가족 수를 구하라.

5.2 바람직하지 않은 쿼리문


SELECT a.사원번호, MIN(a.성명), MIN(급여총액),
       NVL(COUNT( * ),0) 고령자수  
FROM 사원 a, 급여 b, 가족 c
WHERE c.사원번호( + ) = b.사원번호
       and c.생년월일( + ) < '19280101'
      and a.사원번호    = b.사원번호
       and b.년월        = '199801'
      and b.급여총액    >= 3500000
GROUP BY a.사원번호


<NESTED LOOPS 조인인 경우>

SORT (GROUP BY)
   NESTED LOOPS (OUTER)
       NESTED LOOPS
           TABLE ACCESS (BY ROWID) OF '급여'
              INDEX (RANGE SCAN) OF '년월_IDX' 
           TABLE ACCESS (BY ROWID) OF '사원'
              INDEX (UNIQUE SCAN) OF '사원_PK' 
       TABLE ACCESS (BY ROWID) OF '가족'
          INDEX (RANGE SCAN) OF '가족_PK'


<SORT MERGE 조인인 경우>

SORT (GROUP BY NOSORT) 
     MERGE JOIN 
         MERGE JOIN (OUTER) 
            SORT (JOIN) 
                TABLE ACCESS (BY ROWID) OF '급여' 
                   INDEX (RANGE SCAN) OF '년월_IDX' 
            SORT (JOIN) 
                TABLE ACCESS (BY ROWID) OF '가족' 
                   INDEX (RANGE SCAN) OF '생년월일_IDX' 
         SORT (JOIN)  
             TABLE ACCESS (FULL) OF '사원'

1) NESTED LOOPS 조인인 경우 모든 사원에 대해 처리해야 했으므로 급여테이블(급여테이블이 드라이빙) 혹은 사원테이블(사원테이블이 드라이빙)로부터 받은 사원번호가 범위를 줄여주지 못함.
2) SORT MERGE 조인의 경우도 다른 테이블로 부터 영향을 받지 않고 각자의 테이블을 스캔하므로 범위를 줄여주지 못함.
3) 즉, 위 두 경우 모두 70세 이상의 가족수는 그리 많지 않을 것임이 분명함에도 불구하고 모든 사원에 대해 검색을 하며 힘들게 검색한 로우가 종국에는 생년월일 체크 조건에 의해서 나머지 대부분 버려짐으로써 운반단위를 늦게 채우는 현상이 발생한다.
4) 가족테이블의 경우 생년월일에 설령 인덱스가 잡혀 있다 하더라도 사원번호라고 하는 강력한 주전선수를 가지고 있는 급여,사원테이블에게 경쟁에 밀려 드라이빙 테이블이 될 가능성은 적으며 설령 힌트나 SUPRESS기법을 통해 가족테이블이 드라이빙이 될지라도 1.단계적 조인을 위한 활용편에서 보았던 잘못을 범하게 된다.(불필요한 조인의 발생)

5.3 바람직한 쿼리문


SELECT /*+ ORDERED USE_NL(x y) */
       y.사원번호, y.성명, 급여총액, 고령자수  
FROM 
( 
         SELECT /*+ USE_MERGE( b c) */ 
                b.사원번호, 급여총액, 고령자수
           FROM 급여 b, 
	     ( SELECT 사원번호, COUNT( * ) 고령자수 
                   FROM 가족 
                   WHERE 생년월일 < '19280101'
                GROUP BY 사원번호 
                 ) c
          WHERE c.사원번호( + ) = b.사원번호
                  and b.년월 = '199801'
                and b.급여총액 >= 3500000 
) x, 사원 y
 WHERE y.사원번호 = x.사원번호 ;

1) 가족 테이블의 생년월일 인덱스를 범위처리하여 생년월일이 19280101 미만인 로우만 엑세스한 후 사원번호로 group by하여 내부적으로 저장.
2) 급여테이블을 년월인덱스로 범위처리하여 급여총액을 체크하여 만족한 로우만 저장.
3) 이 집합들을 사원번호로 머지.(Merge outer조인)
4) 머지된 사원번호에 대해서만 사원테이블의 기본키로 엑세스(Nested Loops조인)함으로써 엑세스 효율을 높임.
5) 또한 /*+ ORDERED USE_NL(x y) */ 힌트를 이용하여 사원테이블이 드라이빙 테이블이 됨으로써 전체 사원을 엑세스할지도 모르는 사태를 방지함.
6) 1.단계적인 조인을 위한 활용편에서 보았듯 1:M 관계인 두 테이블이 조인되고 M쪽의 자료를 집계하는 형태의 쿼리라면 M쪽을 GROUP BY로 인라인 뷰로 만들어 1쪽의 테이블과 조인하는 것이 바람직하다.