NO_UNNEST 힌트 in Oracle

Table of Contents

1. NO_UNNEST 힌트란?

NO_UNNEST 힌트는 서브쿼리(Subquery)의 변환(UNNESTING) 최적화를 방지하는 데 사용됩니다. NEST의 사전적 의미는 무언가를 “포개다, 겹치다”의 의미로 중첩을 의미합니다. 서브쿼리에 unnest 힌트를 사용하게 되면, 서브쿼리를 펴서 메인쿼리와 조인으로 풀어내겠다는 의미로 보시면 됩니다. no_unnest는 중첩을 풀지 않고 필터조건으로 풀어내는 것으로 보면 됩니다.

기본적으로 Oracle 옵티마이저는 가능하면 서브쿼리를 메인 쿼리에 병합(UNNEST)하여 성능을 최적화하려 합니다.
하지만 특정 상황에서는 서브쿼리를 유지하는 것이 더 유리할 수 있으며, 이때 NO_UNNEST 힌트를 사용합니다.

※ /*+ unnest */ : 서브쿼리를 unnesting 함으로써 JOIN 방식으로 최적화하도록 유도
/*+ no_unnest */ : 서브쿼리를 둔 상태에서 필터 방식으로 최적화하도록 유도

2. UNNESTING (서브쿼리 변환)이란?

Oracle은 IN, EXISTS, SCALAR 서브쿼리 등을 JOIN 형태로 변환하여 성능을 개선할 수 있음.
이를 “UNNESTING (언네스팅, 서브쿼리 해제)” 이라고 하며, 옵티마이저가 자동 수행.
하지만 경우에 따라 서브쿼리 최적화가 비효율적일 수 있음.
- 옵티마이저가 불필요한 JOIN 변환을 수행하는 경우
- 특정 실행 계획에서 서브쿼리를 유지하는 것이 성능이 더 나은 경우

✅ NO_UNNEST 힌트를 사용하면 옵티마이저가 서브쿼리를 변환하지 않고 원래 형태로 유지함.

3. NO_UNNEST 사용 예제

📌 1) 기본적인 UNNESTING 최적화 예제

아래와 같은 서브쿼리는 Oracle에서 자동으로 JOIN으로 변환될 수 있음.

SELECT e.employee_id, e.first_name
FROM employees e
WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location_id = 1700);

기본적으로 IN 서브쿼리는 옵티마이저에 의해 SEMI JOIN으로 변환될 가능성이 큼.

📌 2) NO_UNNEST 적용 (서브쿼리 유지)

NO_UNNEST 힌트를 사용하면 서브쿼리를 유지하도록 강제할 수 있음.

SELECT /*+ NO_UNNEST */ e.employee_id, e.first_name
FROM employees e
WHERE e.department_id IN (SELECT d.department_id FROM departments d WHERE d.location_id = 1700);

옵티마이저가 서브쿼리를 풀지 않고 원래 상태로 유지.
특정 실행 계획에서 서브쿼리를 유지하는 것이 더 나은 경우 유용.

4. NO_UNNEST 사용 사례

상황	기본 동작 (Unnesting O)	NO_UNNEST 적용 시 (Unnesting X)
IN 서브쿼리	서브쿼리를 `SEMI JOIN`으로 변환	서브쿼리를 그대로 유지
EXISTS 서브쿼리	`ANTI JOIN` 등으로 변환	EXISTS 형태 유지
SCALAR 서브쿼리	`OUTER APPLY (LATERAL)` 등으로 변환	서브쿼리 그대로 유지
뷰(View) 내부 서브쿼리	뷰 내부 서브쿼리를 조인으로 변환	서브쿼리를 분리된 상태로 유지

5. UNNESTING이 비효율적인 경우

✅ JOIN 변환이 과도하게 발생하여 성능 저하되는 경우

옵티마이저가 서브쿼리를 자동 변환했지만, 실행 계획이 비효율적일 때.

✅ SEMI JOIN / ANTI JOIN보다 서브쿼리가 유리한 경우

IN 또는 EXISTS 서브쿼리를 유지하는 것이 더 효율적일 때.

✅ 뷰(View) 내부에서 서브쿼리 유지가 필요한 경우

뷰 내부의 서브쿼리가 조인으로 변환되면 예상과 다른 결과가 나올 때.

6. NO_UNNEST와 관련된 힌트

힌트	설명
NO_UNNEST	서브쿼리의 UNNESTING을 방지
UNNEST	서브쿼리를 반드시 UNNESTING 수행
NO_MERGE	뷰 병합을 방지 (뷰 내부 서브쿼리를 유지하는 데 유용)
PUSH_SUBQ	서브쿼리를 먼저 실행하도록 강제
NO_PUSH_SUBQ	서브쿼리의 푸시다운을 방지