Post

조인 튜닝

Posted Updated
By da9dac
42 min read
조인 튜닝

NL 조인

인덱스를 이용한 조인이기 때문에, 인덱스 원리를 이해하고 있다면 쉽다고 한다.
과연 정말 쉬운지 한 번 읽으면서 정리해보자…
(사실 이미 머리에서 1~3장의 내용이 흐릿하다…)

기본 메커니즘

첫 번째 조건을 만족하는 데이터들을 찾은 후, 각 데이터마다 다른 테이블을 한 번씩 살펴보며 다음 조건과 일치하는 것을 찾는 것을 반복하는 방식이다.

일반적으로, Outer(Driving)와 Inner(Driven) 양쪽 테이블 모두 인덱스를 이용하고, Outer 테이블이 크지 않다면 인덱스를 이용하지 않고 Table Full Scna을 사용할 수도 있다. 하지만, Inner 테이블은 인덱스를 사용해야만 한다.

이유는 Outer 루프는 한 번만 스캔이 일어나지만, Inner 루프는 Outer 루프 결과의 수만큼 반복해야 하기 때문에 인덱스를 이용하지 않고 매번 테이블 전체를 스캔한다면 엄청난 I/O가 발생하기 때문이다.

실행 순서는 아래와 같다.

  1. Outer 루프 조건에 해당하는 건을 인덱스에서 찾는다.
  2. 인덱스로 테이블의 레코드를 찾아간다.
  3. 테이블에서 가져온 데이터로 Inner 루프 조건에 해당하는 건을 인덱스에서 찾는다.
  4. 인덱스로 테이블의 레코드를 찾아간다.
  5. Inner 루프를 조건과 불일치할 때까지 반복한다.
  6. Outer 조인을 반복한다.

소트 머지 조인과 해시 조인도 Sort Area나 Hash Area에 가공해 둔 데이터를 사용한다는 점만 다르고, 기본적인 조인 프로세싱은 다르지 않다.

NL 조인 실행계획 제어

매커니즘에서 배웠던 것처럼 실행계획을 살펴보면 위쪽 테이블 기준으로 아래쪽 테이블과 NL 조인을 하고, 테이블 액세스 시 인덱스를 이용하는 것을 확인할 수 있다.

use_nl(A) 힌트를 사용해 A와 조인할 때는 NL 조인을 사용하라고 지정할 수 있다.
ordered 힌트를 사용해 from 절에 명시한 순서대로 조인을 수행하게 할 수 있다.
leading(C, A, D, B) 힌트를 사용해 from 절과 상관없이 조인 순서를 정할 수 있다.
ordered나 leading 힌트를 사용하지 않는 경우에는 옵티마이저 스스로 순서를 정한다.

NL 조인 수행 과정 분석

NL 조인 시 조건절은 적힌 순서대로 실행되는 것이 아니라, Outer 테이블의 조건부터 처리한 후에 Inner 테이블의 조건을 처리하게 되고, 인덱스는 각 테이블의 첫 조건에 해당하는 인덱스를 사용한다.

NL 조인 튜닝 포인트

Outer 테이블의 인덱스를 읽고 나서 테이블에 액세스 할 때, 많은 양의 랜덤 액세스가 발생했고, 후행 조건으로 필터링을 많이 할 수 있어 랜덤 액세스를 줄일 수 있다면 해당 컬럼을 인덱스에 추가하는 방안을 고려할 수도 있다.

Ineer 인덱스를 탐색하는 횟수는 조인 액세스 횟수와 비례해서 증가하고, 당연히 탐색 횟수가 많을수록 성능이 느려진다. 조인 액세스 횟수는 결국 Outer의 조건에서 결정되는 것이기 때문에 적절한 조건을 통해 필터링을 해줘서 줄여야 한다.

올바른 조인 메소드 선택

OLTP 환경에서 튜닝할 때는 우선적으로 NL 조인을 고려하는 것이 일반적인 순서인데, 성능이 느린 쿼리가 있다면, 각 단계의 수행 일량을 분석에 과도한 랜덤 액세스가 발생하는 지점을 파악한 후, 조인 순서 변경 혹은 더 효율적인 인덱스를 통해 랜덤 액세스 발생량을 줄일 방법을 생각해야 한다.

여러 방안을 검토해봐도 NL 조인으로 성능을 낼 수 없다면, 그때 가서 소트 머지 조인이나 해시 조인을 검토해야 한다.

NL 조인 특징 요약

  1. 랜덤 액세스 위주의 조인 방식이기 때문에 대량 데이터 조인 시 NL 조인은 불리할 수 밖에 없다.
  2. 조인을 한 레코드씩 순차적으로 진행해 아무리 큰 테이블과 조인해도 매우 빠른 응답 속도를 낼 수 있다. 즉, 한 줄씩 순차적이라 부분 범위 처리가 가능해, 필요한 데이터만 빠르게 검색이 가능하다.
  3. 인덱스 구성 전략에 따라 조인 효율이 크게 달라진다.

그래서 소량 데이터를 처리하거나 부분범위 처리가 가능한 OLTP 시스템에 적합한 조인이다.

NL 조인 튜닝 실습

튜닝 시 Outer 조건에서 불필요한 테이블 액세스를 줄이는 것도 중요하지만, 불필요한 액세스를 줄인 후에도 블록 요청 횟수도 살펴봐서 인덱스에서 필요한 레코드를 얻기 위해 읽은 총 레코드 수를 줄이는 것도 중요하다.

테이블 액세스와 읽는 레코드 수까지 모두 줄였다면, Outer가 Inner 보다 작은지 확인한 후 그렇지 않다면 조인 순서를 변경하는 것을 고려해볼 수도 있다. 조인 순서를 변경해도 별 차이가 없다면 소트 머지 조인과 해시 조인을 검토해봐야 한다.

NL 조인 확장 메커니즘

테이블 Prefetch
인덱스를 이용해 테이블을 액세스하다 디스크 I/O가 필요하면, 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다.

배치 I/O
디스크 I/O Call을 쌓아두고, 일정량 쌓이면 한 번에 처리하는 기능이다.

NL 조인 시 Inner 테이블을 미리 액세스 해서 읽은 후 버퍼 캐시에 적재하거나, Inner 테이블 액세스 시 배치 I/O를 사용해 콜 횟수를 줄이는 식으로 사용된다.

Inner 테이블을 모두 버퍼캐시에서 읽게 된다면 성능에 차이가 없고, 데이터 출력 순서도 같지만, 일부를 디스크에서 읽게 된다면 성능 차이가 나타날 수 있다. 또한, 배치 I/O는 정렬 순서를 보장하지 않기 때문에 주의해야 한다.

배치 I/O를 사용해도 Outer 선에서 정렬을 다룬다면 정렬은 유지된다. Inner에 대해서만 정렬이 되지 않을 뿐이다.

NL 조인 자가 진단

1
2
3
4
5
6
7
8
9
10

-- 인덱스 PRA_HST_STC_N1 : SALE_ORG_ID + STRD_GRP_ID + STRD_ID + STC_DT
-- Outer : a
-- Inner : b
select *
	FROM PRA_HST_STC a, ODM_TRMS b
WHERE 
	a.SALE_ORG_ID = :sale_org_id
	AND b.STRD_GR_ID = a.STRD_GRP_ID
	AND b.STRD_ID = a.STRD_ID
ORDER BY a.STC_DT desc

Outer 테이블인 PRA_HST_STC에서 조건에 사용되는 컬럼은 SALE_ORG_ID뿐이고 정렬에 STC_DT 컬럼이 사용된다. 따라서 SALE_ORG_ID + STC_DT 로 인덱스를 구성해줘야 불필요한 컬럼을 인덱스에서 제외하고, 정렬 연산을 생략할 수 있다.

소트 머지 조인

조인 컬럼에 인덱스가 없는 경우, 대량 데이터 조인으로 인해 인덱스가 효율적이지 않은 경우에 옵티마이저는 NL 조인이 아닌 소트 머지 조인 혹은 해시 조인을 선택한다.

SGA vs. PGA

SGA에 캐시된 데이터는 여러 프로세스가 공유하지만, 락으로 인해 동시에 액세스할 수는 없다. 마찬가지로 DB 버퍼 캐시에서도 블록을 읽으려면 버퍼 락을 얻어야 한다.

PGA는 각 오라클 서버 프로세스에 할당된 고유 메모리 영역으로, 프로세스에 종속적인 고유 데이터를 저장한다. 다른 프로세스와 공유하지 않기 때문에 래치 메커니즘이 불필요해 SGA의 버퍼캐시보다 빠르게 데이터를 읽을 수 있다. 메모리 공간이 부족하다면 Temp 테이블 스페이스를 사용한다.

기본 메커니즘

이름 그대로 소트와 머지 두 단계로 진행된다.

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

NL 조인 기준으로 순서를 정리해보자

  1. Outer 조건에 해당하는 데이터를 읽는다.
  2. 읽은 결과집합을 조인 컬럼 기준으로 정렬한다.
  3. 정렬한 결과집합을 PGA의 Sort Area에 저장한다.
  4. PGA에 담을 수 없는 크기라면 Temp 테이블스페이스에 저장한다.
  5. Inner 조건에 해당하는 데이터를 읽는다.
  6. 읽은 결과집합을 조인 컬럼 기준으로 정렬한다.
  7. 정렬한 결과집합을 PGA의 Sort Area에 저장한다.
  8. PGA에 담을 수 없는 크기라면 Temp 테이블스페이스에 저장한다.
  9. PGA 혹은 Temp 테이블스페이스에 저장한 Outer 데이터를 스캔하면서 Inner 데이터와 조인한다.

주목할 점은 Outer를 기준으로 매번 Inner를 전체 스캔하지 않는 것인데, 이는 Inner 데이터가 정렬되어 있기 때문에 조인 대상 레코드의 시작점과 종료점을 쉽게 찾을 수 있기 때문이다.

즉, Sort Area에 저장한 데이터 자체가 인덱스 역할을 수행하기 때문에 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다. 조인 컬럼에 인덱스가 있어도 대량 데이터 조인 시에는 NL 조인보다 소트 머지 조인이 유리하다.

소트 머지 조인이 빠른 이유

NL 조인은 인덱스를 이용한 조인이기 때문에 랜덤 액세스로 인해 대량 데이터 조인시 피할 수 없는 성능 저하가 발생한다. 반면에 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장한 후에 조인한다. 또한, PGA는 프로세스별로 독립적인 공간이기에 데이터를 읽을 때 래치 획득 과정이 없기에 더욱 빠르다.

뮬론, 소트 머지 조인도 처음 양쪽 테이블로부터 조인 대상 집합을 읽을 때는 DB 버퍼캐시를 경유해 NL 조인에서 발생하는 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 피할 수 없다.

소트 머지 조인의 주용도

이후에 등장한 해시 조인이 대부분의 경우 더 빠르기 때문에 잘 사용되지는 않지만, 해시 조인은 조건이 등치 조건이 아니면 사용할 수 없다는 단점이 있다. 그래서 소트 머지 조인은 주로 조건이 등치 조건이 아닌 대량 데이터 조인이나, 조인 조건식이 아예 없는 크로스 조인에 사용된다.

소트 머지 조인 특징 요약

조인을 위해 PGA 영역에 정렬을 수행 한 결과집합을 저장하고, 이를 사용해 조인을 한다. 따라서 초기 결과집합 조회를 위한 액세스를 제외하면, 조인 시 발생하는 액세스는 모두 PGA를 경유해 NL 조인보다 빠르다.

또한, 조인 컬럼의 인덱스 유무에 영향을 받지 않고, 양쪽 집합을 개별적으로 읽고 조인을 시작한다. 그래서 조인 컬럼에 인덱스가 없고, 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 효율적이다.

스캔 위주의 액세스 방식을 사용하지만, 모든 처리가 스캔 방식을 사용하지는 않고, 조인 대상 레코드를 찾을 때 인덱스를 이용할 수 있고, 그때는 랜덤 액세스가 발생하기도 한다.

해시 조인

해시 조인은 소트 머지 조인과 다르게 양쪽 테이블을 정렬할 필요가 없다. 하지만 모든 조인을 처리할 수 없기에 상황에 맞게 조인 방식을 선택할 줄 알아야 한다.

기본 메커니즘

Build + Probe 두 단계로 진행된다.

  1. Build : 작은 쪽(Build Input, Outer) 테이블을 읽어 해시 테이블을 생성
  2. Probe : 큰 쪽(Probe Input, Inner) 테이블을 읽어 해시 테이블을 탐색하며 조인

마찬가지로, NL 조인 기준으로 순서를 정리해보자

  1. Outer 조건을 만족하는 데이터를 읽어 해시 테이블을 생성한다.
  2. 조인컬럼을 해시 테이블 키 값으로 사용한다.
  3. PGA의 Hash Area에 해시 테이블이 저장된다. (혹은 Temp 테이블스페이스)
  4. Inner 조건을 만족하는 데이터를 하나씩 읽어 해시 테이블을 탐색한다.
  5. 조인컬럼을 해시 함수에 입력한다.
  6. 반환된 값으로 해시 체인을 찾는다.
  7. 해시 체인을 스캔해 값이 같은 데이터를 찾는다.
  8. 찾으면 조인 성공, 못찾으면 실패다.

해시 조인이 빠른 이유

해시 조인도 소트 머지 조인과 마찬가지로 PGA에 해시 테이블을 할당하기 때문에 래치 획득 과정 없이 탐색 및 조인이 가능해 빠르다.

해시 테이블에는 조인 키값만 저장하는 것은 아니고, SQL에 사용한 컬럼을 모두 저장한다. 키값만 저장한다면 나머지 데이터를 가져오기 위해 테이블에 액세스 해야해서 NL 조인과 다를게 없다.

해시 조인이 소트 머지 조인보다 빠른 이유는 조인 오퍼레이션 실행 전 양쪽 집합을 모두 정렬해 PGA 담는 것이 아닌, 양쪽 중 더 작은 집합으로 해시 맵을 만들어 한쪽만 넣기 때문에 공간이 부족해 Temp 테이블스페이스, 즉 디스크에 쓰는 작업이 발생할 일이 없기 때문이다.

Build Input이 너무 커서 Hash Area 크기를 초과하더라도 대량 데이터 조인 시에는 일반적으로 해시 조인이 가장 빠르기 때문에 다른 조인 메서드를 선택할 필요는 없다.

대용량 Build Input 처리

조인 대상이 모두 대용량 테이블이라 인메모리 해시 조인이 불가능한 경우 DBMS는 분할 정복 방식으로 해시 조인을 처리한다.

  1. 파티션 단계
    1. 양쪽 집합을 읽어 조인 컬럼에 해시 함수를 적용한다.
    2. 해시 값에 따라 동적으로 파티셔닝한다.
    3. 즉, 같은 해시값끼리 같은 집합과 페어로 파티셔닝한다.
  2. 조인 단계
    1. 페어에 대해 하나씩 조인을 수행한다.
    2. 파티션 전 테이블 크기와 상관없이 페어별로 작은 쪽은 Build Input으로 선택한다.
    3. 해시 테이블을 생성한다.
    4. 반대쪽 파티션 로우를 하나씩 읽으면서 모든 페어에 대한 처리를 마칠 때까지 반복한다.

해시 조인 실행계획 제어

use_hash 힌트를 사용해 해시 조인 실행계획을 제어할 수 있고, 해당 힌트만 사용하면 옵티마이저가 둘 중 카디널리티가 작은 테이블을 선택한다. 직접 선택하고 싶은 경우에는 마찬가지로 leading 이나 ordered 힌트를 사용하면 되고, swap_join_inputs 힌트로 명시적으로 선택할 수도 있다.

세 개 이상 테이블 해시 조인

중간 테이블을 어디로 보는지에 따라 경로가 여러 개가 되지만 결국은 하나의 경로로 표현할 수 있다. leading 힌트를 사용해 조인 순서를 잘 설정해주면 된다. 이때 힌트에 사용된 첫 번째 테이블은 무조건 Build Input으로 선택되기에, 이를 바꾸고 싶다면 swap_join_inputs 힌트를 사용해야 한다. 만약에 조인 결과집합을 Build Input으로 선택하고 싶다면, no_swap_join_inputs 힌트를 사용하면 된다.

조인하는 테이블이 몇 개든 상관 없이, 조인 연결고리를 따라 순방향 혹은 역방향으로 leading 힌트를 사용해주고, Build Input 테이블 선택을 위해 swap_join_inputs와 no_swap_join_inputs 힌트를 사용하면 된다.

조인 메소드 선택 기준

소량과 대량의 기준은 조인 방식 기준으로 최적화를 했음에도 불구하고 랜덤 액세스가 많아 만족할만한 성능을 내지 못하는 경우로 구분한다.

  1. 소량 데이터 조인 → NL 조인
  2. 대량 데이터 조인 → 해시 조인
  3. 해시 조인으로 처리 불가한 대량 데이터 조인 → 소트 머지 조인
  4. 최적화된 NL 조인과 해시 조인 성능이 같은 경우 → NL 조인
  5. 해시 조인이 조금 더 빠른 경우 → NL 조인
  6. NL 조인보다 해시 조인이 훨씬 빠른 경우 → 해시 조인

결국은 항상 NL 조인부터 고려해보라는 뜻으로, NL 조인에서 사용하는 인덱스는 영구적으로 유지하면서 공유 및 재사용되지만, 해시 테이블 같은 경우는 단 하나의 쿼리를 위해 생기고 바로 소멸된다.

그래서 해시 조인은 수행 빈도가 낮고, 쿼리 수행 시간이 오래 걸리고, 대량 데이터 조인 시에만 사용하는 것이 좋고, 이는 배치 프로그램과 DW, OLAP성 쿼리의 특징과도 같다.

서브쿼리 조인

실무에서는 서브쿼리와 조인하는 경우도 많기 때문에 옵티마이저가 이를 어떻게 처리하는지 이해하고, 이러한 실행계획을 제어할 수 있는 방법을 알아야 한다.

서브쿼리 변환이 필요한 이유

같은 결과집합을 다양한 SQL로 표현할 수 있기 때문에 옵티마이저는 전달받은 SQL을 의미적으로 동일하면서 더 나은 쿼리로 바꾸는 쿼리 변환 작업을 먼저 수행하고 비용 평가와 실행계획 생성을 수행한다.

오라클은 서브쿼리를 인라인 뷰, 중첩된 서브쿼리, 스칼라 서브쿼리로 분류한다.

  • 인라인 뷰 : FROM 절에 사용한 서브쿼리
  • 중첩된 서브쿼리 : WHERE 절에 사용한 서브쿼리
  • 스칼라 서브쿼리 : 레코드 당 하나의 값만 반환하는 서브쿼리

옵티마이저는 쿼리 블록 단위로 최적화를 수행하고, 메인 쿼리도 하나의 쿼리 블록이기에 메인 쿼리 한 개와 서브쿼리의 수를 더한 만큼의 쿼리 블록이 최적화된다. 물론 각 쿼리별로 최적화를 진행해도 전체적으로 최적화된 것은 아니고, 옵티마이저가 각각의 쿼리가 아닌 전체 쿼리를 바라보는 관점에서 쿼리를 이해할 수 있게 먼저 서브쿼리를 풀어야 한다.

서브쿼리와 조인

메인쿼리와 서브쿼리는 부모와 자식 형태의 종속적이고 계층적인 관계가 존재한다. 즉, 서브쿼리는 메인쿼리에 종속적이라 단독으로 실행할 수 없고, 메인쿼리 수만큼 값을 받아 반복적으로 실행해야 한다.

필터 오퍼레이션

no_unnest 힌트를 사용해 서브쿼리를 풀어내지 않고 그대로 수행하도록 할 수 있어서 서브 쿼리를 필터 방식으로 처리할 수 있다. NL 조인과 같은 처리 루틴으로 수행되고, 부분범위 처리도 가능하다.

NL 조인과의 차이점은 다음과 같다.

  1. 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 다음 로우로 넘어간다. 이는, 1:1 매칭을 통해 중복 데이터를 방지하기 위함이다. (EXIST의 경우 일치하는 행 하나의 여부만 필요한데, 굳이 하나를 찾았는데, 나머지가 더 있든 없든은 중요하지 않기 때문이다.)
  2. 필터 처리한 결과인 서브쿼리 입력 값에 따른 반환 값을 캐싱하기 때문에 서브쿼리 수행 전에 캐시를 확인 후에 없는 경우만 수행한다. 이러한 캐싱은 쿼리 단위로 이루어지고, PGA 메모리에 저장된다.
  3. 메인쿼리에 종속되어 조인 순서가 항상 메인쿼리가 드라이빙 집합으로 고정된다.

서브쿼리 Unnesting

옵티마이저가 일반적으로 Unnesting 방식을 선택하지만, 명시적으로 unnest 힌트를 사용할 수도 있다. 이는 메인과 서브쿼리 간의 계층구조를 풀어 같은 레벨로 만드는 방식이다.

서브쿼리의 중첩을 풀지 않으면 필터 방식으로만 사용가능하지만, 풀고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다. 즉, 필터 방식은 항상 메인쿼리가 드라이빙 집합이지만, 중첩을 풀고 나면 서브쿼리를 먼저 처리할 수도 있고, 다양한 힌트와 조인을 사용할 수 있다.

ROWNUM 사용 시 주의사항

잘 사용하면 쿼리 성능을 높일 수도 있지만, 쿼리 성능을 떨어뜨릴 수도 있다. EXIST와 ROWNUM을 같이 사용하면 의미의 중복이라 아무런 영향은 없을 수 있다고 생각할 수 있지만 그렇지 않다. 서브쿼리에 ROWNUM을 사용하면 옵티마이저가 해당 서브쿼리 블록의 중첩을 풀 수 없게 만든다.

그래서 중첩을 풀어 더 좋은 실행경로를 찾을 가능성이 사라지기에 Unnesting을 방지하려는 목적이 아니라면 서브쿼리에 함부로 사용하지 않는 것이 좋다.

서브쿼리 Pushing

Unnesting 되지 않은 서브쿼리는 항상 필터 방식으로 처리되고, 대부분 실행계획에서 마지막 단계에 처리된다. 하지만 서브쿼리 필터링을 먼저 처리해서 많은 필터링을 할 수 있다면 성능을 향상시킬 수 있는데, 이를 위해 push_subq 힌트를 사용할 수 있다.

해당 힌트는 서브쿼리 필터링을 가능한 앞 단계에서 처리할 수 있게 강제할 수 있고, Unnesting 되지 않은 서브쿼리에만 작동한다. Unnesting 되는 순간 다양한 조인 방식으로 실행되기 때문에 해당 힌트가 무용지물이라 no_unnest 힌트와 같이 사용해야 한다.

반대로 서브쿼리 필터링을 가능한 나중에 처리하게 하고 싶다면 no_push_subq 힌트를 사용하면 된다.

뷰와 조인

최적화 단위는 쿼리 블록이기에 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다. 이러면 인라인 뷰의 경우에는 메인쿼리의 조건과 상관없이 그 전에 뷰 쿼리 블록이 독립적으로 최적화되어 버려 비효율적으로 쿼리가 실행될 수 있다.

이러한 문제를 merge 힌트를 사용해 뷰와 메인쿼리를 병합해서 메인쿼리 블록에 포함시키면 해결할 수 있다. 메인쿼리의 조건을 포함해서 처리할 수 있기 때문에 불필요한 데이터 처리를 줄일 수 있다.

조인 조건 Pushdown

메인 쿼리를 실행하며 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 쿼리 변환 기능이다. push_pred 힌트를 사용하면 되고, 실행계획에는 VIEW PUSHED PREDICATE 오퍼레이션으로 표시된다.

무슨 말인지 이해가 잘 안될 수도 있는데, 조인 후에 조건을 수행하는 것이 아니라, 조인 전에 미리 조인 조건을 수행해 조인되는 데이터량을 줄일 수가 있어진다고 생각하면 된다.

뷰를 독립적으로 실행할 때처럼 모두 읽을 필요도 없고, 뷰를 머징할 때처럼 조인 결과 집합을 Group By 할 필요도 없다. 중간에 멈출 수도 있어서 부분범위 처리가 가능하기 때문이다.

마찬가지로 뷰가 머징되면 사용할 수 없기 때문에 힌트 사용 시 no_merge 힌트를 함께 사용해야만 한다.

Lateral 인라인 뷰, Cross/Outer Apply 조인
  • 오라클 12c부터 인라인 뷰를 Lateral로 선언하면 뷰 안에서 메인쿼리 테이블 컬럼을 참조할 수 있어졌다.
  • Cross 혹은 Outer Apply를 사용해 인라인 뷰에 조인 구문을 사용할 수도 있다.
  • 조인 조건 Pushdown으로도 충분하기 때문에 굳이 사용할 필요는 없는 기능들이다.
  • 튜닝 과정에서 알 수 없는 이유로 Pushdown이 잘 작동되지 않는 경우에만 활용하는 것이 좋다.

스칼라 서브쿼리 조인

스칼라 서브쿼리의 특징

함수는 메인쿼리의 건수만큼 재귀적으로 반복 실행되지만, 스칼라 서브쿼리는 메인쿼리 건수만큼 테이블을 반복해서 읽지만 재귀적으로 실행하지는 않는다. 그래서 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 하나처럼 실행한다.

스칼라 서브쿼리는 Outer 조인문처럼 NL 조인 방식으로 실행된다고 생각하면 되고, 조인 실패 시에도 똑같이 NULL을 출력한다. NL 조인과 차이가 있다면 처리 과정에서 캐싱 작용이 있다는 것이다.

스칼라 서브쿼리 캐싱 효과

오라클은 스칼라 서브쿼리 조인 시 조인 횟수 최소화를 위해 입출력값을 내부 캐시에 저장하고, 조인 때마다 캐시를 확인 후 없을 때만 조인을 수행한다. 앞에서 살펴본 필터 서브쿼리 캐싱과 같은 기능이다.

그래서 함수에 스칼라 서브쿼리를 덧씌우면 캐싱 효과를 사용해 함수 호출 횟수를 최소화 할 수 있기에, 사용자 정의 함수 사용 시 스칼라 서브쿼리를 사용해 간편하게 최적화가 가능하다.

스칼라 서브쿼리 캐싱 부작용

스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 적어 해시 충돌 가능성이 적을 때 효과적이고, 그렇지 않다면 캐시를 매번 확인해야 해서 오히려 CPU와 메모리 성능적으로 좋지 않다.

그리고 결국 서브쿼리 캐싱은 쿼리 단위로 사용하고 버리기 때문에 메인쿼리 집합이 클수록 캐싱을 많이 사용하여 효율이 높지만, 집합이 작다면 캐시 재사용성이 낮다.

두 개 이상의 값 반환

스칼라 서브쿼리는 NL 조인처럼 동작하고 캐싱 효과도 있지만 두 개 이상의 값을 반환할 수 없다는 제약이 있다. 그렇다고 같은 데이터를 반복해서 읽는 것은 비효율적이기에 문자열로 결합 후에 바깥쪽 액세스 쿼리에서 substr 함수로 분리하면 여러 값을 가져올 수 있다.

스칼라 서브쿼리 대신 인라인 뷰를 사용하는 방법도 있는데, 11g 이후 버전이라면 조인 조건 Pushdown 기능 때문에 인라인 뷰를 사용해도 괜찮다.

스칼라 서브쿼리 Unnesting

NL 방식으로 조인하기에 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있기에, Unnesting 해서 다른 조인 방식을 선택할 수 있으면 좋다. 또한 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이기 때문에 가급적이면 병렬 쿼리에선 스칼라 서브쿼리를 사용하지 않는 것이 좋다.

오라클 12c부터 스칼라 서브쿼리도 Unnesting이 가능해졌지만, 오라클 업데이트 시 기존 실행 계획의 변경으로 인해 예상치 못한 상황이 발생할 수 있으니 일단은 옵션을 꺼서 옵티마이저가 선택하는 것이 아니라 사용자가 직접 선택하게 해야한다.

Book, 친절한 SQL 튜닝
SQL DB Oracle
This post is licensed under CC BY 4.0 by the author.