[오성고원해] 쿼리변환

비용기반 옵티마이저는 사용자 SQL을 최적화에 유리한 형태로 재작성하는 작업을 먼저한다.

비용기반 옵티마이저의 서브 엔진 Query Transformer가 해당 역할을 담당한다.

 

쿼리변환 : 쿼리 옵티마이저가 SQL을 분석해 의미적으로 동일(같은 결과를 리턴)하면서도 더 나은 성능을 기대되는 형태로 재작성하는 것을 말한다.

 

1. 서브쿼리 Unnesting
2. 뷰 Merging
3. 조건절 Pushing
4. 조건절 이행
5. 공통 표현식 제거
6. Outer 조인을 Inner 조인으로 변환
7. 실체화 뷰 쿼리로 재작성
8. Star 변환
9. Outer 조인 뷰에 대한 조인 조건 Pushdown
10. OR-expansion

이 존재한다.

 

쿼리 변환은 2가지 종류가 있는데

• 휴리스틱 쿼리 변환 : 결과만 보장된다면 무조건 쿼리 변환을 수행한다. 규칙 기반 최적화 기법이라고 할 수 있다 
• 비용기반 쿼리 변환 : 변환된 쿼리의 비용이 더 낮을때만 그것을 사용하고 그렇지 않을때는 원본 쿼리 그대로 두고 최적화를 수행한다. 

서브쿼리 Unnesting

서브쿼리는 하나의 SQL문장 내에서 괄호로 묶인 별도의 쿼리 블록을 말한다. 즉 쿼리에 내장된 또 다른 쿼리다.

1. 인라인 뷰 : from 절에 나타나는 서브쿼리를 말한다
2. 중첩된 서브쿼리 : 결과집합을 한정하기 위해 where절에 사용된 서브쿼리를 말한다. 서브쿼리가 메인쿼리에 있는 컬럼을 참조하는 형태를 '상관관계 있는 서브쿼리'라고한다.
3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 컬럼 값만을 리턴하는 것, select-list에서 대부분 사용하지만 컬럼이 올 수 있는 대부분 위치에서 사용가능하다.

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

하지만 각 서브쿼리가 최적화되었다고 쿼리 전체가 최적화됐다고 할 수 없다.

 

서브쿼리를 풀어내는 두 가지 쿼리 변환중 '서브쿼리 Unnesting'은 중첩된 서브쿼리와 관련있고

뷰 Merging은 인라인 뷰와 관련있다.

 

서브쿼리 Unnesting의 의미

nest는 상자 등을 차곡차곡 포개넣다 라는 설명이 있다.

즉 unnest는 중첩된 상태를 풀어낸다가 된다.

 

중첩된 서브쿼리는 부모와 자식이라는 중속적이고 계층적인 관계가 존재한다.

이런 종속적이고 계층적인 존재는 처리과정에서 IN, Exists를 불문하고 필터 방식이어야한다. 즉 메인 쿼리에서 읽히는 레코드마다 서브쿼리를 반복수행하면서 조건에 맞지 않는 데이터를 골라내는 것이다.

하지만 필터 방식이 항상 최적의 수행속도를 보장하지 못하므로 옵티마이저는 둘 중 하나를 선택한다.

• 동일한 결과를 보장하는 조인문으로 변환하고 나서 최적화한다. -> 서브쿼리 Unnesting
• 서브쿼리를 Unnesting하지 않고 원래대로 둔 상태에서 최적화한다. 메인쿼리와 서브쿼리를 별도의 서브플랜으로  구분해 각각 최적화를 수행하며 이때 서브쿼리에 필터 오퍼레이션이 나타난다.

첫번째 서브쿼리 Unnesting은 메인과 서브쿼리 간의 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어준다는 점에서 서브쿼리 Flattienng이라고도 부른다.

두번째처럼 Unnesting하지 않고 쿼리 블록별로 최적화할때는 각각의 최적이 쿼리문 전체의 최적을 달성하지 못할때가 많다. 그리고 Plan Generator가 고려대상으로 삼을만한 다양한 실행계획을 생성해 내는 작업이 매우 제한적인 범위 내에서만 이루어진다.

서브쿼리를 위해 오라클 옵티마이저가 사용하는 최적화 기법이 한가지 더 있다. where 조건절에 사용된 서브쿼리가 메인쿼리와 상관관계에 있지 않으면서, 단일로우를 리턴하는 아래와 같은 형태의 서브쿼리를 처리할 때 나타나는 방식이다.
- SELECT * FROM tab1 WHERE key1 = (select avg(col1) from tab2)l
- SELECT * FROM tab1 WHERE (key1, key2) = (SELECT col1, col2 from tab2 WHERE col3 >=5000 and rownum = 1);
위와 같은 서브쿼리는 Fetch가 아닌 Execute 시점에 먼저 수행되어 그 결과 값을 메인 쿼리에 상수로 제공한다
- SELECT * FROM tab1 WHERE key1 = value1
- SELECT * FROM tab1 WHERE (key1, key2) = (value1, value2)
조건절에서 서브쿼리를 in이 아닌 '='조건으로 비교한다는 것은 서브쿼리가 단일 로우를 리턴하게 됨을 의미하므로 이런 방식을 사용할 수 있는 것이다. 이들 서브쿼리가 2개 이상의 로우를 리턴하면 에러가 발생하므로 rownum <= 1이나 stopkey, min, max, avg등 집계함수가 사용된다.

서브쿼리 Unnesting의 이점

서브쿼리를 메인쿼리와 같은 레벨로 풀어낸다면 다양한 액세스 경로와 조인 메소드를 평가할 수 있다. 특히 옵티마이저는 많은 조인 테크닉을 가지기 때문에 조인 형태로 변활했을때 더 나은 실행계획을 찾을 가능성이 높아진다.

그래서 옵티마이저는 서브쿼리 Unnesting을 선호한다

옵티마이저 힌트 : unnest, no_unnest

 

예시 : SELECT * FROM emp WHERE deptno in (SELECT deptno FROM dept)

Unnesting하지않고 그대로 최적화하면 옵티마이저는 필터 방식의 실행계획을 수립한다.

Predicate 정보를 보면 필터 방식으로 수행된 서브쿼리의 조건절이 바인드 변수로 처리된 부분 (DEPTNO = :B1)이 눈에 띄는데, 이것을 통해 옵티마이저가 서브쿼리를 별도의 서브플랜으로 최적화한다는 것을 알수있다. 메인 쿼리도 하나의 쿼리 블록이므로 서브쿼리를 제외한 상태에서 별도의 최적화가 이루어졌다.

Unnesting하지 않은 서브쿼리를 수행할 때는 메인 쿼리에서 읽히는 레코드마다 값을 넘기면서 서브쿼리를 반복수행한다.

서브쿼리 Unnest를 하면 서브쿼리인데도 일반적인 NL 조인 방식으로 수행되었다.

 

Unnestinge된 쿼리의 조인 순서 조정

leading 힌트를 사용해서 쿼리의 조인 순서를 조정할 수 있다.

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

결과 오류가 발생하거나. Unique 인덱스가 없어서 옵티마이저는 emp와 dept 간의 관계를 알 수 없고, 결과를 확신할 수 없어서 일반 조인문으로 쿼리 변환을 시도하지 않는다.

그래서

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

Sort Unique 오퍼레이션

Sort Unique는 distinct로 만들어진거다.

세미 조인 방식으로 수행

만약 같은 값을 찾으면 바로 끝내도록 수행, 해시 세미 조인, 소트머지 조인도 가능

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

서브쿼리를 Unnesting 하지 않으면 쿼리를 최적화하는 데 있어 선택의 폭이 넓지 않아 불리하다. 메인 쿼리를 수행하면서 건건이 서브쿼리를 반복 수행하는 단순한 필터 오퍼레이션을 사용할 수 밖에 없기때문. 대량의 집합을 기준으로 Random 액세스 방식으로 서브쿼리 집합을 필터링 한다면 결코 빠른 수행 속도를 얻을 수 없다.

--> 오라클은 서브쿼리 수행 결과를 버리지않고 내부 캐시에 저장하고 있다가 같은 값이 입력되면 저장된 값을 출력하는 캐싱을 사용한다.

근데 10g이상부터는 세미 조인도 캐싱 효과를 가지므로 필터 오퍼레이션은 따잇 당해버렸다.

Anti 조인

not exists, not in 서브쿼리도 unnesting하지 않으면 아래와 같이 필터 방식으로 처리된다.

기본 루틴은 exists 필터와 동일하며 조인에 성공하는 레코드가 하나도 없을 때만 결과집합에 포함시킨다는 점이다.

- exists 필터 : 조인에 성공하는 레코드를 만나는 순간 결과집합에 담고 다른 레코드로 이동한다.

- not exists 필터 : 조인에 성공하는 레코드를 만나는 순간 버리고 다음 레코드로 이동한다. 조인에 성공하는 레코드가 하나도 없을때만 결과집합에 담는다.

집계 서브쿼리 제거

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

Pushing 서브쿼리

Pushing 서브쿼리는 실행계획상 가능한 앞단계에서 서브쿼리 필터링이 처리되도록 강제하는 것. (push_subq)

Pushing 서브쿼리는 Unnesting 되지않은 서브쿼리에만 작동된다. --> push_subq는 no_unnest힌트와 같이 기술

 

뷰 Merging

뷰 쿼리 블록은 액세스 쿼리 블록과의 머지 과정을 거쳐 일반적인 쿼리 형태로 변환하는데 이를 뷰 Merging이라고 한다. 이래야 옵티마이저가 더 다양한 액세스 경로를 조사대상으로 삼는다.

 

단순 뷰 Merging

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

반면 group by절이나 distinct연산을 포함하는 복합 뷰는 파라미터 설정 또는 힌트 사용에 의해서만 뷰 Merging이 가능하다. 또한 집합 영산자. connect by, rownum등을 포함하는 복합 뷰는 아예 뷰 머징이 불가능한다.

복합 뷰 Merging

group by 절, select-list에 distinct 연산자

같은 복합 뷰는 _complex_view_merging 파라미터를 true로 했을때만 머징이 일어난다.

 

merge 힌트를 써도

• 집합 연산자 (union, union all, intersect, minus)
• connect by 절
• ROWNUM pesudo 컬럼
• select-list에 집계 함수 사용
• 분석 함수

는 뷰 머징을 쓸수없다.

Merging 되지않은 뷰의 처리방식

뷰 Merging을 시행했을때 오히려 비용이 증가한다고 판단되거나 부정확한 결과집합이 만들어질 경우, 옵티마이저는 뷰 Merging을 포기한다. 어떤 이유에서건 뷰 Merging이 이루어지지 않았을땐 2차적으로 조건절 Pushing을 시도한다. 하지만 이마저도 실패한다면 뷰 쿼리 블록을 개별적으로 최적화하고, 거기서 생성된 서브플랜을 전체 실행계획을 생성하는 데 사용한다. 실제 쿼리를 수행할 때도 뷰 쿼리의 수행 결과를 액세스 쿼리에 전달하는 방식을 사용한다.

조건절 Pushing

뷰를 액세스하는 쿼리를 최적화할때 옵티마이저는 1차적으로 뷰 머징을 고려한다. 하지만

• 복합 뷰 머지 기능이 비활성화
• 사용자가 no_merge 힌트사용
• Non-mergable Views : 뷰 머징 시행하면 부정확한 결과 가능성
• 비용기반 쿼리 변환이 작동해 No Merging 선택시

뷰 머징을 실패할 수 있다.

실패하면 옵티마이저는 뷰를 참조하는 쿼리 블록의 조건절을 뷰 쿼리 블록 안으로 Pushing하는 기능을 일컫는다.

조건절이 가능한 빨리 처리되도록 뷰 안으로 밀어 넣는다면, 뷰 안에서의 처리 일량을 최소화하게 됨은 물론 리턴되는 결과 건수를 줄임으로써 다음 단계에서 처리해야할 일량을 줄일 수 있다.

 

종류 

• 조건절 Pushdown : 쿼리 블록 밖에 있는 조건들은 쿼리 블록 안쪽으로 밀어 넣는 것을 말함
• 조건절 Pullup : 쿼리 블록 안에 있는 조건들을 쿼리 블록 밖으로 내오는 것을 말하며, 그것을 다시 다른 쿼리 블록에 Pushdown 하는데 사용함
• 조인 조건 Pushdown : NL 조인 수행 중에 드라이빙 테이블에서 읽은 값을 건건이 Inner 쪽 뷰 쿼리 블록 안으로 밀어 넣는 것을 말함

조건절 Pushdown과 Pullup은 항상 더 나은 성능을 보장하므로 별도의 힌트를 제공하지 않는다. 하지만 조인 조건 Pushdown은 NL 조인을 전제로 하기 때문에 성능이 더 나빠질 수 있다. 따라서 오라클은 조인 조건 Pushdown을 제어할 수 있도록 push_pred와 no_push_pred 힌트를 제공한다.

그런데 해당 기능이 10g에서 비용기반 쿼리 변환으로 바뀌었고 이 때문에 9i에서 빠르게 수행되던 쿼리가 10g로 이행하면서 오히려 느려지는 현상이 종종 나타난다. 이때는 opt_param('_optimizer_push_pred_cost_based', 'false') 힌트를 사용하면 된다.