권순용의 DB 이야기
우리가 수행하는 SQL은 변한다. 4부. 0 8 5,928

by axiom Query Transformation 인라인 뷰 뷰 Merging View Merging Inline View 쿼리변환 [2013.01.10]


우리는 변화에 익숙해 있지는 않다. 특히, 우리가 의도하지 않았음에도 불구하고 SQL이 변경되는 것에는 대부분의 사람들이 익숙해 있지 않은 것이 사실이다.

하지만, 이제 우리는 이와 같이 SQL이 변하는 것에 익숙해져야만 한다. 왜 익숙해져야 하는 것인가? 그것은 우리가 사용하는 SQL의 많은 부분이 변하기 때문이다.

인라인 뷰는 실제 SQL을 작성하는 단계에서 많이 사용하게 된다. 많은 개발자들은 원하는 데이터를 추출하기 위해 인라인 뷰를 많이 사용하는 것이 현실이다.

하지만 대부분의 개발자들은 자신이 작성한 인라인 뷰가 변하는지 작성한 그대로 수행되는지를 인지하지 못하는 경우가 대부분이다.

이와 같이 SQL을 작성해도 우리는 원하는 데이터를 추출할 수는 있지만 원하는 성능을 보장받기는 쉽지 않을 것이다. SQL의 첫 번째 목적은 필요한 데이터를 정확히 추출하는 것임에는 틀림 없다.

하지만 원하는 데이터라 할지라도 그 데이터를 추출하는 데 많은 시간이 소요된다면 과연 우리가 업무를 하면서 해당 데이터를 확인하게 될까?

대부분의 경우는 성능이 저하되는 SQL에 대해서는 사용하지 않게되므로 아무리 필요한 데이터를 추출할지라도 성능을 보장하는 것은 매우 중요한 것이다.

이번 강의에서는 우리가 수행하는 SQL은 변한다의 마지막인 인라인 뷰가 변하지 않는 경우를 확인해보자.

인라인 뷰는 수학의 괄호

인라인 뷰는 실행 방식에 의해 주 쿼리의 조건이 인라인 뷰에서 사용되는 경우와 인라인 뷰에서 사용되지 않는 경우의 두 가지 종류가 존재한다.

결국, 다음과 같이 인라인 뷰의 종류가 존재하게 된다.

  • - Mergeable 인라인 뷰
  • - Non-Mergeable 인라인 뷰

Non-Mergeable 인라인 뷰는 뷰 Merging이 발생하지 않는 인라인 뷰이다. 이와 같은 인라인 뷰는 어떻게 수행되는 것일까?

SELECT a.department_name, c.employee_name, c.address
  FROM department a,
     ( SELECT department_id, grade, employee_name, address
         FROM employees b
        WHERE sal > 200
        UNION ALL
       SELECT department_id, grade, employee_name, address
         FROM employees b
        WHERE sal < 100
     ) C
 WHERE a.department_id = b.department_id
   AND c.grade = 'S';

이 SQL에서 WHERE 문의 GRADE 조건이 인라인 뷰 안으로 삽입된다면 뷰 Merging이 발생하여 Mergeable 인라인 뷰로 수행되게 된다.

이와 같은 경우 EMPLOYEES 테이블에 GRADE+SAL 인덱스가 존재한다면 해당 인덱스를 이용할 수있게 된다.

그렇다면 뷰 Merging이 발생하지 않는 Non-Mergeable 인라인 뷰로 수행된다면 어떻게 되겠는가? Non-Mergeable 인라인 뷰로 수행된다면 이는 수학의 괄호와 같다.

수학의 괄호와 비교해 보자. 수학의 괄호는 우선순위를 정하게된다. 그렇기 때문에 괄호가 사용된 부분은 별도로 수행하게 된다.

Non-Mergeable 인라인 뷰는 수학의 괄호와 같다. 그렇기 때문에 EMPLOYEES 테이블에 GRADE+SAL 인덱스가 존재하더라도 해당 인덱스를 이용할 수 없게 된다.

이는 주 쿼리의 조건인 GRADE 조건이 Non-Mergeable 인라인 뷰에서는 인라인 뷰로 삽입되지 못하기 때문에 해당 조건이 처리 범위를 감소시키는 조건으로 사용될 수 없기 때문이다.

결국, 인라인 뷰가 별도로 수행되는 현상이 발생하는 것이 Non-Mergeable 인라인 뷰가 되며 이는 수학의 괄호와 같이 인라인 뷰의 괄호가 연산자의 순서를 결정하는 역할을 수행하게 된다.

Non-Mergeable 인라인 뷰 확인하기

그렇다면 우리가 인라인 뷰만을 보고 어떻게 뷰 Merging이 발생하는지 아닌지를 확인할 수 있겠는가?

물론, SQL의 형태만을 보고 판단할 수는 없다. 하지만, 다음과 같은 문법이 SQL에 사용된다면 Non-Mergeable 인라인 뷰가 되거나 Mergeable 인라인 뷰가 되더라도 주 쿼리의 조건이 인라인 뷰로 삽입되는 현상이 발생된다.

  • - UNION ALL
  • - UNION
  • - DISTINCT
  • - GROUP BY
  • - ROWNUM
  • - 집합 함수

이와 같은 문법을 사용한다면 해당 SQL은 주 쿼리의 조건이 인라인 뷰 안으로 삽입되거나 Non-Mergeable 인라인 뷰로 수행될 가능성이 높다.

물론, 해당 문법을 사용한 인라인 뷰는 인라인 뷰가 주 쿼리와 합쳐지는 형태의 뷰 Merging이 발생하지 않는다. 하지만, 이와 같은 것만으로 우리가 Mergeable 인라인 뷰인지 Non-Mergeable 인라인 뷰인지를 판단할 수는 없다.

그렇다면 어떻게 우리는 정확히 Non-Mergeable 인라인 뷰인지 아닌지를 판단할 수 있을까? 이는 해당 SQL의 실행 계획을 통해 확인 해야 한다. 해당 SQL의 실행 계획을 확인해 보자.

SELECT STATEMENT
  NESTED LOOPS
    TABLE ACCESS (FULL) OF 'DEPARTMENT'
    VIEW
      UNION-ALL
        TABLE ACCESS (FULL) OF 'EMPLOYEES'
        TABLE ACCESS (FULL) OF 'EMPLOYEES'

이렇게 하면 VIEW 실행 계획이 생성된다.

VIEW 실행 계획이 생성된다면 이는 인라인 뷰가 별도로 수행되어 메모리에 해당 데이터를 생성하는 것이다. 이는 마치 인라인 뷰가 수학의 괄호와 같이 수행되는 것이다.

결국, 실행 계획에 VIEW 실행 계획이 생성된다면 이는 인라인 뷰가 별도로 엑세스되는 것이므로 Non-Mergeable 인라인 뷰로 수행되는 것이다. 그렇기 때문에 VIEW 실행 계획이 생성되지 않는다면 이는 Mergeable 인라인 뷰로 수행되는 형태가 된다.

여기서의 Mergeable 인라인 뷰는 인라인 뷰가 주 쿼리와 합쳐지는 형태의 Mergeable 인라인 뷰로 수행되는 것이다.

만약, Mergeable 인라인 뷰 중 주 쿼리의 조건이 인라인 뷰 안으로 삽입된다면 실행 계획은 어떻게 수행되겠는가? 이와 같다면 실행 계획에는 VIEW PREDICATE라는 실행 계획이 VIEW 실행 계획 위치에 생성되게 된다.

앞서 실행 계획을 통해서 우리는 뷰 Merging이 발생했는지 발생하지 않았는지를 판단할 수 있게 된다. 이런 이유 때문에 인라인 뷰를 작성하면서 해당 SQL의 실행 계획 확인은 매우 중요하다.

하지만 아직도 많은 곳에서는 이와 같이 인라인 뷰에 대한 실행 계획을 확인하지 않는 것이 대부분이다. 이제라도 인라인 뷰에 대한 실행 계획을 확인하는 습관을 가지는 것은 매우 중요하다.

우리가 작성하는 SQL은 언젠가 변할 수 있다는 것을 명심하길 바란다. 변하는 SQL을 예측하여 그에 최적인 형태를 준비하는 것이 우리가 할 수 있는 최선의 방법이다.

- 강좌 URL : http://www.gurubee.net/lecture/2245

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 도영철 [2013.01.13 12:46:16]

자세한 설명 감사드립니다.


by 아발란체 [2013.01.18 08:50:41]

커피 한잔 마시며 보기에는 조금 어렵지만 결론은 버킹검~ !


by 참된신자 [2014.07.31 17:47:53]

감사합니다 :)


by BOREE [2017.08.09 16:08:57]

질문이 있습니다.
SELECT a.department_name, c.employee_name, c.address
  FROM department a,
     ( SELECT department_id, grade, employee_name, address
         FROM employees b
        WHERE sal > 200
        UNION ALL
       SELECT department_id, grade, employee_name, address
         FROM employees b
        WHERE sal < 100
     ) C
 WHERE a.department_id = b.department_id
   AND c.grade = 'S';
해당 쿼리가 Merging 단계 없이 실행되는 경우,
C VIEW의 INDEX 는 인라인뷰에서 사용된 테이블의 인덱스 중, SELECT 에서 출력된 컬럼으로 선정되는 건가요?
인라인 뷰도 하나의 테이블 역할을 하는데, 어떻게 INDEX를 선정하는지 궁금합니다.


by jkson [2017.08.09 17:26:43]

index는 select절이 아닌 where 조건에 의해 결정됩니다.

해당 쿼리는 뷰를 완전히 해체하여 주쿼리와 합쳐주는 것이 불가능합니다.

왜냐하면 어제 말씀 드렸던 해체 불가 사유 중 union all 연산이 있기 때문입니다.

이 경우 주 쿼리의 조건절이 뷰 안으로 삽입이 가능하면 삽입을 하게 되는데

이를 조건절 pushing이라 부른다고 알려드렸구요. 이 글 본문에서도 설명하고 있습니다.

뷰안의 employees 테이블을 탐색하려면 뷰자체 만으로는 sal 조건 밖에 없습니다.

grade + sal 로 구성된 인덱스가 있다고 했을 때 효과적으로 해당 인덱스를 활용할 수가 없게 되죠.

뷰를 완전 해체할 수 있었다면 사용가능했겠지만 union all 때문에 해체가 불가능합니다.

이 때 주쿼리의 grade = 'S' 조건을 뷰 안으로 밀어넣어주게 됩니다.

그러면 뷰는

SELECT department_id, grade, employee_name, address
 FROM employees b
WHERE sal > 200
  AND grade = 'S'
UNION ALL
SELECT department_id, grade, employee_name, address
 FROM employees b
WHERE sal < 100
  AND grade = 'S'

의 형태로 바뀌게 되고 grade + sal 인덱스를 효과적으로 활용할 수 있게됩니다.

인라인뷰도 하나의 테이블 역할을 한다.. 는 말씀은 무슨 의미죠?

 


by BOREE [2017.08.09 18:31:17]

네 제가 말씀 드린 것은 pushing 이나 merging 이 일어나지 않는다는 가정 하에,

VIEW 'C'도 하나의 테이블처럼 A 테이블과 조인 연산을 하고 GRADE 가 'S' 인 데이터를 찾는데

이때 VIEW 'C'에서 인덱스 역할을 하는 컬럼이 무엇인지, 그 기준은 무엇인지가 궁금했습니다. (주 쿼리 관점에서 VIEW 'C'의 INDEX)

 


by jkson [2017.08.09 18:50:08]

역시나 제가 언어이해능력이 많이 떨어지네요ㅠㅠ

다시 설명해주신 것 보고 원래 댓글 읽어보니 이제 이해가 됩니다.

당연히 조인은 select 절의 컬럼으로 조인이 되니

select 절의 컬럼이 조인키의 역할을 하게 되고 해당 키에 해당하는 인덱스가 있다면

해당 인덱스를 사용하게 되겠죠.(nl 조인시)

위의 쿼리에서 department_id로 조인이 이루어집니다.

위의 쿼리가 nl 조인일 경우

a가 드라이빙 테이블이 되구요.

a테이블 1개 row 탐색후 b로 가야하는데 이때

a에서 탐색했던 department_id 를 b뷰의 where 조건으로 넣어주게 됩니다.

(실행계획에서 view pushed predicate로 확인이 가능합니다.)

만약 department_id + grade + sal 로 이루어진 인덱스가 있다면

해당 인덱스를 사용하게 되겠죠(아마도?)

만약 nl 조인이 아니라 hash 조인이라면(department_id에 index가 없거나 hash가 유리한 경우)

grade+sal 인 인덱스가 있다면 해당 인덱스로 b 그룹을 만들고 hash 조인이 될 거구요.

 


by BOREE [2017.08.10 13:43:20]

아하 ㅎㅎ  아닙니다 ㅠ 제가 질문을 이해하기 어렵게 했네요.

덕분에 이해 하고 갑니다 ~! 감사합니다

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