DISTINCT 를 1대N 관계 에서 N 을 Nested Subquery 로 전환해 제거 (2)

1대N JOIN의 N쪽을 EXISTS 서브쿼리로 빼낸 뒤, UNNEST + NL_SJ 힌트로 NESTED LOOPS SEMI를, NO_UNNEST로 FILTER를 강제하는 옵티마이저 컨트롤.

게시 2026/04/30

By 반지현

읽는 시간 15분

1대N 관계의 JOIN 에서 결과 컬럼이 1쪽만 필요해 DISTINCT 가 강제되었다면, N쪽을 EXISTS 서브쿼리로 빼는 것만으로 row 폭증을 막을 수 있고 — 이때 옵티마이저는 UNNEST + NL_SJ 힌트로 NESTED LOOPS (SEMI) 를, NO_UNNEST 힌트로 FILTER 를 강제할 수 있습니다.

핵심 정리

1:N JOIN 을 펼치면 1쪽 한 행이 N쪽 매칭 행 수만큼 복제되어 결과가 부풀고, 1쪽 컬럼만 필요할 때는 DISTINCT 로 다시 압축해야 합니다 — N쪽을 EXISTS 서브쿼리로 빼면 1쪽이 그대로 유지되어 DISTINCT 자체가 사라집니다 (원리는 (1) 참고).
EXISTS 를 만난 옵티마이저는 두 길로 갈 수 있습니다 — UNNEST + NL_SJ 힌트로 NESTED LOOPS (SEMI) 형태의 Semi Join 을 강제하거나, NO_UNNEST 힌트로 FILTER 형태(외부 행마다 서브쿼리를 한 번씩 호출)를 강제하거나. 둘 다 DISTINCT 를 제거한다는 결과는 같지만 실행 형태가 다릅니다.
효과는 N : 1 카디널리티 비율 에 좌우됩니다 — 1쪽 1행당 N쪽 평균 행 수가 작으면(예: 상품 1억 vs 계약 2억 = 1:2) EXISTS 인덱스 반복 호출이 풀스캔보다 비싸 오히려 손해이고, 비율이 클수록(예: 상품 1000 vs 계약 5천만 = 1:50,000) 필터링 효과가 극대화됩니다.

시나리오

상품(1쪽) 과 계약(N쪽) 사이의 1:N JOIN 입니다. 결과 컬럼은 상품 쪽뿐인데 DISTINCT 로 중복을 제거해야 하는 전형적 패턴입니다.

두 테이블: 상품 P (1쪽) / 계약 C (N쪽)
결과 컬럼: P.상품번호, P.상품명, P.상품가격, P.상품분류코드 (1쪽만)
필터: P.상품유형코드 = :PCLSCD, C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12)) (최근 12개월 계약)

1) 원본 — DISTINCT + JOIN

상품과 계약을 펼쳐 JOIN 한 뒤 1쪽 컬럼만 SELECT 하면, 옵티마이저는 NESTED LOOPS 로 두 테이블을 연결한 다음 마지막에 HASH (UNIQUE) 로 중복을 제거합니다.

SELECT DISTINCT P.상품번호, P.상품명, P.상품가격, P.상품분류코드
FROM   상품 P, 계약 C
WHERE  P.상품유형코드 = :PCLSCD
AND    C.상품번호 = P.상품번호
AND    C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12));

---------------------------------------------------------------------------
    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=3 Card=1 Bytes=80)
0     HASH (UNIQUE) (Cost=3 Card=1 Bytes=80)
1       NESTED LOOPS
2         NESTED LOOPS (Cost=2 Card=1 Bytes=80)
3           TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=1 ...)
4             INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=1 Card=1)
3           INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=1 Card=1)
2         TABLE ACCESS (BY INDEX ROWID) OF '계약' (TABLE) (Cost=1 ...)
---------------------------------------------------------------------------

Id 1 의 HASH (UNIQUE) 가 root 위에 붙어 있다는 점이 핵심 비효율 신호입니다 — 두 테이블을 NESTED LOOPS 로 펼친 결과(상품 한 행이 매칭 계약 수만큼 복제) 를 메모리에 모아 중복을 제거합니다. 결과 컬럼이 어차피 상품 쪽뿐인데 계약을 펼쳤다 다시 압축하는 일이 두 번 일어나는 셈입니다.

위 플랜은 (1)편의 박스형 DBMS_XPLAN.DISPLAY_CURSOR 출력과 다른, 트리 형식 EXPLAIN PLAN 출력입니다 — 같은 정보를 표기 방식만 달리 보여주는 것뿐입니다.

2) 수정 A — UNNEST + NL_SJ (SEMI)

계약 을 EXISTS 서브쿼리로 옮기고 /*+ unnest nl_sj */ 힌트로 옵티마이저에게 “이 EXISTS 를 NESTED LOOPS Semi Join 으로 펴라” 를 명시합니다.

SELECT /*+ leading(P) */ P.상품번호, P.상품명, P.상품가격, P.상품분류코드
  FROM 상품 P
 WHERE P.상품유형코드 = :PCLSCD
   AND EXISTS ( SELECT /*+ unnest nl_sj */ 1
                  FROM 계약 C
                 WHERE P.상품번호 = C.상품번호
                   AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12)))
;

---------------------------------------------------------------------------
    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2K Card=1000 Bytes=15K)
0     NESTED LOOPS (SEMI) (Cost=2K Card=1000 Bytes=15K)
1       TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=28 Card=...)
2         INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=3 Card=1000)
1       INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=2 Card=4K Bytes=51K)
---------------------------------------------------------------------------

Id 1 이 NESTED LOOPS (SEMI) 로 바뀌면서 HASH (UNIQUE) 가 사라졌습니다 — Semi Join 은 정의상 “왼쪽 한 행 당 오른쪽 매칭이 하나라도 발견되면 즉시 통과” 이므로 row 복제 자체가 일어나지 않고, 따라서 DISTINCT 도 필요 없습니다. Id 4 의 계약 인덱스 RANGE SCAN 도 매칭 즉시 종료되므로 매칭 후보 4K 행을 끝까지 읽지 않습니다.

3) 수정 B — NO_UNNEST (FILTER)

같은 EXISTS 라도 /*+ no_unnest */ 힌트를 붙이면 옵티마이저는 “서브쿼리를 펴지 말고 메인 쿼리 한 행마다 한 번씩 호출하라” 의 FILTER 형태를 강제합니다.

SELECT P.상품번호, P.상품명, P.상품가격, P.상품분류코드
  FROM 상품 P
 WHERE P.상품유형코드 = :PCLSCD
   AND EXISTS ( SELECT /*+ no_unnest */ 1
                  FROM 계약 C
                 WHERE P.상품번호 = C.상품번호
                   AND C.계약일자 >= TRUNC(ADD_MONTHS(SYSDATE, -12)))
;

---------------------------------------------------------------------------
    SELECT STATEMENT Optimizer=ALL_ROWS (Cost=2K Card=1000 Bytes=15K)
0     FILTER
1       TABLE ACCESS (BY INDEX ROWID) OF '상품' (TABLE) (Cost=28 Card=...)
2         INDEX (RANGE SCAN) OF '상품_X1' (INDEX) (Cost=3 Card=1000)
1       INDEX (RANGE SCAN) OF '계약_X2' (INDEX) (Cost=2 Card=4K Bytes=51K)
---------------------------------------------------------------------------

Id 1 이 FILTER 로 바뀌었습니다 — 상품 한 행이 나올 때마다 계약 인덱스를 한 번 호출해 존재 여부만 확인합니다. row 복제도, HASH (UNIQUE) 도 없습니다. (1)편이 다룬 형태가 바로 이 FILTER 입니다 — 외부 행마다 종속 서브쿼리를 한 번씩 호출하는 정통 Nested Subquery 실행 모델입니다.

분석

두 변형의 차이 — SEMI JOIN vs FILTER

두 변형 모두 1쪽이 복제되지 않아 DISTINCT 를 제거한다는 결과는 같지만, 옵티마이저가 N쪽 접근을 어떻게 풀어내느냐가 다릅니다.

NESTED LOOPS (SEMI): 옵티마이저가 EXISTS 를 다시 조인 형태로 풀어내되 “왼쪽 한 행당 오른쪽 매칭 첫 발견 즉시 종료” 시맨틱을 유지합니다. 옵티마이저의 비용 모델 안에서 hash semi join, sort merge semi join 등 다른 join method 로도 자유롭게 옮겨갈 수 있어, 통계가 충분하면 자동으로 더 좋은 plan 을 선택할 여지가 큽니다.
FILTER: SEMI JOIN 변환을 막고 외부 행마다 서브쿼리를 한 번씩 호출하는 Nested Subquery 실행 모델을 강제합니다. 외부 결과가 매우 적고 서브쿼리 인덱스가 잘 잡혀 있으면 단순하고 효율적이지만, 옵티마이저가 다른 join method 로 갈 길을 막아버립니다.

옵티마이저는 보통 SEMI JOIN 변환을 선호합니다 — UNNEST + NL_SJ 는 이를 명시적으로 보장하는 안전한 디폴트이고, NO_UNNEST 는 FILTER 형태가 실측상 더 빠른 케이스에서만 의도적으로 선택하는 도구입니다.

적용 가능 조건 — N : 1 카디널리티 비율

이 패턴은 N이 1 대비 충분히 클 때만 효과가 큽니다. 두 변형 모두 본질적으로 “1쪽 한 행마다 N쪽 인덱스를 한 번씩 타는” 구조이므로, 1쪽 행당 인덱스 호출 비용 × 1쪽 행 수 가 원본 JOIN 의 풀스캔/HASH JOIN 비용보다 작아야 의미가 있습니다.

비율	1쪽 (상품)	N쪽 (계약)	1쪽 1행당 평균 N행	EXISTS 변환 효과
비효율 케이스	1억	2억	약 2	❌ 인덱스 반복 호출이 풀스캔보다 비쌈 — 오히려 손해
효율 케이스	1,000	5천만	약 50,000	✅ 필터링 효과 극대화 — 큰 개선

EXISTS 변환 전에 두 테이블의 카디널리티 비율 과 결과 카디널리티 추정치 를 반드시 먼저 확인하세요 — 1쪽 1행당 평균 N행이 한 자릿수 수준이면 다른 튜닝 전략(예: 인덱스 추가, JOIN 순서 강제) 을 검토하는 게 낫습니다.

Cost 추정치의 역설

위 두 수정 플랜의 Cost=2K 는 원본의 Cost=3 보다 표면상 더 비싸 보입니다. 이는 통계 정보의 한계 — 옵티마이저가 :PCLSCD 바인드 변수의 선택도를 잘못 추정하거나 N쪽 인덱스 RANGE SCAN 의 실제 종료 시점(SEMI 의 단축 효과) 을 비용 모델에 충분히 반영하지 못하는 경우 흔히 나타납니다. 실제 실행에서는 row 복제와 HASH (UNIQUE) 가 사라지면서 일관되게 빨라지므로, 추정 Cost 보다 실측 Buffers / 실행 시간으로 검증 해야 합니다.

비교 요약

항목	원본 (DISTINCT + JOIN)	수정 A (UNNEST + NL_SJ)	수정 B (NO_UNNEST)
실행 형태	NESTED LOOPS → HASH UNIQUE	NESTED LOOPS (SEMI)	FILTER
`DISTINCT` 단계	있음	없음	없음
1쪽 행 복제	있음 (N쪽 매칭 수만큼)	없음	없음
N쪽 접근	인덱스 RANGE SCAN (전체 매칭)	인덱스 RANGE SCAN (첫 매칭 즉시 종료)	인덱스 RANGE SCAN (첫 매칭 즉시 종료)
적용 시점	(제약 없음)	디폴트 권장	FILTER 형태가 실측상 더 빠를 때

EXISTS 변환은 단순 SQL 리팩터링이 아니라 옵티마이저에게 “이 테이블은 결과가 아니라 체크용이다” 라는 의도를 명시하는 작업이며, UNNEST + NL_SJ 와 NO_UNNEST 는 그 위에서 “체크 방식” 을 한 단계 더 지정하는 미세 조정입니다.

정리

결과 컬럼이 1쪽만 필요한데 N쪽 때문에 DISTINCT 가 들어갔다면, N쪽을 EXISTS 서브쿼리로 분리해 row 복제 자체를 막습니다.
EXISTS 의 실행 형태는 두 가지 — UNNEST + NL_SJ 로 NESTED LOOPS (SEMI), NO_UNNEST 로 FILTER. 디폴트는 SEMI JOIN 형태가 안전하며, FILTER 는 실측상 더 빠를 때만 의도적으로 강제합니다.
적용 전 N : 1 카디널리티 비율을 반드시 확인합니다 — 1쪽 1행당 평균 N행이 한 자릿수면 인덱스 반복으로 오히려 비효율, 수만 단위 이상이어야 효과가 살아납니다.
옵티마이저의 추정 Cost 가 역전돼 보일 수 있으니 (통계/추정 한계), 실제 효과는 Buffers·실행시간 같은 실측치로 확인합니다.

SQL Tuning, 케이스 스터디