[Oracle] 물리적 조인 (Physical Join)

• Oracle에서 SQL 조인이 실행될 때, 논리적 조인(Logical Join)(예: INNER JOIN, LEFT JOIN)은 물리적 조인(Physical Join) 방식으로 변환되어 실행
• 물리적 조인은 데이터를 실제로 처리하는 방법이며, Oracle의 옵티마이저가 가장 효율적인 방식을 자동으로 선택

1. Nested Loop Join (중첩 루프 조인)

🔹 기본 개념:

• 외부 테이블의 각 행을 내부 테이블과 비교하여 일치하는 행을 찾는 방식
• 작은 데이터셋이나 내부 테이블에 인덱스가 있는 경우 효율적
• 일반적으로 인덱스가 존재하는 경우 자동 선택됨

🔹 동작 방식:

1. 외부 테이블의 첫 번째 행을 가져옴
2. 내부 테이블에서 조인 조건을 만족하는 행을 찾음 (Index Lookup 가능)
3. 반복하여 모든 행을 처리

SELECT /*+ USE_NL(B) */ A.*, B.*
FROM TableA A
JOIN TableB B ON A.id = B.id;

| Id | Operation          | Name   |
|----|-------------------|--------|
|  0 | SELECT STATEMENT  |        |
|  1 |  NESTED LOOPS     |        |
|  2 |   TABLE ACCESS FULL | TableA |
|  3 |   TABLE ACCESS BY INDEX ROWID | TableB |
|  4 |    INDEX UNIQUE SCAN | idx_tableB_id |

✅ 적합한 경우:

• 소규모 테이블 조인
• 내부 테이블에 인덱스가 존재하는 경우
• 선행 테이블이 필터링되어 검색 범위가 작을 때

❌ 비효율적인 경우:

• 대량 데이터 조인 시 성능 저하 (O(N²))
• 내부 테이블에 인덱스가 없을 경우 Full Scan 발생 가능

2. Hash Join (해시 조인)

🔹 기본 개념:

• 작은 테이블을 해시 테이블로 메모리에 올리고, 큰 테이블을 스캔하면서 빠르게 매칭하는 방식
• 인덱스 없이도 효율적으로 조인 가능
• 대량의 데이터를 조인할 때 주로 사용됨

🔹 동작 방식:

1. Build 단계: 작은 테이블(TableB)을 메모리에 로드하고 해시 테이블 생성
2. Probe 단계: 큰 테이블(TableA)을 읽으며 해시 테이블에서 조인 키를 찾음

SELECT /*+ USE_HASH(B) */ A.*, B.*
FROM TableA A
JOIN TableB B ON A.id = B.id;

| Id | Operation         | Name   |
|----|------------------|--------|
|  0 | SELECT STATEMENT |        |
|  1 |  HASH JOIN       |        |
|  2 |   TABLE ACCESS FULL | TableA |
|  3 |   TABLE ACCESS FULL | TableB |

✅ 적합한 경우:

• 대량 데이터 조인
• 조인 키에 인덱스가 없을 때
• 메모리 크기(PGA_AGGREGATE_TARGET)가 충분한 경우

❌ 비효율적인 경우:

• 메모리가 부족할 경우 성능 저하 (디스크 I/O 발생 가능)
• 해시 테이블이 너무 클 경우 해시 분할이 필요

3. Sort Merge Join (정렬 병합 조인)

🔹 기본 개념:

• 양쪽 테이블을 정렬한 후, 정렬된 데이터를 병합하여 조인하는 방식
• 인덱스가 없을 때도 비교적 효율적
• 해시 조인이 불가능한 경우(>, <, BETWEEN 등 범위 조건 조인) 사용됨

🔹 동작 방식:

1. Sort 단계: 두 테이블을 조인 키를 기준으로 정렬
2. Merge 단계: 정렬된 두 테이블을 병합하여 조인

SELECT /*+ USE_MERGE(B) */ A.*, B.*
FROM TableA A
JOIN TableB B ON A.id = B.id;

| Id | Operation           | Name   |
|----|--------------------|--------|
|  0 | SELECT STATEMENT   |        |
|  1 |  SORT MERGE JOIN   |        |
|  2 |   TABLE ACCESS FULL | TableA |
|  3 |   TABLE ACCESS FULL | TableB |

✅ 적합한 경우:

• 두 테이블에 인덱스가 없고, 정렬이 필요할 때
• 범위 조건 (>, <, BETWEEN)이 있는 조인

❌ 비효율적인 경우:

• 정렬 비용이 크므로, 테이블 크기가 크면 비효율적
• 해시 조인이 가능한 경우, 해시 조인이 더 유리함

결론: 언제 어떤 조인을 사용할까?

• Nested Loop Join → 인덱스가 존재하고, 데이터 크기가 작을 때
• Hash Join → 대량 데이터 조인, 인덱스가 없을 때
• Sort Merge Join → 범위 조건이 포함된 조인, 정렬이 필요할 때