[친절한 SQL 튜닝] 조인 튜닝

ironk.im 2025. 6. 1. 15:56

2025. 6. 1. 15:56

NL(Nested Loop) 조인

NL 조인은 중첩 루프문과 같은 수행 구조를 사용

NL 조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 사용

Outer 쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다(Table Full Scan 하더라도 한 번에 그치기 때문)

단, Inner 테이블은 인덱스를 사용해야 한다

Outer 에서 읽은 건수 만큼 Table Full Scan을 반복하기 때문이다

위 NL 조인 실행 계획을 보면 윗쪽 테이블 기준 아래쪽 테이블과 NL 조인 한다고 생각하면 된다

use_nl(alias) : alias를 Inner 테이블로 해서 NL 조인 하라는 hint

이 처럼 여러 테이블의 alias를 사용하면, 네개의 테이블을 NL 조인하는데 순서는 옵티마이저가 알아서 정하도록 하는 것이다

Leading 등을 활용해서 순서를 제어하는 것이 힌트 사용시 도움이 될 것 같다

위 쿼리의 경우 비교 순서는 2 ,3 1, 4 이다

ordered 로 사원, 고객 순서로 수행되고 index(e)로 인덱스를 이용한다

현재 조건문에 존재하는 컬럼이 포함된 인덱스는 사원_x1이므로 입사일자 조건이 먼저 수행되고 이어서 부서코드로 filter 하게된다

그리고 조인 컬럼을 포함하는 인덱스가 고객_x1이므로 해당 인덱스를 사용하여 NL 조인한 후 최종주문금액 조건으로 filter 하게 된다

NL 조인 튜닝 포인트

조인 액세스 횟수가 많을 수록 성능은 느려진다. 따라서, 조인 액세스 횟수가 Outer 테이블을 읽고 필터링한 결과 건수에 의해 결정

온라인 트랜잭션 처리(OLTP) 시스템에서 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서

NL 조인 특징

랜덤 액세스 위주
한 레코드씩 순차적으로 진행
인덱스 구성 전략이 특히 중요

이런 특징 때문에 NL 조인은 소량 데이터를 주로 처리하거나 부분범위 처리가 가능한 온라인 트랜잭션 처리 시스템에 적합

NL 조인 확장 메커니즘

1. 전통적인 실행계획

2. 테이블 Prefetch 실행계획

nlj_prefetch, no_nlj_prefetch 힌트로 사용, 비사용 지정 가능

3. 배치 I/O 실행계획

: 배치 I/O 실행계획이 나타날 때는 결과 집합의 정렬 순서도 다를 수 있으므로 주의 필요

소트 머지 조인

조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효율적이지 않을 때 사용

SGA

공유 메모리 태시된 데이터를 여러 프로세스가 공유해서 사용. 직렬화 매커니즘 존재(Lock, 래치)

PGA

프로세스에 할당된 메모리 영역으로 프로세스만 독립적으로 사용하는 공간으로 래치 메커니즘 불필요

- SGA 버퍼캐시에서 읽을 때 보다 속도가 더 빠르다

- PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블 스페이스를 이용한다

소트머지 기본 메커니즘

소트 단계
양쪽 집합을 조인 컬럼 기준으로 정렬
소트 시 PGA의 Sort Area 에 저장, 공간 부족 시 Temp 테이블스페이스에 저장
머지 단계
정렬한 양쪽 집합을 서로 머지 NL 조인과 방식이 다르지 않음
다만 소트 단계에 의해 정렬되어 있으므로 중간에 멈출 수 있음

use_merge(alias) 힌트로 소트머지 조인 유도 가능

ordered에 의해 순서대로 조인하고 고객 테이블 조인 시 소트 머지 조인 사용하도록 하는 힌트

소트 머지가 사용되는 상황

조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)

소트 머지 조인 특징

조인 전 양쪽 집합 정렬
NL 조인과 같은 방식으로 진행
실시간으로 인덱스 생성하는 것과 같음 (정렬)
PGA 사용으로 직렬화 과정 없어 SGA에 비해 속도 우위

해시 조인

해시 조인 기본 메커니즘

Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블(해시 맵) 생성
Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인

use_hash(alias) 힌트로 유도

해시 테이블은 PGA Hash Area 에 저장 된다

해시 테이블이 너무 커서 PGA 에 담을 수 없으면, Temp 테이블 스페이스에 저장

Build와 Probe 단계에서 같은 해시 함수를 사용하므로 같은 값을 입력하면 같은 해시 값을 반환한다

따라서 해시 함수가 반환한 값에 해당하는 해시 체인만 스캔

해시 조인이 빠른 이유

PGA 영역을 사용하므로 래치 획득 과정이 없어 빠르게 탐색 가능하기 때문

!중요

해시 테이블에는 ROWID 만 있는 것이 아닌 SQL에 사용한 컬럼을 모두 저장 하고 있다

안그러면 ROWID로 다시 테이블 블록을 액세스해야 하기 때문

소트 머지 조인 보다 해시 조인이 빠른 이유는 사전 작업(정렬)의 과정과 해시 테이블(해시 맵)을 만드는 과정의 차이에서 발생하는 것

대용량 Build Input 처리

해시 조인하려는 두 테이블이 모두 대용량인 경우 인메모리 해시 조인이 불가능 하다

이럴 경우 분할 정복 방식으로진행된다

파티션 단계
조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환되는 해시 값에 따라 동적으로 파티셔닝
독립적으로 처리할 수 있느 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝 생성
Temp 테이블 스페이스를 사용하므로 인메모리 보다 성능이 많이 떨어짐
파티션 짝에 대해 하나씩 조인 수행
파티션 하기 전 테이블 크기와 상관 없이, 파티션 짝 별로 작은 쪽은 Build Input으로 선택하여 해시 테이블(해시 맵) 생성

해당 sql의 경우 use_hash(e, c) 힌트로 해시 조인을 유도 했다

이때 use_hash만 사용했으므로 Build Input은 옵티마이저에 의해 결정된다

일반적으로 각 테이블에 대한 카디널리티를 비교하고 작은 테이블을 선택한다

Build Input을 직접 선택하고자 한다면 ordered 나 reading 힌트를 사용하면 된다

3개 이상의 테이블을 해시 조인 하는 경우

Build Input을 선택한 상황에서 순서를 바꾸고 싶을 때는 swap_join_inputs 힌트를 이용하여 순서를 바꿀 수 있다

swap_join_inputs : Build Input으로 지정하는 힌트

no_swap_join_inputs : Probe로 지정하는 힌트

조인 메소드 선택 기준

소량 데이터 조인 ⇒ NL 조인(use_nl)

대량 데이터 조인 ⇒ 해시 조인(use_hash)

대량 데이터 조인이지만 해시 조인으로 처리 불가능 할 때(조건식이 등치 조건이 아닐 때) ⇒ 소트 머지 조인(use_merge)

수행 빈도가 매우 높은 쿼리에 대한 기준

(최적화된) NL 조인과 해시 조인 성능이 같으면 ⇒ NL 조인
해시 조인이 약간 더 빨라도 NL 조인
NL 조인보다 해시 조인이 매우 빠른 경우 ⇒ 해시 조인

NL 조인을 먼저 고려해야 하는 이유

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

해시 조인 사용 조건

수행 빈도가 낮고
쿼리 수행 시간이 오리 걸리는
대량 데이터 조인할 때

⇒ 배치, DW, OLAP성 쿼리 특징

서브쿼리 조인

서브쿼리 변환

쿼리 변환은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서 더 나은 성능이 기대되는 형태로 재작성하는 것

서브쿼리란

하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록

스칼라 서브쿼리 : 한 레코드당 하나의 값을 반환하는 서브쿼리. 주로 SELECT-LIST에서 사용하지만 몇 가지 예외 사항을 제외하면 컬럼이 올 수 있는 대부분의 위치에 사용 가능
인라인 뷰 : FROM 절에 사용한 서브쿼리
중첩된 서브쿼리 : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리. 특히, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 “상관관계 있는 서브쿼리” 라고 한다.

오라클에서는 3가지로 분류 된다.