- 다음 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.