by 강정식 [강정식] View Merging [2008.08.13 11:00:07]
이번 절에서는 View 생성 및 관리시 Alias를 전략적으로 사용하여 글로벌 힌트가 잘 사용되도록
하는 방법을 살펴보겠습니다.
1. Local Hint vs Global Hint
1) Local Hint란?
- 로컬 힌트란 View 또는 Inline View 안에서 사용하는 힌트를 말합니다.
2) Global Hint란?
- 글로벌 힌트란 View 또는 Inline View 밖에서 로컬 힌트를 사용한것처럼 사용하는 힌트를 말합니다.
3) Local Hint와 Global Hint Sample
① EMP 테이블에서 JOB이 ’SALESMAN’인 인라인뷰와 DEPT를 조인한 SQL
SELECT *
FROM (SELECT E.*
FROM EMP E
WHERE E.JOB = ’SALESMAN’) INLINE_E,
DEPT D
WHERE INLINE_E.DEPTNO = D.DEPTNO
;
- 이 SQL에서 대해서 옵티마이저는 인라인 뷰 집합과 DEPT를 SORT MERGE JOIN하여 실행계획을
만들었습니다. 아마 Cost 계산에서 이게 더 좋다고 판단되어 이렇게 실행계획을 만든거 같습니다.
아래에서는 이 SQL에 로컬 힌트를 사용하여 실행계획을 변경해 보겠습니다.
② DEPT 테이블을 먼저 액세스하고 인라인뷰와 Nested Loop 조인을 유도하도록 힌트 사용
SELECT /*+ LEADING(D) USE_NL(D INLINE_E) */
*
FROM (SELECT E.*
FROM EMP E
WHERE E.JOB = ’SALESMAN’) INLINE_E,
DEPT D
WHERE INLINE_E.DEPTNO = D.DEPTNO
;
- 힌트를 사용하여 DEPT 테이블을 먼저 액세스하고 인라인 뷰 EMP 테이블을 Nested Loop
조인하는 실행계획으로 옵티마이저가 풀었습니다.
이제 3번째 Sample에서는 인라인 뷰 안에서 EMP 테이블을 FULL SCAN하도록 로컬 힌트를 사용해
보겠습니다.
③ DEPT 테이블을 먼저 액세스하고 인라인뷰와 Nested Loop 조인을 유도하되, 인라인 뷰의
EMP 테이블을 FULL SCAN 하도록 로컬 힌트 사용
SELECT /*+ LEADING(D) USE_NL(D INLINE_E) */
*
FROM (SELECT /*+ FULL(E) */
E.*
FROM EMP E
WHERE E.JOB = ’SALESMAN’) INLINE_E,
DEPT D
WHERE INLINE_E.DEPTNO = D.DEPTNO
;
- 보시는 것처럼 EMP 테이블에 DEPTNO 인덱스가 있음에도 불구하고 로컬힌트를 사용하여
FULL TABLE SCAN 하도록 유도하였습니다.
이제 마지막으로 인라인 뷰에 로컬 힌트를 사용했던 ③의 내용을 글로벌 힌트를 사용하여
테스트 해보겠습니다.
④ ③번의 내용에서 인라인 뷰의 로컬 힌트를 글로벌 힌트로 접근함
4) View에서 Hint를 사용할 때...
- 위에서 살펴보셨듯이 인라인 뷰 안에 사용한 힌트를 로컬힌트로 보시면 되고 뷰 밖에서 사용한
힌트를 글로벌 힌트로 보시면 됩니다. 좀더 자세한 내용은 아래의 URL을 참조하시기 바랍니다.
(http://www.gurubee.net/article/13975)
- 여기서 로컬 힌트와 글로벌 힌트의 샘플내용을 보신것처럼 View에는 가급적 로컬힌트를 사용하시면
안됩니다. 그 이유는 ③번의 내용에서도 보셨듯이 옵티마이저가 효율적인 실행계획을 만들어서
데이터를 가져오려고 해도 View 내부에서 로컬 힌트가 존재한다면 그 힌트에 맞는 액세스 패스를
더 우선순위로 가져가기 때문입니다.
- 또한 로컬힌트는 해당 View를 사용하는 특정 어플리케이션에만 최적화가 된 힌트들이 대부분이기
때문에 그 View를 사용하는 수많은 어플리케이션에는 오히려 액세스 패스가 불리하여 성능이 더
안좋아질 수 있습니다.
- 그러므로 가급적(아니 반드시) View 내부에는 힌트가 들어가지 않도록 설계를 해야 합니다.
- 하지만 View 내부에 로컬 힌트를 사용하지 않고 View 밖에서 글로벌 힌트를 사용하였을 경우,
그 힌트가 View 내부로 잘 침투가 되기 위해서는 View 내부의 Alias가 잘 설계되어 있어야 합니다.
- 아래 내용에서는 왜 View 내부에서 Alias를 전략적으로 잘 사용해야 하는지 2가지 Case를 가지고
살펴보겠습니다.
2. 인라인 뷰에는 반드시 Alais를 주어 연결고리를 살려놔야 함
- 글로벌 힌트는 View의 이름 또는 Alias로 침투를 시키기 때문에 침투하기 위한 다리 역할을 합니다.
- 이런 다리 역할을 하는 Alias가 명시가 되어 있지 않을 경우 글로벌 힌트를 사용할 때 침투시키는
다리가 없기 때문에 글로벌 힌트를 사용할 수가 없습니다.
- 아래는 이에 대해 테스트 한 결과입니다.
1) Test Sample
① 글로벌 힌트 사용 시 인라인 뷰의 Alias가 없을 경우
SELECT /*+ LEADING(D) USE_NL(D INLINE_E) FULL(INLINE_E.E) */
*
FROM (SELECT *
FROM (SELECT E.*
FROM EMP E
WHERE E.JOB = ’SALESMAN’) -- 인라인 뷰 Alias 없음
WHERE JOB = ’MANAGER’) INLINE_E,
DEPT D
WHERE INLINE_E.DEPTNO = D.DEPTNO
;
② 글로벌 힌트 사용 시 인라인 뷰의 Alias가 있을 경우
SELECT /*+ LEADING(D) USE_NL(D INLINE_E.SUB_E.E) FULL(INLINE_E.SUB_E.E) */
*
FROM (SELECT SUB_E.*
FROM (SELECT E.*
FROM EMP E
WHERE E.JOB = ’SALESMAN’) SUB_E --> 인라인 뷰 Alias 존재
WHERE SUB_E.JOB = ’MANAGER’) INLINE_E,
DEPT D
WHERE INLINE_E.DEPTNO = D.DEPTNO
;
- 보시는 것처럼 ①번에서는 ’SALESMAN’인 집합을 가지는 인라인 뷰에 Alias가 없기 때문에
힌트에서 ’FULL(INLINE_E.E)’를 사용하여도 침투가 되지 않고, 결국 FULL이 아닌 Range 스캔을
하게 됩니다.
- 하지만 ②번에서는 인라인 뷰에 ’SUB_E’라는 Alias를 주었기 때문에 침투할 수 있는 다리가
생겼고 ’FULL(INLINE_E.SUB_E.E)’ 힌트를 사용하여 EMP 테이블을 FULL SCAN 하도록 유도하였습니다.
- 그림에서 보듯이 TOP_VIEW에서 하위 DEPTH_VIEW로 글로벌 힌트를 침투시키려 할 때
1_DEPTH_VIEW는 Alias 다리가 있어 가능하나 2_DEPTH_VIEW ~ 4_DEPTH_VIEW에는 Alais 다리가 없기
때문에 침투가 불가합니다.
- 그러므로 반드시 인라인 뷰에는 Alias를 명시해서 달아주셔야 합니다.
3. ’UNION ALL’로 연결 시 인라인 뷰, 뷰, 테이블 Alias를 모두 다르게 주어 연결고리를 살려놔야 함
- 이번에는 View 안에 여러 UNION ALL로 SQL이 묶여 있을 때 글로벌 힌트를 이 View 안으로 잘
침투시키기 위해 Alais를 사용하는 방법입니다.
- 글로벌 힌트를 View 안으로 침투시킬 때 방법은 ’Alias.Alias...’과 같이 Alias라는 다리로밖에
침투를 못시킵니다. 하지만 UNION ALL과 같이 여러 SQL이 묶여져 있을 때 UNION ALL
1번, 2번, 3번... SQL에 각각 침투를 시키는 힌트 명령어는 아직까지는 존재하지 않습니다.
- 그렇기 때문에 UNION ALL로 묶인 SQL의 Alias 이름을 각각 다르게 주어야 글로벌 힌트를 사용할
때 각각 명시를 하여 침투를 시킬 수 있습니다.
- 그림에서 보시듯이 UNION ALL로 여러 SQL들이 묶여있을 때 인라인 뷰, 뷰, 테이블 Alias 이름이
같은것이 있다면 첫번째 까지만 침투가 가능하고 그 하단에는 침투가 불가합니다.
- 그러므로 반드시 UNION ALL로 구성된 View에서 Alias 이름을 다르게 주어 향후 이 View를
사용하는 어플리케이션에서 글로벌 힌트가 잘 들어가도록 구성을 해야 합니다.
- 아래는 이에 대해 테스트 한 결과입니다.
1) Test Sample
① 그림에서 설명한 구조로 만든 SQL
- 정상적으로 수행된 실행계획
SELECT *
FROM DEPT D,
(SELECT EMP_V_1.*
FROM EMP EMP_V_1
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
) TOP_VIEW
WHERE D.DEPTNO = TOP_VIEW.DEPTNO
AND D.DEPTNO = 10
;
② UNION ALL로 구성된 View에서 Alias 이름이 같을 경우
- UNION ALL 안의 SQL에 힌트를 주어 변화를 시도
SELECT /*+ FULL(TOP_VIEW.EMP_V_1)
FULL(TOP_VIEW.EMP_V_2)
FULL(TOP_VIEW.EMP_V_2)
FULL(TOP_VIEW.EMP_V_2) */
*
FROM DEPT D,
(SELECT EMP_V_1.*
FROM EMP EMP_V_1
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
) TOP_VIEW
WHERE D.DEPTNO = TOP_VIEW.DEPTNO
AND D.DEPTNO = 10
;
③ UNION ALL로 구성된 View에서 Alias 이름이 다를 경우
- UNION ALL 안의 SQL에 힌트를 주어 변화를 시도
SELECT /*+ FULL(TOP_VIEW.EMP_V_1)
FULL(TOP_VIEW.EMP_V_2)
FULL(TOP_VIEW.EMP_V_3)
FULL(TOP_VIEW.EMP_V_4) */
*
FROM DEPT D,
(SELECT EMP_V_1.*
FROM EMP EMP_V_1
UNION ALL
SELECT EMP_V_2.*
FROM EMP EMP_V_2
UNION ALL
SELECT EMP_V_3.*
FROM EMP EMP_V_3
UNION ALL
SELECT EMP_V_4.*
FROM EMP EMP_V_4
) TOP_VIEW
WHERE D.DEPTNO = TOP_VIEW.DEPTNO
AND D.DEPTNO = 10
;
- 결과에서 보듯이 ①번에서는 DEPT가 먼저 드라이빙되고 거기서 나온 DEPTNO로 TOP_VIEW와
조인을 하였기 때문에 TOP_VIEW 안의 EMP들에게 모두 DEPTNO가 공급이 되어 INDEX RANGE SCAN을
하였습니다.
- 그 다음 ②번에서는 TOP_VIEW 안의 UNION ALL SQL들을 글로벌 힌트로 실행계획을 변화시키도록
유도를 하였습니다. 하지만 2번째 UNION ALL까지만 글로벌 힌트 내용이 침투가 되고 그 아래에는
침투를 시키지 못했습니다.
- 마지막 Test Sample인 ③번에서는 TOP_VIEW 안의 UNION ALL SQL Alias 이름을 모두 다르게 주었기
때문에 글로벌 힌트에서 이들을 명시해 주어 EMP 테이블을 모두 FULL SCAN하도록 유도하였습니다.
4. 정리
- 이번 절에서는 View를 사용하는 어플리케이션에서 View 안으로 글로벌 힌트를 사용할 때
글로벌 힌트가 잘 들어가기 위해서 Alias를 어떻게 사용해야 하는지 살펴보았습니다.
- 저도 개발을 할 때 편의를 위해 Alias를 빼먹거나 UNION ALL에서 같은 이름의 Alias를
사용하여 View를 생성한적이 많았습니다. 실제 이렇게 생성을 해도 View를 만들 때 아무런
이슈가 없으며 그 View를 사용하는 SQL에서도 결과가 잘 나오기 때문에 문제가 없었기
때문입니다.
- 하지만 이렇게 만들어진 View는 위에서 보았던 것처럼 글로벌 힌트로 View 안의 내용을
핸들링 할 수 없기 때문에 반드시 Alias를 명시해 주고 이름을 다르게 주거나 침투가
가능합니다.
- 또한 View 내부에서는 가급적 로컬 힌트를 사용하지 않는 것이 향후 그 View를 이용하는
어플리케이션이 잘 수행될 수 있기 때문에 이 점을 기억해 두셔야 합니다.
- 다음 시간에는 ’View Merging’을 방해하는 나머지 요소들에 대해서 살펴보겠습니다.
보시고 궁금하신 점이 있으시면 리플 달아주시기 바랍니다.
감사합니다.