4.2 소트 머지 조인
- 조인 컬럼에 인덱스가 없을 때
- 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때
4.2.1 SGA vs. PGA
SGA는 캐시된 데이터가 들어가있는 공유 메모리 영역
여러 프로세스가 공유할 수 있지만, 동시에 액세스할 수 없다.
래치(Latch) : 동시에 액세스하려는 프로세스 간 액세스를 직렬하기 위한 Lock 메커니즘
오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역인 PGA를 갖고 있다. 할당 받은 PGA 공간이 부족할 경우 Temp 테이블 스페이스를 활용한다.
PGA는 독립적인 메모리 공간이므로, 래치가 불필요하기 때문에 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.
4.2.2 기본 메커니즘
- 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
- 조건에 해당하는 데이터를 읽어 조인컬럼 순으로 정렬 후 PGA 영역의 Sort Area(부족할 경우 Temp 테이블)에 저장
- 머지 단계 : 정렬한 양쪽 집합을 서로 머지(Merge)
- PGA에 저장한 데이터를 스캔하면서 조인
머지 단계는 NL 조인과 다르지 않다.
Sort 되어 있기 때문에 매번 Full Scan 하지 않을 수 있다.
Sort Area에 저장한 데이터 자체가 인덱스 역할이므로 조인 컬럼에 인덱스가 없어도 사용할 수 있다.
4.2.3 소트 머지 조인이 빠른 이유
NL 조인이 대량 데이터 조인에서 불리한 이유 : NL 조인은 단적으로 말해 ‘인덱스를 이용한 조인 방식’이다. 조인 과정에서 액세스하는 모든 블록을 랜덤 액세스 방식으로 ‘건건이’ DB 버퍼 캐시를 경유해서 읽는다. 즉, 인덱스든 테이블이든 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다. 버퍼캐시에서 찾지 못한 블록은 ‘건건이’ 디스크에서 읽어 들인다. 인덱스 손익분기점 한계를 그대로 드러낸다.
반면, 소트머지 조인은 양쪽 테이블로부터 조인 대상 집합을 ‘일괄적으로’ 읽어 PGA에 저장한 후 조인한다. PGA는 독립적인 메모리 공간이므로 래치 획득 과정이 없기 때문에 대량 데이터 조인에 유리하다.
4.2.4 소트 머지 조인의 주용도
대량 데이터 처리에서 대부분의 경우 해시 조인이 더 빠르다. 하지만, 해시 조인은 조인 조건식이 등치(=) 조건이 아닐 때 사용할 수 없다는 단점이 있다.
- 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
- 조인 조건식이 아예 없는 조인(Cross Join, 카테시안 곱)
4.2.5 소트 머지 조인 제어하기
/* ordered use_merge(c) */
/* leading(e) use_merge(c) */ordered(leading)는 FROM 절에 기술한 순서대로 조인하라고 지시하는 힌트다.
위에서는 ordered(leading)와 use_merge(c) 를 동시에 사용했으므로 양쪽 테이블을 조인 컬럼 순으로 각각 정렬한 후 정렬된 e를 기준으로 정렬된 c와 조인하라는 뜻이다.
4.2.6 소트 머지 조인 특징 요약
소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름 없다.
따라서 소트 부하만 감수한다면, 건건이 버퍼캐시를 경유하는 NL 조인보다 빠르다.
조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 매우 유리하다.
스캔 위주의 액세스 방식을 사용한다는 점도 중요한 특징이다. 가끔씩은 조인 대상 레코드를 찾는데 인덱스를 사용할 수 있고, 그 때는 랜덤 액세스가 일어난다.
🔹 Sort-Merge Join의 부하 예시
SELECT *
FROM big_a a
JOIN big_b b ON a.id = b.id;
-- 두 테이블 다 수백만 건, 정렬된 인덱스 없음- 옵티마이저가 Hash Join보다 SMJ를 선택했을 경우:
a.id,b.id를 모두 정렬해야 함 → PGA 메모리 압박- 메모리 부족 → TEMP 디스크로 Spill
- 결과: 소트 부하 발생
4.3 해시 조인
NL 조인은 인덱스를 아무리 완벽하게 구성해도 랜덤 I/O 때문에 대량 데이터 처리에 불리하고, 버퍼캐시 히트율에 따라 들쭉날쭉한 성능을 보인다. 소트 머지와 해시 조인은 조인 과정에서 인덱스를 사용하지 않기 때문에 대량 데이터 조인 시에 훨씬 빠르고, 일정한 성능을 보인다. 소트 머지 조인은 항상 양쪽 테이블을 정렬하는 부담을 보이는데, 해시 조인은 그런 부담도 없다.
4.3.1 기본 메커니즘
- Build 단계 : 작은 쪽 테이블(Build Input)을 읽어 해시 테이블(해시 맵) 생성
- 이 때, 조인컬럼을 해시 테이블 키 값으로 사용, 해시 체인에 데이터를 연결
- 해시 테이블은 PGA 영역에 할당된 Hash Area에 저장 (해시 테이블이 너무 크면 Temp 테이블)
- Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인
- 조인 컬럼을 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인을 스캔해서 값이 같은 컬럼 값을 찾는다.
실제 조인을 수행하는 Probe 단계는 NL 조인과 다르지 않다는 사실을 알 수 있다.
4.3.2 해시 조인이 빠른 이유
Hash Area에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 프로세싱 자체는 NL 조인과 같다. 그럼에도 빠른 결정적인 이유는 소트 머지 조인이 빠른 이유와 동일하게, 해시 테이블을 PGA 영역에 할당하기 때문이다.
해시 조인도 각 테이블을 읽을 때는 DB 버퍼캐시를 경유한다. 이 때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 해시 조인이라도 피할 수 없다.
그런데 대량 데이터를 조인할 때 소트 머지 조인보다 해시 조인이 더 빠른 이유는 무엇일까? 두 조인 메소드의 성능 차이는 사전 준비작업에 기인한다.
소트 머지 조인은 ‘양쪽’ 집합을 모두 정렬해서 PGA에 담는다. PGA는 그리 큰 메모리 공간이 아니므로 두 집합 중 어느 하나가 중대형 이상이면, Temp 테이블스페이스, 즉 디스크에 쓰는 작업을 반드시 수행한다.
해시 조인은 둘 중 작은 집합을 해시 맵 Build Input으로 선택하므로 두 집합 모두 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면, 디스크에 쓰는 작업은 전혀 일어나지 않는다. (In-Memory Hash Join)
설령 Temp 테이블스페이스를 쓰게 되더라도 소트머지처럼 양쪽 집합을 미리 정렬하는 부하도 없기에 일반적으로 해시 조인이 가장 빠르다.
4.3.3 대용량 Build Input 처리
두 테이블 모두 대용량이어서 인메모리 해시 조인이 불가능할 때, DBMS는 분할·정복 방식을 사용한다.
- 파티션 단계
- 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다.
- 조인 단계
- 각 파티션 짝에 대해 하나씩 조인을 수행한다. 각 파티션 짝별로 작은 쪽을 Build Input으로 선택하고 해시 테이블을 생성한다. 해시 테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다. 모든 파티션 짝에 대한 처리를 마칠 때까지 반복한다.
4.3.4 해시 조인 실행계획 제어
use_hash 힌트만 사용할 경우 Build Input을 옵티마이저가 선택하는데, 일반적으로 둘 중 (전체가 아닌 각 테이블 조건절에 대한) 카디널리티가 작은 테이블을 선택한다.
leading, ordered 힌트를 사용하여 가장 먼저 읽는 테이블을 Build Input을 지정할 수 있다.
swap_join_inputs 힌트를 통해 Build Input을 명시적으로 선택할 수도 있다.
세 개 이상의 테이블을 조인할 때도 마찬가지로 순서에 따라 leading 힌트를 지정하고, swap_join_inputs 힌트를 통해 Build Input을 조정하면 된다. Build Input으로 선택하고 싶은 테이블이 조인된 결과 집합이어서 힌트로 지정하기 어렵다면, no_swap_join_inputs 힌트를 통해 반대쪽 Probe Input을 선택해주면 된다.
4.3.5 조인 메소드 선택 기준
- 소량 데이터 조인할 때 → NL 조인
- 대량 데이터 조인할 때 → 해시 조인
- 대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때, 즉 조인 조건식이 등치(=) 조건이 아니거나 카테시안 곱일 때 → 소트 머지 조인
여기서 소량과 대량의 기준은 단순히 데이터량의 많고 적음이 아니라, NL 조인 기준으로 ‘최적화했는데도’ 랜덤 액세스가 많아 만족할만한 성능을 낼 수 없을 경우 대량 데이터 조인에 해당한다.
수행 빈도가 매우 높은 쿼리에 대해서는 아래와 같은 기준도 필요하다.
- (최적화된) NL 조인과 해시 조인 성능이 같으면, NL 조인
- 해시 조인이 약간 더 빨라도 NL 조인
- NL 조인보다 해시 조인이 매우 빠른 경우, 해시 조인
매우 빠른 경우는 대량 데이터 조인이라는 의미와 같다. SQL 최적화할 때 옵티마이저가 수행빈도까지 고려하지 않으므로 매우 중요한 선택 기준이다.
NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유하는 반면, 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다. 따라서 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리를 해시 조인으로 처리하면 CPU와 메모리 사용량이 크게 증가하며 래치 경합도 자주 발생한다.
결론적으로 해시 조인은 ① 수행 빈도가 낮고 ② 쿼리 수행 시간이 오래 걸리는 ③ 대량 데이터 조인할 때 사용해야한다. OLTP 환경에서 최적화된 NL 조인으로 0.1초 걸리는 쿼리를 0.01초로 단축할 목적으로 해시 조인을 쓰는 것은 가급적 자제하라는 뜻이다.
4.4 서브쿼리 조인
4.4.1 서브쿼리 변환이 필요한 이유
옵티마이저는 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 쿼리 변환부터 진행한다.
서브쿼리는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록을 말한다.
- 인라인 뷰(Inline View) : FROM 절에 사용한 서브쿼리
- 중첩된 서브쿼리(Nested Subquery) : WHERE 절에 사용한 서브쿼리. 특히 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 ‘상관관계 있는(Correlated) 서브쿼리’라고 부른다.
- 스칼라 서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리. 주로 SELECT-LIST에서 사용한다.
옵티마이저는 쿼리 블록 단위로 최적화를 수행한다. 서브쿼리별 최적화된 쿼리가 전체적으로도 최적화됐다고 말할 수는 없다.
4.4.2 서브쿼리와 조인
서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없다. 메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행해야 한다.
필터 오퍼레이션 : 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다.
서브쿼리 입력 값에 따른 반환 값(true/false)을 캐싱하여 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다. PGA 메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가며, 쿼리를 마치는 순간 공간을 반환한다.
필터 서브쿼리는 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.
서브쿼리 Unnesting : 메인과 서브쿼리 간의 계층구조를 풀어 같은 레벨(flat한 구조)로 만들어 준다.
서브쿼리를 그대로 두면 필터 방식을 사용할 수밖에 없지만, Unnesting 하고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.
NL 세미 조인은 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리하는 NL 조인이다. 캐싱 기능도 갖고 있으므로 필터 오퍼레이션과 큰 차이가 없다.
Unnesting된 서브쿼리는 NL 세미조인 외에도 다양한 방식으로 사용될 수 있다. 필터방식은 항상 메인쿼리가 드라이빙 집합이지만, Unnesting된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.
서브쿼리를 Unnesting 해서 메인쿼리와 같은 레벨로 만들면, 다양한 조인 메소드를 선택할 수 있고 조인 순서도 마음껏 정할 수 있다. 필터 오퍼레이션보다 더 좋은 실행경로를 찾을 가능성이 높아진다.
ROWNUM : 병렬쿼리나 서브쿼리에 rownum을 자주 사용하는데, 이는 의미의 중복이고(필터 오퍼레이션) 성능에 문제를 일으킬 수 있다. 서브쿼리에 rownum을 쓰면 옵티마이저에게 “이 서브쿼리 블록은 손대지 말라”고 선언하는 것이기 때문에, 서브쿼리 Unnesting 으로 더 좋은 실행경로를 찾을 가능성이 사라진다.
서브쿼리 Pushing : 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능이며, push_subq 힌트로 제어한다. 이 기능은 Unnesting 되지 않은 서브쿼리에만 작동한다. 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법이다.
4.4.3 뷰(View)와 조인
최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다. 문제는, 필터링 조건이 인라인 뷰 바깥에 있는데 인라인 뷰 안에서는 바깥의 데이터를 읽어야 하는 시점이다. 이럴 때 Merge 힌트를 이용해 뷰를 메인쿼리와 머징하여 문제를 해결할 수 있다.
단점은 인덱스를 이용한 NL 조인에서 성공한 전체 집합을 Group By 하고서야 데이터를 출력할 수 있다는 데 있다. 즉, 부분범위 처리가 불가능하다. 그런 상황에서는 해시 조인을 사용한다.
조인 조건 Pushdown : 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다. (11g) 실행계획의 VIEW PUSHED PREDICATE 오퍼레이션을 통해 이 기능의 동작 여부를 확인할 수 있다.
이 방식을 사용하면 ‘건건이’ 읽어서 조인하고 Group By를 수행한다. 즉, 부분범위 처리가 가능하다. 뷰를 독립적으로 실행할 때처럼 테이블을 모두 읽지 않아도 되고, 뷰를 머징할 때처럼 조인에 성공한 전체 집합을 Group By 하지 않아도 된다.
push_pred 힌트를 사용하며 옵티마이저가 뷰를 머징하면 힌트가 작동하지 않으니 no_merge 힌트를 함께 사용하는 습관이 필요하다.
4.4.4 스칼라 서브쿼리 조인
(1) 스칼라 서브쿼리의 특징
반복해서 읽는다는 측면에서 함수와 비슷해 보이지만, 함수처럼 ‘재귀적으로’ 실행하는 구조가 아니다. 컨텍스트 스위칭 없이 메인 쿼리와 서브쿼리를 한 몸체처럼 실행한다.
Outer 조인문처럼 NL 조인 방식으로 실행되지만, 처리 과정에서 캐싱 작용이 일어난다는 차이점이 있다.
(2) 스칼라 서브쿼리 캐싱 효과
스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하기 위해 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장해 둔다. 스칼라 서브쿼리의 입력 값은, 그 안에서 참조하는 메인 쿼리의 컬럼 값이다.
캐싱은 쿼리 단위로 이루어진다. 쿼리를 시작할 때 PGA 메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가며, 쿼리를 마치는 순간 공간을 반환한다.
SELECT-LIST에 사용하는 함수도 스칼라 서브쿼리를 덧씌우면 캐싱 효과로 호출 횟수를 최소화할 수 있다.
(3) 스칼라 서브쿼리 캐싱 부작용
캐시 공간은 늘 부족하다. 스칼라 서브쿼리에 사용하는 캐시도 매우 작은 메모리 공간이다.
스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다. 반대의 경우라면 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 사용률만 높게 만들고 메모리 공간 낭비도 심해진다.
메인 쿼리 집합이 매우 작은 경우도 캐싱이 성능에 도움을 주지 못한다. 스칼라 서브쿼리 캐싱은 쿼리 단위이기 대문에 메인쿼리 집합이 클수록 재사용성이 높아 효과도 크기 때문이다.
(4) 두 개 이상의 값 반환
스칼라 서브쿼리에는 두 개 이상의 값을 반환할 수 없다는 치명적인 제약이 있다. 부분범위 처리가 가능한 스칼라 서브쿼리의 장점을 이용하고 싶을 때 고민이 생기기 마련이다.
이러한 상황에서는 구하는 값들을 문자열로 모두 결합하고, 바깥쪽 액세스 쿼리에서 substr 함수로 다시 분리하여 문제를 해결할 수 있다. 인라인 뷰를 사용하면 편하긴 하지만, 뷰가 머징되지 않았을 때 테이블 전체를 읽어야하거나 머징될 때 Group By 때문에 부분범위 처리가 안 되는 문제가 있다. 다행히 11g 이후로는 조인 조건 Pushdown 기능이 잘 작동하므로 편하게 사용 가능하다.
(5) 스칼라 서브쿼리 Unnesting
스칼라 서브쿼리도 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다. 특히 병렬 쿼리에서는 대량 데이터를 처리하므로 해시 조인으로 처리해야 효과적이다.
오라클 12c부터 스칼라 서브쿼리도 Unnesting이 가능해졌다. _optimizer_unnest_scalar_sq 파라미터를 true로 설정하면, 스칼라 서브쿼리를 Unnesting 할지 여부를 옵티마이저가 결정한다.
