01 쿼리 변환이란?
쿼리 옵티마이저가 SQL 을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.
9i 부터 옵티마이저의 핵심적인 변화가 대부분 쿼리 변환을 중심으로 진행되고 있어 이제 SQL 튜닝을 논할 때 빼놓을 수 없는 중심 주제가 되었다.
* 쿼리 변환의 종류
1. 서브 쿼리 Unnesting
2. 뷰 Merging
3. 조건절 Pushing
4. 조건절 이행
5. 공통 표현식 제거
6. Outer 조인을 Inner 조인으로 변환
7. 실체화 뷰 쿼리로 재작성
8. Star 변환
9. Outer 조인 뷰에 대한 조인 조건 Pushdown
10. OR-expansion
* 쿼리 변환 방식
- 휴리스틱 (Heuristic) 쿼리 변환: 결과만 보장된다면 무조건 쿼리 변환을 수행한다. 위 1~6
- 비용기반 (Cost-based) 쿼리 변환: 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다. 7~10
02 서브 쿼리 Unnesting
(1) 서브쿼리의 분류
서브쿼리는 하나의 SQL 문장 내에서 괄호로 묶인 별도의 쿼리 블록
1. 인라인 뷰 (Inline View): from 절에 나타나는 서브쿼리
2. 중첩된 서브쿼리 (Nested Subquery): where절에 사용된 서브쿼리
3. 스칼라 서브쿼리 (Scalar Subquery): 주로 select-list 에서 사용되지만
서브쿼리 Unnesting 은 중첩된 서브쿼리와 관련있고
뷰 Merging 은 인라인 뷰와 관련있다.
(2) 서브쿼리 Unnesting 의 의미
중첩된 서브쿼리를 풀어내는 것
메인쿼리와 부모와 자식이라는 종속적이고 계층적인 관계가 존재
(3) 서브쿼리 Unnesting 의 이점
서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다.
관련 힌트
unnest : 서브쿼리를 Unnesting 함으로써 조인 방식으로 최적화하도록 유도
no_unnest : 서브쿼리를 그대로 둔 상태에서 필터 방식으로 최적화하도록 유도
(4) 서브쿼리 Unnesting 기본 예시
(5) Unnesting 된 쿼리의 조인 순서 조정
Unnesting 에 의해 일반 조인문으로 변환된 후에는 emp, dept 어느 쪽이든 드라이빙 집합으로 선택될 수 있다.
(6) 서브쿼리가 M 쪽 집합이거나 Nonunique 인덱스 일 때
* Sort Unique 오퍼레이션 수행
* 세미 조인 방식으로 수행
(7) 필터 오퍼레이션과 세미조인의 캐싱 효과
(8) Anti 조인
(9) 집계 서브 쿼리 제거
(10) Pushing 서브 쿼리
실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것
Pushing 서브 쿼리는 Unnesting 되지 않은 서브쿼리에만 작동한다.
03 뷰 Merging
(1) 뷰 Merging 이란?
사람의 눈으로 볼 때는 쿼리를 블록화하는 것이 더 편할지 모르지만
최적화를 수행하는 옵티마이저의 시각에서는 더 불편하다.
(2) 단순 뷰 (Simple View) Merging
조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 언제든 Merging 이 일어난다.
(3) 복합 뷰 (Complex View) Merging
group by 절, select-list 에 distince 연산자가 포함된 절을 포함하는 복합 뷰는 _complex_view_merging 파라미터를 true 로 설정할 때만 merging 이 일어난다.
집합연산자(union, union all, intersect, minus), connect by 절, ROWNUM pseudo 컬럼, select-list 에 집계함수 사용, 분석함수 가 포함된 복합 뷰는
_complex_view_merging 파라미터를 true로 설정하더라도 merging 될 수 없다. Non-mergeable Views.
(4) 비용기반 쿼리 변환의 필요성
(5) Merging 되지 않은 뷰의 처리 방식
04 조건절 Pushing
* 뷰 Merging 에 실패하는 이유
- 복합 뷰 (Complex View) Merging 기능이 비활성화
- 사용자가 no_merge 힌트 사용
- Non-mergeable Views: 뷰 Merging 시행하면 부정확한 결과 가능성
- 비용기반 쿼리 변환이 작동해 No Merging 선택
* 조건절 Pushing 종류
- 조건절 Pushdown: 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어넣는 것
- 조건절 Pullup: 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것
- 조인 조건 Pushdown: NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것
* 관련 힌트와 파라미터
* Non-pushable View
(1) 조건절 Pushdown
* Group BY 절을 포함한 뷰에 대한 조건절 Pushdown
* UNION 집합 연산자를 포함한 뷰에 대한 조건절 Pushdown
union 집합 연산자를 포함한 뷰는 Non-mergeable View 에 속하므로 복합 뷰(Complex View) Merging 기능을 활성화하더라도 뷰 Merging에 실패한다.
따라서 Pushing을 통해서만 최적화가 가능하다.
(2) 조건절 Pullup
조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥 쪽으로 끄집어 내기도 하는데,
이를 '조건절 Pullup' 이라고 한다.
(3) 조인조건 Pushdown
* GROUP BY 절을 포함한 뷰에 대한 조인 조건 Pushdown
* UNION 집합 연산을 포함한 뷰에 대한 조인 조건 Pushdown
* Outer 조인 뷰에 대한 조인 조건 Pushdown
05 조건절 이행
* 조건절 이행이 효과를 발휘하는 사례
06 조인 제거
1:M관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조 하지 않는다면,
쿼리 수행 시 1쪽 테이블은 읽지 않아도 된다. 결과집합에 영향을 미치지 않기 때문이다.
옵티마이저는 이 특성을 이용해 M쪽 테이블만 읽도록 쿼리를 변환하는데, 이를 '조인 제거' 또는 '테이블 제거' 라고 한다.
07 OR-Expansion
(1) OR-Expansion 기본
(2) OR-Expansion 브랜치별 조인 순서 최적화
(3) 같은 컬럼에 대한 OR-Expansion
(4) nvl/decode 조건식에 대한 OR-Expansion
08 공통 표현식 제거
같은 조건식이 여러 곳에서 반복 사용될 경우, 오라클은 해당 조건식이 각 로우당 한번씩만 평가되도록 쿼리를 변환하는데
이를 공통표현식 제거라고 하며 _eliminate_common_subexpr 파라미터를 통해 제어한다.
09 Outer 조인을 Inner 조인으로 변환
Outer 조인문을 작성하면서 일부 조건절에 Outer 기호 (+)를 빠뜨리면 Inner 조인할 때와 같은 결과가 나온다.
이럴 때 옵티마이저는 Outer 조인을 Inner 조인문으로 바꾸는 쿼리 변환을 시행한다.
10 실체화 뷰 쿼리로 재작성
뷰는 쿼리만 저장하고 있을 뿐 자체적으로 데이터를 갖지 않는다.
반면, 실체화 뷰(Materialized View) 는 'materialize'가 의미하는 것처럼 물리적으로 실제 데이터를 갖는다.
과거 분산 환경에서 실시간 또는 일정 주기로 데이터를 복제하는데 사용하던 Snapshot 기술을 DW 분야에 적응시킨 것이며,
여전히 데이터 복제 용도로 사용할 수 있다.
DW 분야에서는 주로 두 개 이상의 테이블을 미리 조인해 두거나 (Join View)
대량의 테이블을 미리 group by 해서 집계해 두는 형태 (Aggregate View) 로 많이 활용되고 있다.
MV를 사용하게 만드는 가장 중요한 두 가지 특징
* Refresh 옵션을 이용해 오라클이 집계 테이블을 자동 관리하도록 할 수 있다.
* 옵티마이저에 의한 Query Rewrite 가 지원된다.
11 집합 연산을 조인으로 변환
Intersect나 Minus 같은 집합 연산을 조인 형태로 변환하는 것을 말한다.
12 기타 쿼리 변환
(1) 조인 컬럼에 IS NOT NULL 조건 추가
(2) 필터 조건 추가
(3) 조건절 비교 순서