조인 튜닝 심화

ORACLE SQL TUNING · DEEP DIVE 2

조인 튜닝 심화

NL · Hash · Sort Merge의 내부 동작, 조인 순서 결정, 서브쿼리와 쿼리 변환, 아우터 조인까지

선수 학습 · SQL 튜닝 입문 7장, 인덱스 튜닝 심화커리큘럼 · 8~9주차난이도 · 중급

1. 조인 방식 선택의 기준

Nested Loops
"적은 건수를 인덱스로 빨리"
OLTP · 부분 범위 처리 · 첫 응답 속도

Hash Join
"많은 건수를 메모리에서 한 번에"
배치 · 대량 집계 · 조인 인덱스 없음

Sort Merge Join
"양쪽을 정렬해서 병합"
비동등 조인 · 이미 정렬된 입력

판단 질문 3가지
① 조인 결과(또는 Outer 집합)가 몇 건인가? — 소량이면 NL, 대량이면 Hash
② Inner 쪽 조인 컬럼에 인덱스가 있는가? — 없으면 NL은 사실상 불가
③ 사용자에게 첫 화면만 빨리 보여주면 되는가, 전체 결과가 필요한가?

2. Nested Loops Join 심화

2.1 동작 원리

  for each row in OUTER (Driving)          ← 1회 스캔
      search INNER using index on join key ← Outer 행 수만큼 반복 (Starts)
      if match → return row

• 총 비용 ≈ Outer 스캔 비용 + Outer 행 수 × (Inner 인덱스 탐색 + 테이블 액세스)
• 결과를 한 건씩 바로 반환할 수 있어 부분 범위 처리에 유리하다.

2.2 NL 조인 튜닝 포인트

포인트	내용
Driving 집합 최소화	Outer 쪽 조건을 최대한 적용해 반복 횟수(Starts) 감소
Inner 인덱스	조인 컬럼 + Inner 쪽 필터 조건을 함께 포함한 결합 인덱스
Inner 테이블 액세스	인덱스에서 걸러 테이블 방문 최소화, 필요 시 커버링
조인 순서	걸러지는 비율이 큰 테이블을 먼저 조인 (3개 이상 조인 시)

2.3 NL 조인 실행계획 형태 (11g+)

|  1 |  NESTED LOOPS                 |             |     ← 테이블 액세스용 NL
|  2 |   NESTED LOOPS                |             |     ← 인덱스 탐색용 NL
|  3 |    TABLE ACCESS BY INDEX ROWID| TB_CUSTOMER |     ← Outer
|* 4 |     INDEX RANGE SCAN          | IX_CUST_01  |
|* 5 |    INDEX RANGE SCAN           | IX_ORDER_01 |     ← Inner 인덱스
|  6 |   TABLE ACCESS BY INDEX ROWID | TB_ORDER    |     ← Inner 테이블 (일괄 처리)

• NL이 두 번 나오는 것은 NLJ Batching(Inner 인덱스 결과를 모아 테이블 I/O를 일괄 처리) 때문이다. 정상이다.
• 이 방식에서도 결과 순서가 Outer 순서와 다를 수 있으므로 정렬은 반드시 ORDER BY로 명시한다.

3. Hash Join 심화

3.1 동작 원리

  [Build 단계]  작은 집합(Build Input)을 읽어 조인 키로 해시 테이블 생성 → PGA
  [Probe 단계]  큰 집합(Probe Input)을 읽으며 해시 테이블 탐색 → 일치하면 반환

  HASH JOIN
    ├─ TABLE ACCESS FULL  TB_CUSTOMER   ← 위쪽 = Build (작아야 함)
    └─ TABLE ACCESS FULL  TB_ORDER      ← 아래쪽 = Probe

3.2 메모리와 성능

처리 방식	조건	실행계획 표시
Optimal	Build 해시 테이블이 PGA에 모두 들어감	Used-Mem (0)
One-Pass	일부를 Temp에 쓰고 한 번 더 읽음	Used-Mem (1), Used-Tmp
Multi-Pass	여러 번 Temp를 오감 (매우 느림)	Used-Mem (n)

-- 현재/최근 작업 영역 사용 현황
SELECT sql_id, operation_type, policy,
       ROUND(estimated_optimal_size/1024/1024) AS opt_mb,
       ROUND(last_memory_used/1024/1024)       AS used_mb,
       last_execution, optimal_executions, onepass_executions, multipasses_executions
  FROM v$sql_workarea
 WHERE sql_id = '&sql_id';

3.3 Hash Join 튜닝 포인트

• Build 집합을 작게 — 조건을 먼저 적용, 필요한 컬럼만 SELECT (해시 테이블 크기 = 행 수 × 컬럼 크기)
• Build/Probe 지정 — LEADING(작은쪽) USE_HASH(큰쪽), 또는 SWAP_JOIN_INPUTS(t)로 Build 지정
• 양쪽 읽기 비용 — Hash Join은 각 집합을 1번만 읽으므로, 각 집합 자체의 액세스 효율(Full Scan, 파티션 프루닝, 병렬)이 전체 성능을 좌우
• = 조인 조건만 가능 — 비동등 조인은 Hash Join 불가

3.4 NL vs Hash 실측 비교

-- 대상: VIP 고객(5,000명)의 최근 1년 주문 집계
SELECT /*+ GATHER_PLAN_STATISTICS LEADING(c) USE_NL(o) */
       c.cust_no, SUM(o.order_amt)
  FROM lab_cust c JOIN lab_order o ON o.cust_no = c.cust_no
 WHERE c.grade_cd = 'VIP' AND o.order_dt >= ADD_MONTHS(SYSDATE, -12)
 GROUP BY c.cust_no;

SELECT /*+ GATHER_PLAN_STATISTICS LEADING(c) USE_HASH(o) */
       c.cust_no, SUM(o.order_amt)
  FROM lab_cust c JOIN lab_order o ON o.cust_no = c.cust_no
 WHERE c.grade_cd = 'VIP' AND o.order_dt >= ADD_MONTHS(SYSDATE, -12)
 GROUP BY c.cust_no;

방식	Inner Starts	테이블 랜덤 액세스	Buffers (예)	판단
NL	5,000	수십만 회	약 400K	결과가 대량이라 비효율
Hash	1	0 (Full Scan)	약 16K	이 경우 유리

※ 수치는 실행계획 읽기 실습의 LAB 테이블 기준 예시. 직접 실행해 비교해 보자.

4. Sort Merge Join

• 양쪽 입력을 조인 키로 정렬한 후 병합한다. 정렬은 PGA에서 수행된다.
• 적합한 경우 — 비동등 조인(BETWEEN, <, >), 양쪽 모두 이미 인덱스 등으로 정렬되어 있음, Hash Join이 불가능한 조건
• MERGE JOIN CARTESIAN이 실행계획에 보이면 조인 조건 누락이나 카디널리티 1로 잘못 추정된 경우를 의심한다.

-- 비동등 조인 예: 주문 금액 구간별 등급 매핑
SELECT /*+ USE_MERGE(g) */ o.order_no, g.amt_grade
  FROM tb_order o
  JOIN tb_amt_grade g ON o.order_amt BETWEEN g.min_amt AND g.max_amt
 WHERE o.order_dt >= TRUNC(SYSDATE);

5. 조인 순서

5.1 조인 순서 결정 원칙

1. Driving 테이블 — 자체 조건으로 가장 많이 걸러지는(결과 건수가 가장 적은) 테이블
2. 다음 조인 대상 — 조인 후 건수가 가장 많이 줄어드는 테이블을 먼저
3. 건수를 늘리는 조인(1:N의 N쪽)은 가능한 한 뒤로
4. 3개 이상 조인은 LEADING(t1 t2 t3)로 전체 순서를 명시 (ORDERED보다 명확)

5.2 조인 순서 예

-- 조건: 특정 상품분류(1%), 최근 7일 주문(0.7%), 서울 고객(20%)
SELECT /*+ LEADING(p oi o c) USE_NL(oi) USE_NL(o) USE_HASH(c) */
       c.cust_nm, o.order_no, p.prod_nm, oi.order_qty
  FROM tb_product    p
  JOIN tb_order_item oi ON oi.prod_cd  = p.prod_cd
  JOIN tb_order      o  ON o.order_no  = oi.order_no
  JOIN tb_customer   c  ON c.cust_no   = o.cust_no
 WHERE p.cate_cd   = :cate
   AND o.order_dt >= TRUNC(SYSDATE) - 7
   AND c.region    = '서울';

• 위 순서가 정답은 아니다. 주문(최근 7일) → 주문상세 → 상품 순서가 나을 수도 있다. 각 순서를 실측해 단계별 A-Rows가 가장 빨리 줄어드는 순서를 고른다.

6. 서브쿼리와 쿼리 변환

6.1 서브쿼리 처리 방식

방식	동작	실행계획	힌트
Unnesting (조인 변환)	서브쿼리를 조인으로 풀어 조인 방식·순서 선택 가능	NESTED LOOPS SEMI, HASH JOIN SEMI/ANTI	UNNEST, NL_SJ, HASH_SJ
Filter	메인 행마다 서브쿼리 실행, 입력값별 결과 캐싱	FILTER (자식 2개)	NO_UNNEST
Pushing Subquery	서브쿼리 필터를 실행계획 앞단계에서 먼저 수행	FILTER 위치 변화	PUSH_SUBQ

6.2 Semi Join / Anti Join

-- EXISTS → Semi Join: 일치하는 첫 행을 찾으면 Inner 탐색 중단
SELECT c.* FROM tb_customer c
 WHERE EXISTS (SELECT 1 FROM tb_order o WHERE o.cust_no = c.cust_no AND o.order_dt >= :d);

-- NOT EXISTS → Anti Join
SELECT c.* FROM tb_customer c
 WHERE NOT EXISTS (SELECT 1 FROM tb_order o WHERE o.cust_no = c.cust_no);

⚠ NOT IN과 NULL — WHERE cust_no NOT IN (SELECT cust_no FROM tb_order)에서 서브쿼리 결과에 NULL이 하나라도 있으면 결과는 0건이다. 의도가 "주문 없는 고객"이라면 NOT EXISTS를 쓴다. (Oracle은 NULL-Aware Anti Join HASH JOIN ANTI NA로 처리하지만 의미는 그대로다.)

6.3 스칼라 서브쿼리

-- 메인 결과 행마다 실행 (입력값별 캐싱 있음)
SELECT o.order_no,
       (SELECT c.cust_nm FROM tb_customer c WHERE c.cust_no = o.cust_no) AS cust_nm
  FROM tb_order o
 WHERE o.order_dt >= TRUNC(SYSDATE);

• 입력값 종류가 적으면 캐싱 효과로 빠르다 (예: 코드명 조회).
• 입력값 종류가 많고 메인 결과가 대량이면 → 아우터 조인으로 바꾸고 Hash Join 검토
• 12c+ 옵티마이저는 스칼라 서브쿼리를 조인으로 자동 변환(Scalar Subquery Unnesting)하기도 한다.

6.4 뷰 병합과 조건 푸시다운

변환	의미	제어
View Merging	인라인 뷰를 메인 쿼리와 합쳐 하나로 최적화	MERGE / NO_MERGE
Predicate Pushing	메인 쿼리 조건을 뷰 안으로 밀어 넣음	PUSH_PRED / NO_PUSH_PRED
Join Elimination	FK 관계상 불필요한 조인 제거	NO_ELIMINATE_JOIN
OR-Expansion	OR 조건을 UNION ALL 형태로 분리	OR_EXPAND / NO_OR_EXPANSION

• GROUP BY·DISTINCT·ROWNUM·분석 함수가 있는 뷰는 병합이 제한된다. 실행계획에 VIEW가 남아 있으면 병합되지 않은 것이다.

7. 아우터 조인

• 아우터 조인은 기준 테이블(보존되는 쪽)이 Outer가 되어야 한다는 제약이 있었다.
• NL 아우터 조인 — 반드시 기준 테이블이 Driving
• Hash 아우터 조인 — 10g+ HASH JOIN RIGHT OUTER로 작은 Inner 쪽을 Build로 사용 가능 (SWAP_JOIN_INPUTS)
• 아우터 조인 대상 테이블의 조건은 ON 절에 둘 것인가, WHERE 절에 둘 것인가에 따라 결과가 달라진다.

-- ON 절: 주문이 없는 고객도 나오고, 주문은 '10' 상태만 붙음
SELECT c.cust_no, o.order_no
  FROM tb_customer c LEFT JOIN tb_order o
    ON o.cust_no = c.cust_no AND o.stat_cd = '10';

-- WHERE 절: o.stat_cd = '10' 조건으로 NULL 행이 제거되어 사실상 이너 조인
SELECT c.cust_no, o.order_no
  FROM tb_customer c LEFT JOIN tb_order o ON o.cust_no = c.cust_no
 WHERE o.stat_cd = '10';

8. 적응적 조인 (Adaptive Plan, 12c+)

• 실행 중 STATISTICS COLLECTOR가 실제 행 수를 세어, 임계값을 넘으면 NL → Hash로 전환한다.
• 확인: DBMS_XPLAN.DISPLAY_CURSOR(…, 'ALLSTATS LAST +ADAPTIVE') — 비활성 단계는 - 표시
• Note 섹션의 this is an adaptive plan 문구로 확인
• 관련 파라미터 (19c): OPTIMIZER_ADAPTIVE_PLANS (기본 TRUE), OPTIMIZER_ADAPTIVE_STATISTICS (기본 FALSE)

9. 조인 튜닝 체크리스트

□ Driving 집합이 가장 적은 건수로 시작하는가
□ NL Inner 쪽 조인 컬럼 + 필터 컬럼 결합 인덱스가 있는가
□ NL Inner Starts가 수만 이상이면 Hash Join과 비교했는가
□ Hash Join의 Build 집합이 작은 쪽인가, Temp를 쓰지 않는가
□ 조인 순서상 건수가 빨리 줄어드는 순서인가
□ MERGE JOIN CARTESIAN이 의도된 것인가
□ 서브쿼리가 FILTER 방식이라면 입력값 캐싱 효과가 있는가
□ NOT IN 사용 시 NULL 가능성을 확인했는가
□ 아우터 조인 조건 위치(ON / WHERE)가 의도와 일치하는가

10. 실습 과제

과제 1. 3.4절의 두 SQL을 실행해 Buffers와 A-Time을 비교하고, VIP 조건을 특정 고객 1명으로 바꿨을 때 결과가 어떻게 뒤바뀌는지 확인하라.

기대 결과 5,000명 → Hash 유리, 1명 → NL이 수십 블록으로 압도적 유리. 조인 방식의 우열은 건수가 결정한다.

과제 2. EXISTS 서브쿼리를 NO_UNNEST(FILTER)와 UNNEST HASH_SJ로 각각 실행해 Starts와 Buffers를 비교하라. 메인 집합을 코드성 값(입력값 종류 10개)으로 바꿔 다시 비교하라.

기대 결과 입력값 종류가 많으면 Semi Join이 유리하고, 종류가 적으면 FILTER 캐싱으로 서브쿼리 실제 수행 횟수가 크게 줄어 FILTER도 충분히 빠르다.

과제 3. 4개 테이블 조인 SQL(5.2절)에서 가능한 Driving 테이블 후보 3개로 각각 실행하여 단계별 A-Rows 흐름표를 작성하라.

작성 양식 순서 / 1단계 A-Rows / 2단계 A-Rows / 3단계 A-Rows / 최종 / 총 Buffers. 중간 단계 A-Rows의 합이 가장 작은 순서가 대개 가장 효율적이다.

이전 ← 인덱스 튜닝 심화 · 실행계획 읽기 실습  |  다음 → AWR 리포트 분석  |  처음으로 → SQL 튜닝 입문