쿼리 변환

1. 쿼리 변환이란?

• 쿼리 변환이란 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 SQL을 재작성하는 것을 말한다. 논리적 최적화라고도 한다.
• 쿼리 변환은 결과만 보장된다면 무조건 쿼리 변환을 수행하는 규칙 기반 최적화 기법이라고도 할 수 있는 휴리스틱 쿼리 변환과 변환된 쿼리의 비용이 더 낮을 때만 그것을 사용하고 그렇지 않을 때는 원복 쿼리를 그대로 사용하는 비용기반 쿼리 변환이 있다. 

2. 서브쿼리 Unnesting

• 서브쿼리 Unnesting은 중첩된 서브쿼리(where절)를 풀어내는 것을 말한다. 오라클은 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. 메인과 서브쿼리 간의 계층구조를 풀어서 서로 같은 레벨로 만들어주는 것이다. 같은 결과일 때 조인문으로 변환하는 이유는 조인 형태로 변환했을 때 더 나은 실행계획을 찾을 가능성이 높기 때문이다.
• Unnesting에 의해 일반 조인문으로 변환된 후에는 두 테이블 중에서 어느 테이블이 드라이빙 집합으로 될지는 옵티마이저가 통계정보에 의해 결정된다. 옵티아미저가 잘못된 판단을 할 경우 leading 힌트나 쿼리 블록마다 이름을 지정할 수 있는 qb_name 힌트를 사용해서 정확하게 제어할 수 있다.
• 서브쿼리가 M 쪽 집합이거나 Nonunique 인덱스일 때 옵티마이저는 1쪽 집합임을 확신할 수 없는 서브쿼리 쪽 테이블이 드라이빙된다면 sort unique 오퍼레이션을 수행하고 1쪽 집합으로 만든 다음 조인을 한다. 또는 메인 쿼리 쪽 테이블이 드라이빙된다면 세미 조인 방식으로 조인한다.
• Pushing 서브쿼리는 실행계획 상 가능한 앞 단계에서 서브쿼리 필터링이 처리되도록 강제하는 것을 말한다. Pushing 서브쿼리는 Unnesting 되지 않은 서브쿼리에만 작동하기 때문에  no_unnest push_subq 두 힌트를 같이 써야 한다. 
• 쿼리 변환은 대게 더 나은 성능을 제공하지만 때때로 그렇지 않을 때도 있다. 그래서 나온 것이 비용기반 쿼리 변환 방식이다.  Unnesting과 조건절 Pushing도 비용기반 쿼리 변환 방식으로 전환되었다. 하지만 이 기능도 때때로 쿼리마다 성능이 느려질 수 있기 때문에 /+ '_optimizer_push_pred_cost_based', 'false') */ 힌트를 사용할 수 있다.

3. 뷰 Merging

• 개발자들의 눈으로 볼 때는 쿼리를 블록화 하는 것이 더 읽기 편하지만 옵티마이저에서는 더 불편하기 때문에 머지 과정을 거쳐서 합친다. 조건절과 조인문만을 포함하는 단순 뷰는 보통 Merging이 발생하지만 group by, distinct는 힌트 사용에 의해서만 Merging이 가능하고 집합 연산자, connect by, rownum 등의 복합뷰는 Merging 할 수 없다.

4. 조건절 Pushing

• 옵티마이저가 쿼리를 최적화시키는 1차 과정에서 뷰 Merging에 실패하면 2차 과정으로 조건절 Pushing을 시도한다. 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing 하는 기능이다.
• 쿼리 변환은 대게 더 나은 성능을 제공하지만 때때로 그렇지 않을 때도 있다. 그래서 나온 것이 비용기반 쿼리 변환 방식이다.  Unnesting과 조건절 Pushing도 비용기반 쿼리 변환 방식으로 전환되었다. 하지만 이 기능도 때때로 쿼리마다 성능이 느려질 수 있기 때문에 /+ '_optimizer_push_pred_cost_based', 'false') */ 힌트를 사용할 수 있다.
• 조건절 Pushdow은 쿼리 블록 밖에 있는 조건들을 쿼리 블록 안쪽으로 밀어 넣는 것을 말하며 group by 절에 포함한 복합 뷰로 인해 Merging이 실패하면 쿼리 밖에 있는 조건절을 블록 안쪽에 밀어 넣어 group by 해야 하는 데이터량을 줄인다. Union 집합 연산자를 포함한 뷰에도 조건절 Pushdown으로 최적화 가능하다.
• 조건절 Pullup은 조건절을 쿼리 블록 안으로 밀어 넣을 뿐만 아니라 안쪽에 있는 조건들을 바깥쪽으로 끄집어내고 그것을 다시 다른 쿼리 블록에 Pushdown 하는 데 사용한다. 
• 조인 조건 Pushdown은 조인 조건절을 뷰 쿼리 블록 안으로 밀어 넣어 NL 조인 수행 중에 드라이빙 테이블에서 읽은 조인 칼럼 값을 Inner 쪽 뷰 쿼리 블록 내에서 참조할 수 있도록 하는 기능이다. 인라인 뷰 내에서 메인 쿼리에 있는 칼럼을 참조할 수 없음에도 옵티아미저가 이름 참조하는 조인 조건을 뷰 안쪽에 생성해 주는 것이다.

5. 조건절 이행

• 조건절 이행은 a=b, b=c면 a=c라고 추론하여 새로운 조건절을 내부적으로 만들어주는 쿼리변환이다.

6. 조인 제거

• 1:M 관계인 두 테이블을 조인하는 쿼리문에서 조인문을 제외한 어디에서도 1쪽 테이블을 참조하지 않으면 옵티아미저는 M 쪽 테이블만 읽도록 쿼리를 변환하는 것이 조인 제거이다.

7. OR-Expansion

• OR 연산자로 연결된 각 조건에 개별적인 SELECT 문으로 분리되어 각각 인덱스를 사용하여 중복 선택을 제거한뒤  union all 최종 결과를 CONCATENATION 하는 기능이다.

8. 공통 표현식 제거

• 같은 조건식이 여러 곳에서 반복 사용될 경우, 해당 조건식을 각 로우당 한 번씩만 평가되도록 쿼리를 변환하는 기능이다. 이를 통해 비교 연산을 덜 하게 되기도 하지만 새로운 인덱스 액세스 조건을 만들 수도 있다.

9. Outer 조인을 Inner조인으로 변환

• Outer 조인문을 작성하면서 일부 조건절에 Outer 기호(+)를 빠뜨리면 Inner 조인할 때와 같은 결과가 나오는데 Outer 조인을 Inner 조인문으로 바꾸는 쿼리 변환한다. 조인 순서를 자유롭게 결정해 더 나은 실행결과를 만들기 위해서다.
• Outer 조인에서 기호를 정확히 구사하지 않거나 ANSI Outer 조인문일 때 on절에 기술하지 않고 where절에 필터조건을 작성하면 Inner 조인처럼 되기 때문에 성능이 나빠진다.

10. 실체화 뷰 쿼리로 재작성

• 뷰는 쿼리만 저장하고 있을 뿐 자체적으로 데이터를 갖지는 않는다. 하지만 실체화 뷰(MV)는 물리적으로 실제 데이터를 갖는다. DW분야에서는 주로 두 개 이상의 테이블을 미리 조인해 두거나 대량의 테이블을 미리 group by 해서 집계해 두는 형태로 많이 활용하고 있다.
• 사용자가 직접 관리하는 일반 집계 테이블과 비교할때 MV의 가장 큰 장점은 자동으로 쿼리가 재작성된다. MV 사용자는 집계 테이블의 존재를 몰라도 옵티마이저가 알아서 MV를 액세스 하도록 쿼리를 변환시켜 준다.

11. 집합 연산을 조인으로 변환

• Intersect나 Minus 같은 집합 연산을 조인 형태로 변환하는 것이다.

12. 기타 쿼리 변환

• 조인문을 처리할 때 조인 칼럼이 null인 데이터는 조인 액세스가 불필요하기 때문에 옵티아이저가 알아서 조인 칼럼마다 is not null 필터 조건을 추가해 주어 불필요한 테이블 액세스 및 조인 시도를 줄일 수 있다.
• 사전에 두 값을 비교해 공집합이라면 옵티마이저가 임의로 필터 조건식을 추가한다.
• 테이블 전체를 스캔하거나 인덱스를 수평적으로 스캔할 때의  Filter 조건식을 평가할 때 선택도가 낮은 칼럼을 먼저 처리하도록 순서를 조정한다. 단 CPU Costing 모델을 활성화해야 동작한다.

 

 

- 참고 서적 : 오라클 성능 고도화 원리와 해법 2 -