[DB] 4장. NL조인

1. NL조인

🔎 NL조인(Nested Loop)의 기본 메커니즘

SQL이 탄생하기 전에는 조인하는 프로그램을 개발자가 직접 작성해야했다. → JAVA 이중for문

<C, Java>
for(i=0; i< 100; i++){
	for(j=0; j< 100; j++){
    	//do
	}
}

<PL/SQL>
for outer in 1..100 loop
	for inner in 1..100 loop
    		dms_output_line(outer||':'||inner);
   	end loop;
end loop;

🔎 NL조인 ( Nested Loops Join )

인덱스를 이용한 조인
Outer, Inner 양쪽 테이블을 모두 사용한다.
Outer쪽 테이블은 Table Full Scan 하더라라도 한번만 거치기 때문에 인덱스가 필수적이지 않는다.
Inner쪽 테이블은 인덱스를 사용해야된다. 인덱스를 사용하지 않으면 Outer 루프에서 읽은 건수 만큼 Table Full Scan 반복 하기 때문이다. (그러므로 "NL조인은 인덱스를 사용한다"라고 한다.)

🔎 NL조인 실행계획 제어

⚙️ 해석 : 사원 테이블 기준으로 아래쪽 고객테이블과 NL조인했다.

Execution Plan
----------------------------------------------------------------
0      SELECT STATMENT Optimizer=ALL_ROWS
1   0	 NESTED LOOPS
2   1	  TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
3   2	   INDEX (RANGE SCAN) OF '사원_X1'	(INDEX)
4   3	  TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
5   4	    INDEX (RANGE SCAN) OF '고객_X1'	(INDEX)

⚙️use_nl과 ordered 사용하여 조인할때

use_nl 힌트 : NL조인방식으로 조인해라 (NL조인을 제어할때 사용)
ordered 힌트: from절에 기술한 순서대로 조인해라

SELECT /*+ ordered use_nl(c) */
    e.사원명, c.고객명, c.전화번호
FROM 사원 e, 고객 c
WHERE e.입사일자 >= '19960101' 
AND c.관리사원번호 = e.사원번호

해석 : 사원테이블기준으로 고객 테이블과 NL방식으로 조인해라

⚙️ 3개 이상 테이블을 조인할때

SELECT /*+ ordered use_nl(B) use_nl(C) use_hash(D) */
FROM A,B,C,B
WHERE ......

해석 : A → B → C → D 순으로 조인하되, B와 조인할때 , C와 조인할때는 NL방식으로 조인하고, D와 조인할때는 해시방식으로 조인해라

⚙️ 조인 순서를 자유롭게 하고싶을때

leading 힌트: from절을 바꾸지 않고도 마음껏 순서를 제어( Ordered 대신 leading 힌트 사용)

SELECT /*+ leading(C,A,D,B) use_nl(A) use_nl(D) use_hash(B) */
FROM A,B,C,B
WHERE ......

⚙️ NL조인하되, 옵티마이저에게 순서를 맡길때 ( Ordered, leading 기술X )

SELECT /*+ use_nl(A,B,C,D)*/
FROM A,B,C,B
WHERE ......

🔎 NL 조인 수행 분석 과정

힌트에 지시한 대로 수행할때, 아래의 조건절 비교순서는 어떻게 될까?

SELECT /*+ ordered use_nl(c) index(e) index(c) */
    e.사원번호, e.사원명, e.입사일자, c.고객번호, c.고객명, c.전화번호, c.최종주문금액
FROM 사원 e, 고객 c
WHERE c.관리사원번호 = e.사원번호 ----1
AND e.입사일자 >= '19960101'	----2
AND e.부서코드 = 'Z123'	----3
    AND c.최종주문금액 >= 20000 ----4
    

--인덱스 구성 
사원_PK : 사원번호
사원_X1: 입사일자
고객_PK : 고객변호
고객_X1: 관리사원번호
고객_X2 : 최종주문금액

위에 쿼리로 수행했을때 실행계획은 아래와 같으며, 사용되는 인덱스는 사원_X1, 고객X1이다.

----------------------------------------------------------------
 Id   Operation                         Name  Rows  Bytes  Cost
----------------------------------------------------------------
0  | SELECT STATMENT                 |    
1  |   NESTED LOOPS                  |
2  |   TABLE ACCESS BY INDEX ROWID   | 사원 
3  |    INDEX RANGE SCAN             | 사원_X1
4  |   TABLE ACCESS BY INDEX ROWID   | 고객 
5  |    INDEX RANGE SCAN             | 고객_X1
----------------------------------------------------------------

결과 : ② →③ → ① → ④

② : 입사일자 >= '19961010' 조건을 만족하는 레코드를 찾을려고 사원_X1 인덱스를 Range Scan
③ : 사원_X1 인덱스에서 읽은 ROWID로 사원테이블을 액세스해서 부서코드 = 'Z123'를 만족하는지 확인
① : 사원테이블에서 읽은 사원번호 값으로 조인 조건을 만족하는 고객쪽 레코드를 찾으려고 고객_X1 인덱스를 Range Scan
④ : 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스해서 최종주문금액 >= 20000 필터조건을 만족하는지 확인

✨ 중요한 포인트는 각 단계에서 모두 완료하고 다음 단계로 넘어가는 것이 아니라 한 레코드씩 순차적으로 진행한다는 것!

11,19,31,32는 스캔할 데이터가 더 있는지 확인
O는 테이블 필터 조건에 의해 레코드가 걸러지지 않은 것
X는 테이블 필터 조건에 의해 걸리진것

🚩 NL조인 튜닝 포인트

첫번째 튜닝 포인트 : 사원_X1 인덱스를 읽고 사원테이블을 액세스 하는 부분
- 단일 컬럼 인덱스를 '>='조건으로 스캔해서 비효율 없이 6건을 읽었고, 6개의 테이블 랜덤 액세스 발생했다.

Q. 만약 사원테이블로 아주 많은 양의 랜덤 액세스가 발생했고, 테이블에서 부서코드 'Z=123' 조건에 의해 필터링되는 비율이 높다면?
: 사원 _X1인덱스에 부서코드 컬럼을 추가하는 방안을 고려해야 한다.

두번째 튜닝 포인트 : 고객_X1 인덱스를 탐색하는 부분
- 고객_X1 인덱스를 탐색하는 횟수, 즉 액세스 횟수가 많을 수록 성능이 느려진다.
조인 액세스 횟수는 Outer 테이블인 사원을 읽고 필터링한 결과 건수에 의해 결정되며, 부서코드가 'Z=123' 조건을 만족하는 건수만큼 3번의 조인 시도가 있었다.

Q. 만약 부서코드 조건을 만족하는 레코드가 10만건이고, 고객_X1 인덱스 Depth가 3이라면?
: 인덱스 수직적 탐색과정에서만 30만개 블록을 읽어야하고, 리프 블록을 수평적으로 스캔하는 과정에서 추가적인 블록 I/O가 더해진다.

세번째 튜닝 포인트 : 고객_X1 인덱스를 읽고나서 고객 테이블을 액세스하는 부분
- 최종주문금액 >=20000 조건에 의해 필터링되는 비율이 높다면 고객_X1인덱스에 최종주문금액 컬럼을 추가하는 방안을 고려해야한다.

네번째 튜닝 포인트 : 맨 처음 액세스하는 사원_X1에서 얻은 결과건수에 의해 전체 일량이 좌우
- 사원_X1 인덱스를 스캔하면서 추출한 레코드가 많으면 사원테이블로 랜덤 액세스하는 횟수, 고객_X1 인덱스를 탐색하는 횟수, 고객테이블로 랜덤 액세스하를 횟수가 전반적으로 많아진다.

🚓 올바른 조인 메소드 선택

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

- 성능이 느리다면 과도한 랜덤 액세스가 발생하는 지점을 파악하고, 조인순서를 변경해서 랜덤 액세스 발생량을 줄일 수 있는지 인덱스를 검토한다. (더 효과적인 인덱스가 있는지, 인덱스를 추가 또는 인덱스 구성을 변경해야하는지)

- NL조인으로 좋은 성능을 내기 어렵다고 판단될때, 소트머지 조인이나 해시조인을 검토한다.

🔎 NL조인 특징

1. 랜덤 액세스 위주의 조인방식 사용한다.

대량 데이터 조인할때 불리하다.

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

부분범위 처리가 가능한 상황에서 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있다.

3. 다른 조인 방식과 비교할때 인덱스 구성전략이 중요하다.

> NL조인은 소량데이터를 처리하거나 부분범위처리가 가능한 OTLP 시스템에 적합한 조인방식이다.

🔎 NL 조인 확장 메커니즘

버전이 올라가면서 오라클은 NL조인 성능을 높이기 위해 테이블 Prefetch, 배치I/O 기능을 도입했으며, 두 기능은 읽는 블록마다 건건히 I/O Call을 발생시키는 비효율을 줄이기 위해 고안되었다.

테이블 Prefetch : 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 곧 읽게될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능
배치I/O : 디스크 I/O Call을 미뤘다가 읽은 블록이 일정량 쌓이면 한꺼번에 처리하는 기능

1. 전통적인 실행계획

오라클이 NL조인을 표현하기 위해 전통적으로 사용해온 방식

Rows Rows Source Operation
---- --------------------------
 5     NESTED LOOPS
 3       TABLE ACCESS BY INDEX ROWID OF 사원
 5         INDEX RANGE SCAN OF 사원_X1
 5        TABLE ACCESS BY INDEX ROWID OF 고객
 8         INDEX RANGE SCAN OF 고객_X1

2. 테이블 Prefetch 실행계획

오라클 9i부터 시작되었고, Inner쪽 테이블에 대한 디스크 I/O 과정에 테이블 Prefetch기능이 작동할 수 있음을 표시
nlj_porefetch, no_nlj_prefetch힌트를 이용해 해당 실행계획이 노출 여부를 결정

Rows Rows Source Operation
---- --------------------------
 5     TABLE ACCESS BY INDEX ROWID OF 고객
 12      NESTED LOOPS
 3         TABLE ACCESS BY INDEX ROWID OF 사원
 3             INDEX RANGE SCAN OF 사원_X1
 8           INDEX RANGE SCAN OF 고객_X1

3. 배치 I/O 실행계획

오라클 11g부터 시작되었고, Inner쪽 테이블에 대한 디스크 I/O과정에 배치 I/O기눙이 작동할 수 있음을 표시
nlj_batching, no_nlj_batching힌트를 이용해 해당 실행계획이 노출 여부를 결정

Rows Rows Source Operation
---- --------------------------
 5     NESTED LOOPS
 8       NESTED LOOPS
 3         TABLE ACCESS BY INDEX ROWID OF 사원
 3             INDEX RANGE SCAN OF 사원_X1
 8           INDEX RANGE SCAN OF 고객_X1
 5         TABLE ACCESS BY INDEX ROWID OF 고객

2. 소트머지조인

조인컬럼에 인덱스가 없을 경우 대량 데이터 조인이어서 인덱스가 효과적이지 않을때 옵티마이저는 소트머지조인이나 해시머신을 선택한다.

🔎 SGA vs PGA

SGA ( System Global Area 또는 Shared Global Area )
- 공유메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있지만, 동시에 액세스할 수 없다.
- 동시에 액세스하려면 프로세스 간 액세스를 직렬화하기위한 Lock메커니즘으로서 래치가 존재한다.
- 테이블 블록과 인덱스 블록을 캐싱하는 DB 버퍼캐시는 SGA의 가장 핵심적인 구성요소이고, 블록을 읽으려면 버퍼 Lock도 얻어야 한다.
- 오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서 동시에 자신만의 고유 메모리를 갖는다.

PGA
- 각 오라클 서버 프로세스에 할당된 메모리 영역이며, 프로세스에 종속적인 고유 데이토를 저장하는 용도로 사용한다.
- 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을때 Temp 테이블스페이스를 이용한다.
- PGA는 다른 프로세스와 공유하지 않는 독립적인 메모리 공간이므로 래치 메커니즘이 불필요하다.
- 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

🔎 소트머지 (Sort Merge Join) 의 기본메커니즘

1. 소트단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
2. 머지단계 : 정렬한 양쪽 집합을 서로 머지
⚙️ 해석 : 사원테이블을 기준으로 고객 테이블과 조인할때 소트머지조인방식을 사용하라
소트머지조인은 use_merge힌트 사용

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

위 SQL 수행과정을 풀면 아래와 같다.
1. 소트단계

아래 조건에 해당하는 사원 데이터를 읽어 조인컬럼인 사원번호 순으로 정렬한다.
정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다.
정렬한 결과집합이 PGA에 담을 수 없을 정도로 크면, Temp 테이블스페이스에 저장한다.

SELECT 사원번호, 사원명, 입사일자
FROM 사원
WHERE 입사일자 >= '19960101'	
AND e.부서코드 = 'Z123'	
ORDER BY 사원번호

2. 소트단계

아래 조건에 해당하는 고객 데이터를 읽어 조인컬림인 관리사원번호 순으로 정렬한다.
정렬한 결과집합은 PGA 영역에 할당된 Sort Area에 저장한다.
정렬한 결과집합이 PGA에 담을 수 없을 정도로 크면, Temp 테이블스페이스에 저장한다.

SELECT 고객번호, 고객명,전화번호, 최종주문금액
FROM 고객 
WHERE c.최종주문금액 >= 20000
ORDER BY 관리사원번호

3. 머지단계

PGA에 저장한 고객데이터와 조인한다.

✨ 주목할 사항은 사원데이터 기준으로 고객 데이터를 매번 Full Scan하지 않는다는 점이다.
고객데이터가 정렬되어 있으므로 조인 대상 레코드가 시작되는 지점을 쉽게 찾을 수 있고, 조인에 실패하는 레코드를 만나는 순간 바로 멈출 수 있다.

🔎 소트머지조인이 빠른 이유

NL조인은 대량데이터 조인할때 성능이 느리기때문에 소트머지조인과 해시조인이 개발되었으며, 인덱스를 이용한 조인방식으로 액세스하는 모든 블록을 랜덤액세스 방식으로 건건이 읽는다.
소트머지조인은 양쪽 테이블로부터 조인대상집합을 일괄적으로 읽어 PGA에 저장한 후 조인한다.
PGA는 프로세스만을 위한 독립적인 메모리공간이므로 데이터를 읽을때 래치 획득 과정이 없기에 대량 데이터 조인에 유리하다.

🔎 소트머지조인의 주 용도

해시조인이 소트 머지 조인보다 빠르지만, 조인 조건식이 등치 조건이 아니면 사용할 수 없다는 단점이 있다.
소트 머지 조인은 아래와 같은 상황에서 주로 사용한다.
- 조인 조건식이 등치(=)조건이 아닌 대량 데이터 조인
- 조인 조건식이 아예 없는 조인

🔎 소트머지조인 제어하기

아래는 소트 머지 조인 실행계획이다.
⚙️ 해석 : 양쪽 테이블을 소트한 후, 위쪽 사원 테이블 기준으로 아래쪽 고객 테이블과 머지 조인한다

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Opimizer=ALL ROWS
1 0   MERGE JOIN
2 1    SORT (JOIN)
3 2     TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
4 3      INDEX (RANGE SCAN) OF '사원_X1' (INDEX)
5 1    SORT (JOIN)
6 5     TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
7 6      INDEX (RANGE SCAN) OF '고객_X1' (INDEX)

소트 머지 조인 실행계획을 제어할 때 use_merge 힌트를 사용한다.

ordered 힌트 : FROM 절에 기술한 순서대로 조인하라고 옵티마이저에 지시하는 힌트
ordered 대신 leading(e) 힌트를 사용해도된다.

⚙️ ordered와 use_merge(c) 힌트를 같이 사용했으므로 양쪽 테이블을 조인 컬럼 순으로 각각 정렬한 후, 정렬된 사원 기준으로 정렬된 고객과 조인하라

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

🔎 소트머지조인의 특징

조인을 위해 실시간으로 인덱스를 생성하는 조인이다.
양쪽 집합을 정렬한 다음 NL조인과 같은 방식으로 진행하지만, PGA 영역을 사용해서 소트 부하만 감수하면 NL보다 빠르다.
NL조인은 인덱스 유무에 크게 영향받으나, 소트 머지 조인은 영향받지 않는다.
양쪽 집합을 개별적으로 읽고 조인을 시작한다.
조인컬럼에 인덱스가 없는 상황에서 두 테이블을을 각각 읽어 조인 대상 집합을 줄일 수 있을때 유리하다.
스캔 위주 액세스 방식을 사용한다.
양쪽 소스 집합으로부터 조인 대상 레코드를 찾을때는 인덱스를 이용할 수도 있다.

3. 해시 조인

🔎 해시조인(Hash Join)의 기본 메커니즘

1. Bulid 단계 : 작은 쪽 테이블(Bulid Input)을 읽어 해시 테이블을 생성한다.
2. Probe 단계 : 큰 쪽 테이블(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.
⚙️ 해석 : 사원테이블 기준으로 고객 테이블과 조인할 때 해시조인방식을 사용하라
해시조인은 use_hash힌트를 사용

SELECT /+ ORDRED 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

위 SQL 수행과정을 풀면 아래와 같다.

1. Bulid 단계

아래 조건에 해당하는 사원테이블을 읽어 해시 테이블을 생성한다. 이때, 조인컬림인 사원번호를 해시 테이블 키 값으로 사용한다.(사원번호를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인에 데이터를 연결한다.)
해시테이블은 PGA영역에 할당된 Hash Area에 저장한다.
해시 테이블이 너무 커 PGA에 담을 수 없으면, Temp테이블스페이스에 저장한다.

2. Prob 단계

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

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

Bulid 단계에서 사용한 해시함수를 Probe단계에서도 사용하므로 같은 사원번호를 입력하면 같은 해시 값을 반환한다.
따라서 해시함수가 반환한 값에 해당하는 해시 체인만 스캔하면 된다.

🔎 해시조인이 빠른 이유

해시조인이 NL조인과 차이점은 Hash Area에 생성한 해시테이블을 이용한다라는 점이다.
해시조인이 NL조인보다 빠른 이유는 소트머지조인과 동일한데, 해시테이블을 PGA영역에 할당하기 때문이다.
- NL조인 : Outer테이블 레코드마다 Inner쪽 테이블 레코드를 읽기위해 래치 획득 및 캐시버퍼 체인스캔 과정 반복
- 해시조인: 래치획득 과정 없이 PGA에서 빠르게 데이터 탐색하고 조인
해시조인도 Bulid Input과 Probe Input각 테이블을 읽을 때 DB 버퍼캐시를 경우하며, 인덱스를 이용하기도 한다.
해시조인과 소트머지조인의 공통점은 조인 오퍼레이션을 PGA에서 처리한다 이지만, 대량데이터를 처리할때는 해시조인이 더빠르다.

Q. 대량데이터를 처리할때 소트머지조인보다 해시조인이 더 빠른 이유는?
- PGA에서 데이터를 탐색하는 알고리즘 차이도 있지만, 조인 오퍼레이션 시작전, 사전 준비작업에서 차이가 난다.
- 소트머지조인의 사전 준비작업은 양쪽 집합을 모두 정렬해서 PGA를 담는 작업이며, PGA는 큰 메모리가 공간이 아니므로 두 집합 중 하나가 중대형 이상일 경우 디스크에 쓰는 작업을 수반한다.
- 해시조인의 사전 준비작업은 양쪽 집합 중 한쪽을 읽어 해시 맵을 만드는 작업이며, 둘 중 작은 집합을 해시맵 Bulid Input으로 선택하여 두 집합 모두 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면 디스크에 쓰는 작업은 일어나지 않는다.

✨ 정리하자면,

해시조인은 NL조인처럼 조인과정에서 발생하는 랜덤 액세스 부하가 없고, 소트머지처럼 양쪽 집합을 미리 정렬하는 부하도 없다.
해시테이블 생성비용이들지만 둘중 작은집합을 Build Input선택하므로 대개 부담이크지않다.
Temp 테이블 스페이스를 쓰게되더라도 대량 데이터 조인에는 일반적으로 해시조인이 가장 빠르다.

해시 테이블에 담기는 정보는 해시테이블에 조인키값만 저장되지 않고, SQL에 사용한 컬럼 모두 저장한다.

🔎 대용량 Bulid Input 처리

Q.두 테이블(T1, T2) 모두 대용량 테이블이어서 인메모리 해시조인이 불가능한 상황일때, DBMS는 어떤 방식으로 해시 조인을 처리할까?
- 분할, 정복(Divide & Conquer)방식을 사용한다.

1. 파티션 단계

조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다.
독립적으로 처리할 수 있는 여러개의 작은 서브 집합으로 분할함으로써 파티션 짝을 생성하는 단계이다.
양쪽 테이블을 읽어 디스크 Temp 공간에 저장해야하므로 인메모리 해시 조인보다 성능이 많이 떨어진다.

2. 조인단계

파티션 단계를 완료하면 각 파티션에 대해 하나씩 조인을 수행한다.
각각에 대한 Bulid Input과 Probe Input은 독립적으로 결정된다.
즉, 파티션하기 전 어느쪽이 작은 테아블이었는지 상관없이 각 파티션 짝별로 작은 쪽을 Bulid Input을 선택하고 해시 테이블을 생성한다.
해시테이블을 생성하고 나면 반대쪽 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다.
모든 파티션 짝에 대한 처리를 마칠때까지 이 과정을 반복한다.

🔎 해시 조인 실행계획 제어

⚙️ 해석 : 위쪽(Hash Join아래) 사원데이터(Build Input)로 해시테이블을 생성한 후, 아래쪽 고객테이블(Probe Input)에서 읽은 조인 키값으로 해시 테이블을 탐색하면서 조인한다

Execution Plan
----------------------------------------------------------------
0      SELECT STATMENT Optimizer=ALL_ROWS
1   0	 HASH JOIN
2   1	  TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
3   2	   INDEX (RANGE SCAN) OF '사원_X1'	(INDEX)
4   3	  TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
5   4	    INDEX (RANGE SCAN) OF '고객_X1'	(INDEX)

⚙️ 해시조인은 use_hash힌트 사용 (Bulid Input가 옵티마이저가 선택했으며, 카티널리티가 작은 테이블을 선택)

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

⚙️ Bulid Input을 사용자가 선택하고 싶을 때

leading이나 ordered 힌트를 사용

SELECT /*+ leading(e) use_hash(c) */ -- 또는 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

⚙️swap_join_inputs 힌트 : Bulid Input을 명시적으로 선택가능

SELECT /*+ leading(e) use_hash(c) swap_join_inputs(c) */
       e.사원번호, e.사원명, e.입사일자
     , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE c.관리사원번호 = e.사원번호
   AND e.입사일자 >= '19960101'
   AND e.부서코드 = 'Z123'
   AND c.최종주문금액 >= 20000

⚙️ 3개 이상 테이블 조인할때

- 조인 연결고리에 따라 순방향 또는 역방향으로 leading힌트를 기술한 뒤, Bulid Input으로 선택하고 싶은 테이블을 swap_join_inputs 힌트에 지정해주면 된다.
- Bulid Input으로 선택하고 싶은 테이블이 조인된 결과 집합이어서 swap_join_inputs 힌트로 지정하기 어렵다면, no_swap_join_inputs 인트로 반대쪽 Probe Input을 선택해주면 된다.

select /*+ leading(T1,T2,T3) use_hash(T2) use_hash(T3) */ *
from T1,T2,T3
where T1.key = T2.key
and T2.key = T3.key

🔎 조인 메소드 선택기준

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

Q. 소량 대량의 기준은 무엇일까?

NL조인 기준으로 최적화했는데도 랜덤 액세스가 많아 만족할만한 성능을 낼 수 없다면, 대량 데이터 조인에 해당한다.

⚙️ 수행빈도가 매우 높은 쿼리에 대한 기준
1. (최적화된) NL조인과 해시조인 성능이 같으면, NL조인
2. 해시조인이 약간 빨라도 NL조인
3. NL조인보다 해시조인이 매우 빠른 경우, 해시조인
(= 대량데이터 조인할때 해시조인을 사용하라라는 의미와 동일 )

Q. 조인메소드를 선택할때 NL조인을 가장 먼저 고려해야 하는 이유는?
NL조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조이다.
해시테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조이다.
결론적으로 해시조인은 "수행빈도가 낮고, 쿼리 수행시간이 오래 걸리는 대량데이터 조인할때" 조건을 만족하는 SQL문을 사용한다.

4. 서브쿼리 조인

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

하나의 결과 집합을 얻기 위해 SQL을 여러 가지 다른 형태로 표현할 수 있고, 어떤 것을 선택하느냐에 따라 성능도 달라질 수 있다.
그래서 최근 옵티마이저는 비용을 평가하고 실행게획을 생성하기에 앞서 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업인 쿼리 변환부터 진행한다.

쿼리 변환 : 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것
서브쿼리(Subquery): 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록

SELECT c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
     , (SELECT 고객분류명 FROM 고객분류 WHERE 고객분류코드 = c.고객분류코드) -> 스칼라 서브쿼리
  FROM 고객 c
     , (SELECT 고객번호, AVG(거래금액) 평균거래
             , MIN(거래금액) 최소금액, MAX(거래금액) 최대거래
          FROM 거래
         WHERE 거래일시 >= TRUNC(SYSDATE, ‘MM’)
         GROUP BY 고객번호) t                                              -> 인라인 뷰
 WHERE c.가입일시 >= TRUNC(ADD_MONTHS(SYSDATE, -1), ‘mm’)
   AND t.고객번호 = c.고객번호
   AND exists (SELECT ‘X’
                 FROM 고객변경이력 h
                WHERE h.고객번호 = c.고객번호
                  AND h.변경사유코드 = ‘ZCH’
                  AND c.최종변경일시 BETWEEN h.시작일시 AND h.종료일시) -> 중첩된 서브쿼리

⚙️ 오라클은 서브쿼리를 3가지로 분류
1. 인라인뷰 (Inline View)

From 절에 사용한 서브쿼리

2. 중첩된 서브쿼리 (Nested Subquery)

결과집합을 한정하기 위해 where절에 사용한 서브쿼리이며, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 상관관계 있는 서브쿼리라고 부른다.

3. 스칼라 서브쿼리 (Scalar Subquery)

한 레코드당 정확히 하나의 값을 반환하는 서브쿼리이며, select-list에서 사용되지만, 몇가지 예외사항을 제외하면 컬림이 올 수 있는 대부분 위치에 사용할 수 있다.

이들 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 최적화를 수행한다.

🔎 서브쿼리와 조인

메인쿼리와 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.
서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없다.
메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행해야 한다.

⚙️ 필터 오퍼레이션

서브쿼리를 필터방식으로 처리할때 실행계획이다.
서브쿼리를 필터방식으로 처리하게 하려고 의도적으로 no_unnest 힌트를 사용하였다.
no_unnest 힌트 : 서브쿼리를 풀어내지 않고, 그대로 수행하라고 옵티마이저에 지시한다.

SELECT c.고객번호, c.고객명
  FROM 고객 c
 WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)
   AND EXISTS (SELECT /*+ no_unnest */
                 FROM 거래
                WHERE 고객번호 = c.고객번호
                  AND 거래일시 >= TRUNC(SYSDATE, ‘mm’))

Excution    Plan
-------------------------------------------------------------------------------
0      SELECT STATMENT Optimizer=ALL_ROWS (Cost=289 Card=1 Bytes=39)
1    0    FILTER
2    1      TABLE ACCESS (BY INDEX ROWID) OF ‘고객’ (TABLE) (Cost=4 Card=190 ...)
3    2        INDEX (RANGE SCAN) OF ‘고객_X01’ (INDEX) (Cost=2 Card=190)
4    1      INDEX (RANGE SCAN) OF ‘고객_X01’ (INDEX) (Cost=3 Card=4K Bytes=92k)

필터 오퍼레이션은 기본적으로 NL조인과 처리루틴이 동일하며, FILTER를 NESTED LOOPS로 치환하고 처리 루틴을 해석하면 된다. NL조인처럼 부분범위처리도 가능하다.

NL조인과의 차이가 있다면,
첫째, 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다는 점이다.
둘째, 필터는 캐싱기능을 갖는다는 점이다. 이는 필터 처리한 결과, 즉 서브쿼리 입력 값에 따른 반환 값(true 또는 false)을 캐싱하는 기능이다.
셋째, 필터 서브쿼리는 일반 NL 조인과 달리 메인쿼리에 종속되므로 조인 순서가 고정된다. 항상 메인쿼리가 드라이빙 집합이다.

⚙️ 서브쿼리 Unnesting

메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어 준다는 의미
서브쿼리 Flattening이라고 부르기도 한다.
Unnesting된 서브쿼리는 NL세미조인외에도 다양한 방식으로 실행될 수 있다.
필터방식은 항상 메인쿼리가 드라이빙 집합이지만, Unnesting 된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.

Unnesting을 하고나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다. 아래 쿼리에서는 unnest와 nl_sj 힌트를 함께 사용했으므로 NL 세미조인 방식으로 실행되었다

select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1) 'mm')
and exists (
         select /*+ unnest nl_sj */ 'x'
         from 거래
         where 고객번호 = c.고객번호
         and 거래일시 >= trunc(sysdate, 'mm'))

Unnesting 된 서브쿼리가 드라이빙되도록 leading 힌트를 사용했을때 아래와 같다.
서브쿼리를 그대로 풀어서 조인하면, 메인쿼리 결과집합(고객)이 서브쿼리 M쪽 집합(거래) 수준으로 확장될 수 있으므로 서브쿼리 집합에 대한 Sort Unique 오퍼레이션부터 수행했음을 확인 가능하디.

SELECT /*+ leading(거래@subq) use_nl(c) */
		c.고객번호, c.고객명
  FROM 고객 c
 WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)
   AND EXISTS (SELECT /*+ qd_name(subq) unnest */
                 FROM 거래
                WHERE 고객번호 = c.고객번호
                  AND 거래일시 >= TRUNC(SYSDATE, ‘mm’))

⚙️ 서브쿼리 Pushing

Unnesting 되지 않는 서브쿼리는 항상 필터 방식으로 처리되며, 실행계획 상에서 맨 마지막 단계에 처리된다.
push_subq힌트 : 서브쿼리 필터링을 먼저 처리하게하려고 사용한다.
서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 기능이며, push_subq/no_push_subq 힌트로 제어한다.
해당 기능은 Unnesting 되지 않는 서브쿼리에만 작동한다
push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 방법이다.
Pushing 서브쿼리와 반대로, 서브쿼리 필터링을 가능한 나중에 처리하게 하려면 no_unnest와 no_push_subq를 같이 사용한다.

🔎 뷰(View) 와 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰(view) 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
아래 쿼리는 뷰를 독립적으로 최적화하려니 당월 거래 전체를 읽어 고객번호 수준으로 Group By하는 실행계획을 수립하였고, 고객테이블과 조인은 그 다음에 처리한다.

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
    , (select 
    	고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
       from 거래
       where 거래일시 >= trunc(sysdate, 'mm')           
       group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호

문제는 고객 테이블에서 전월 이후에 가입한 고객을 필터링 하는 조건이 인라인 뷰 바깥에 있다는 사실이다.
해당 조건이 있는데도 인라인 뷰안에서 당월 거래한 모든 고객의 거래 데이터를 읽어야 한다.
아래는 merge힌트를 이용해 뷰를 메인 쿼리와 머징을 하도록 하였다.
참고로 뷰 머징을 방지하고자 할때 no_merge 힌트를 사용한다.

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
    , (select /*+ merge */ 
    		고객번호
    		, avg(거래금액) 평균거래
    		, min(거래금액) 최소거래, max(거래금액) 최대거래
       from 거래
       where 거래일시 >= trunc(sysdate, 'mm')           -- 당월 발생한 거래
       group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호

실행계획을 확인해보면 쿼리가 아래처럼 변환되었음을 확인할 수 있다.

select c.고객번호, c.고객명,  avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
from 고객 c, 거래 t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and t.고객번호 = c.고객번호
and t.거래일시 >= trunc(sysdate, 'mm')
group by c.고객번호, c.고객명

가장 먼저 액세스하는 고객_X01 인덱스는 가입일시가 선두컬럼이며, 인덱스를 Range Scan한 사실을 통해 알 수 있다.
거래_X02 인덱스는 고객번호+거래일시 순으로 구성되어 있는것이 최적이므로, 그렇게 가정해보자!
실행계획을 보면 고객 테이블을 먼저 읽는다.
인덱스를 이용해 전월 이후 가입한 고객만 읽고 거래 테이블과 조인할 때는 해당 고객들에 대한 당월 거래만 읽는다.
거래테이블은 고객번호+거래일시 순으로 구성된 인덱스를 이용해 NL방식으로 조인하기 때문이다.
단점은, 조인에 성공한 전체 집합을 Group By 하고서야 데이터를 출력한다는 점이다. 즉, 부분범위 처리가 불가능하다.
만약 전월 이후 가입한 고객이 매우 많고 당월 거래도 매우 많다면, 부분범위 처리 불가능한 상황에서 NL 조인은 좋은 선택이 아니다.
그런 상황에선 해시 조인이 빠르다!

Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Opimizer=ALL ROWS
1 0   HASH (GROUP BY)
2 1    HASH JOIN
3 2     TABLE ACCESS (FULL)  OF '고객' (TABLE)
4 2     TABLE ACCESS (FULL)  OF '거래' (TABLE)

🔎조인조건 Pushdown

11g 이후 쿼리 변환기능이 작동한다.
메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.
push_pred 힌트를 사용한다.
옵티마이저가 뷰를 머징하면 힌트가 작동하지 않으니 no_merge 힌트를 함께 사용이 필요하다.

🔎 스칼라 서브쿼리 조인

1. 스칼라 서브쿼리의 특징

create or replace function GET_DNAME(p_deptno number) return varchar2
is
    l_dname dept.dname%TYPE;
begin
    select dname into l_dname from dept where deptno = p_deptno;
    return l_dname;
exception
    where others then
    return null;
end;

위처럼 GET_DNAME 함수를 만들었는데 이 함수를 사용하면 SELECT 쿼리를 메인쿼리 건수만큼 재귀적으로 반복한다.

SELECT EMPNO, ENAME, SAL, HIREDATE, GET_DNAME(E.DEPTNO) AS DNAME
 FROM EMP E
WHERE SAL >= 2000

아래의 서브쿼리는 메인 쿼리 레코드마다 하나의 값만 반환한다. 즉, 재귀적으로 실행하는 구조가 아니다.

SELECT EMPNO, ENAME, SAL, HIREDATE
      , (SELECT D.DNAME, FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
  FROM EMP E
 WHERE SAL >= 2000

서브쿼리를 사용한 위 쿼리문 아래 Outer 조인문처럼 NL조인을 사용한다. DEPT와 조인에 실패하는 EMP 레코드는 DNAME에 NULL 값을 출력하는 점도 같다.

SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
AND SAL >= 2000

차이가 있다면, 스칼라 서브쿼리는 처리과정에서 캐싱작용이 일어난다는 점이다.

2. 스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cashe)에 저장한다.
조인할 때마다 일단 캐시에서 ‘입력 값’을 찾아보고, 찾으면 저장된 ‘출력 값’을 반환한다.
캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.

SELECT EMPNO, ENAME, SAL, HIREDATE,
    (SELECT D.DNAME --> 출력 값 : D.DNAME
        FROM DEPT D
        WHERE D.DEPTNO = E.EMPNO --> 입력 값 : E.EMPNO
    ) 
FROM EMP E
WHERE SAL >= 2000

스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능이다.
메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면 조인 수행횟수를 최소화 할 수 있기 때문이다.
SELECT-LIST에 사용한 함수는 메인쿼리 건수만큼 반복 수행되는데, 스칼라 서브쿼리 덧씌우면 호출횟수를 최소화 할 수 있다.

SELECT EMPNO, ENAME, SAL, HIREDATE, 
		(SELECT GET_DNAME(e.deptno) FROM dual) dname
FROM EMP E
WHERE SAL >= 2000

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

입력값이 많으면 캐시를 매번 확인하는 비용때문에 성능이 나빠지고 CPU 사용률도 높아지며 메모리도 더 사용된다
- 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 좋다.
메인쿼리집합이 매우 작으면 캐시 재사용성이 낮다.

4. 2개 이상의 값 반환

스칼라 서브쿼리는 2개이상의 값을 반환할수 없다는 제약이 있으므로 아래와 같은 쿼리를 작성할수 없다.

SELECT c.고객번호, c.고객명
     , (SELECT AVG(거래금액), MIN(거래금액), MAX(거래금액) --불가능!
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
          AND 고객번호 = c.고객번호)
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

아래는 SQL튜너들이 전통적으로 많이 사용해온 방식이다.
구하는 값들을 문자열로 결합하고, 바깥쪽 액세스 쿼리에서 substr함수로 다시 분리하는 함수이다.

SELECT 고객번호, 고객명
     , to_number(substr(거래금액,1,10)) 평균 거래 금액
     , to_number(substr(거래금액,11,10)) 최소 거래 금액
     , to_number(substr(거래금액,21)) 최대 거래 금액
FROM  c.고객번호, c.고객명
		, (SELECT lpad(AVG(거래금액),10) || lpad(MIN(거래금액),10) || MAX(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
          AND 고객번호 = c.고객번호) 거래금액
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

두 개 이상 값을 반환하고 싶을 경우 인라인 뷰를 사용하면 되긴 하지만, 부분범위 처리가 안되는 문제가 있었다.
허나, 11g이후 조인조건 Pushdown기능이 잘 작동하므로 인라인뷰를 사용할 수 있게 되었다.

5. 스칼라 서브쿼리 Unnesting

스칼라 서브쿼리도 NL방식으로 조인하므로 캐싱효과가 크지 않으면 랜덤 I/O에 부담이 있다.
특히, 병렬(Parallel) 쿼리에선 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 한다.
대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이다.
12c 버전부터 스칼라 서브쿼리도 Unnesting이 가능하다. 즉, 옵티마이저가 사용자대신 자동으로 쿼리를 변환해주는 것이다.

optimizer_unnest_scalar_sq 파라미터를 true로 설정하면, 스칼라 서브쿼리를 Unnesting 할지 여부를 옵티마이저가 결정한다. false로 설정하면 옵티아미저가 이 기능을 사용하지 않지만, 사용자가 unnest힌트를 유도할 수 있다.

아래는 스칼라 서브쿼리를 Unnesting 할때의 실행계획이며, 스칼라 서브쿼리지만, NL조인이 아닌 해시조인으로 실행되는 이유는 Unnesting되었기 때문이다.

select c.고객번호, c.고객명
     , (select /*+ unnest */ round(avg(거래금액), 2) 평균거래금액
         from 거래
         where 거래일시 >= trunc(sysdate, 'mm')
         and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')


Execution Plan
---------------------------------------------------
0    SELECT STATEMENT Opimizer=ALL ROWS
1 0   HASH JOIN (OUTER)
2 1    TABLE ACCESS (FULL) OF '고객' (TABLE)
3 1    VIEW OF 'SYS.VW_SSQ_1' (VIEW)
4 3     HASH (GROUP BY)
5 4      TABLE ACCESS (BY INDEX ROWID) '거래' (TABLE)

스칼라 서브쿼리 Unnesting 으로 인해 일부 쿼리에 문제가 생겼을 경우 optimizer_unnest_scalar_sq를 false로 하지 않고, no_unnest 힌트를 사용해 부분적으로 문제를 해결할 수도 있다.

select c.고객번호, c.고객명
     , (select /*+ no_unnest */ round(avg(거래금액), 2) 평균거래금액
         from 거래
         where 거래일시 >= trunc(sysdate, 'mm')
         and 고객번호 = c.고객번호)
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')

'Study > 친절한 SQL 튜닝' 카테고리의 다른 글

[DB] 6장. DML 튜닝(1) (0)	2025.03.30
[DB] 5장. 소트 튜닝 (0)	2025.03.17
[DB] 3장. 인덱스 튜닝(2) (0)	2025.01.31
[DB] 3장. 인덱스 튜닝(1) (0)	2025.01.31
[DB] 2장. 인덱스 기본 (0)	2025.01.25