권순용의 DB 이야기
우리가 수행하는 SQL은 변한다. 3부. 1 6 99,999+

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


이미 수개월 동안 같은 제목으로 글을 쓰고 있다. 그만큼 강조하고 싶은 탓이다.

우리가 수행하는 SQL은 변한다. 우리가 서브쿼리를 사용했다고 또는 인라인 뷰를 사용했다고 그대로 수행되지는 않는다. 이 얼마나 놀라운 일인가? 데이터베이스의 옵티마이저는 낙성한 SQL을 변경하여 해당 SQL을 수행하게 된다.

이와 같은 현상을 우리가 막을 수는 없다. 이와 같이 옵티마이저가 SQL을 변경하는 이유는 성능을 보장하는 SQL로 변경하기 위해서다. 그렇기 때문에 이와 같은 현상을 막아서는 안 될 것이다.

이에 우리가 할 수 있는 것은 무엇일까? SQL이 변하는 것을 막을 수 없다면 우리는 SQL이 변하는 것을 정확히 이해하고 이를 이용해야 할 것이다.

SQL이 어떻게 변하는지를 확인하는 것은 매우 중요한 일이다. 무엇을 가지고 우리는 SQL이 변하는 것을 확인할 수 있는 것일까?

SQL이 어떻게 변하는지를 확인하는 방법은 실행 계획을 통해 확인하게 된다. 실행 계획을 통해 SQL의 변화를 확인하고 이를 통해 우리는 SQL을 최적화하게 된다.

SQL이 변하는 것에 대해 불평하지 말고 이를 이용하는 방법을 터득하여 SQL을 최적화해야 할 것이다. 이번 강의에서는 지난 강의에 이어 인라인 뷰의 변화에 대해 자세히 확인해 보겠다.

인라인 뷰도 변한다.

다른 SQL과 마찬가지로 인라인 뷰도 변하게 된다. 특히 인라인 뷰의 변화는 말로 표현하기 힘들 정도로 심하게 발생한다. 이를 뷰 Merging이라고 한다.

인라인 뷰를 사용하는 경우 우리는 보통 괄호를 사용하게 된다. 이와 같은 괄호를 수학의 괄호로 생각하면 안 된다. 수학의 괄호는 어떠한가? 괄호로 표현한 부분부터 연산을 수행하게 된다. 하지만 데이터베이스에서 사용하는 괄호는 이와 같은 역할을 수행하지 않게 된다.

데이터베이스에서 사용하는 괄호는 우선 순위를 구분하는 역할을 수행하지 않으며 단지 SQL의 문법적인 구분의 역할만을 수행하게 된다. 그렇기 때문에 괄호를 사용하는 인라인 뷰는 언제든 변할 수 있게 된다.

이와 같은 이유에서 인라인 뷰는 두 가지로 구분된다.

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

Mergeable 인라인 뷰는 뷰가 해체되거나 뷰 안으로 조건이 삽입되는 인라인 뷰를 의미한다. 반면에 Non-Mergeable 인라인 뷰는 뷰가 수학의 괄호와 같이 별도로 수행되는 인라인 뷰를 의미한다.

모든 인라인 뷰는 이와 같이 둘 중 하나로 수행될 수 있다. 대부분의 인라인 뷰는 두 가지 방법 모두 수행이 가능하다.

이와 같은 Mergeable 인라인 뷰는 지난 강의에 언급한 것과 같이 두 가지로 구분할 수 있다.

  • - 주 쿼리의 조건이 인라인 뷰 안으로 삽입되는 뷰 Merging
  • - 인라인 뷰 또는 뷰의 SQL이 주 쿼리로 합쳐지는 뷰 Merging

지난 강의에는 주 쿼리의 조건이 인라인 뷰로 삽입되는 Mergeable 인라인 뷰에 대해 확인해 보았으며 이번 호에서는 인라인 뷰가 주 쿼리와 통합되는 Mergeable 인라인 뷰에 대해 확인해 보자.

인라인 뷰가 주 쿼리와 통합되는 Mergeable 인라인 뷰

Mergeable 인라인 뷰 중 인라인 뷰가 주 쿼리와 통합되는 Mergeable 인라인 뷰에 대해 확인해 보자.

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 
     ) c 
 WHERE a.department_id = b.department_id 
   AND c.grade= 's';

이 SQL은 어떻게 수행되는가?

위의 SQL은 실제 작성한 것과 같이 인라인 뷰가 독립적으로 수행될 수 있는가?

물론, 위의 SQL은 인라인 뷰가 별도로 수행되어 실행 계획에는 VIEW 실행 계획이 생성될 수도 있다. 하지만, 많은 경우는 해당 SQL은 인라인 뷰가 별도로 수행되지 않고 주 쿼리와 통합되어 수행되는 경우가 대부분이다.

인라인 뷰가 주쿼리와 통합되어 수행된다면 다음과 같이 수행될 것이다.

SELECT a.department_name, b.employee_name, b.address 
  FROM department a, employees b 
 WHERE a.department_id = b.department_id 
   AND b.grade = 'S'
   AND b.sal > 200;

결국, 인라인 뷰를 이용한 앞의 SQL은 위와 같이 인라인 뷰가 없이 수행되게 된다.

이와 같이 수행되는 이유는 인라인 뷰가 사용할 수 있는 조건을 모두 확보하기 위해서이다.

만약에 앞의 SQL만을 보고 인덱스를 생성한다면 보통의 경우에는 EMPLOYEES 테이블의 SAL 컬럼에만 인덱스를 생성하게 된다. 하지만, 위와 같이 SQL이 변경된다는 것을 이해한다면 처리 범위를 더 감소시킬 수 있게 인덱스는 GRADE+ SAL로 인덱스를 생성하게 될 것이다.

과연, 어떤 인덱스를 생성해야 할까? 가장 최적의 인덱스는 처리 범위를 감소시킬 수 있는 인덱스를 생성해야 한다는 것이다.

이처럼 인라인 뷰의 병합 현상은 어쩔 수 없이 옵티마이저의 선택에 의해 발생하게 되며 이와 같은 현상에 대해 우리가 할 수 있는 일은 변경되는 SQL을 고려하여 최적의 인덱스를 선정하는 것이다.

이 것이 Mergeable 인라인 뷰를 효과적으로 이용하는 것일 것이다.

만약 SQL이 Mergeable 인라인 뷰로 수행되기를 원하더라도 이는 옵티마이저의 선택에 달려있다.

하지만, 우리는 경우에 따라서 Non-Mergeable 인라인 뷰를 Mergeable 인라인 뷰로 변경해야 할 경우가 발생한다. 그렇다면 어떻게 해야 할까?

많은 경우에는 힌트를 이용하여 이와 같은 현상을 제어하게 된다.

SELECT  /*+ MERGE(C) */ a.department_name, c.employee_name, c.address 
  FROM department a, 
     ( SELECT department_id, grade, employee_name, address 
         FROM employees b 
        WHERE sal > 200 
     ) c 
 WHERE a.department_id = b.department_id 
   AND c.grade= 's';

이 SQL이 Mergeable 인라인 뷰로 수행되지 않는다면 힌트로 /*+ MERGE(C) */라는 힌트를 주 쿼리의 SELECT 절 옆에 설정하여 인라인 뷰가 Mergeable 인라인 뷰로 수행되게 할 수 있다.

물론, 이와 같이 힌트를 설정한다고 무조건 Mergeable 인라인 뷰로 수행되지는 않는다. 제한 사항에 의해 Mergeable 인라인 뷰로 수행되지 않을 수도 있다.

이러한 제한 사항에 대해서는 다음 강의에서 자세히 언급하도록 하겠다.

뷰 Merging은 Query Transformation의 대표적인 예이다.

이제 우리가 괄호를 이용하여 인라인 뷰를 사용한다고 무조건 괄호를 독립적으로 수행하는 것은 아니다.

괄호는 괄호일 뿐 언제든 제거될 수 있다. 이러한 현상을 항상 고려해야 할 것이다.

인라인 뷰가 제거되는지 아닌지를 고려하여 그에 맞는 최적의 인덱스를 생성하여 성능을 극대화해야 할 것이다.

Query Transformation에 대해 이것이 우리가 할 수 있는 최선의 선택이 된다. 다음 강의에서는 Mergeable 인라인 뷰와 Non-Mergeable 인라인 뷰에 대해 자세히 확인해 보도록 하겠다.

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

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

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

by 아발란체 [2013.01.09 09:14:35]
킹왕짱... Query Transformation,
DNA에서도 Transformation라는 것이 있는데 과정이 그것을 보는 것 같습니다.
Transitivity, View Merging .... MySQL이나 CUBRID 같은 것도 이런 절차는 있을 것 같은데
동일한 과정을 가지고 있는지 급 궁금하긴 하네요.
좋은글 감사합니다 ~:)
 

by 참된신자 [2014.07.31 17:43:46]

감사합니다 :)


by BOREE [2017.08.07 18:28:32]

sqld 공부하면서 인덱스 부분은 이해하기 어려웠는데

여기 보면서 덕분에 쉽게 이해하고 갑니다. ^ㅇ^

근데 이해가 안가는 부분이 있는데요..

이전 파트에서 알려주신 inline 뷰가 주쿼리로 merging 되는 부분이랑

주 쿼리의 조건이 inline 뷰로 merging 되는 부분이랑

사실 쿼리가 똑같다고 보ㅏ야 하는데

그냥 예시를 이해하기 쉽도록 들어주신건가요?

아니면 저 둘의 쿼리의 차이가 무엇인지 궁금합니다.


by jkson [2017.08.08 08:28:57]

1.인라인 뷰 또는 뷰의 SQL이 주 쿼리로 합쳐지는 것을 뷰머징이라고 하고

2.주 쿼리의 조건이 인라인 뷰 안으로 삽입되는 되는 것은 조건절 pushing이라고 알고 있습니다.(조건절 pushing이 뷰머징의 한 가지 형태인지는 모르겠네요)

1번의 경우 뷰자체가 완전 해체 되어 뷰의 형태가 아닌 일반 테이블 조인의 형태로 풀릴 때 뷰머징이라고 하고

2번은 뷰머징이 어려운 상황일 때는 주 쿼리의 조건을 단순히 뷰의 조건절로 넣어버림으로써 성능향상을 유도하고자 하는 목적이 있습니다.

이 글을 쓰신 분이 2번 조건절 pushing을 설명하신 예를 보면

SELECT A.사원번호, A.사원이름, B.부서번호, B.부서이름 
  FROM
     ( SELECT 부서번호, 사원번호, 사원이름, SUM(급여) 
         FROM 급여 
        WHERE 부서번호 = 10 
        GROUP BY 부서번호, 사원번호, 사원이름 
     ) A, 부서 B 
 WHERE A.부서번호 = B.부서번호 
   AND A.사원번호 = '100';

위와 같은 상황일 때

뷰머징이 불가하다면(위 쿼리의 경우 뷰머징이 가능할 것 같은데 만약 안 된다고 하면;;)

부서번호 10인 사람들로 모두 group by 하고 조인하는 것보다

조건절 사원번호 = '100' 조건을 뷰 안으로 밀어넣음으로써

SELECT A.사원번호, A.사원이름, B.부서번호, B.부서이름 
  FROM
     ( SELECT 부서번호, 사원번호, 사원이름, SUM(급여) 
         FROM 급여 
        WHERE 부서번호 = 10 
          AND 사원번호 = '100'
        GROUP BY 부서번호, 사원번호, 사원이름 
     ) A, 부서 B 
 WHERE A.부서번호 = B.부서번호;

조인 되기 전에 group by 되는 집합의 크기를 줄여주고 조인하게 되어 좀더 효율적인 조인이 가능하겠죠.

두 가지는 다르다고 봐야할 것 같습니다.

1번 뷰머징이 불가능한 경우는

대표적으로 뷰에 no_merge 힌트가 쓰인 경우

rownum을 사용한 경우

계층쿼리를 사용한 경우

union all 같은 집합 연산을 사용한 경우 등이 있겠네요.


by BOREE [2017.08.08 15:23:40]

앗 감사합니다 ~

예시로 둔 쿼리 모두 (인라인 뷰 -> 주 쿼리) / (주 쿼리 -> 인라인 뷰) merging 둘 다 가능해 보이는데,

그냥 이해하기 쉽도록 예를 든거라 그런건지, 아니면 어떤 차이가 있는지 궁금했습니다 ~

 

 


by jkson [2017.08.08 16:07:27]

아 쿼리 구조가 똑같은데 왜 두 가지로 쿼리 변환되냐는 말씀이셨군요. 제가 언어능력이 좋지않습니다ㅎㅎ 쿼리의 구조는 동일하지만 통계정보에 따라 뷰머징을 하지 않을 수 있고 뷰머징을 하지 않았을 경우 조건절 pushing을 시도하게됩니다. 따라서 같은 구조의 쿼리라도 두 가지 쿼리 변환 가능성이 모두 있어요.

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