스칼라 서브쿼리 UNNESTING
스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 에 따른 부담이 클 수 있다.
그래서 NL 이 아닌 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환해야 하는 경우가 있다.
그럴 때 예전에는 사용자가 직접 쿼리를 변환해야 했지만, 오라클 12c 부터는 스칼라 서브쿼리도 unnesting 이 가능해졌다.
옵티마이저가 사용자를 대신해 자동으로 쿼리를 변환해주는 것.
Unnesting(언네스팅) 은 데이터베이스 옵티마이저가 중첩된 서브쿼리(Subquery) 를 풀어서 메인 쿼리와 동일한 레벨로 합치는 최적화 기법이다.
1. 개념 및 목적
서브쿼리는 본래 메인 쿼리에서 필터링 역할을 수행하는 부속적인 존재다. 하지만 중첩된 상태(Nested)로 두면 옵티마이저가 선택할 수 있는 조인 경로가 제한된다. Unnesting은 이 서브쿼리를 일반적인 Join 형태로 변환함으로써 더 넓은 범위의 실행 계획을 탐색할 수 있게 한다.
-
목적: 서브쿼리와 메인 쿼리를 같은 레벨로 만들어 최적의 조인 순서(Join Order)와 조인 방식(Method)을 결정하기 위함이다.
-
반대 개념: 서브쿼리를 풀지 않고 그대로 둔 채 순차적으로 실행하는 방식은 Filter 단계로 남게 된다.
2. 변환 방식
필터 방식 (Unnesting 미수행)
메인 쿼리의 행을 하나씩 읽으면서 매번 서브쿼리를 반복 실행한다. (Nested Loop 방식과 유사)
Unnesting 방식 (Join으로 변환)
서브쿼리를 독립적인 테이블처럼 취급하여 메인 쿼리와 조인을 수행한다.
- Unique Subquery: 서브쿼리 결과가 유일(Unique)함이 보장되면 단순 Join으로 변환한다.
- Semi-Join: 서브쿼리 결과에 중복이 있을 수 있는 경우(예:
IN,EXISTS), 결과 집합이 불어나지 않도록 Semi-Join 기법을 사용하여 데이터 무결성을 유지하며 처리한다.
3. Unnesting의 이점
- 다양한 조인 알고리즘 사용 가능: 단순 필터링 방식은 대개 Nested Loop 형태에 국한되지만, Unnesting을 하면 Hash Join이나 Sort Merge Join을 활용할 수 있다.
- 조인 순서 변경 가능: 메인 쿼리보다 서브쿼리의 결과가 작을 경우, 서브쿼리로부터 생성된 집합을 드라이빙 테이블(Driving Table)로 삼아 효율을 높일 수 있다.
4. 제어 방법 (힌트 사용)
옵티마이저가 자동으로 Unnesting을 수행하지 않거나, 반대로 강제하고 싶을 때 SQL 힌트를 사용한다. (Oracle 기준)
/*+ UNNEST */: 서브쿼리를 강제로 Unnesting 하도록 유도한다./*+ NO_UNNEST */: 서브쿼리를 풀지 않고 Filter 방식으로 남겨두도록 강제한다.
요약
Unnesting은 서브쿼리의 ‘중첩’ 장벽을 허물어 일반 조인문으로 재작성하는 최적화 과정이다. 이를 통해 옵티마이저는 데이터 접근 경로를 더 효율적으로 선택할 수 있다.
그렇게 단정할 수 없다. Unnesting이 항상 이득은 아니며, 특정 상황에서는 오히려 성능을 심각하게 저하시킬 수 있다. 옵티마이저가 Unnesting을 포기하고 Filter 방식을 선택하는 데에는 명확한 공학적 이유가 존재한다.
Unnesting이 불리한 대표적인 케이스는 다음과 같다.
1. 메인 쿼리의 결과 집합이 매우 적은 경우
메인 쿼리에서 추출되는 행이 단 몇 건에 불과한데, 서브쿼리가 참조하는 테이블이 거대할 경우다.
-
Filter 방식: 메인 쿼리의 행 수만큼만 서브쿼리를 반복 수행하면 된다. (Index가 있다면 매우 빠름)
-
Unnesting 방식: 서브쿼리 전체를 하나의 집합으로 구성하여 조인을 수행해야 한다. 대량의 데이터를 해시 테이블로 만들거나 정렬하는 과정에서 불필요한 리소스 소모가 발생한다.
2. 서브쿼리의 선택도(Selectivity)가 낮은 경우
서브쿼리가 메인 쿼리의 데이터를 거의 걸러내지 못할 때 발생한다.
- 조인으로 변환했을 때 조인 순서(Join Order)가 잘못 잡히면, 거대한 중간 집합(Cartesian Product에 가까운 형태)이 생성되어 메모리(TempDB 또는 Temp Tablespace)를 과다하게 사용할 위험이 있다.
3. 데이터 무결성 유지를 위한 오버헤드
서브쿼리에 중복 데이터가 존재할 경우, 단순 Join으로 풀면 메인 쿼리의 결과가 불어나는 현상이 발생한다. 이를 방지하기 위해 옵티마이저는 Semi-Join이나 Distinct 연산을 추가해야 하는데, 이 과정 자체가 단순 필터링보다 더 많은 비용(Cost)을 초래할 수 있다.
4. Filter vs Unnesting 전략 비교
| 상황 | Filter(Unnesting 미수행) 유리 | Unnesting(Join 변환) 유리 |
|---|---|---|
| 메인 쿼리 크기 | 매우 작음 (1~100건) | 큼 |
| 서브쿼리 인덱스 | 효율적인 인덱스 존재 | 인덱스 없거나 Full Scan 유리 |
| 데이터 분포 | 메인 쿼리 조건으로 대폭 필터링됨 | 서브쿼리가 결과 집합을 주도함 |
Sheets로 내보내기
5. 결론: 옵티마이저의 판단 기준
옵티마이저는 **비용 기반(CBO, Cost-Based Optimizer)**으로 작동한다.
-
서브쿼리를 풀었을 때의 예상 비용과 풀지 않았을 때의 비용을 계산한다.
-
만약 Unnesting 시 발생하는 정렬/해시/중복 제거 비용이 필터링 반복 비용보다 크다면, 옵티마이저는 의도적으로 Unnesting을 수행하지 않는다.