NO_UNNEST 힌트 in Oracle

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	서브쿼리의 푸시다운을 방지

7. 결론

• NO_UNNEST 힌트는 옵티마이저가 서브쿼리를 JOIN으로 변환하지 않고 원래 상태로 유지하도록 강제하는 기능.
• IN, EXISTS, SCALAR 서브쿼리가 자동 변환될 때, 성능 최적화를 위해 사용.
• 특정 경우(뷰 내부 서브쿼리 유지, 과도한 JOIN 변환 방지 등)에서 성능 개선 가능.

🚀 옵티마이저가 서브쿼리를 자동 변환할 때 성능이 저하된다면, NO_UNNEST를 활용하여 튜닝할 수 있습니다!