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

1대N 관계의 JOIN 에서 결과 컬럼이 1쪽만 필요한데 N쪽 때문에 DISTINCT 가 들어갔다면, N쪽을 EXISTS 서브쿼리로 분리해 옵티마이저에게 “체크용” 임을 알려주는 것만으로 row 폭증과 DISTINCT 비용을 동시에 제거할 수 있습니다.

핵심 정리

1. 1:N JOIN 을 펼치면 1쪽 한 행이 N쪽의 매칭 행 수만큼 복제되어 결과가 부풀고, 1쪽 컬럼만 필요할 때는 DISTINCT 로 다시 압축해야 합니다 — N쪽을 EXISTS 서브쿼리로 빼면 1쪽이 그대로 유지되어 DISTINCT 자체가 불필요해집니다.
2. 옵티마이저는 EXISTS 를 자동으로 SEMI JOIN 으로 다시 펴는 Subquery Unnesting 을 시도하므로, 의도한 FILTER 형태를 강제하려면 NO_UNNEST 힌트가 필요합니다.
3. 단, N의 카디널리티가 1 대비 충분히 커야 (대략 1행당 평균 수십 행 이상) EXISTS 필터링 효율이 살아납니다 — 비율이 1:2 정도로 낮으면 인덱스 반복 호출이 더 비싸 오히려 비효율입니다.

---

시나리오

같은 두 테이블·같은 조인 키·같은 필터 조건. 단 하나만 다릅니다 — N쪽 (ORDERS) 을 JOIN 으로 펼치고 DISTINCT 로 정리하느냐, EXISTS 서브쿼리로 존재 체크만 하느냐.

• 두 테이블: EMPLOYEE A (1쪽) / ORDERS B (N쪽)
• 결과 컬럼: A.EMPLOYEE_ID, A.LAST_NAME (1쪽만)
• 필터: A.JOB_ID = 'J04', B.ORDER_DATE 5개월 범위, B.ORDER_STATUS = 10

---

1) 원본 — DISTINCT + JOIN

A.EMPLOYEE_ID = B.EMPLOYEE_ID 로 두 테이블을 JOIN 한 뒤 1쪽 컬럼만 SELECT 하면, 옵티마이저는 두 테이블을 모두 펼쳐 매칭 행을 만든 다음 마지막에 HASH UNIQUE 로 중복을 제거합니다.

SELECT DISTINCT A.EMPLOYEE_ID, A.LAST_NAME
  FROM EMPLOYEE A, ORDERS B
 WHERE A.EMPLOYEE_ID = B.EMPLOYEE_ID
   AND A.JOB_ID = 'J04'
   AND B.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
   AND B.ORDER_DATE < TO_DATE('20120601', 'YYYYMMDD')
   AND B.ORDER_STATUS = 10;

---------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | A-Rows | Buffers | Used-Mem  |
---------------------------------------------------------------------------------
|   1 | HASH UNIQUE         |           |      1 |     28 |   19629 |           |
|   2 |  HASH JOIN          |           |      1 |    886 |   19629 | 1024K (0) |
|*  3 |   TABLE ACCESS FULL | EMPLOYEES |      1 |     28 |       9 |           |
|*  4 |   TABLE ACCESS FULL | ORDERS    |      1 |  20697 |   19629 |           |
---------------------------------------------------------------------------------

Id 4 의 TABLE ACCESS FULL ORDERS 가 20,697 행을 풀스캔하며 19,620 buffers 를 소비 하고, Id 2 의 HASH JOIN 결과가 886 행으로 부풀어 Id 1 의 HASH UNIQUE 가 다시 28 행으로 압축합니다 — 결과는 28건인데 중간에 886 행을 만들었다 버리는 셈입니다.

---

2) 수정 — EXISTS + NO_UNNEST

ORDERS 를 EXISTS 서브쿼리로 옮겨 “조건을 만족하는 주문이 하나라도 있는 직원” 만 골라내고, NO_UNNEST 힌트로 옵티마이저가 SEMI JOIN 으로 다시 펴는 것을 막습니다.

SELECT A.EMPLOYEE_ID, A.LAST_NAME
  FROM EMPLOYEE A
 WHERE EXISTS (SELECT /*+ NO_UNNEST */ 1
                 FROM ORDERS B
                WHERE B.ORDER_DATE >= TO_DATE('20120101', 'YYYYMMDD')
                  AND B.ORDER_DATE < TO_DATE('20120601', 'YYYYMMDD')
                  AND B.ORDER_STATUS = 10)
   AND A.JOB_ID = 'J04';

---------------------------------------------------------------------------------
| Id  | Operation                      | Name      | Starts | A-Rows | Buffers |
---------------------------------------------------------------------------------
|*  1 | FILTER                         |           |      1 |     28 |     331 |
|*  2 |  TABLE ACCESS FULL             |           |      1 |     28 |      10 |
|*  3 |  TABLE ACCESS BY INDEX ROWID   | EMPLOYEES |     28 |     28 |     321 |
|*  4 |   INDEX RANGE SCAN             | ORDERS    |     28 |    244 |      85 |
---------------------------------------------------------------------------------

Id 1 의 FILTER 가 root 가 되어 1쪽 (EMPLOYEE) 28 행만 그대로 유지하고, N쪽 (ORDERS) 은 Id 4 의 INDEX RANGE SCAN 으로 존재 여부만 체크합니다 — 총 Buffers 19,629 → 331 (약 60배 개선), 그리고 DISTINCT 자체가 사라졌습니다.

---

분석

왜 원본은 DISTINCT 가 강제되는가

1:N JOIN 의 본질 — 1쪽 한 행이 N쪽의 매칭 행 수만큼 복제됩니다. 결과 컬럼이 1쪽 (EMPLOYEE_ID, LAST_NAME) 뿐이어도 SQL 의미상으로는 “조인 결과의 모든 행” 이 후보가 되므로 N쪽 행 수만큼 동일한 1쪽 행이 중복 출력됩니다. 옵티마이저는 “어차피 결과 컬럼은 1쪽만 필요하니 N쪽은 존재 체크만 해라” 를 자동으로 추론하지 않습니다 — 이 의도는 SQL 작성자가 명시해야 합니다.

EXISTS + NO_UNNEST 가 하는 일

• EXISTS: N쪽 ORDERS 가 결과 행을 만들지 않고 boolean 체크만 수행합니다 — 1쪽 row 수가 그대로 28 로 유지되어 DISTINCT 가 불필요해집니다.
• NO_UNNEST: 옵티마이저는 EXISTS 를 다시 SEMI JOIN 으로 변환 (Subquery Unnesting) 하려고 시도합니다. 이 힌트는 그 변환을 막아 FILTER 형태를 강제 — 의도한 실행 형태를 보장합니다.

적용 가능 조건 — N의 카디널리티

이 패턴은 N이 1 대비 충분히 클 때만 효과가 큽니다.

비율	EMPLOYEE	ORDERS	EXISTS 변환 효과
본 케이스	28	20,697 (≈ 739 행/직원)	✅ 매우 유리 (60배 개선)
비효율 케이스	1억	2억 (≈ 2 행/직원)	❌ 오히려 손해

EXISTS 로 바꿔도 결국 1쪽 각 행마다 N쪽 인덱스를 한 번씩 타야 하므로, 1쪽 행당 인덱스 호출 비용 × 1쪽 행 수 가 원본 JOIN 의 풀스캔 비용보다 작아야 의미가 있습니다. 1행당 평균 N행이 작으면 인덱스 반복 호출이 더 비싸집니다.

EXISTS 변환은 단순 SQL 리팩터링이 아니라 옵티마이저에게 “이 테이블은 결과가 아니라 체크용이다” 라는 의도를 명시하는 작업입니다. 같은 결과를 얻는 두 SQL 이라도 옵티마이저가 받는 정보는 완전히 다릅니다.

비교 요약

항목	원본 (DISTINCT + JOIN)	수정 (EXISTS + NO_UNNEST)
실행 형태	HASH JOIN → HASH UNIQUE	FILTER + INDEX RANGE SCAN
ORDERS 접근	TABLE ACCESS FULL (20,697 행)	INDEX RANGE SCAN (244 행)
중간 행 수	886 (DISTINCT 압축 전)	28 (1쪽만 그대로)
총 Buffers	19,629	331
적용 조건	(제약 없음)	N : 1 카디널리티 비율이 충분히 클 때

---

정리

1. 결과 컬럼이 1쪽만 필요한데 N쪽 때문에 DISTINCT 가 들어갔다면, N쪽을 EXISTS 서브쿼리로 분리합니다.
2. 의도한 FILTER 형태를 보장하려면 NO_UNNEST 힌트로 옵티마이저의 Subquery Unnesting 을 차단합니다.
3. 적용 전 N : 1 카디널리티 비율을 확인합니다 — 1행당 평균 수십 행 이상이어야 효과가 살아남고, 그렇지 않으면 인덱스 반복으로 오히려 비효율입니다.