[liveSQL] 쿼리 변환, 서브 쿼리 Unnesting(1)

2020/02/09 - [공부/SQL] - [liveSQL] 실행계획 확인

1. 서브쿼리

- FROM 절의 서브쿼리 = 인라인뷰, 그 외 서브 쿼리라고 명침. 구분하기로 이 둘은 다른 것이라고 함. (SELECT 절의 서브 쿼리 = 스칼라 서브쿼리)

(db가이드넷) '서브쿼리에서는 ordery by절을 사용할 수 없다. 그러나 인라인뷰에서 사용할 수 있다.'

 

- IN, EXISTS 절의 서브쿼리 = 세미 조인 (ref.구루비) 대표 형태. 반면, WHERE a.id = b.id '일반 조인문'이라 함.

 

- 현재 다룰 것은 세미조인 형태에서 unnesting 사례

 

2. Unnesting 

select * from A where id in (select id from B)

/* Unnesting */
-> select * from A a, (select id from B) b where a.id = b.id;

 

옵티마이저는 서브쿼리를 만났을 때 2가지로 처리함. 1) 일반 조인문으로 풀어내기(flatting 작업), 2) 필터 방식.

이 때, 세미 조인 형태에서 풀어낼 때 서브 쿼리의 상태에 따라 경우의 수가 존재함.

 

(1) 서브 쿼리쪽에 PK/FK 제약 또는 Unique한 인덱스가 있을 경우,

• [ 메인 : 서브 = M : 1 ], (결과 정상) unnesting 되어 M쪽 집합이 출력됨.

• [ 메인 : 서브 = 1: M ],  (결과 오류) 세미조인(현재 쿼리문은 IN, EXISTS이므로 세미조인이라고 칭함)의 특성상 결과 집합이 1쪽이 되야하지만 unnesting 되어 M쪽 집합이 출력됨.

 

(2) ~ 그러한 인덱스와 제약 사항이 없는 경우,

• [ 메인 : 서브 = M : 1 ], (결과는 정상) 오류는 없지만! 옵티마이저가 해당 테이블 간의 관계를 알 수 없으므로 unnesting 쿼리 변환를 실제로 하지 않음.

 

그럼에도 불구하고, 사용자가 직접 unnesting 시킬 경우(hint /*+ unnest */ 사용) "unnesting 이후 어느 쪽 집합을 먼저 드라이빙 하느냐"에 따라 케이스가 나눠짐.

 

(2.1) 1쪽 집합인지 뭔지 모를 서브쿼리쪽을 드라이빙(먼저 access) 할 경우, sort unique 먼저 수행.

(2.2) 메인 쿼리 쪽 테이블이 먼저 드라이빙 할 경우, semi join 방식 수행.

 

 

( 이 과정에서 어느 테이블에 (not unique) index 컬럼이 잡혀있느냐가 영향을 미쳤음)

 

3. 결과

< 메인 쿼리 [ IN | EXISTS ] 서브 쿼리 >

"메인 테이블의 인덱스 컬럼을 만들 경우, Sort unique 오퍼레이션 동작함 " (3번, 7번케이스)

그 외는 Semi Join 형식으로 모두 동작함.

* (5)~(8) : 메인(1), 서브(M), 이 경우는 관심사는 아니나 추가 자료로 출력하였음.

(1) Driving : (M쪽) 메인 - Index : none = (2.2) 범주 속함

 

세미 조인 방식은 결과 집합이 M 쪽으로 확장되는 것을 막는 알고리즘을 사용함 : outer 테이블의 한 로우가 Inner 테이블의 로우와 조인 첫 성공을 하게 되는 경우 다음 outer 테이블의 로우로 넘어감.

 

(2) Driving : (M쪽) 메인 - Index : (M쪽) 메인 = (2.1) 범주 속함

 

(3) Driving : (M쪽) 메인 - Index : (1쪽) 서브 = (2.2) 범주 속함

 

(4) Driving : (M쪽) 메인 - Index : (M쪽) 메인, (1쪽) 서브 = (2.2) 범주 속함

 

(참고.5) Driving : (1쪽) 메인 - Index : none = (2.2) 범주 속함

 

(참고.6) Driving : (1쪽) 메인 - Index : (M쪽) 서브 = (2.2) 범주 속함

 

(참고.7) Driving : (1쪽) 메인 - Index : (1쪽)메인 = (2.1) 범주 속함

 

(참고.8) Driving :  (1쪽) 메인 - Index : (M쪽) 서브, (1쪽) 메인 = (2.2) 범주 속함

 

4. 결론

- db가이드넷식 표현을 떠올려보자면,

결국 unnesting된 상태에서는 모두 같은 표현을 가지는 데, 이 때 메인 테이블의 인덱스 컬럼(not unique, not PK/FK 제약 인덱스)을 정의하게 되면 서브 쿼리를 먼저 드라이빙하였고 sort unique 연산을 하며 중복없는 1쪽 집합으로 만들어줌.(ref.db가이드넷식 표현)

- 구루비식 표현을 떠올려보자면

서브 쿼리가 먼저 실행된 후(driving) 메인 쿼리가 실행된 것을 보고 서브 쿼리는 '제공자'로서 동작하였고, 그 외 HASH Semi Join으로 실행된 것은 '확인자'로서 동작한 것이다.(ref.구루비식 표현). 그리고 메인 쿼리의 결과 집합을 보장하기 위하여 Sort Unique 연산이 실행되었다.

* 그러나, 의문 : "왜 인덱스 여부에 따라 달라지는 것인가?"

 

https://github.com/snrndi121/SQL_CODE_STUDY/blob/master/join/transQuery.md#11-%EC%84%9C%EB%B8%8C%EC%BF%BC%EB%A6%AC%EC%97%90-pkfk-%EB%98%90%EB%8A%94-unique-%EC%9D%B8%EB%8D%B1%EC%8A%A4-%EC%97%86%EB%8A%94-%EA%B2%BD%EC%9A%B0

snrndi121/SQL_CODE_STUDY