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

다음 SQL을 통하여 실행계획의 제어에 대해서 알아보자.


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.사원번호

위의 SQL이 NESTED LOOPS JOIN인 경우와 SORT MERGER JOIN인 경우 실행계획은 다음과 같다.


<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'

- 사원 테이블을 '전체 테이블 스캔' 방법으로 읽어 내려간다.
- 각각의 사원번호에 대해 급여 테이블의 지급년월이 '199801'인 로우를 기본키로 한건씩 (Unique Scan) 찾아 급여총액을 체크하여 만족하면,
- 해당 사원번호에 대해 가족 테이블의 기본키를 이용해 범위처리 (Range Scan) 하여 테이블을 읽고 생년월일을 체크한다.
- 이 때 가족 테이블에 만족하는 로우가 없어도 결과는 추출된다. (Outer Join)


<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 '사원'

- 먼저 가족 테이블의 생년월일 인덱스를 범위처리하여 만 70세 이상인 로우만 액세스하여 사원번호로 GROUP BY 하여 내부적으로 저장한다.
- 이번에는 급여 테이블을 년월 인덱스로 범위처리하여 급여총액을 체크하여 만족한 로우만 사원번호별로 정렬하여 저장한다.
- 주어진 조건에 비추어 볼 때 저장된 두 개의 집합은 크지 않다는 것을 알 수 있다. 이 집합들은 사원번호로 머지 된다. (Merge Outer Join)
- 머지된 소량의 사원번호에 대하서만 사원 테이블의 기본키로 액세스된다. (Nested Loops Join)


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.사원번호 ;

최적의 SQL은 급여, 가족 테이블은 SORT MERGE JOIN을 하고 그 결과와 사원 테이블을 NESTED LOOPS JOIN을 하는 것이다. 옵티마이져가 앞에서 언급한 절차대로 실행되지 않는다면 위와 같은 방법을 사용하여 이를 해결할 수 있다.


SELECT STATEMENT
 NESTED LOOPS
  MERGE JOIN (OUTER)
   SORT (JOIN)
    TABLE ACCESS (BY INDEX ROWED) OF '급여'
     INDEX (RANGE SCAN) OF '급여_년월_IDX' (NON-UNIQUE)
   SORT (JOIN)
    VIEW
     SORT (GROUP BY)
      TABLE ACCESS (BY INDEX ROWID) OF '가족'
       INDEX (RANGE SCAN) OF '가족_생년월일_IDX' (NON-UNIQUE)
  TABLE ACCESS (BY INDEX ROWED) OF '사원'
   INDEX (UNIQUE SCAM) OF '사원_PK' (UNIQUE)


<NESTED LOOPS인 경우>

SELECT STATEMENT
 NESTED LOOPS
  NESTED LOOPS (OUTER)
   TABLE ACCESS (BY INDEX ROWED) OF '급여'
    INDEX (RANGE SCAN) OF '급여_년월_IDX' (NON-UNIQUE)
   VIEW
    SORT (GROUP BY)
     TABLE ACCESS (BY INDEX ROWID) OF '가족'
      INDEX (RANGE SCAN) OF '가족_생년월일_IDX' (NON-UNIQUE)
     TABLE ACCESS (BY INDEX ROWID) OF '사원'
      INDEX (UNIQUE SCAN) OF '사원_PK' (UNIQUE)

위와 같이 같은 SQL의 형태를 어떻게 구성했느냐, 혹은 어떤 실행계획이 수립되었느냐는 매우 중요하며 상황에 따라 차이가 있다.

About Doc.

최초작성자 : 안종식
최초작성일 : 2009년 5월 15일
이 문서는 오라클클럽 01.대용량 데이터베이스 스터디 모임에서 작성하였습니다.
{*}이 문서의 내용은 이화식님의 '대용량 데이터베이스 솔루션2'을 참고했습니다.*