참고소스
https://gitlab.com/kimdongy1000/sqltuningproject
EX22 참고
View 쿼리
하나의 쿼리를 뭉뚱그려셔 view 쿼리라고 부르게 됩니다 예를 들어서 다음과 같은 쿼리에서 view 쿼리는 이런 모양입니다
1
2
3
4
5
SELECT c.고객번호 , c.고객명 , t.평균거래 , t.최소거래 , t.최대거래
from 고객 c , (SELECT 고객번호 , avg(거래금액) 평균거래 , min(거래금액) 최소거래 , max(최대거래) FROM 거래 ...)
WHERE c.가입일시 >=
and t.고객번호 = c. 고객번호
이때 괄호 안에 들어간 SELECT 문을 View 쿼리라고 합니다 이때 최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화합니다
쿼리
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SELECT *
FROM EX22_CUSTOMER C , (
SELECT
CUSTOMER_ID c_id,
ROUND(AVG(TRANSACTION_AMOUNT)) avg_transaction_amt ,
MIN(TRANSACTION_AMOUNT) min_transaction_amt ,
MAX(TRANSACTION_AMOUNT) max_transaction_amt
FROM EX22_TRANSACTION
WHERE TRANSACTION_DATE >= trunc(sysdate , 'mm')
GROUP BY CUSTOMER_ID
) T
WHERE c.JOIN_DATE >= trunc(ADD_MONTHS(sysdate , -1) , 'mm')
AND T.c_id = C.CUSTOMER_ID;
이 쿼리는 문맥은 맞지만 논리적으로 어울리지 않는다 예를 들어서 EX22_CUSTOMER 테이블의 where 조건을 보자 c.JOIN_DATE >= trunc(ADD_MONTHS(sysdate , -1) , 'mm') 이 조건은 오늘을 기점으로 한 달 전 가입자를 걸러내는 방식이다 그리고 인라인 뷰 안에서는 TRANSACTION_DATE >= trunc(sysdate , 'mm') 오늘을 기점으로 당월 발생한 거래를 보게 된다 즉 쿼리는 결과를 잡아낼 수 있지만 이렇게 되면 고객을 필터링하는 조건이 바깥에 있는데도 인라인 뷰 안에서는 당월에 거래한 모든 고객의 거래 데이터를 먼저 집계를 하게 된다
merge 힌트
옵티마이저에게 view 쿼리 또는 인라인 뷰를 가능하면 원본 테이블로 펼쳐서 최적화를 하는 것을 말합니다 이렇게 하는 이유는 옵티마이저가 view 쿼리 또는 인라인 뷰를 그 자체로 처리하려고 한다면 안쪽 테이블의 조인 최적화에 활용할 수 없을 것이다 그래서 view 안의 쿼리를 메인 쿼리에 병합을 시키면 더 많은 인덱스를 활용할 수 있는데 이때 어떻게 되는지 알아보자
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
10 10 10 HASH GROUP BY (cr=72 pr=19 pw=0 time=5849 us starts=1 cost=131 size=4522 card=119)
61 61 61 HASH JOIN (cr=72 pr=19 pw=0 time=9978 us starts=1 cost=130 size=4522 card=119)
61 61 61 NESTED LOOPS (cr=72 pr=19 pw=0 time=8359 us starts=1 cost=130 size=4522 card=119)
61 61 61 NESTED LOOPS (cr=14 pr=19 pw=0 time=2466 us starts=1 cost=130 size=4522 card=119)
10 10 10 STATISTICS COLLECTOR (cr=2 pr=0 pw=0 time=221 us starts=1)
10 10 10 TABLE ACCESS BY INDEX ROWID BATCHED EX22_CUSTOMER (cr=2 pr=0 pw=0 time=247 us starts=1 cost=2 size=368 card=16)
10 10 10 INDEX RANGE SCAN EX22_CUSTOMER_IDX (cr=1 pr=0 pw=0 time=98 us starts=1 cost=1 size=0 card=16)(object id 77483)
61 61 61 INDEX RANGE SCAN EX22_TRANSACTION_IDX2 (cr=12 pr=19 pw=0 time=2228 us starts=10 cost=1 size=0 card=7)(object id 77485)
61 61 61 TABLE ACCESS BY INDEX ROWID EX22_TRANSACTION (cr=58 pr=0 pw=0 time=1631 us starts=61 cost=8 size=105 card=7)
0 0 0 TABLE ACCESS BY INDEX ROWID BATCHED EX22_TRANSACTION (cr=0 pr=0 pw=0 time=0 us starts=0 cost=8 size=105 card=7)
0 0 0 INDEX FULL SCAN EX22_TRANSACTION_IDX2 (cr=0 pr=0 pw=0 time=0 us starts=0 cost=1 size=0 card=7)(object id 77485)
********************************************************************************
이때 힌트는 다음처럼 변화게 된다 이는 이렇게 될 것이다 하고 상상하고 적은 것이다
SELECT C.CUSTOMER_ID , ROUND(AVG(T.TRANSACTION_AMOUNT)) avg_transaction_amt , MIN(T.TRANSACTION_AMOUNT) min_transaction_amt , MAX(T.TRANSACTION_AMOUNT) max_transaction_amt
FROM EX22_CUSTOMER C , EX22_TRANSACTION T
WHERE WHERE c.JOIN_DATE >= trunc(ADD_MONTHS(sysdate , -1) , 'mm')
AND T.c_id = C.CUSTOMER_ID;
AND TRANSACTION_DATE >= trunc(sysdate , 'mm')
GROUP BY C.CUSTOMER_ID
제일 먼저 실행된 순서를 보자
INDEX RANGE SCAN EX22_CUSTOMER_IDX 인덱스 Range Scan 을 통해서 인덱스를 먼저 검색
그 인덱스를 바탕으로 TABLE ACCESS BY INDEX ROWID BATCHED EX22_CUSTOMER 테이블 조회
STATISTICS COLLECTOR 옵티마이저가 통계 수집을 먼저 함
INDEX RANGE SCAN EX22_TRANSACTION_IDX2 인덱스 Range Scan 을 통해서 인덱스 먼저 검색
4의 인덱스를 바탕으로 TABLE ACCESS BY INDEX ROWID BATCHED EX22_TRANSACTION 테이블 조회 이때
NESTED LOOPS 1 ~ 5를 반복
그리고 쿼리를 보게 되면 우려와는 달리 오늘로부터 한 달 전 가입한 고객의 데이터만 필터링 되어서 EX22_TRANSACTION 테이블과 조인되어 가져오는 것을 알 수 있다
no_merge 힌트와 VIEW PUSHED PREDICATE
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
Rows (1st) Rows (avg) Rows (max) Row Source Operation
---------- ---------- ---------- ---------------------------------------------------
10 10 10 NESTED LOOPS (cr=74 pr=0 pw=0 time=333 us starts=1 cost=146 size=992 card=16)
10 10 10 TABLE ACCESS BY INDEX ROWID BATCHED EX22_CUSTOMER (cr=4 pr=0 pw=0 time=297 us starts=1 cost=2 size=368 card=16)
10 10 10 INDEX RANGE SCAN EX22_CUSTOMER_IDX (cr=2 pr=0 pw=0 time=130 us starts=1 cost=1 size=0 card=16)(object id 77483)
10 10 10 VIEW PUSHED PREDICATE (cr=70 pr=0 pw=0 time=2124 us starts=10 cost=9 size=39 card=1)
10 10 10 FILTER (cr=70 pr=0 pw=0 time=1683 us starts=10)
10 10 10 SORT AGGREGATE (cr=70 pr=0 pw=0 time=1246 us starts=10)
61 61 61 TABLE ACCESS BY INDEX ROWID BATCHED EX22_TRANSACTION (cr=70 pr=0 pw=0 time=1636 us starts=10 cost=9 size=105 card=7)
61 61 61 INDEX RANGE SCAN EX22_TRANSACTION_IDX2 (cr=12 pr=0 pw=0 time=534 us starts=10 cost=2 size=0 card=7)(object id 77485)
********************************************************************************
SELECT *
FROM EX22_CUSTOMER C , (
SELECT
CUSTOMER_ID c_id,
ROUND(AVG(TRANSACTION_AMOUNT)) avg_transaction_amt ,
MIN(TRANSACTION_AMOUNT) min_transaction_amt ,
MAX(TRANSACTION_AMOUNT) max_transaction_amt
FROM EX22_TRANSACTION
WHERE TRANSACTION_DATE >= trunc(sysdate , 'mm')
AND T.c_id = C.CUSTOMER_ID;
GROUP BY CUSTOMER_ID
) T
WHERE c.JOIN_DATE >= trunc(ADD_MONTHS(sysdate , -1) , 'mm')
이때 실행계획과 최적화 쿼리를 보면 일단 최적화 쿼리는 저렇게 동작할 것이다 예상한 것이다 실제로 문법에는 맞지 않는다 그 이유는 인라인 뷰는 완전히 독립적으로 돌아야 한다 즉 안쪽 쿼리 안에서는 메인 쿼리의 별칭이나 컬럼을 참조할 수 없다 다만 이렇게 동작할 것이다 하고 추측만 하는 것이다
현재 쿼리는 no_merge 즉 인라인 뷰 풀어쓰지 않고 바로 적용한 결과이다 이때 특이한 실행계획 하나를 볼 수 있다 VIEW PUSHED PREDICATE는 옵티마이저가 인라인 뷰 안쪽으로 메인 쿼리의 조건을 밀어 넣어서 성능을 최적화하려는 시도입니다 이때 끼워들어간 조건은 AND T.c_id = C.CUSTOMER_ID; 이것이 끼어들어가게 됩니다 일반적으로 인라인 뷰는 SELECT 결과를 먼저 다 만든 다음 그걸 메인 쿼리 조건으로 필터링하는데 이는 지금과 같은 쿼리에서는 매우 비효율적이다 그렇기에 옵티마이저가 메인 쿼리의 조건을 인라인 뷰 안에 집어넣어서 인라인 뷰 결과 자체를 줄이고 메인 쿼리와 조인하는 것이다
그래서 이때 오퍼레이션 순서를 보면
INDEX RANGE SCAN EX22_CUSTOMER_IDX 인덱스 Range Scan 을 통해서 인덱스를 먼저 검색
그 인덱스를 바탕으로 TABLE ACCESS BY INDEX ROWID BATCHED EX22_CUSTOMER 테이블 조회
VIEW PUSHED PREDICATE 이때 메인 쿼리의 조건을 인라인 뷰 안에 집어넣어서 인라인 뷰 실행
3의 결과로 줄어진 데이터로 INDEX RANGE SCAN EX22_TRANSACTION_IDX2 인덱스 조회
4의 결과로 TABLE ACCESS BY INDEX ROWID BATCHED EX22_TRANSACTION 본 테이블 조회
그리고 여기서는 조인 없이 바로 집계를 진행한다 SORT AGGREGATE merge에서는 최종적으로 조인 이후 집계를 하는 모습을 볼 수 있다
장단점
먼저 흐름을 파악하면
머지 힌트는 고객 + 거래를 조인을 한 후 필터링 이후 집계
나머지 힌트는 고객을 찾은 후 해당 고객을 인라인 뷰에 밀어 넣어서 거래 데이터 자체를 줄인 후 조인 없이 바로 집계
만약 대량 데이터가 있다면 이때는 해시 조인이 유리하기 때문에 머지 힌트가 옳지만 적은 데이터를 가져올 때는 no_merge 조인으로 처리하는 것이 좋다