Oracle SQL 조인 수행 원리

조인이란 두 개 이상의 테이블을 하나의 집합으로 만드는 연산이다. SQL문에서 FROM 절에 두 개 이상의 테이블이 나열될 경우 조인이 수행된다. 조인 연산은 두 테이블 사이에서 수행된다. 만일 FROM 절에 A, B, C 테이블을 조인할 경우 A, B를 먼저 조인하고 그 결과와 C를 조인하게 되는 과정을 거친다. 또한 각 조인을 수행할 때 기법을 달리하여 수행할 수 있다. 대표적인 조인 기법은 NL Join, SM Join, HASH Join 등이 있다.

NL (Nested Loops) 조인

특징

1. 인덱스에 의한 랜덤 액세스에 기반을 두기 때문에 대량의 데이터 처리시 불리하다.
2. Driving 테이블로는 ROW수가 적거나 WHERE절의 조건으로 적절하게 ROW를 제한할 수 있는 것이어야 한다.
3. Driven 테이블에는 조인시 사용할 연결고리 컬럼에 적절한 인덱스가 있어야한다.

주의

🔥 인덱스 사용에 따른 랜덤 액세스로 인한 오버헤드가 발생할 수 있다.

요약

• 랜덤 액세스 위주의 조인
• 한 레코드씩 순차적으로 진행
• 테이블의 크기가 작을수록 좋음
• 온라인 프로그램에서 사용할 때 좋음 (OLTP)
• Driving 테이블의 크기가 가장 많은 영향을 끼침

SM (Sort Merge) 조인

각 테이블로부터 동시에 독립적으로 테이블을 읽어 들인 후, 연결고리 컬럼을 대상으로 정렬 작업을 수행한다. 정렬이 모두 끝나면 조인을 수행한다.

1. 각 테이블로부터 데이터를 빨리 처리하도록 한다.
2. 정렬 작업을 위한 메모리(SORT_AREA_SIZE)를 최적화

연결고리 컬럼에 인덱스가 전혀 없어도 빠른 조인을 수행하며 WHERE절의 조건으로 조인 범위를 줄이지 못하는 경우에 효율적이다. 또한 조인이 되는 두 컬럼은 모두 정렬돼야 한다.

특징

1. 연결고리에 인덱스가 생성되어 있지 않은 경우에 빠른 조인을 위해 사용
2. 조인하고자 하는 각 테이블에 대하여 독립적으로 데이터를 읽어들일 때, 이를 얼마나 빠르게 할 것인가가 중요
3. 각 테이블로부터 읽어 온 데이터를 연결고리에 대해 정렬을 수행할 때, 이를 얼마나 빠르게 할 것인가가 중요

• 각 테이블로부터 데이터를 읽어 온 후 정렬을 수행할 때 정렬에 수행되는 시간이 각 테이블이 모두 동일하게 소요되지는 않으며 이 소요되는 시간이 차이가 많이 날 경우 성능상 불리하다.

Sort Merge 조인을 수행해야 하는 경우

1. 연결고리 컬럼에 인덱스가 전혀 없는 경우
2. 대용량의 자료를 조인해야 함으로써 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은 경우

주의

🔥 정렬 작업으로 인한 오버헤드가 발생할 수 있다.

요약

• 조인하기 전 정렬
• 넓은 데이터 처리에 유리
• 정렬에 필요한 데이터가 많아지면 메모리 대신 디스크를 사용하여 성능이 떨어질 수 있음
• 조인 조건의 인덱스 유무에 영향을 받지 않지만 정렬 작업이 필요

HASH 조인

Driving 테이블로 하나를 선택해서 데이터를 먼저 읽어들인 후, hashing을 통해 해시값을 만들어 메모리에 올린다. 다음으로 조인해야 할 테이블로부터 데이터를 읽어서 hashing을 통해 해시값을 만든다. 이렇게 해서 모든 해시값으로 조인을 하며 인덱스를 사용하지 않는다.

1. Driving 테이블이 중요하다.

✅ 메모리 크기로 인해 규모가 작은 테이블을 Driving 테이블로 선택

2. 각 테이블로부터 데이터를 빨리 처리하도록 한다.
3. 조인 작업을 위한 (HASH_AREA_SIZE) 최적화

✅ HASH_AREA_SIZE는 SORT_AREA_SIZE의 두 배가 기본값이다.

해쉬 조인은 조인을 위해 만든 해시값을 저장하기 위해 HASH BUCKET이 구성되는데 이를 위해 많은 메모리와 CPU를 필요로 하므로 하드웨어 자원이 넉넉한 상황에서는 다른 조인보다 효율적일 수 있지만 부족한 상황에서는 오히려 다른 조인보다 비효율적이다.

요약

• 한 쪽 테이블의 크기가 적을수록 좋음
• NL JOIN의 랜덤 액세스, SM JOIN의 정렬 작업에 대한 부담으로 등장
• 인덱스 대신 HASH 함수 사용
• 동등조인(=)에서만 사용
• 수행빈도가 높은 OLTP성 작업에 불리
• 수행빈도가 낮고 쿼리 수행시간이 오래 걸리는 대용량 테이블 조인 작업에 유리 (DW, OLAP)

	Nested Loops	Sort Merge	Hash
힌트	USE_NL(table명 or 별칭)	USE_MERGE(table명 or 별칭)	USE_HASH(table명 or 별칭)
조인 조건	Any Join	Any Join	Equi Join Only
튜닝 포인트	조인 순서, 조인 조건에 대한 인덱스 구성	SORT_AREA_SIZE 메모리 부족시 disk I/O	HASH_AREA_SIZE 메모리 부족시 disk I/O