조인튜닝

1.NL조인 (Nested Loop, 중첩 루프문)

↳ NL조인은 인덱스를 이용한 조인이다. (Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다.)

1. 사원 X1 인덱스에서 입사일자 >= '19960101'인 첫번째 레코드 찾음
2. 인덱스 ROWID로 사원 테이블 찾아감
3. 사원 테이블에서 읽은 사원 번호로 고객_X1 인덱스를 탐색
4. 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블 레코드를 찾아감
   -> 해당 사원 번호보다 작을 때 까지 고객_X1 인덱스 스캔 진행
5. 위 루프를 사원 X1 인덱스에서 입사일자 >= '19960101'인 데이터에 대해서 지속적으로 반복

NL조인 실행계획 : 아래는 NL 조인 실행 계획 예시이다. 사원이 Outer에 해당하고 고객이 Inner에 해당한다

NESTED LOOPS
	TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
		INDEX(RANGE SCAN) OF '사원_X1' (INDEX)
	TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
		INDEX(RANGE SCAN) OF '고객_X1' (INDEX)​​

 

 

NL 조인을 제어할 때는 아래와 같이 use_nl 힌트를 사용한다.

select /*+ ordered use_nl(c) */
	e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
and c.관리사원번호 = e.사원번호

↳ ordered 힌트는 from절에 기술한 순서대로 조인하라고 옵티마이저에 지시할때 사용, use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용

↳ 위에서는 ordered와 use_nl(c)힌트를 같이 사용했으므로 사원 테이블 기준으로 고객 테이블과 NL방식으로 조인하라는 뜻이다.

 

NL 조인 특징 요약

1) 랜덤 액세스 위주의 조인 방식이다.

  ↳레코드 하나를 읽으려고 블록을 통째로 읽는 랜덤 액세스 방식은 비효율이 존재한다. 그래서 인덱스 구성이 아무리 완벽해도 대량 데이터 조인할 때 NL 조인이 불리한 이유다

2) 한 레코드씩 순차적으로 진행한다.

  ↳ 이 특징 덕분에 아무리 큰 테이블을 조인하더라도 빠른 응답 속도를 낼수 있다.(부분범위 처리가 가능한 상황에서)

3) 인덱스 구성 전략이 특히 중요하다.

  ↳ 조인 컬럼에 대한 인덱스가 있느냐, 없느냐에 따라 조인 효율이 크게 달라진다.

 

->NL조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리(OLTP) 시스템에 적합한 조인방식이다.

 

2. 소트 머지 조인

[ SGA vs PGA ]

SGA(System Global Area 또는 Shared Global Area)는 공유 메모리 영역으로 여러 프로세스가 공유될 수 있다. 하지만 동시에 액세스할 수 없다. 동시에 액세스하려는 프로세스 간 액세스를 직렬화하기 위한 Lock 매커니즘으로 래치(Latch)가 존재한다.

PGA(Process/Program/Private Global Area)는 각 오라클 서버 프로세스에 할당된 메모리 영역이다. 프로세스에 종속적인 고유데이터를 저장하는 용도로 사용한다. PGA는 독립적인 메모리 공간으로 래치 매커니즘이 불필요하다. 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

 

[ Sort merge join ]

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

2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지(merge)한다.

select /*+ ordered use_hash(c) */
	e.사원번호, e.사원명, e.입사일자
    , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원e, 고객c
where c.관리사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'Z123'
and c.최종주문금액 >=20000

↳ use_merge힌트로 유도하고 사원테이블 기준으로 (ordered) 고객 테이블과 조인할 때 소트머지방식을 사용하라(use_merge)고 지시하고 있다.

↳ 위 sql 수행과정을 풀어서 설명하면 아래와 같다

 

1. 조건에 해당하는 사원데이터를 읽어 조인컬럼인 사원번호 순으로 정렬한다. 정렬한 결과집합은 PGA영역에 할당된 Sort Area에 저장한다. PGA에 다 저장하지 못하면 TEMP 테이블스페이스에 저장한다.

select 사원번호, 사원명, 입사일자
from 사원
where 입사일자 >='19960101'
and 부서코드 = 'z123'
order by 사원번호

2. 조건에 해당하는 고객데이터를 읽어 조인컬럼이 관리사원번호 순으로 정렬한다. 정렬한 결과집합은 PGA영역에 할당된 Sort Area에 저장한다. PGA에 다 저장하지 못하면 TEMP 테이블스페이스에 저장한다.

select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객
where 최종주문금액 >= 20000
order by 관리사원번호

3. PGA에 저장된 사원데이터를 스캔하면서 PGA에 저장된 고객 데이터와 조인한다. 

[ 소트머지 조인이 빠른 이유 ]

↳ 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장한 후 조인한다. PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 그래서 대량 데이터 조인에 유리하다.

 

[ 소트 머지 조인의 주용도 ]

↳ 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인

↳ 조인 조건식이 아예 없는 조인(카테시안 곱)

 

 

3. 해시조인

[ Hash join ]

1. Build 단계 : 작은쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵)을 생성한다.

2. Probe 단계 : 큰 쪽 테이블(probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.

select /*+ ordered use_hash(c) */
	e.사원번호, e.사원명, e.입사일자
    , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원e, 고객c
where c.관리사원번호 = e.사원번호
and e.입사일자 >= '19960101'
and e.부서코드 = 'Z123'
and c.최종주문금액 >=20000

↳해시 조인은 use_hash 힌트로 유도한다. 

↳ 위 sql 수행과정을 풀어서 설명하면 아래와 같다.

 

1. build 단계 : 조건에 해당하는 사원데이터를 읽어 해시테이블을 생성한다. 이때, 조인컬럼인 사원번호를 해시 테이블 키 값으로 사용한다. 사원번호를 해시함수에 입력해서 반환된 값으로 해시 체인으로 찾고, 데이터를 연결한다.

select 사원번호, 사원명, 입사일자
from 사원
where 입사일자 >= '19960101'
and  부서코드 ='Z123'

2. probe단계: 조건에 해당하는 고객데이터를 읽어 앞서 생성한 해시 테이블을 탐색한다. 관리사원번호를 해시함수에 입력해서 반환된 값으로 해시체인을 찾고, 그 해시체인을 스캔해서 값이 같은 사원번호를 찾는다. 찾으면 조인성공이다.

select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객
where 최종주문금액 >= 20000

* 해시조인이 빠른이유는 소트머지 조인이 빠른이유와 동일하다.

 

 

[ 대용량 Build input 처리 ]

대용량 테이블이어서 인메모리 해시조인이 불가능한 상황에서 해시조인을 어떻게 할까?

-> 분할 & 정복 (divide & conquer)방식을 사용한다.

 

1. 파티션 단계

  ↳ 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다. 독립적으로 처리할 수 있는 여러개의 작은 서브 집합으로 분할함으로써 파티션 짝(pair)을 생성한다.

 

2. 조인 단계

  ↳ 각 파티션 짝(pair)에 대해 하나씩 해시조인한다. 이때, 각각에 대한 build input과 probe input은 독립적으로 결정된다

  ↳ 해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다. 모든 파티션 짝에 대한 처리를 마칠 때까지 이 과정을 반복한다.

 

조인 메소드 선택 기준

1) 소량 데이터 조인할 때 - NL조인

2) 대량 데이터 조인할 때 - 해시조인

3)대량 데이터 조인인데 해시 조인으로 처리할  수 없을 때, 즉 조인 조건식이 등치(=)조건이 아닐 때 - 소트머지조인

 

[수행빈도 매우 높은 쿼리에 대해서]

1) (최적화된) NL조인과 해시 조인 성능이 같으면 NL조인

2) 해시 조인이 약간 더 빨라도 NL조인

3) NL조인보다 해시 조인이 매우 빠른 경우, 해시 조인

 

  ↳ NL조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조다. 반면 해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸되는 자료구조다.

  

결국 해시조인은 아래 세가지 조건을 만족하는 SQL문에 주로 사용한다.

1) 수행빈도가 낮고

2) 쿼리 수행시간이 오래걸리는

3) 대량 데이터 조인할 때

 

 

 

출처: 개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝