SubQuery Unnesting

게시 2026/04/30

By 반지현

읽는 시간 11분

SubQuery Unnesting

Subquery Unnesting 은 IN / EXISTS 형태의 서브쿼리를 FROM 절의 일반 join 으로 끌어올려 변환 하는 옵티마이저 변환입니다.
변환 후에는 결과집합이 작은 쪽이 driving 이 되어 메인 테이블의 인덱스를 정확히 lookup 합니다.

핵심 정리 (개념)

변환의 정의: 서브쿼리가 FROM 절 위로 올라가 일반 join (NL / HASH / SORT-MERGE) 으로 풀립니다. WHERE col IN (subquery) 가 FROM main, subq WHERE main.col = subq.col 형태로 변환됩니다.
효과의 본질: unnesting 후 옵티마이저가 cost 기반으로 driving 을 결정합니다. 결과집합이 작은 테이블이 driving 이 되면 작은 결과를 outer 로 두고 큰 메인 테이블의 인덱스를 정확히 lookup 할 수 있어 압도적으로 빠릅니다.
적용 효과 조건: subquery 와 연결되는 메인 컬럼에 적절한 인덱스 + subquery 결과가 메인을 큰 폭으로 좁혀 주는 access 조건일 때 가장 큰 이득을 봅니다.

Subquery Unnesting 의 변환 흐름

[원본 SQL]

SELECT ... FROM main
 WHERE col IN (SELECT col
                 FROM subq
                WHERE <subq filter>)

[Unnesting 후 (옵티마이저 내부)]

SELECT ... FROM main, subq
 WHERE main.col = subq.col
   AND <subq filter>

변환 후 두 테이블이 같은 레벨에서 join 됩니다. 옵티마이저가 LEADING / cost 기반으로 driving 을 결정합니다.
서브쿼리 결과가 작으면 자동으로 driving 이 되어 메인 인덱스 lookup 으로 풀립니다.
서브쿼리 결과가 크고 메인이 작으면 옵티마이저가 driving 을 메인으로 선택할 수도 있습니다.

Unnesting 의 몇 가지 형태

형태	변환 결과	특징
`IN` / `= ANY`	inner join 또는 SEMI JOIN	결과 row 수가 메인과 동일 (semi) 또는 늘어남 (1:N inner)
`NOT IN` / `<> ALL`	ANTI JOIN	NULL 처리에 주의
`EXISTS`	SEMI JOIN	한 번 매칭되면 stop
`NOT EXISTS`	ANTI JOIN	NULL 안전

SEMI JOIN / ANTI JOIN 으로 변환되면 row 수가 변하지 않아 정합성이 안전합니다. 일반 inner join 으로 변환되는 IN 의 경우는 1:N 관계에서 row 수가 늘어날 수 있습니다.

1:N 일 때 SORT UNIQUE 가 추가되는 이유

메인 1 row : 서브쿼리 N row 매칭이 가능한 형태 (서브쿼리 컬럼이 unique 가 아닐 때) 에서 IN 을 일반 inner join 으로 풀면 메인 row 가 N 배로 부풀려집니다. 옵티마이저는 이를 막기 위해 서브쿼리 컬럼을 SORT UNIQUE 또는 HASH UNIQUE 로 distinct 처리한 뒤 join 합니다.

옵티마이저가 서브쿼리 컬럼이 PK / unique 인 것을 알면 (constraint 가 명시되어 있으면) SORT UNIQUE 단계가 생략됩니다. 따라서 constraint 명시가 unnesting 비용을 줄이는 추가적인 효과 가 있습니다.

시나리오

ORDER_ITEMS (대용량) 에서 특정 상품 (PRODUCT_NAME = 'CPU D600') 의 1 개월치 주문을 조회합니다.

인덱스는 다음과 같습니다.

테이블	인덱스	컬럼
`ORDER_ITEMS`	`IX_ORDER_ITEMS_N2`	`PRODUCT_ID, ORDER_DATE`
`PRODUCTS`	`IX_PRODUCTS_PK`	`PRODUCT_ID` (PK)

IX_ORDER_ITEMS_N2 (PRODUCT_ID, ORDER_DATE) 가 본 시나리오의 핵심입니다. subquery 가 unnest 되어 PRODUCT_ID 가 driving 이 되면 이 결합 인덱스를 정확히 RANGE SCAN 할 수 있습니다.

1) 원본 — `NO_UNNEST` 로 unnesting 차단

SELECT ORDER_ID, ORDER_DATE, PRODUCT_ID
     , UNIT_PRICE, QUANTITY
  FROM ORDER_ITEMS A
 WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND ORDER_DATE <  TO_DATE('20120201', 'YYYYMMDD')
   AND PRODUCT_ID IN (SELECT /*+ NO_UNNEST */ PRODUCT_ID
                        FROM PRODUCTS B
                       WHERE PRODUCT_NAME = 'CPU D600');

NO_UNNEST 힌트로 변환을 차단하면 옵티마이저는 메인 ORDER_ITEMS 를 처리하면서 각 row 마다 서브쿼리를 evaluation 합니다. 메인 result 후보가 많을수록 서브쿼리가 그만큼 호출됩니다.

---------------------------------------------------------------------------------------
| Id | Operation                       | Name             | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                  |      1 |    719 |    137K |
|* 1 |  FILTER                         |                  |      1 |    719 |    137K |
|* 2 |   TABLE ACCESS FULL             | ORDER_ITEMS      |      1 |   210K |   70833 |
|* 3 |   TABLE ACCESS BY INDEX ROWID   | PRODUCTS         |  33205 |      1 |   66410 |
|* 4 |    INDEX UNIQUE SCAN            | IX_PRODUCTS_PK   |  33205 |  33205 |   33205 |
---------------------------------------------------------------------------------------

Id 2 의 A-Rows = 210K 가 메인 풀 스캔 결과이고, Id 3 / Id 4 의 Starts = 33,205 가 서브쿼리가 33K 회 호출되었다는 뜻입니다. Buffers 137K 의 대부분이 PRODUCTS 의 33K 회 PK lookup 에 소비됩니다.

2) 수정 — `UNNEST` + `LEADING(B@SUB A)` 로 driving 변경

SELECT /*+ LEADING(B@SUB A) USE_NL(A) */
       ORDER_ID, ORDER_DATE, PRODUCT_ID
     , UNIT_PRICE, QUANTITY
  FROM ORDER_ITEMS A
 WHERE ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND ORDER_DATE <  TO_DATE('20120201', 'YYYYMMDD')
   AND PRODUCT_ID IN (SELECT /*+ UNNEST QB_NAME(SUB) */ PRODUCT_ID
                        FROM PRODUCTS B
                       WHERE PRODUCT_NAME = 'CPU D600');

UNNEST 힌트로 unnesting 을 명시 활성화하고, LEADING(B@SUB A) 로 서브쿼리 (SUB block) 를 driving 으로 강제합니다. USE_NL(A) 로 NL 조인까지 강제.

----------------------------------------------------------------------------------------
| Id | Operation                       | Name              | Starts | A-Rows | Buffers |
----------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                |                   |      1 |    719 |     743 |
|  1 |  NESTED LOOPS                   |                   |      1 |    719 |     743 |
|  2 |   NESTED LOOPS                  |                   |      1 |    719 |      27 |
|* 3 |    TABLE ACCESS FULL            | PRODUCTS          |      1 |      1 |      14 |  ← PRODUCTS 서브쿼리가 먼저 수행되어 PRODUCT_ID 공급
|* 4 |    INDEX RANGE SCAN             | IX_ORDER_ITEMS_N2 |      1 |    719 |      13 |  ← 결합 인덱스에 정확히 공급되어 비효율 개선
|  5 |   TABLE ACCESS BY INDEX ROWID   | ORDER_ITEMS       |    719 |    719 |     716 |
----------------------------------------------------------------------------------------

Id 3 의 PRODUCTS 풀 스캔 = 1 row (driving 이 됨).
Id 4 의 IX_ORDER_ITEMS_N2 RANGE SCAN 이 PRODUCT_ID = :sub_result AND ORDER_DATE BETWEEN ... 로 결합 인덱스 access path 가 정확히 동작합니다.
Buffers 137K → 743 으로 약 184 배 절감.

분석

Unnesting vs No Unnesting 비교

항목	NO_UNNEST (원본)	UNNEST (수정)
메인 처리 방식	풀 스캔 (210K rows)	인덱스 RANGE SCAN (719 rows)
서브쿼리 호출	매 메인 row 마다 (33,205 회)	1 회 (driving)
Plan 형태	FILTER + 두 자식	NESTED LOOPS (정상 join)
Buffers	137K	743

옵티마이저는 언제 자동 unnesting 을 보류하는가

대부분의 경우 옵티마이저는 자동으로 unnest 하지만 다음 케이스에서는 보류합니다.

서브쿼리가 복잡 (set operation, hierarchical, recursive 등) 하여 변환 안전성을 보장 못 할 때
서브쿼리 cost 가 충분히 작아 nested 실행이 더 유리하다고 판단할 때
NO_UNNEST 힌트가 명시될 때
옵티마이저 통계 부족 / 부정확

이런 경우 UNNEST 힌트로 강제할 수 있습니다.

`WHERE COL IN (서브쿼리)` 의 unnesting 효과 조건

다음 두 조건이 갖춰질 때 unnesting 이 압도적으로 유리합니다.

메인의 join 컬럼에 인덱스 가 있음.
서브쿼리 결과가 메인을 큰 폭으로 좁혀 주는 access 조건임.

본 시나리오의 IX_ORDER_ITEMS_N2 (PRODUCT_ID, ORDER_DATE) 가 정확히 이 조건을 만족합니다. PRODUCT_ID 가 인덱스 선두 컬럼이라 subquery 결과로 즉시 RANGE SCAN 시작 가능.

Subquery Unnesting 은 옵티마이저가 자동으로 적용하는 변환이지만, driving 결정과 access path 선택은 통계와 hint 에 좌우 됩니다. 의도와 다르게 풀릴 때는 UNNEST + LEADING(@subq_block main) 으로 명시적으로 변환과 순서를 강제하는 것이 안전합니다.

Buffers 비교

단계	원본 (NO_UNNEST)	수정 (UNNEST)
ORDER_ITEMS access	70,833 (풀 스캔)	729 (인덱스 + ROWID)
PRODUCTS access	66,410 (33K 회 PK lookup)	14 (1 회 풀 스캔)
합계	137K	743

정리

Subquery Unnesting 은 IN / EXISTS 서브쿼리를 FROM 절의 일반 join 으로 변환 하는 옵티마이저 변환입니다. 결과집합이 작은 쪽이 driving 이 되어 메인 인덱스를 정확히 lookup 합니다.
효과 조건: 메인의 join 컬럼에 인덱스 + 서브쿼리 결과가 메인을 큰 폭으로 좁혀 주는 access 조건. 이 둘이 갖춰지면 압도적인 성능 개선이 가능합니다.
1:N 정합성 보장: 서브쿼리 컬럼이 unique 가 아니면 옵티마이저가 SORT UNIQUE / HASH UNIQUE 를 자동 추가합니다. PK / UNIQUE constraint 가 명시되어 있으면 이 단계가 생략됩니다.

SQL Tuning, 케이스 스터디