[강정식의 오라클 이야기]IV. VIew안에서 Alias 전략적으로 사용하는 방법 1 1 9,362

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’을 방해하는 나머지 요소들에 대해서 살펴보겠습니다.

 

보시고 궁금하신 점이 있으시면 리플 달아주시기 바랍니다.

감사합니다.

http://blog.naver.com/xsoft

by 최단경로 [2009.02.14 12:18:04]
안녕하세요.

많은 테스트를 해보지 않았으나 새로운 사실을 알게 되었습니다.

아시는 분은 아시겠지만 좀 적어 봅니다.

내부적으로 View Merge, Pushed Predicate 등 Query Transformation 이 일어나지만. ANSI SQL 문법을 사용하게 되면 해당이 안되는것 같습니다.

INNER, OUTER 등을 사용할때 ON 절에 명시적으로 드라이빙 조건을 기술해 줌으로써 해당 힌트가 사용이 되지 않는것 같습니다.

참고하세요. ^^
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입