1. 쿼리 변환이란 ?

쿼리 변환은 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.
본격적으로 최적화를 하기 전에 사전 정지 작업을 하는 것이라고 말할 수 있다.

쿼리 변환은 크게 두 가지로 나뉜다.

1. 휴리스틱 쿼리 변환

결과만 보장된다면 무조건 쿼리 변환을 수행한다.
일종의 규칙 기반 최적화 기법이라고 할 수 있으며, 경험적으로 항상 더 나은 성능을 보일 것이라는 옵티마이저 개발팀의 판단이 반영되는 것이다.

2. 비용기반 쿼리 변환

변환된 쿼리의 비용이 더 낮은 때만 그것을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 두고 최적화를 수행한다.

필요한 부분에 대해서는 이미 모두 비용기반으로 개선이 이뤄졌다.

---

2. 서브쿼리 Unnesting

1. 서브쿼리의 분류

1. 인라인 뷰

from 절에 나타나는 서브쿼리를 말한다

2. 중첩 서브쿼리

결과집합을 한정하기 위해 where 절에 사용된 서브쿼리를 말한다.
서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 가져 '상관관계 서브쿼리' 라고도 부른다.

3. 스칼라 서브쿼리

한 레코드당 하나의 컬럼 값만을 리턴한다는 특징을 가지고 있다.
주로 select 절에 사용된다.

이들 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 브록 단위로 최적화를 수행한다.
즉, 쿼리 블록 단위로 액세스 경로와 조인 순서, 조인 방식을 선택하는 것을 목표로 한다.

2. 서브쿼리 Unnesting의 의미

'nunest'란 "중첩된 상태를 풀어낸다" 는 뜻을 말하며 서브쿼리 Unnesting 이란 중첩된 서브쿼리를 풀어내는 것을 말한다.

서브쿼리를 처리하는 데 있어 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 아래 둘 중 하나의 방법을 선택한다.

1. 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. => 서브쿼리 Unnesting

   => '서브쿼리 Flattening' 이라고도 부르고, 쿼리 변환 후 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있게 된다.

2. 서브쿼리를 Unnesting 하지 않고 원래대로 둔 상태에서 최적화한다.
   메인쿼리와 서브쿼리를 별도의 서브 플랜으로 구분해 각각 최적화를 수행하며,
   이때 서브쿼리에 Filter 오퍼레이션이 나타난다.

   => Plan Generator가 고려대상으로 삼을만한 다양한 실행계획을 생성해 내는 작업이 매우 제한적인 범위 내에서만 이루어진다.

3. 서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있고, 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높아진다.

이런 이점 때문에 옵티마이저는 서브쿼리 Unnesting을 선호한다.
오라클 9i 에서는 결과집합이 보장되면 무조건 서브쿼리 Unnesting을 사용했다 -> 휴리스틱 쿼리변환
10g 부터는 서브쿼리 Unnesting이 비용기반으로 전환돼 비용이 더 낮을 때만 Unnesting된 버전을 사용하고, 그렇지 않을 때는 원본 쿼리 그대로 필터 방식으로 최적화한다.

4. Unnesting된 쿼리의 조인 순서 조정된 쿼리의 조인 순서 조정

Unnesting에 의해 일반 조인문으로 변환된 후에는 어느 테이블이든 Outer 테이블이 될 수 있다
어느 테이블이 될 지는 옵티마이저가 통계정보를 보고 판단한다.

=> 힌트로 서브쿼리 테이블을 먼저 Outer 테이블로 지정하고 싶다면 order힌트를 사용해야 된다.
10g 부터는 쿼리 블록마다 이름을 지정하는 qb_name 힌트가 제공되므로 더 정확하게 제어할 수 있다.

5. 서브쿼리가 M쪽 집합이거나 Nonunique 인덱스 일 때

옵티마이저는 이럴 때 두 가지 방식 중 하나를 선택하는데, Unnesting 후 어느 쪽 집합이 먼저 드라이빙 되느냐에 따라 달라진다.

1. 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면, 먼저 sort unique 오퍼레이션을 수행함으로써 1쪽 집합으로 만든 다음에 조인한다.

2. 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다.

6. 필터 오퍼레이션과 세미조인의 캐싱 효과

서브쿼리를 Unnesting해 조인문으로 바꾸고 나면 NL 조인은 물론 해시 조인, 소트 머지 조인 방식을 선택할 수 있고, 조인 순서도 자유롭게 선택할 수 있다.

오라클은 필터 조건 최적화 기법을 한 가지 가지고 있는데, 서브쿼리 수행 결과를 버리지 않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 방식이다.
스칼라 서브쿼리의 캐싱 동작과 똑같다. (서브쿼리리를 필터 조건으로 둬도 캐싱한다.)

9i에서의 NL 세미 조인은 캐싱효과가 없지만, 10g 부터는 NL 세미 조인도 캐싱 효과를 갖는다.

7. Anti 조인

not exists, not in 서브쿼리도 Unnesting 하지 않으면 필터 방식으로 처리된다.

8. 집계 서브쿼리 제거

10g에서 집계 함수를 포함하는 서브쿼리를 Unnesting 하고, 이를 다시 윈도우 함수로 대체하는 쿼리변환이 도입되었다.

집계 함수를 서브쿼리를 Unnesting하면 조인문을 만든 쿼리를 한번 더 쿼리변환을 시도해 인라인 뷰를 Merging 하거나 그대로 둔 채 최적화할 수 있다.

10g부터는 서브쿼리로부터 전환된 인라인 뷰를 제거하고 메인 쿼리에 윈도우 함수를 사용하는 형태로 변환하는 것이 가능하다.
이 기능은 _remove_aggr_subquery 파라미터에 의해 제어되며, 비용기반으로 작동한다.
집계 서브쿼리 제거 기능이 작동했을 때는 실행계획에 window buffer 오퍼레이션이 작동한다.

9. Pushing 서브쿼리

Pushing 서브쿼리는 실행계획상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것을 말하며, 이를 제어하기 위해 push_subq 힌트를 사용한다.
Pushing 서브쿼리는 unnesting 되지 않은 서브쿼리에만 작동한다.
따라서 no_unnest + push_subq 힌트를 같이 사용하는 것이 올바른 사용 방법이다.

---

3. 뷰 Merging

뷰 Merging 이란 뷰를 참조하는 쿼리 블록과의 머지 과정을 거쳐 조인문 형태로 변환하는 것이다.
뷰 Merging을 거친 쿼리는 옵티마이저가 더 다양한 액세스 경로를 조사 대상으로 삼을 수 있게 된다.
merge, no_merge 힌트로 제어한다.

1. 단순 뷰(Simple View) Merging

조건절과 조인문만을 포함하는 단순 뷰는 no_merge 힌트를 사용하지 않는 한 무조건 Merging이 일어난다.

2. 복합 뷰(Complex View) Merging

group by절이나 distinct 연산을 포함하는 복합 뷰는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging 이 가능하다.
=> _complex_view_merging = true (8i default = false / 9i default = true)

✅ 복합 뷰 중 뷰 Merging 이 불가능한 경우

• 집합 연산자

• connect by 절

• ROWNUM pseudo 컬럼

• group by 없이 쓰는 전체 집계 함수

• 윈도우 함수 (Analytic Function)

3. 비용기반 쿼리 변환의 필요성

9i 에서 복합 뷰를 무조건 Merging 하도록 구현한 것은, 보편적으로 더 나은 성능을 보였기 때문이다.
그런데 다른 쿼리 변환은 더 나은 성능을 제공하지만, 복합 뷰 Merging은 그렇지 못할 때가 많다.

그래서 10g 부터는 비용기반 쿼리 변환 방식으로 전환하게 되었고,
_optimizer_cost_based_transformation 파라미터를 사용해 제어한다.

• on, off, exhaustive, linear, iterative

각 쿼리 변환마다 제어할 수 있는 힌트가 따로 있고, 필요하다면 10gR2 부터 opt_param 힌트를 이용해 쿼리 레벨에서 파라미터를 변경할 수있다.

실제 비용기반 쿼리 변환으로 바뀌면서 쿼리 성능이 느려지는 경우가 있는데 이럴 때 opt_param 힌트를 이용해 파라미터를 false로 변경하면 된다.

4. Merging 되지 않은 뷰의 처리방식

10g 부터 뷰 Merging을 시행했을 때 비용이 오히려 더 증가한다고 판단되거나 부정확한 결과집합이 만들어질 가능성이 있을 때 옵티마이저는 뷰 Merging을 포기한다.

뷰 Merging을 포기했을 땐 2차적으로 조건절 Pushing을 시도한다.

이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는 데 사용한다.
실제 쿼리를 수행할 때도 뷰 쿼리의 수행 결과를 액세스 쿼리에 전달하는 방식을 사용한다.