조인 3형제 — NL · Hash · Sort Merge

왜 알아두어야 하는가

같은 FROM emp e JOIN dept d ON e.deptno = d.deptno 구문이라도 옵티마이저는 상황에 맞춰 서로 다른 알고리즘을 선택합니다. 어떤 조인이 선택되는지에 따라 실행 속도가 크게 달라질 수 있습니다.

SQLD에서 자주 등장하는 질문은 “다음 중 Hash Join이 유리한 경우는?” 처럼 알고리즘별 특징을 비교하는 유형입니다. 겉보기 SQL은 같아도 데이터 크기·인덱스·조건에 따라 선택되는 알고리즘이 달라집니다.

Nested Loop Join (NL)

외부 테이블의 행 하나를 가져와 그 키와 일치하는 내부 테이블 행을 찾기 위해 내부를 스캔합니다. 이 과정을 외부 행 수만큼 반복합니다.

Nested Loop Join

step 1 / 13

외부 테이블 각 행마다 내부 테이블 전체를 순회 비교. 외부가 작고 내부에 인덱스가 있으면 최적.

EMP (외부)

• KING (dept=10)
• SCOTT (dept=20)
• ALLEN (dept=30)
• NONE (dept=40)

DEPT (내부)

• ACCOUNTING
• RESEARCH
• SALES

✓ 외부 [1] · 내부 [1] 키 일치 → 결과에 포함

← 이전▶ 재생다음 →↺ 리셋

유리한 경우

• 외부 테이블이 작고 내부 테이블 조인 키에 인덱스가 있을 때 — 각 조회가 인덱스 한 번으로 끝납니다.
• 선택도가 높은(= 적게 걸리는) 조건 — 외부가 미리 많이 걸러진 상태입니다.

불리한 경우

• 외부·내부 둘 다 크고 인덱스가 없는 경우 → M × N 비교가 발생하여 대용량에서는 부담이 큽니다.

SQLD 출제 포인트

• USE_NL 힌트 — NL 조인을 강제합니다. 외부 쪽 지정: /*+ USE_NL(e d) */.
• 이너 테이블에 FULL 힌트를 함께 주면 힌트가 상충되어 감점 요인이 됩니다.

Hash Join

작은 쪽 테이블로 해시 테이블을 메모리에 만들고(빌드), 큰 쪽을 스캔하면서 키로 해시 조회를 수행합니다(프로브). 등가 조인(=)에서만 사용할 수 있습니다.

Hash Join

step 1 / 9

작은 쪽으로 해시 테이블을 빌드 → 큰 쪽 스캔하며 조회. 등가 조인(=)에서 대용량 배치에 최적.

PROBE (외부)

• KING (dept=10)
• SCOTT (dept=20)
• ALLEN (dept=30)
• NONE (dept=40)

BUILD (해시)

• ACCOUNTING
• RESEARCH
• SALES

BUILD: 내부 [1] → 해시 테이블에 삽입

← 이전▶ 재생다음 →↺ 리셋

유리한 경우

• 대용량 배치 작업 — 양쪽 모두 크고 인덱스가 없는 경우입니다.
• 등가 조건 — 범위 조건(BETWEEN, <)에서는 사용할 수 없습니다.
• 메모리가 충분하면 한 번의 스캔으로 처리할 수 있습니다.

불리한 경우

• 메모리가 부족하면 디스크 임시공간을 사용하게 되어 성능이 떨어집니다.
• OLTP의 짧은 쿼리에는 해시 빌드 오버헤드 때문에 NL보다 느릴 수 있습니다.

구조 요약

  BUILD 단계:  작은 쪽 → 해시 테이블 (키 → 행)
  PROBE 단계:  큰 쪽 → 해시에서 O(1) 조회

Sort Merge Join

양쪽을 조인 키로 정렬한 뒤, 두 커서를 나란히 두고 병합하듯 진행합니다. 같으면 결과에 포함하고 둘 다 전진하며, 다르면 작은 쪽만 전진합니다.

Sort Merge Join

step 1 / 5

양쪽을 조인 키로 정렬한 뒤 병합. 이미 정렬되어 있거나 범위 조건이 섞인 대용량에 유리.

EMP (외부)

• KING (dept=10)
• SCOTT (dept=20)
• ALLEN (dept=30)
• NONE (dept=40)

DEPT (내부)

• ACCOUNTING
• RESEARCH
• SALES

양쪽을 키로 정렬 후 병합 시작

← 이전▶ 재생다음 →↺ 리셋

유리한 경우

• 양쪽이 이미 정렬되어 있거나 인덱스로 정렬 순서를 확보할 수 있는 경우입니다.
• 범위 조건(BETWEEN, <=)과 섞인 대용량 — Hash는 등가만 가능하지만 Sort Merge는 부분 범위도 처리할 수 있습니다.
• 외부 조인(LEFT OUTER)이 필요한 대용량 처리에 적합합니다.

불리한 경우

• 정렬 비용이 크므로 메모리·임시공간을 사용하게 되며, 작은 데이터에서는 오버헤드가 될 수 있습니다.

한 표로 정리

	NL	Hash	Sort Merge
외부/내부 크기	외부 小, 내부 대용량 OK	작은 쪽 → 빌드	양쪽 대용량
조인 조건	=, <, BETWEEN 모두	등가 = 만	모두 (정렬 기반)
필요한 것	내부 조인 키 인덱스	메모리 (빌드 용)	정렬 가능성
강점	OLTP 짧은 쿼리	대용량 배치	정렬된 대용량
약점	대용량 × 인덱스 없음	범위 조건 불가	정렬 오버헤드

자주 확인되는 포인트

Q. 대용량 테이블 두 개를 등가 조인할 때 어떤 조인을 사용하는 것이 좋을까요?

A. Hash Join이 적합합니다. 양쪽이 크고 인덱스가 없을 때, 해시가 메모리에 올라갈 수 있다면 훨씬 빠르게 처리됩니다.

Q. OLTP에서 단건 조회에 Hash Join이 선택되면 어떻게 될까요?

A. 작은 결과를 얻기 위해 해시 빌드 오버헤드가 추가되므로 느려질 수 있습니다. 이 경우에는 NL + 인덱스가 적합합니다.

Q. USE_NL 힌트를 사용했는데 이너 테이블에 FULL 힌트를 함께 쓰면 어떻게 될까요?

A. 상충되는 힌트로 감점 요인입니다. NL의 의미(인덱스로 타라)와 FULL(전체 스캔)이 반대 방향이기 때문입니다.

한 문장 요약

NL은 반복문, Hash는 딕셔너리, Sort Merge는 지퍼로 기억해 두시면 좋습니다.

이렇게 머릿속에 그려두시면 실행 계획 관련 문제를 빠르게 해결하는 데 도움이 됩니다.