오라클 조인 종류와 특징 분석 [대용량 데이터 베이스 튜닝 및 설계 3일차]

오라클 조인 종류와 특징 분석 [대용량 데이터 베이스 튜닝 및 설계 3일차]

 

 

(Nested Loops Join)

 

 

Nested Loops 조인 – NL 조인의 특징

---

 

 

Random 액세스 위주의 조인방식

 인덱스 구성이 완벽해도 대량의 데이터 조인시 비효율적

 

조인을 한 레코드씩 순차적으로 진행

 대용량 집합이더라도 매우 극적인 응답속도 가능

 부분범위처리가 가능한 상황에서 먼저 액세스되는 테이블(Driving Table)의 처리 범위에 의해 전체 일량이 결정

 

다른 조인방식보다 인덱스 구성 전략이 특히 중요

 소량의 데이터를 처리하거나 부분범위 처리가 가능한 OLTP성 환경에 적합한 조인방식이다

 

 

 

 

(sort merge Join)

소트 머지 조인 – 개요/정의 

---

 

NL 조인을 효과적으로 수행하려면 조인 컬럼에 인덱스 필요

적절한 인덱스가 없다면 Inner 테이블을 반복적으로 Full Scan

조인 컬럼에 적절한 인덱스가 없을 경우, 소트머지 or 해시조인 고려

 

Sort Merge Join : 두 테이블을 각각 정렬한 다음에 두 집합을 Merge하면서 조인을 수행

 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬

머지 단계 : 정렬된 양쪽 집합을 서로 Merge 한다

 

 

특징

 

NL 조인과 달리 소트 머지 조인은 조인 컬럼의 인덱스 유무에 따른 영향력 적음

양쪽 집합을 개별적으로 읽고 나서 조인함

조인 컬럼에 인덱스가 없는 상황에 아주 유리

 First 테이블은 인덱스에 의한 sort 작업 생략이 가능하나, Second 테이블은 인덱스가 있더라도 sort 작업이 일어난다

 다만 정렬된 순서대로 Sort Area에 담기만 하므로 부하는 그리 크지 않다

 SCAN 위주의 액세스 방식을 사용

정렬 대상 레코드를 찾는 작업 만큼은 Random 엑세스 방식으로 처리될 수 있고, 이 Random 액세스량이 많다면 소트 머지 조인의 이점이 사라짐

소트 머지 조인이 유리한 상황

 First 테이블에 소트 연산을 대체할 인덱스가 있다

 조인할 First 집합이 이미 정렬돼 있을때 (group by, order by, distinct 등)

 조인 조건식이 등치(=) 조건이 아닐때

 

소트 머지 조인 – 힌트/동작원리

 

 

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename

from dept d, emp e

where d.deptno=e.deptno;

 

 

 

 

(Hash Join)

 

해시조인 - 기본 메커니즘

---

 

 

소트머지 조인과 NL조인이 효과적이지 못한 상황에 대한 대안으로 개발됨

 둘 중 작은 집합(Build Input)을 읽어 Hash Area에 해시 테이블을 생성하고

 

반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색 하면서 조인한다

 

NL 조인과 달리 Random 액세스 부하가 없다

단, 양쪽집합을 읽는 과정에서 인덱스를 이용한다면 Random 액세스 발생

소트머지조인과 달리 조인 전에 미리 양쪽 집합을 정렬하는 부담이 없다

해시 테이블 생성 비용 발생 -> Build Input이 작을 때  효과적 (hash_area_size)

해시 키 값으로 사용되는 컬럼에 중복값이 거의 없을 때 효과적

NL조인 과 달리 Hash Area에 미리 생성해 둔 해시 테이블(또는 해시 맵)을 이용한다

 해시테이블을 만드는 단계는 전체범위처리 불가피

 Probe Input을 스캔하는 단계는 NL조인처럼 부분범위처리가능

NL조인 과 달리 래치획득 과정없이 PGA(Program Global Area)에서 빠르게 데이터 탐색한다

 

 

해시조인의 사용기준

 

키 포인트

한 쪽 테이블이 Hash Area(hash_area_size)에 담길 정도로 충분히 작아야 함

Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함

 

 

언제 사용하는 것이 효과적인가 ?

조인 컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때

조인 커럼에 인덱스가 있더라도 NL 조인시, Inner 쪽 집합으로 Random 액세스 부하가 심할 때

소트 머지 조인시 두 테이블이 너무 커서 소트 부하가 심할 때

수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블 조인할 때

 

 

주의

해시 테이블은 재사용 불가 고비용 자료구조

수행 빈도가 높은 쿼리에 적용하면 CPU, 메모리 사용률, 래치 경합 매우 증가