포스트

EXISTS 로 DISTINCT 구문을 대체 (Filter, Semi Join)

1:N JOIN 의 N 쪽을 EXISTS 로 빼낸 뒤, NO_UNNEST 로 FILTER, UNNEST + NL_SJ 로 SEMI JOIN 을 각각 강제하는 두 길을 같은 데이터(T_CUST × T_ORDER3) 에서 trace 로 비교합니다.

게시
By 반지현
읽는 시간 15분
EXISTS 로 DISTINCT 구문을 대체 (Filter, Semi Join)

1:N JOIN 을 EXISTS 서브쿼리로 빼면 결과 row 폭증이 사라지고 — 옵티마이저는 두 갈래로 처리할 수 있습니다.
NO_UNNEST → FILTER (외부 row 마다 서브쿼리 한 번씩, subquery cache + ROWNUM=1 STOP) / UNNEST + NL_SJ → NESTED LOOPS (SEMI).
둘 중 어느 길이 빠른지는 데이터 분포에 달려 있습니다.

핵심 정리

  1. EXISTS 는 매칭 row 1건만 발견하면 즉시 stop 합니다 — 불필요한 조인이 줄어 성능이 오르고, 결과가 1쪽 row 단위로 보장되어 DISTINCT 가 자연스럽게 사라집니다.
  2. 옵티마이저는 EXISTS 를 두 갈래로 처리합니다 — UNNEST + NL_SJ 힌트로 NESTED LOOPS (SEMI) 를 강제하거나, NO_UNNEST 힌트로 FILTER (외부 row 마다 서브쿼리를 호출, ROWNUM=1 에서 stop) 를 강제하거나. 어느 쪽이 유리한지는 데이터 분포에 따라 다릅니다.
  3. UNNEST 결과 실행계획에 SORT UNIQUE / HASH UNIQUE 가 끼어 있다면 — 옵티마이저가 안에서 다시 unique 화 비용을 들이고 있다는 뜻이라 오히려 비효율일 수 있습니다. 그럴 때는 /*+ NO_UNNEST */ 로 FILTER 길로 빼서 trace 를 비교해 봅니다.

시나리오

T_CUST × T_CUST_GRAD (outer) × T_ORDER3 — 1:N 관계의 전형적 마케팅 추출 쿼리입니다. 결과 컬럼은 1쪽 (T_CUST, T_CUST_GRAD) 만 필요한데 N쪽 (T_ORDER3) 과 join 하느라 row 가 부풀어, DISTINCT 로 다시 압축하는 패턴입니다.

  • 1쪽: T_CUST C + outer T_CUST_GRAD G
  • N쪽: T_ORDER3 O (최근 7일 주문)
  • 인덱스: IX_T_CUST_BRTHDY (BRTH_DY), IX_T3_ORDER_CUSTID_DY (CUST_ID, ORDER_DY), PK_T_CUST_GRAD (CUST_ID)
  • 결과 컬럼: C.CUST_NM, C.CUST_ID, G.CUST_GRAD, C.BRTH_DY, AGE (모두 1쪽)

1) 원본 — DISTINCT + JOIN

3 테이블을 모두 펼쳐 join 한 뒤 마지막에 DISTINCT (SORT) 로 중복을 제거합니다. T_ORDER3 의 매칭 row 수만큼 1쪽이 복제되어 19,248 row 까지 부푸르고, 이를 다시 6,659 로 압축합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13

SELECT DISTINCT C.CUST_NM, C.CUST_ID, G.CUST_GRAD, C.BRTH_DY,
       TRUNC((TO_DATE('20230331', 'YYYYMMDD') - TO_DATE(C.BRTH_DY, 'YYYYMMDD')) / 365) AS AGE
  FROM T_CUST      C,
       T_CUST_GRAD G,
       T_ORDER3    O
 WHERE C.BRTH_DY >  TO_CHAR(TO_DATE('20230301', 'YYYYMMDD') - (21 * 365), 'YYYYMMDD')
   AND C.BRTH_DY <= TO_CHAR(TO_DATE('20230301', 'YYYYMMDD') - (20 * 365), 'YYYYMMDD')
   AND C.CUST_ID = G.CUST_ID(+)
   AND C.CUST_ID = O.CUST_ID
   AND O.ORDER_DY >= TO_CHAR((TO_DATE('20230301', 'YYYYMMDD') - 7), 'YYYYMMDD')
   AND O.ORDER_DY <= TO_CHAR( TO_DATE('20230301', 'YYYYMMDD'), 'YYYYMMDD')
   AND O.ORDER_TY IS NOT NULL
 ORDER BY C.CUST_NM;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

--------------------------------------------------------------------------------------------------
| Id | Operation                 | Name               |  Rows | Elapsed Time  | CR Gets | Starts |
--------------------------------------------------------------------------------------------------
|  1 | DISTINCT (SORT)           |                    |  6659 | 00:00:00.0499 |       0 |      1 |
|  2 |  INDEX JOIN               |                    | 19248 | 00:00:00.0041 |       0 |      1 |
|  3 |   INDEX JOIN (LEFT OUTER) |                    |  7016 | 00:00:00.0001 |       0 |      1 |
|  4 |    ORDER BY (SORT)        |                    |  7016 | 00:00:00.0024 |       0 |      1 |
|  5 |     TABLE ACCESS (FULL)   | T_CUST             |  7016 | 00:00:00.0115 |    5858 |      1 |
|  6 |    TABLE ACCESS (ROWID)   | T_CUST_GRAD        |  7016 | 00:00:00.0014 |    7016 |   7016 |
|  7 |     INDEX (UNIQUE SCAN)   | PK_T_CUST_GRAD     |  7016 | 00:00:00.0055 |   14032 |   7016 |
|  8 |   TABLE ACCESS (ROWID)    | T_ORDER3           | 19248 | 00:00:00.7969 |   1042K |   7016 |
|  9 |    INDEX (RANGE SCAN)     | IX_T_ORDER3_CUSTID | 1042K | 00:00:00.0392 |   13680 |   7016 |
--------------------------------------------------------------------------------------------------
Predicate Information:
----------------------
  5 - filter: ("C"."BRTH_DY" <= '20030306') AND ("C"."BRTH_DY" > '20020306')
  7 - access: ("G"."CUST_ID" = "C"."CUST_ID")
  8 - filter: ("O"."ORDER_DY" >= '20230222') AND ("O"."ORDER_DY" <= '20230301') AND ("O"."ORDER_TY" IS NOT NULL)
  9 - access: ("O"."CUST_ID" = "C"."CUST_ID")

Id 1 의 DISTINCT (SORT) 가 부풀어난 19,248 row 를 다시 6,659 로 압축하는 비용 입니다. 그리고 Id 9 의 IX_T_ORDER3_CUSTID Range Scan 이 CR Gets 1,042K — T_CUST 의 7,016 row 각각에 대해 매칭 주문 인덱스를 풀스캔처럼 훑은 결과입니다.

2) 수정 (1) — NO_UNNEST → FILTER

T_ORDER3 를 EXISTS 서브쿼리로 빼고 /*+ NO_UNNEST */ 힌트로 FILTER 처리를 강제했습니다. 옵티마이저는 외부 row 마다 서브쿼리를 호출하되 — CACHE 로 동일 키 결과를 재사용하고 COUNT (STOP NODE) 로 첫 매칭 (ROWNUM=1) 에서 stop 합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT C.CUST_NM, C.CUST_ID, G.CUST_GRAD, C.BRTH_DY,
       TRUNC((TO_DATE('20230331', 'YYYYMMDD') - TO_DATE(C.BRTH_DY, 'YYYYMMDD')) / 365) AS AGE
  FROM T_CUST      C,
       T_CUST_GRAD G
 WHERE C.BRTH_DY >  TO_CHAR(TO_DATE('20230301', 'YYYYMMDD') - (21 * 365), 'YYYYMMDD')
   AND C.BRTH_DY <= TO_CHAR(TO_DATE('20230301', 'YYYYMMDD') - (20 * 365), 'YYYYMMDD')
   AND C.CUST_ID = G.CUST_ID(+)
   AND EXISTS ( SELECT /*+ NO_UNNEST */ 1
                  FROM T_ORDER3 O
                 WHERE O.CUST_ID = C.CUST_ID
                   AND O.ORDER_DY >= TO_CHAR((TO_DATE('20230301', 'YYYYMMDD') - 7), 'YYYYMMDD')
                   AND O.ORDER_DY <= TO_CHAR( TO_DATE('20230301', 'YYYYMMDD'), 'YYYYMMDD')
                   AND O.ORDER_TY IS NOT NULL
              )
 ORDER BY C.CUST_NM;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

----------------------------------------------------------------------------------------------------------------
| Id | Operation                            | Name                  |  Rows | Elapsed Time  | CR Gets | Starts |
----------------------------------------------------------------------------------------------------------------
|  1 | ORDER BY (SORT)                      |                       |  6659 | 00:00:00.0190 |       0 |      1 |
|  2 |  HASH JOIN (LEFT OUTER)              |                       |  6659 | 00:00:00.0071 |       0 |      1 |
|  3 |   TABLE ACCESS (ROWID)               | T_CUST                |  6659 | 00:00:01.3127 |   32201 |      1 |  ← Operator 에 FILTER 표시는 없지만 EXISTS 가 FILTER 로 처리됩니다
|  4 |    INDEX (RANGE SCAN)                | IX_T_CUST_BRTHDY      |  7016 | 00:00:00.0001 |      24 |      1 |
|  5 |    CACHE                             |                       |  7016 | 00:00:00.0067 |       0 |   7016 |
|  6 |     COUNT (STOP NODE) (STOP LIMIT 2) |                       |  6659 | 00:00:00.0008 |       0 |   7016 |
|  7 |      TABLE ACCESS (ROWID)            | T_ORDER3              |  6659 | 00:00:00.0245 |    6659 |   7016 |
|  8 |       INDEX (RANGE SCAN)             | IX_T3_ORDER_CUSTID_DY | 19166 | 00:00:01.2254 |   21319 |   7016 |
|  9 |   TABLE ACCESS (FULL)                | T_CUST_GRAD           | 70000 | 00:00:00.0068 |    2199 |      1 |
----------------------------------------------------------------------------------------------------------------
Predicate Information:
----------------------
  3 - filter: EXISTS ( SELECT /*+ NO_UNNEST */ 1 FROM T_ORDER3 O
                       WHERE O.CUST_ID = C.CUST_ID
                         AND O.ORDER_DY >= '20230222'
                         AND O.ORDER_DY <= '20230301'
                         AND O.ORDER_TY IS NOT NULL )    ← Id 3 자체에 EXISTS 가 FILTER 로 묶여 있습니다
  4 - access: ("C"."BRTH_DY" > '20020306') AND ("C"."BRTH_DY" <= '20030306')
  6 - filter: (ROWNUM = 1)
  7 - filter: ("O"."ORDER_TY" IS NOT NULL)
  8 - access: ("O"."CUST_ID" = :B1) AND ("O"."ORDER_DY" >= '20230222') AND ("O"."ORDER_DY" <= '20230301')

Id 3 의 TABLE ACCESS (ROWID) T_CUST 자체에 EXISTS 가 FILTER 로 매달려 있고, Id 5 CACHE + Id 6 COUNT (STOP NODE) 가 그 동작을 보여줍니다 — 외부 7,016 row 마다 서브쿼리를 호출하되, 동일 CUST_ID 는 캐시로 재사용하고, 첫 매칭 발견 즉시 stop. 결과적으로 T_ORDER3 인덱스 접근(Id 8) 의 CR Gets 가 21,319 — 원본의 13,680 + α 수준으로 줄었고, 무엇보다 Id 9 (T_ORDER3 풀 ROWID 접근) 의 1,042K 비용이 사라졌습니다.

3) 수정 (2) — UNNEST + NL_SJ → SEMI JOIN

같은 EXISTS 를 /*+ UNNEST NL_SJ */ 힌트로 NESTED LOOPS Semi Join 으로 강제했습니다. plan tree 상에 INDEX JOIN (SEMI) 가 명시적으로 보이고, 외부 row 마다 inner index 를 probe 하되 첫 매칭에서 stop 한다는 점은 FILTER 와 동일합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

SELECT C.CUST_NM, C.CUST_ID, G.CUST_GRAD, C.BRTH_DY,
       TRUNC((TO_DATE('20230331', 'YYYYMMDD') - TO_DATE(C.BRTH_DY, 'YYYYMMDD')) / 365) AS AGE
  FROM T_CUST      C,
       T_CUST_GRAD G
 WHERE C.BRTH_DY >  TO_CHAR(TO_DATE('20230301', 'YYYYMMDD') - (21 * 365), 'YYYYMMDD')
   AND C.BRTH_DY <= TO_CHAR(TO_DATE('20230301', 'YYYYMMDD') - (20 * 365), 'YYYYMMDD')
   AND C.CUST_ID = G.CUST_ID(+)
   AND EXISTS ( SELECT /*+ UNNEST NL_SJ */ 1
                  FROM T_ORDER3 O
                 WHERE O.CUST_ID = C.CUST_ID
                   AND O.ORDER_DY >= TO_CHAR((TO_DATE('20230301', 'YYYYMMDD') - 7), 'YYYYMMDD')
                   AND O.ORDER_DY <= TO_CHAR( TO_DATE('20230301', 'YYYYMMDD'), 'YYYYMMDD')
                   AND O.ORDER_TY IS NOT NULL
              )
 ORDER BY C.CUST_NM;

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17

--------------------------------------------------------------------------------------------------
| Id | Operation              | Name                  |  Rows | Elapsed Time  | CR Gets | Starts |
--------------------------------------------------------------------------------------------------
|  1 | HASH JOIN (LEFT OUTER) |                       |  6659 | 00:00:00.0254 |       0 |      1 |
|  2 |  INDEX JOIN (SEMI)     |                       |  6659 | 00:00:00.0003 |       0 |      1 |  ← EXISTS 가 SEMI JOIN 으로 처리됩니다
|  3 |   TABLE ACCESS (ROWID) | T_CUST                |  7016 | 00:00:00.0094 |    4223 |      1 |
|  4 |    INDEX (RANGE SCAN)  | IX_T_CUST_BRTHDY      |  7016 | 00:00:00.0001 |      24 |      1 |
|  5 |   TABLE ACCESS (ROWID) | T_ORDER3              | 19248 | 00:00:00.0289 |   19248 |   7016 |
|  6 |    INDEX (RANGE SCAN)  | IX_T3_ORDER_CUSTID_DY | 19248 | 00:00:00.0305 |   11637 |   7016 |
|  7 |  TABLE ACCESS (FULL)   | T_CUST_GRAD           | 70000 | 00:00:00.0071 |    2199 |      1 |
--------------------------------------------------------------------------------------------------
Predicate Information:
----------------------
  1 - access: ("C"."CUST_ID" = "G"."CUST_ID")
  4 - access: ("C"."BRTH_DY" > '20020306') AND ("C"."BRTH_DY" <= '20030306')
  5 - filter: ("O"."ORDER_TY" IS NOT NULL)
  6 - access: ("C"."CUST_ID" = "O"."CUST_ID") AND ("O"."ORDER_DY" >= '20230222') AND ("O"."ORDER_DY" <= '20230301')

Id 2 의 INDEX JOIN (SEMI) 가 EXISTS 의 의미를 plan tree 에 명시적으로 표현 합니다. T_CUST (Id 3, CR Gets 4,223) 의 각 row 로 IX_T3_ORDER_CUSTID_DY (Id 6, CR Gets 11,637) 를 probe — FILTER 보다 plan 이 단순하고, inner 인덱스 접근 비용도 11,637 로 더 적습니다. 다만 subquery cache 가 없어서 동일 CUST_ID 가 자주 반복되는 데이터에서는 FILTER 가 더 유리할 수 있습니다.

분석

EXISTS 가 DISTINCT 를 제거하는 메커니즘

1:N JOIN 을 펼치면 1쪽 row 가 N 쪽 매칭 수만큼 복제되어 결과가 부풀고, 1쪽 컬럼만 필요할 때는 DISTINCT 로 다시 압축해야 합니다. EXISTS 서브쿼리로 N 쪽을 빼면 — semi-join 의 의미상 “매칭이 한 건이라도 있는지” 만 체크 하므로 1쪽 row 가 그대로 유지됩니다. 결과적으로 DISTINCT (SORT) 단계 자체가 사라집니다.

FILTER vs SEMI JOIN — 어느 쪽이 빠른가

처리 방식plan 형태외부 row 처리매칭 완료 시동일 키 재사용
FILTER (NO_UNNEST)Id 3 의 access 에 EXISTS 가 매달림 + CACHE + COUNT (STOP NODE)모든 외부 rowROWNUM=1 STOPsubquery cache O
NESTED LOOPS (SEMI) (UNNEST + NL_SJ)Id 2 의 INDEX JOIN (SEMI) 명시모든 외부 row첫 매칭 stopsubquery cache X

둘 다 외부 row 마다 inner 를 한 번씩 본다는 점은 같지만 — FILTER 는 동일 외부 row 키에 대해 서브쿼리 결과를 캐시 해 재사용 (Oracle subquery cache). 외부 row 가 동일 키로 자주 반복되면 FILTER 가 유리합니다. SEMI JOIN 은 그런 cache 가 없는 대신 plan tree 가 단순해서 inner 인덱스 효율이 좋으면 더 빠릅니다.

이번 케이스의 trace 를 보면 — FILTER (수정 1) 의 inner 인덱스 CR Gets 21,319, SEMI JOIN (수정 2) 의 inner 인덱스 CR Gets 11,637. 외부 7,016 row 의 CUST_ID 분포가 비교적 고유해 cache hit 효과가 크지 않았고, plan tree 가 단순한 SEMI JOIN 쪽이 약간 더 적은 buffer 로 끝났습니다.

언제 FILTER, 언제 SEMI JOIN

  • 외부 row 의 키가 자주 중복된다면 → FILTER 가 유리 (subquery cache 효과 극대화)
  • 외부 row 키가 거의 고유하고 inner 인덱스가 효율적**이라면 → SEMI JOIN 이 유리
  • UNNEST 결과 plan 에 SORT UNIQUE / HASH UNIQUE 가 끼어 있다면 → 옵티마이저가 안에서 다시 unique 화 비용을 쓰고 있다는 신호. NO_UNNEST 로 FILTER 길로 빼서 trace 비교 권장

EXISTS 는 DISTINCT 의 단순 대체가 아니라, 옵티마이저에게 두 길을 열어 주는 도구 입니다. 어느 길이 더 빠른지는 데이터 분포에 따라 다르므로, FILTER 와 SEMI JOIN 두 trace 를 모두 떠 보고 CR Gets / Elapsed Time 으로 결정합니다.

정리

세 줄로 압축하면:

  1. 1:N JOIN 으로 부풀어난 결과를 DISTINCT 로 압축하고 있다면 — N 쪽을 EXISTS 서브쿼리로 빼서 DISTINCT 자체를 제거할 수 있습니다.
  2. EXISTS 처리는 두 갈래입니다 — NO_UNNEST 로 FILTER (subquery cache 활용), UNNEST + NL_SJ 로 SEMI JOIN (인덱스 probe 효율). 어느 쪽이 빠른지는 데이터 분포에 달렸습니다.
  3. UNNEST 결과 plan 에 SORT UNIQUE / HASH UNIQUE 가 보이면 — 안에서 다시 unique 화 비용이 들고 있다는 신호. NO_UNNEST 로 FILTER 길과 trace 를 비교한 뒤 결정합니다.
SQL Tuning, 케이스 스터디
oracle distinct exists semi-join filter unnest
이 기사는 저작권자의 CC BY 4.0 라이센스를 따릅니다.