[Oracle] 조인(JOIN) 튜닝 - NL조인, 소트 머지 조인, 해시 조인, 스칼라 서브쿼

JOIN의 종류

1. NL(Nested Loops) 조인
2. SORT - MERGE 조인
3. HASH 조인
4. 스칼라 서브쿼리(JOIN 대체)

구분	NL(Nested Loops) 조인	Sort Merge 조인	Hash 조인	스칼라 서브쿼리
핵심 메커니즘	중첩 For문 방식(순차적)	양쪽 정렬 후 병합(동시적)	해시 테이블 생성 후 병합	필터 오퍼레이션 + 캐싱
주요 리소스	Buffer Cache(SGA)	PGA(Sort Area)	PGA(Hash Area)	PGA(Cache Area)
액세스 방식	Random Access(Single-Block)	Scan(Multi-Block)	Scan(Multi-Block)	Random Access(필터형)
연결 고리(인덱스)	인덱스 필수(후행 테이블)	인덱스 없이 가능	인덱스 없이 가능	인덱스 권장(서브쿼리 내)
조인 조건	모든 조건 가능(부등호 포함)	부등호(>, <) 조인에 최적	Equal(=) 조인만 가능	Equal(=) 조인 위주
처리 범위	소량, 부분 범위 처리 유리	대량, 전체 범위 처리	대량, Build 단계 전체 범위	소량, 입력값 종류 적을 때
최적 환경	OLTP(온라인 트랜잭션)	대량 데이터, 인덱스 부재 시	DW, Batch(대용량)	코드명 조회, 조인 대체 시
주요 힌트	/*+ USE_NL(B) */	/*+ USE_MERGE(B) */	/*+ USE_HASH(B) */	/*+ UNSET */ (조인 변환)

---

1. NL 조인

인덱스 스캔이 아주 중요한 영향을 미침, 주로 수직 탐색과 랜덤 액세스를 사용하며, DB Buffer Cache를 경유합니다.

'

• ROWID를 활용한 인덱스 탐색을 통해 데이터를 검색
• 중첩된 FOR문을 사용하여 수직 탐색과 랜덤 액세스가 증가
• DB Buffer Cache를 무조건 경유하여 처리
• 인덱스 스캔을 할 때 효율적 - 없으면 중첩 FOR문의 내부 테이블을 항상 FULL 스캔하게 됨
• 랜덤 액세스를 기반으로 하므로 대상 데이터가 소량이고, OLTP SQL에 적합

 

동작 원리

1. Outer(선행) 테이블에서 조건에 맞는 행을 하나씩 찾아서, 그 결과만큼 Inner(후행) 테이블의 인덱스를 반복해서 탐색합니다.  
2. 후행 테이블의 조인 컬럼 인덱스는 선행 테이블의 결과 건수만큼 반복해서 탐색

 

NL 조인 특징

• 랜덤 액세스 위주의 조인 방식
  • 설명: 인덱스 리프 블록에서 얻은 ROWID로 테이블 블록을 하나하나 찾아가는 방식
  • 포인트: 블록 단위 I/O(Multiblock)가 아닌 Single Block I/O를 수행하므로 대량 데이터 처리 시 성능이 급격히 저하
• 조인을 한 레코드씩 순차적으로 진행
  • 설명: 선행 테이블의 한 레코드를 읽고 바로 후행 테이블로 넘어가서 조인하는 방식
  • 포인트: 전체 결과가 수만 건이라도 첫 번째 행은 즉시 출력될 수 있습니다. 이를 응답 속도가 빠르다고 표현하고, 부분 처리 가능으로 전체를 읽지 않아도 사용자는 첫 화면의 데이터를 즉시 볼 수 있습니다.
• 먼저 액세스 되는 선행 테이블의 처리 범위가 전체 일량을 결정
  • 설명: 후행 테이블을 방문하는 횟수는 오직 선행 테이블에서 필터링된 결과 건수에 달려 있음
  • 포인트: 선행 테이블의 조건절에 의한 필터링 효율이 좋을수록 전체 조인 성능이 향상
• 인덱스 구성 전략이 특히 중요
  • 설명: 후행 테이블의 조인 컬럼에 인덱스가 없으면, 선행 테이블에서 넘어오는 건수마다 후행 테이블을 Full Scan
  • 포인트: 실행 계획을 보고 후행 테이블 조인 컬럼에 인덱스를 생성하여 NL 조인 효율을 높일 수 있음
• 온라인 트랜잭션(OLTP) 환경에 적합한 조인 방식
  • 설명: 소량의 데이터를 빠르게 조회하거나, 전체 결과 중 일부만 먼저 보여줘도 되는 온라인 환경에 최적
  • 포인트: NL 조인은 온라인 트랜잭션(OLTP)에 적합, Batch(배치) 작업이나 DW 환경은 Hash Join, Sort Merge Join이 유리

 

---

2. SORT - MERGE 조인

적합한 인덱스가 없고 데이터가 대용량일 때, PGA를 활용해서 정렬한 후 병합하여 처리

 

• 조건절에 알맞는 인덱스가 없고, 대용량의 데이터를 처리해야 할 경우
• 서버 프로세스별로 할당되는 PGA에 SORT된 결과들을 저장하고 MERGE하여 데이터 탐색
  • PGA를 사용해서 래치(Latch) 경합이 적어 대량 데이터 처리에 유리
• 대상 데이터를 조인 조건으로 SORT 한 후 결과를 NL 조인 방식으로 처리하는 것
  • 양쪽 집합이 정렬된 후, 한쪽 집합을 스캔하면서 반대쪽 집합에서 일치하는 값을 찾음
• PGA에 메모리가 부족 시 Temp Tablespace에 기록하여 처리 → 디스크 I/O 발생으로 성능 저하

 

동작 방식

1. 양쪽 테이블을 조인 조건 컬럼으로 각각 정렬(Sort) 합니다.
2. 정렬된 두 집합을 병합(Merge) 하며 조인합니다.

 

---

 

3. HASH 조인

• 해시 함수를 사용하여 특정 값에 대한 해쉬 테이블을 생성
  • 조인이 되는 두개 테이블 중 작은 집합(Build Input)을 읽어 해시 영역(Hash Area)에 해시 테이블(=Hash Map)을 생성
• 반대쪽 큰 집합을 읽어 해시 테이블을 탐색하면서 조인 → NL 조인 방식과 동일
• 해시 테이블은 PGA영역에 만들어지며, 공간이 부족하면 Temp Tablespace에 저장

 

동작 순서

1. 두 집합 중 작다고 판단되는 집합을 해시 함수를 사용해서 PGA에 해시 테이블(해시 버킷과 해시 체인) 생성(Built Input 단계)
   • 해시 함수 적용: Build Input 테이블에서 레코드를 읽을 때마다 조인 컬럼 값을 해시 함수에 넣음
   • 해시 버킷 생성: 함수가 뱉어낸 숫자(해시 값)에 따라 데이터를 특정 버킷에 배정
   • 해시 체인 형성: 같은 버킷에 여러 데이터가 들어오면 연결 리스트(Linked List) 형태로 줄을 세움
   • 위 구조들을 통칭해서 해시 테이블이라고 부르며 PGA에 상주
2. Probe Input을 스캔해서 테이블 생성을 위해 선택되지 않은 나머지 데이터 집합(Probe Input)을 스캔
3. 해시 테이블 탐색(Probe Input)에서 읽은 데이터로 해시 테이블을 탐색할 때도 해시 함수를 사용. 즉, 해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾음

 

동작 방식

Hash Join은 크게 Build 단계와 Probe 단계로 나뉩니다.

• Build 단계(작은 집합 처리)
  • 작업: 조인할 두 테이블 중 작은 집합(Build Input)을 읽어 해시 함수를 적용합니다.
  • 생성: 해시 함수 결과값을 바탕으로 PGA 영역에 해시 테이블을 생성 합니다.
  • 두 테이블 중 통계적으로 더 작은 테이블을 Build Input으로 선택하는 것이 효율적입니다.
• Probe 단계(큰 집합 탐색)
  • 작업: 이제 큰 집합(Probe Input)을 한 건씩 읽으며 동일한 해시 함수를 적용합니다.
  • 탐색: 해시 결과값이 해시 테이블의 어느 버킷에 있는지 확인하고, 그 버킷에 담긴 데이터와 조인합니다.
  • Probe 단계는 NL 조인의 메커니즘과 유사하게 한 건씩 탐색합니다.
• 메모리(PGA) 관리와 성능
  
  • Temp Tablespace 전이: 데이터를 디스크의 Temp Tablespace에 썼다가 읽는 Grace Hash Join이 발생합니다.
  • NL JOIN처럼 랜덤 액세스의 부담이 없습니다.
    
    • Multi-Block I/O 활용: NL 조인은 한 건마다 인덱스<->테이블을 왔다 갔다(Single Block I/O) 하지만, 해시 조인은 Full Scan(Multi-Block I/O)으로 한 번에 데이터들을 퍼 올립니다.
  • Sort-Merge처럼 양쪽 테이블을 정렬하지 않아도 됩니다.
    • PGA 영역 활용: 해시 테이블은 공유 메모리(SGA)가 아닌 프로세스 개인 공간인 PGA에 생성됩니다. 따라서 래치(Latch) 경합이 적어 NL 조인보다 대량 데이터 처리에 유리 합니다.

 

HASH JOIN의 특징

• 해시 테이블 생성의 자원이 소모, Build Input에 참여하는 테이블이 작고, 해시 키 값으로 사용되는 컬럼의 중복이 최소여야 함
  • 해시 테이블 생성의 자원이 소모: 해시 테이블을 만드는 과정 자체가 CPU와 메모리(PGA)를 쓰기 때문에, 메모리에 쏙 들어갈 만큼 작은 테이블을 선택해야 Grace Hash Join(디스크 전이)을 피할 수 있음
  • 해시 키 값으로 사용되는 컬럼의 중복 최소: 중복이 많으면 해시 충돌(Collision)이 발생해 해시 체인이 길어짐. 이는 탐색 시 성능 저하의 주범
• 해시 테이블 생성 단계는 전체 범위 처리가 불가피, 반대쪽 Probe Input을 스캔하는 단계는 NL Join처럼 부분 범위 처리 가능
  • Build 단계는 전체 범위 처리 불가피: 해시 테이블이 완성되어야 조인을 시작할 수 있으므로, Build Input 테이블은 무조건 끝까지 읽어야함
  • Probe 단계는 부분 범위 처리 가능: 해시 테이블이 완성된 후, Probe Input을 읽으면서 즉시 결과를 낼 수 있으므로 이 단계부터는 부분 범위 처리가 가능
• Build Input의 크기가 한계를 초과한 경우
  • 파티션 단계
    • 조인되는 양쪽 집합 모두 조인 컬럼에 해시 함수 적용
    • 반환된 해시 값에 따라 동적으로 파티셔닝을 실시
    • 독립적으로 처리할 수 있는 여러개의 작은 서브 집합으로 분할함으로써 파티션 짝(pair)을 생성
  • 조인 단계
    • 각 파티션 짝(pair)에 대해 하나씩 조인을 수행하는데 이때 각각에 대한 Build Input과 Probe Input은 독립적으로 결정
  • Grace Hash Join은 분할정복 방식

 

Hash의 사용

• JOIN 컬럼에 적당한 인덱스가 없어 NL JOIN이 비효율적일 때
• JOIN Access량이 많아 Random Access 부하가 심하여 NL JOIN이 비효율적일 때
• Sort Merge Join을 하기에는 두 테이블이 너무 커 Sort 부하가 심할 때
• 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 JOIN 할 때

 

---

 

4. 스칼라 서브 쿼리

SELECT 절에 사용되는 서브쿼리로,한 레코드당 정확히 하나의 값만 반환해야 합니다 .

과거 속도 저하의 원인이였지만, 지금은 캐싱 기능으로 인해 속도에 영향을 미치지 않음

• 데이터의 검색, 결과의 중복을 방지하기 위해서 사용하는 서브쿼리가 사용된 쿼리를 튜닝
• 옵티마이저는 서브쿼리에서 안정적인 결과를 얻기 위해 SQL 변환을 수행
• SELECT 절에서 사용하는 스칼라 서브쿼리, FROM절에서 사용하는 IN-LINE 서브쿼리, WHERE 조건에서 사용하는 (중첩)서브쿼리가 있음
• 메인 쿼리와 부모-자식 관계의 종속성을 가지며, NL 조인 형태로 실행계획이 잡히는 것이 일반적(필터 오퍼레인션 방식)
  • 필터 오퍼레이션: 메인쿼리 한 건당 서브쿼리가 한 번씩 실행되는 NL 조인과 흡사한 방식으로 동작

 

NL 조인과 다른 점

• 필터에 의해 조인되는 첫 행을 만나면 검색 중단
• 캐싱 기능을 수행: 서브쿼리 입력값과 출력 값을 메모리(PGA)에 저장하여 똑같은 입력 값이 들어오면 서브쿼리를 실행하지 않고 캐시에서 바로 결과 값을 꺼내옴 → 입력 값의 종류가 적을수록 캐싱 효율이 극대화

👇스칼라 서브쿼리 캐싱 효과

1. 스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값, 출력 값을 내부 캐시(Query Execution Cache)에 저장
2. 조인할 때 마다 일단 캐시에서 '입력 값'을 찾아보고, 찾으면 저장된 '출력 값'을 반환
3. 캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다
4. 캐싱은 쿼리 단위로 이루어짐
5. 캐싱이 시작되면서 PGA에 영역이 할당되고, 수행 중 사용 후 수행 종료 시 반환

 

필터 방식을 사용하지 않으려면, Unnest힌트 사용

• Unnest 힌트: 서브쿼리를 그대로 두지 않고, 옵티마이저가 일반 조인 문장으로 변환하는 것
• 왜??: 서브쿼리 상태(Fiter 방식)에서는 메인 쿼리가 항상 드라이빙(선행) 테이블이 되어야만 하지만 서브쿼리를 조인으로 풀면(Unnesting), 서브쿼리였던 테이블을 드라이빙으로 쓸 수 있어 조인 전략이 다양해 집니다.

---

💡감 잡기

NL 조인 

1. "NL 조인은 어떤 상황에서도 항상 부분 범위 처리가 가능하다?"

👇 정답

• 정답: (X) 위에 언급한 ORDER BY (인덱스 미사용 시)나 집계 함수가 있으면 NL 조인이라도 전체 범위 처리를 해야 합니다.

 

2. "Hash Join이나 Sort Merge Join도 부분 범위 처리가 가능하다?"

👇 정답

정답: (X) 이들은 구조적으로 데이터를 일단 다 읽어서 메모리(PGA)에 쌓아두고(Build/Sort) 시작해야 하므로 무조건 전체 범위 처리 방식입니다. (매우 중요!)

 

3. "Array Size를 크게 설정할수록 부분 범위 처리에 유리하다?"

👇 정답

정답: (X) Array Size(운반 단위)가 너무 크면 그만큼 데이터를 다 채울 때까지 기다려야 하므로, 최초 응답 속도는 오히려 느려질 수 있습니다.

 

 

SORT - MERGE 조인

1. "Sort Merge 조인은 항상 조인 컬럼에 인덱스가 있어야 한다?"

👇 정답

정답: (X) 인덱스가 없을 때 쓰려고 만든 조인입니다. 물론 인덱스가 있다면 정렬 과정을 생략(Sort Join 단계 생략)할 수 있어 더 빨라지긴 합니다.

 

2. "조인 조건이 '부등호(>, <, BETWEEN)'인 경우 Hash Join을 사용한다?"

👇 정답

정답: (X) Hash Join은 오직 = 조건만 가능합니다. 부등호 조건인데 대용량 데이터를 조인해야 한다면 정답은 무조건 Sort Merge 조인입니다. (매우 중요!)

 

3. "데이터가 너무 커서 Temp Tablespace를 사용해도 NL 조인보다 빠르다?"

👇 정답

정답: (X) 디스크 I/O가 발생하는 순간 Sort Merge의 장점은 사라집니다. 이때는 차라리 메모리를 덜 쓰는 방식이나 파티션 조인을 고민해야 합니다.

 

4. "Sort Merge 조인은 선행 테이블의 크기가 성능을 결정한다?"

👇 정답

정답: (X) NL 조인과 달리 두 집합을 모두 정렬해야 하므로, 어느 한쪽이 작다고 일량이 획기적으로 줄지 않습니다. 양쪽 집합의 전체 크기가 모두 중요합니다.

 

 

해시 조인

1. "Hash Join은 인덱스가 반드시 필요하다?" (X)

👇 정답

정답: Hash Join은 인덱스가 전혀 없어도 수행 가능합니다. 오히려 인덱스가 없을 때 대량 데이터를 처리하기 위한 최적의 대안입니다.

 

2. "조인 조건이 '부등호(<, >)'일 때도 Hash Join이 가능한가?" (X)

👇 정답

정답: Hash Join은 반드시 **동치 조인(Equal Join, '=')**에서만 작동합니다. (해시 함수 특성상 값이 정확히 일치해야 버킷을 찾을 수 있기 때문입니다.) 이 부분은 Sort Merge Join과 비교하는 문제로 정말 많이 나옵니다!

 

3. "Hash Join은 OLTP 환경에서 항상 최우선 고려 대상이다?" (X)

👇 정답

정답: Hash Join은 CPU 사용량이 많고 해시 테이블 생성 비용이 큽니다. 수행 빈도가 높은 짧은 트랜잭션(OLTP)에서는 NL 조인이 유리하며, 대량 데이터를 처리하는 DW(Data Warehouse)나 배치(Batch) 작업에 더 적합합니다.

 

 

스칼라 서브쿼리

1. "스칼라 서브쿼리는 항상 NL 조인보다 빠르다?"

👇 정답

정답: (X) 입력 값의 종류가 너무 많으면 캐싱 엔트리를 교체하는 비용(Overhead) 때문에 오히려 일반 조인보다 느려질 수 있습니다.

 

2. "서브쿼리 필터 방식은 조인 순서를 마음대로 바꿀 수 있다?"

👇 정답

정답: (X) 필터 방식은 무조건 메인 쿼리가 선행(Outer), 서브쿼리가 후행(Inner) 고정입니다. 순서를 바꾸고 싶다면 반드시 Unnesting을 먼저 해야 합니다. (이거 주관식 서술형 핵심 답변입니다!)

 

3. "스칼라 서브쿼리에서 결과가 2건 이상 나오면 에러 없이 첫 번째 값만 반환한다?"

👇 정답

정답: (X) ORA-01427: single-row subquery returns more than one row 에러가 발생하며 SQL이 멈춥니다.

4. "IN 이나 EXISTS 서브쿼리는 항상 NL 조인으로만 풀린다?"

👇 정답

정답: (X) Unnesting 되면 Semi Join으로 변환되어 Hash Semi Join이나 Merge Semi Join으로도 수행될 수 있습니다.

 

 

 

 

 

 

참조: 멋쟁이사자처럼 - 한 번에 합격라는 SQLP 과정