seoyyyy-dev
4회차 조인 튜닝 본문
1. NL 조인(Nested Loop Join)
1.1 기본 메커니즘
NL 조인의 기본 메커니즘을 알아보기 위해 아래 사원과 고객테이블을 이용하여 1996년 1월 1일 이후 입사한 사원이 관리하는 고객 데이터를 추출하는 프로그램을 작성해 보자.
위의 조건을 SQL로 만든다면 아래와 같이 두 테이블을 조인하여 쿼리를 쉽게 작성할 수 있다.
select e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
and c.관리사원번호 = e.사원번호
위의 SQL을 C, JAVA, PL/SQL 와 같은 언어로 프로그래밍한다고 하면 아래와 같이 중첩 루프문(Nested Loop) 수행 구조를 이용하게 된다.
/** C, JAVA 의 경우 **/
for(i = 0; i < 100; i++) {
for(j = 0; j < 100; j++) {
// Do Anything...
}
}
/** PL/SQL의 경우 **/
for outer in 1..100 loop
for inner in 1..100 loop
dbms_output.put_line(outer || ' : ' || inner);
end loop;
end loop;
NL 조인은 위 중첩 루프문과 같은 수행구조를 사용한다.
아래 PL/SQL을 살펴보면 NL 조인이 어떤 순서로 데이터를 액세스 하는지 알 수 있다.
begin
for outer in (select 사원번호, 사원명 from 사원 where 입사일자 >= '19960101')
loop -- outer 루프(outer: 사원)
for inner in (select 고객명, 전화번호 from 고객 where 관리사원번호 = outer.사원번호)
loop -- inner 루프(inner: 고객)
dbms_output.put_line(outer.사원명 || ' : ' || inner.고객명 || ' : ' || inner.전화번호);
end loop;
end loop;
end;
- NL 조인은 '인덱스를 이용한 조인 방식'으로 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다.
- Outer(like 사원 테이블) 테이블 크기가 크지 않다면 인덱스를 이용하지 않는 경우도 있지만 Inner(like 고객 테이블) 쪽 테이블은 인덱스를 사용해야 한다.
- 소트 머지 조인과 해시 조인도 각각 Sort Area와 Hash Area에 가공해 둔 데이터를 이용한다는 점만 다를 뿐, 기본적인 조인 프로세싱은 다르지 않다.
1.2 NL 조인 실행 계획 제어
사원 테이블 기준으로 고객 테이블과 NL(Nested Loop) 조인한 실행계획을 살펴보자
Executiojn Plan
--------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 NESTED LOOP
2 1 TABLE ACCESS (BY INDEX ROWID) OF '사원' (TABLE)
3 2 INDEX (RANGE SCAN) OF '사원_X1' (INDEX)
4 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
5 4 INDEX (RANGE SCAN) OF '고객_X1' (INDEX)
위 실행계획을 살펴보면 각 테이블을 액세스 할 때 인덱스를 이용한다는 사실을 확인할 수 있다.
NL 조인을 제어하기 위해 use_nl 힌트를 사용한다.
또한 ordered 힌트를 사용하면 FROM 절에 기술한 대로 조인하도록 옵티마이저에 지시할 수 있다.
힌트 사용 예시 1.
select /*+ ordered use_nl(c) */
e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
and c.관리사원번호 = e.사원번호
➡️ ordered와 use_nl(c)를 사용하여 사원 테이블(Outer) 기준으로 고객 테이블(Inner)과 NL 방식으로 조인한다.
힌트 사용 예시 2.
select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */ *
from A, B, C, D
where .....
➡️ A → B → C → D 순으로 조인하되, B와 조인할 때, 그리고 이어서 C와 조인할 때 NL 방식으로 조인하고, D와 조인할 때는 해시 방식으로 조인한다.
힌트 사용 예시 3.
select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */ *
from A, B, C, D
where .....
➡️ ordered 대신 leading 힌트를 사용하여 C → A → D → B 순으로 조인하며, B와 C는 조인 시 NL 방식으로 조인하고, D는 해시 방식으로 조인한다.
힌트 사용 예시 4.
select /*+ use_nl(A, B, C, D) */ *
from A, B, C, D
where .....
➡️ ordered, leading 힌트를 사용하지 않고, NL 방식을 사용하되 순서는 옵티마이저가 알아서 정한다.
1.3 NL 조인 실행 계획 제어
만약 아래의 조건절로 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]
두 테이블의 인덱스 구성은 아래와 같다.
* 사원_PX : 사원번호
* 사원_X1 : 입사일자
* 고객_PK : 고객번호
* 고객_X1 : 관리사원번호
* 고객_X2 : 최종주문금액
두 테이블에 인덱스를 사용하도록 index 힌트를 명시했고, 인덱스 명은 명시하지 않았으므로 옵티마이저가 인덱스를 결정한다.
먼저 실행계획은 아래와 같이 사원_X1와 고객_X1 인덱스가 사용되었다.
---------------------------------------------------------------------------------
| ID | Operation | Name | Rows | Bytes | Cost |
---------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5 | 58 | 5 |
| 1 | NESTED LOOPS | | 5 | 58 | 5 |
| 2 | TABLE ACCESS BY INDEX ROWID | 사원 | 3 | 20 | 2 |
| 3 | INDEX RANGE SCAN | 사원_X1 | 5 | | 1 |
| 4 | TABLE ACCESS BY INDEX ROWID | 고객 | 5 | 76 | 2 |
| 5 | INDEX RANGE SCAN | 고객_X1 | 8 | | 1 |
---------------------------------------------------------------------------------
그리고 조건절은 [2] ➡️ [3] ➡️ [1] ➡️ [4] 순으로 비교한다.
1. 조건절 번호 [2]
- 입사일자 >= '19960101' 조건을 만족하는 레코드를 찾기 위해 사원_X1 인덱스를 Range 스캔한다.
- 실행계획 ID = 3 에 해당
2. 조건절 번호 [3]
- 사원 _X1에서 읽은 ROWID로 사원테이블을 액세스 하여 부서코드 = 'Z123' 필터 조건을 만족하는지 확인
- 실행계획 ID = 2 에 해당
3. 조건절 번호 [1]
- 사원테이블에서 읽은 사원번호 값으로 조인 조건(c.관리사원번호 = e.사원번호)을 만족하는 고객 쪽 레코드를 찾기 위해 고객_X1 인덱스를 Range 스캔한다.
- 실행계획 ID = 5 에 해당
4. 조건절 번호 [4]
- 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스 해서 최종주문금액 >= 20000 필터 조건을 만족하는지 확인
- 실행계획 ID = 4 에 해당
참고로 NL 조인은 각 단계를 모두 완료하고 다음 단계로 넘어가는 것이 아닌 한 레코드씩 순차적으로 진행한다.
1.4 NL 조인 튜닝 포인트
1️⃣ 첫 번째 튜닝 포인트
- 만일 위 쿼리에서 사원 테이블로 아주 많은 양의 랜덤 액세스가 발생했고, 부서코드 = 'Z123' 조건에 의해 필터링되는 비율이 높다면 사원_X1 인덱스에 부서코드 컬럼을 추가하는 방안을 고려한다.
2️⃣ 두 번째 튜닝 포인트
- 조인 액세스 횟수는 Outer 테이블인 사원을 읽고 필터링한 결과 건수에 의해 결정되는데 부서코드 = 'Z123' 조건을 만족하는 사원 레코드가 10만 건이고 고객_X1 인덱스의 Depth가 3이라면 인덱스 수직적 탐색 과정만 30(=10만 X3) 개 블록을 읽고, 리프 블록을 수평적 스캔 시에 추가적인 블록 I/O가 더해진다.
3️⃣ 세 번째 튜닝 포인트
- 고객_X1 인덱스를 읽고 나서 고객 테이블을 읽을 때 여기서 최종주문금액 >= 20000 조건에 의해 필터링되는 비율이 높다면 고객_X1 인덱스에 최종주문금액 컬럼을 추가하는 방안을 고려한다.
4️⃣ 네 번째 튜닝 포인트
- 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일 량이 좌우되므로 사원_X1 인덱스를 스캔하며 추출한 레코드가 많으면 사원테이블로 랜덤 액세스하는 횟수, 고객_X1 인덱스를 탐색하는 횟수, 고객테이블로 랜덤 액세스하는 횟수가 많아진다.
⭐ 올바른 조인 메소드 선택
쿼리 튜닝 시 성능이 느릴 때 위의 조인 튜닝 포인트에 따라 각 단계의 수행 일량을 분석해 과도한 랜덤 액세스가 발생하는 지점을 파악하고, 조인 순서를 변경해서 랜덤 액세스 발생량을 줄일 수 있는지, 더 효과적인 다른 인덱스가 있는지 등을 검토한다. 필요시, 인덱스 추가 또는 구성 변경도 고려해 본다.
만일 NL 조인으로 결코 좋은 성능을 내기 어려우면 소트 머지 조인이나 해시 조인도 검토해 본다.
1.5 NL 조인 특징
1️⃣ 랜덤 액세스 위주의 조인 방식이다.
- 레코드 하나를 읽을 때에도 블록을 통째로 읽는 랜덤 액세스 방식으로 설령 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다. 그래서 대량 데이터 조인 시 NL 조인은 불리하다.
2️⃣ 조인을 한 레코드씩 순차적으로 진행한다.
- 부분 범위 조건으로 처리가 가능한 경우 큰 테이블을 조인하더라도 매우 빠른 응답 속도를 낼 수 있다.
3️⃣ 다른 조인 방식과 비교할 때 인덱스 구성 전략이 특히 중요하다.
- 조인 컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 컬럼이 어떻게 구성 됐느냐에 따라 조인 효율이 크게 달라진다.
➡️ 소량 데이터를 주로 처리하거나 부분 범위 처리가 가능한 온라인 트랜잭션 처리(OLTP) 시스템에 적합한 조인방식
1.7 NL 조인 확장 매커니즘
오라클 버전이 올라가면서 NL 조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입했다.
▶️ 전통적인 실행 계획
Rows Row 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
▶️ 테이블 Prefetch
: 인덱스를 이용해 테이블을 액세스 하다가 디스크 I/O가 필요해지면 이어서 곧 읽게 될 블록까지 미리 읽어 버퍼캐시에 적재하는 기능
: 테이블 Prefetch 실행계획
Rows Row 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- 오라클 9i부터 나타난 표현방식으로 Inner(고객테이블) 테이블에 대한 디스크 I/O 과정에 테이블 Prefetch 가 작동할 수 있다.
- 관련 힌트: nlj_prefetch, no_nlj_prefetch
▶️ 배치 I/O: 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능
Rows Row 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 고객- 오라클 11g부터 나타난 표현방식으로 Inner(고객테이블) 쪽 테이블에 대한 디스크 I/O 과정에 배치 I/O 기능이 작동할 수 있다.
- 관련 힌트: nlj_batching, no_nlj_batching
⭐ Inner 쪽 테이블 블록을 모두 버퍼캐시에서 읽는다면 위의 세 가지 방식 중 어떤 방식으로 수행하든 성능에 차이가 없으며 데이터 출력 순서도 100% 동일하다.
하지만 '일부를 디스크에서 읽게 되면' 성능에 차이가 있을 수 있고, 배치 I/O 실행계획이 나타날 땐 결과 집합의 정렬 순서도 달라질 수 있다.
11g부터 NL 조인 Inner 쪽 테이블에 작동하는 배치 I/O 기능도 결과집합의 정렬 순서를 보장하지 않으므로 NL 조인 결과집합이 항상 일정한 순서로 출력되기를 원한다면 배치 I/O 기능이 작동하지 못하도록 no_nlj_batching(b) 힌트를 추가하거나 ORDER BY 절에 정렬 기준을 꼭 명시해야 한다.
2. 소트 머지 조인(Sort Merge Join)
😎 NL조인 보다 소트 머지 조인이 좋은 선택인 경우
▶️ 조인 컬럼에 인덱스가 없을 때
▶️ 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때
2.1 SGA vs PGA
SGA
- 공유 메모리 영역으로 SGA에 캐시 된 데이터는 여러 프로세스가 공유할 수 있다.
- 동시 액세스는 불가, 직렬화 Lock 매커니즘으로 래치(Latch)가 존재한다.
- 블록을 읽기 위해서 버퍼 Lock도 얻어야 한다.
PGA
- 오라클 서버 프로세스 별 각 자신만의 고유 메모리 영역이다.
- 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다.
- 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블 스페이스를 이용한다.
- 다른 프로세스와 공유하지 않는 독립적인 공간으로 래치 메커니즘이 불필요하다.
- 같은 양의 데이터를 읽더라도 SGA 버퍼 캐시에서 읽을 때보다 훨씬 빠르다.
2.2 기본 메커니즘
⭐ 소트 머지 조인의 순서
1️⃣ 소트 단계: 양쪽 집합을 조인 컬럼 기준으로 정렬한다.
2️⃣ 머지 단계: 정렬한 양쪽 집합을 서로 머지(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
수행순서(3단계)는 다음과 같다.
1️⃣ 입사일자 >= '19960101',부서코드 = 'Z123' 조건의 사원 데이터를 읽어 조인 컬럼인 사원번호순으로 정렬하며 정렬한 결과 집합은 PGA 영역에 할당된 Sort Area에 저장, 데이터가 너무 크면 Temp 테이블스페이스에 저장한다. ➡️소트단계
select 사원번호, 사원명, 입사일자
from 사원
where 입사일자 >= '19960101'
and 부서코드 = 'Z123'
order by 사원번호
2️⃣ 최종주문고객 >= 20000 이상 조건의 고객 데이터를 읽어 조인 컬럼인 관리사원번호순으로 정렬하며, 정렬한 결과 집합은 PGA 영역에 할당된 Sort Area에 저장, 데이터가 너무 크면 Temp 테이블스페이스에 저장한다. ➡️소트단계
select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객
where 최종주문금액 >= 20000
order by 관리사원번호
3️⃣ PGA 또는 Temp 테이블스페이스에 저장한 정렬된 사원 데이터를 스캔하며, 정렬된 고객데이터와 조인한다. 이를 PL/SQL로 표현하면 아래와 같다. ➡️머지단계
begin
for outer in (select * from PGA에_정렬된_사원)
loop -- outer 루프
for inner in (select * from PGA에_정렬된_고객)
loop -- inner 루프
dbms_output.put_line( .... );
end loop;
end loop;
end;
참고로 사원 데이터를 기준으로 고객데이터를 매번 Full Scan 하지 않는다.
고객 데이터가 정렬되어 있으므로 조인 대상 레코드가 시작되는 지점을 쉽게 찾을 수 있고, 조인에 실패하는 레코드를 만나는 순간 바로 멈출 수 있다.
Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다. 또한 조인 컬럼에 인덱스가 있더라도 대량 데이터 조인 시 NL 조인보다 소트 머지 조인이 유리할 수 있다.
2.3 소트 머지 조인이 빠른 이유 (대량 데이터 조인 시)
⭐ NL 조인
- 인덱스를 이용한 조인 방식
- 조인 과정에서 액세스 하는 모든 블록을 랜덤 액세스 방식으로 건건이 DB 버퍼 캐시를 경유해서 읽으며 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔 과정을 거친다.
- 버퍼캐시에서 찾지 못한 블록은 건건이 '디스크'에서 읽어 들인다.
⭐ 소트 머지 조인
- 양쪽 테이블에서 조인 대상 집합을 일괄적으로 읽어 PGA(or Temp 테이블스페이스)에 저장한 후 조인한다.
- PGA는 독립적인 메모리 공간으로 데이터를 읽을 때 래치 획득 과정이 필요 없다. ➡️ NL 조인보다 빠르다.
- 소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 땐 DB 버퍼 캐시를 경유하며 이때 인덱스를 이용하기도 한다. 이 과정에서의 버퍼캐시 탐색 비용과 랜덤 액세스 부하는 소트 머지 조인도 피할 수 없다.
2.4 소트 머지 조인의 주 용도
해시 조인의 등장으로 소트 머지 조인의 쓰임새가 많이 줄었지만 다음과 같은 상황에는 여전히 소트 머지 조인이 유리할 수 있다.
▶️ 조인 조건식이 등치(=) 조건이 아닌 대량 데이터 조인
▶️ 조인 조건식이 아예 없는 조인(Cross Join, 카티션 곱)
2.5 소트 머지 조인 제어하기
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
위 쿼리는 ordered 힌트를 이용해 FROM 절에 기술한 순서대로 조인을 하도록 유도했고, use_merge 힌트를 이용해 소트 머지 방식으로 조인을 지시했다.
위 힌트를 사용해 양쪽 테이블을 각각 소트(정렬)하여 왼쪽(사원) 테이블 기준으로 오른쪽(고객) 테이블과 조인한다.
실행계획은 다음과 같다.
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=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 4 SORT (JOIN)
6 5 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE)
7 6 INDEX (RANGE SCAN) OF '고객_X1' (INDEX)
3. 해시조인(Hash Join)
NL 조인은 인덱스를 이용한 조인 방식으로 대량 데이터 처리 시에 불리한데 이때 소트 머지 조인은 독립된 공간인 PGA 영역에 저장하기 때문에 래치 매커니즘이 필요 없어 더 빠르다고 했다.
하지만 소트 머지 조인도 항상 양쪽 테이블을 정렬해야 하는 부담이 있는데 해시 조인은 이런 부담이 없다.
3.1 기본 매커니즘
⭐ 해시 조인 순서 (2단계)
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
1️⃣ Build 단계
- 아래 조건(입사일자, 부서코드) 조건에 해당하는 사원 데이터를 읽어 사원 해시 테이블을 생성한다. 이때 조인컬럼인 사원번호를 해시 테이블 키 값으로 사용한다.
- 사원번호를 해시 함수의 키로 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인에 데이터를 연결한다.
- 해시 테이블은 PGA 영역에 할당된 Hash Area에 저장한다. 해시 테이블이 너무 커 PGA에 담을 수 없으면 Temp 테이블 스페이스에 저장한다.
select 사원번호, 사원명, 입사일자
from 사원
where 입사일자 >= '19960101'
and 부서코드 = 'Z123'
2️⃣ Probe 단계
- 아래 조건(최종주문금액)에 해당하는 고객 데이터를 하나씩 읽어 앞서 생성한 사원 해시테이블을 탐색한다.
- 관리사원번호 (사원 테이블의 사원번호 컬럼과 같은 값)를 해시 함수에 입력해서 반환된 값으로 해시 체인을 찾고, 그 해시 체인을 스캔해서 값이 같은 사원번호를 찾는다.
select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객
where 최종주문금액 >= 20000
Probe 단계의 조인 과정을 PL/SQL로 표현하면 다음과 같다. 아래 Pseudo 코드를 확인했을 때 Probe 단계는 NL 조인과 다르지 않다.
begin
for outer in (select 고객번호, 고객명, 전화번호, 최종주문금액, 관리사원번호
from 고객
where 최종주문금액 >= 20000)
loop -- outer 루프 (고객)
for inner in (select 사원번호, 사원명, 입사일자
from PGA에_생성한_사원_해시맵
where 사원번호 = outer.관리사원번호
)
loop -- inner 루프
dbms_output.put_line( ... );
end loop;
end loop;
end;
3.2 해시 조인이 빠른 이유
- 인덱스 기반의 NL 조인은 Outer 테이블 레코드마다 Inner 쪽 테이블을 읽기 위해 래치 획득 및 캐시 버퍼 체인 스캔 과정을 반복하지만, 해시 조인은 래치 획득 과정 없이 해시테이블을 독립적인 공간인 PGA영역에 할당하여 빠르게 데이터를 탐색하고 조인한다.
- 해시 조인도 Build Input과 Probe Input 각 테이블을 읽을 때는 DB 버퍼캐시를 경유하며 이때 인덱스를 이용하기도 한다. 이 과정에서 생기는 버퍼 캐시 탐색 비용 및 랜덤 액세스 부하는 해시 조인이라도 피할 수 없다.
⭐ (참고) 해시 테이블에 담기는 정보
해시 테이블에는 조인 키값뿐만 아니라 SQL에 사용한 컬럼을 모두 저장한다. 키값만 저장되는 게 아님
😎 해시 조인이 소트 머지 조인보다 빠른 이유!
- 소트 머지조인에서 사전 준비작업은 '양쪽' 집합을 모두 정렬해서 PGA에 담는 작업으로 두 집합 중 어느 하나가 중대형 이상이면 Temp 테이블 스페이스, 즉 디스크에 쓰는 작업을 반드시 수반한다.
- 해시 조인에서 사전 준비작업은 양쪽 집합 중 어느 '한쪽'을 읽어 해시 맵을 만드는 작업으로 둘 중 작은 집합을 해시 맵 Build Input으로 선택하므로 두 집합 모두 Hash Area에 담을 수 없을 정도로 큰 경우가 아니면 Temp 테이블 스페이스, 즉 디스크에 쓰는 작업은 전혀 일어나지 않는다.
▶️ 즉 Build Input이 PGA 메모리에 담길 때(In-Memory) 해시 조인이 효과적이다.
▶️ Build Input이 Hash Area 크기를 초과해 Temp 테이블스페이스에 쓰게 되더라도 대량 데이터 조인 시 일반적으로 해시 조인이 가장 빠르다.
3.3 대용량 Build Input 처리
만약 두 테이블 모두 다 대용량 테이블이어서 인메모리 해시 조인이 불가능한 상황일 때 어떻게 처리해야 할까?
바로 분할·정복 방식(Divied & Conquer) 방식인 아래 두 단계로 나눠서 진행하면 된다.
1️⃣ 파티션 단계
● 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고 반환된 해시 값에 따라 동적으로 파티셔닝 한다.
● 독립적으로 처리할 수 있는 여러 개의 작은 서브 집합으로 분할함으로써 파티션 짝(pair)을 생성하는 단계이다.
● 양쪽 집합(T1, T2)을 읽어 디스크 Temp 공간에 저장해야 하므로 인메모리 해시 조인보다 성능이 많이 떨어진다.
2️⃣ 조인단계
● 파티션 단계를 완료하면 각 파티션 짝(pair)에 하나씩 조인을 수행한다.
● 이때 각각에 대한 Build Input과 Probe Input은 독립적으로 결정된다. (파티션 전에 어느 테이블이 작은 테이블이었는지는 상관없음)
● 해시 테이블(Build Input)을 생성하고 나면 반대쪽(Probe Input) 파티션 로우를 하나씩 읽으면서 해시 테이블을 탐색한다. 모든 짝에 대한 처리를 마칠 때까지 이 과정을 반복한다.
3.4 해시 조인 실행 계획 제어
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
위 쿼리는 use_hash 힌트만 이용해 Build Input을 옵티마이저가 선택을 하며 일반적으로 둘 중 카디널리티(각 테이블 조건절에 대한)가 작은 테이블을 선택한다.
실행계획은 다음과 같다.
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT 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)위쪽 사원 데이터(Build Input)로 해시 테이블을 생성한 후 아래쪽 고객 테이블(Probe Input)에서 읽은 조인 키 값으로 해시 테이블을 탐색하면서 조인한다.
만약 Build Input을 직접 지정하고 싶다면 leading이나 ordered 힌트를 사용하면 된다. 이들 힌트를 지시한 순서에 따라 가장 먼저 읽는 테이블을 Build Input으로 선택한다.
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 힌트로 Build 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
😎 세 개 이상 테이블 해시 조인
조인 대상 테이블이 세 개 이상이어도 원리만 제대로 이해하면 의외로 간단하다.
예를 들어 A, B, C 세 개 테이블이 있고, 이 세 개 테이블을 조인하는 경로는 아래와 같이 두 가지이다.
경로 1, 경로 2를 쿼리로 표현하면 아래와 같다.
-- 경로 1
SELECT *
FROM A, B, C
WHERE A.key = B.key
AND B.key = C.key
-- 경로 2
SELECT *
FROM A, B, C
WHERE A.key = B.key
AND A.key = C.key
경로 1과 경로 2를 다른 케이스로 보면, 힌트 지정하는 방법도 다양해지므로 어렵게 느껴진다.
그렇다면 경로 2를 아래와 같이 바꿔서 표현해 보자.
결국 세 테이블을 조인하는 경로는 단 한 가지다. (T1 = B, T2 = A, T3 = C)
이제 문제가 단순해졌다.
세 테이블에 대한 해시 조인 시 위 그림에 따라 leading 힌트를 지정해 주면 된다.
/*+ leading(T1, T2, T3) use_hash(T2) use_hash(T3) */
SELECT *
FROM T1, T2, T3
WHERE T1.key = T2.key
AND T2.key = T3.key
해시 조인에서 leading 힌트 첫 번째 파라미터로 지정한 테이블이 무조건 Build Input으로 선택된다. 따라서 위와 같이 힌트를 지정했을 때 나올 수 있는 실행 계획 패턴은 다음과 같다.
< 패턴 1 >
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 HASH JOIN
2 1 HASH JOIN
3 2 TABLE ACCESS (FULL) OF 'T1' (TABLE)
4 2 TABLE ACCESS (FULL) OF 'T2' (TABLE)
5 1 TABLE ACCESS (FULL) OF 'T2' (TABLE)
< 패턴 2 >
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 HASH JOIN
2 1 TABLE ACCESS (FULL) OF 'T3' (TABLE)
3 1 HASH JOIN
4 3 TABLE ACCESS (FULL) OF 'T1' (TABLE)
5 3 TABLE ACCESS (FULL) OF 'T2' (TABLE)
T1이 Build Input으로 선택된 상황에서 T2를 Build Input으로 선택하고 싶다면? swap_join_inputs 힌트를 사용하면 된다.
/*+ leading(T1, T2, T3) swap_join_inputs(T2) */
SELECT *
FROM T1, T2, T3
WHERE T1.key = T2.key
AND T2.key = T3.key
그러면 실행계획이 다음처럼 바뀐다.
< 패턴 1 >
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 HASH JOIN
2 1 HASH JOIN
3 2 TABLE ACCESS (FULL) OF 'T2' (TABLE)
4 2 TABLE ACCESS (FULL) OF 'T1' (TABLE)
5 1 TABLE ACCESS (FULL) OF 'T3' (TABLE)
< 패턴 2 >
Execution Plan
------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS
1 0 HASH JOIN
2 1 TABLE ACCESS (FULL) OF 'T3' (TABLE)
3 1 HASH JOIN
4 3 TABLE ACCESS (FULL) OF 'T2' (TABLE)
5 3 TABLE ACCESS (FULL) OF 'T1' (TABLE)
패턴 1을 T3을 Build Input으로 선택하는 패턴 2로 바꾸고 싶다면? 역시 swap_join_inputs을 활용하면 된다.
/*+ leading(T1, T2, T3) swap_join_inputs(T3) */
SELECT *
FROM T1, T2, T3
WHERE T1.key = T2.key
AND T2.key = T3.key;
/*+ leading(T1, T2, T3) swap_join_inputs(T2) swap_join_inputs(T3) */
SELECT *
FROM T1, T2, T3
WHERE T1.key = T2.key
AND T2.key = T3.key
만일 패턴 2를 T1과 T2가 조인한 결과집합인 패턴 1로 바꾸고 싶다면 no_swap_join_inputs을 사용하면 된다.
T3에 no_swap_join_inputs 힌트를 사용하여 Probe Input으로 지정한다.
/*+ leading(T1, T2, T3) no_swap_join_inputs(T3) */
SELECT *
FROM T1, T2, T3
WHERE T1.key = T2.key
AND T2.key = T3.key;
⭐조인하는 테이블이 네 개, 다섯 개 등 테이블이 몇 개든 원리는 똑같다. 조인 연결고리를 따라 순방향 또는 역방향으로 leading 힌트에 기술한 후 Build Input으로 선택하고 싶은 테이블을 swap_join_inputs 힌트에 지정해 준다.
또는 반대로 no_swap_join_inputs 힌트로 반대쪽 Probe Input을 지정해 준다.
3.5 조인 메소드 선택 기준
⭐ 일반적인 조인 메소드 선택 기준
1️⃣ 소량 데이터 조인 시 ➡️ NL 조인
2️⃣ 대량 데이터 조인 시 ➡️ 해시 조인
3️⃣ 대량 데이터 조인인데 조인 조건식이 등치(=) 조건이 아닐 때(조인 조건식이 아예 없는 카테시안 곱 포함) ➡️ 소트 머지 조인
⭐ 수행 빈도가 매우 높은 쿼리일 때 조인 메소드 선택 기준
1️⃣ (최적화된) NL 조인과 해시 조인 성능이 같다. ➡️ NL 조인
2️⃣ 해시 조인이 약간 더 빠르다. ➡️ NL 조인
3️⃣ NL 조인보다 해시 조인이 매우 빠르다. ➡️ 해시 조인
수행빈도가 높은 소량 데이터 조인은 설령 해시 조인이 약간 더 빠르더라도 NL 조인을 선택하도록 한다.
✔️ 왜 NL 조인을 가장 먼저 고려해야 할까?
- NL 조인에 사용하는 인덱스는 영구적(Drop 되지 않는 이상)으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조이다.
- 반면 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다.
- 같은 쿼리를 100개 프로세스가 동시 수행하면 해시 테이블도 100개 만들어진다.
- 따라서 수행 시간이 짧으면서 수행 빈도가 매우 높은 쿼리를 해시조인으로 처리 시 CPU와 메모리 사용률이 크게 증가하고, 해시 맵을 만드는 과정에 여러 가지 래치 경합도 발생한다.
결론적으로 해시 조인은 아래 세 가지 조건을 만족할 때 주로 사용한다.
1️⃣ 수행 빈도가 낮고
2️⃣ 쿼리 수행시간이 오래 걸리는
3️⃣ 대량 데이터 조인 시
➡️ ➡️ ➡️ 배치 프로그램, DW, OLAP 성 쿼리의 특징
4. 서브 쿼리 조인
4.1 서브 쿼리 변환이 필요한 이유
최근 옵티마이저는 비용(Cost)을 평가하고 실행계획을 생성하기에 앞서 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업, 즉 쿼리 변환(Query Transformation)부터 진행한다.
쿼리변환이란 옵티마이저가 SQL을 분석해 의미적으로 동일(같은 결과 집합 생성)하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.
서브쿼리(Subquery)는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록(Query Block)을 말한다.
서브쿼리를 DBMS마다 조금씩 다르게 분류하며, 오라클은 아래 세 가지로 분류한다.
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.종료일시)
1️⃣인라인뷰(Inline View) : FROM 절에 사용한 서브쿼리
2️⃣중첩된 서브쿼리(Nested Subquery) : 결과집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리, 서브쿼리가 메인 쿼리를 참조하는 형태를 '상관관계있는(Correlated) 서브쿼리'라고 한다.
3️⃣스칼라서브쿼리(Scalar Subquery) : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리, 주로 SELECT-LIST에서 사용하지만 몇 가지 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
이들 서브쿼리를 참조하는 메인 쿼리도 하나의 쿼리 블록이며, 옵티마이저는 쿼리 블록 단위로 최적화를 수행한다.
아래 원본 쿼리 실행 시 메인 쿼리(쿼리 블록 1)와 Exists 서브 쿼리(쿼리블록 2)를 각각 최적화한다.
<원본 쿼리>
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
<쿼리 블록 1>
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_month(sysdate - 1), 'mm')
<쿼리 블록 2>
select 'x'
from 거래
where 고객번호 = :cust_no -- 메인 쿼리를 참조하는 조건절은 변수로 처리
and 거래일시 >= trunc(sysdate, 'mm')
4.2 서브쿼리와 조인
메인 쿼리와 서브 쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다.
서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없으며 메인쿼리 건수만큼 값을 받아 반복적으로 필터링하는 방식으로 실행하게 된다.
😎 필터 오퍼레이션
< 예시 쿼리 >
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ no_unnest */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm') )
< 실행 계획 >
Execution Plan
-----------------------------------------------------------------------------------
0 SELECT STATEMENT 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)
< PL/SQL 로직 표현 >
begin
for outer in (select 고객번호, 고객명 from 고객 where ...)
loop
for inner in (select 'x' from 거래 where 고객번호 = outer.고객번호 and ...)
loop
dbms_output.put_line(outer.고객번호 || ',' || outer.고객명);
exit; -- 조인에 성공하면 inner loop exit
end loop;
end loop;
end;위 쿼리의 no_unnest는 서브쿼리를 풀어내지 말고 그대로 수행하도록 지시하는 힌트로 필터 방식으로 처리하게 된다.
⭐ 필터(Filter) 오퍼레이션과 NL 조인의 차이점
필터 오퍼레이션은 NL 조인과 처리 루틴이 비슷하지만 차이점이 있다.
1️⃣첫 번째로 필터는 메인쿼리(고객)의 한 로우가 서브쿼리(거래)의 한 로우와 조인에 성공하는 순간 진행을 멈추고 메인쿼리의 다음 로우를 계속 처리한다는 점이 다르다. (PL/SQL로 표현한 로직 참고!)
2️⃣두 번째는 필터는 서브쿼리 입력값에 따른 반환 값(true or false)을 캐싱하는 기능을 갖는다.
캐싱은 쿼리 단위로 이루어진다. 쿼리를 시작할 때 PGA 메모리에 공간을 할당하고, 쿼리를 수행하면서 공간을 채워나가며 쿼리를 마치는 순간 공간을 반환한다.
3️⃣세 번째는 필터 서브쿼리는 메인쿼리에 종속되므로 조인 순서가 고정되며 항상 메인쿼리가 드라이빙 집합이다.
😎 서브 쿼리 Unnesting
아래는 서브쿼리를 Unnesting 할 때의 실행계획인데 사실 그냥 두어도 옵티마이저가 대게 Unnesting을 선택한다.
'nest'는 '중첩'을 의미하며 접두사에 'unnest'는 '중첩된 상태를 풀어내라'라는 뜻으로 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨(flat) 구조로 만들어 준다는 의미에서 '서브쿼리 Flattening'이라고 부르기도 한다.
< 예시 쿼리 >
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'))
< 실행 계획 >
Execution Plan
-----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS(Cost=384 Card=190 Bytes=11K)
1 0 NESTED LOOPS (SEMI) (Cost=384 Card=190 Bytes=11K)
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=2 Card=427K Bytes=9M)
서브쿼리를 nesting 하면 필터 방식을 사용할 수밖에 없지만 서브쿼리를 Unnesting 하게 되면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다.
위 쿼리에서는 unnest와 nl_sj 힌트를 함께 사용해 NL 세미조인 방식으로 실행되었다.
세미조인: 두 테이블 조인 시 한 테이블의 데이터가 다른 테이블에 존재하는지 여부만을 기준으로 결과를 반환하는 조인 방식(EXISTS, IN)
Unnesting 된 서브쿼리는 NL 세미조인 외에도 다양한 방식으로 실행될 수 있다.
예를 들어 필터방식은 항상 메인쿼리가 드라이빙 집합이라면 Unnesting 된 서브쿼리는 아래 leading 힌트를 활용하여 메인 쿼리 집합보다 먼저 처리되게 할 수 있다.
< 예시 쿼리 >
select /*+ leading(거래@subq) use_nl(c) */c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ qb_name(subq) unnest */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm'))
< 실행 계획 >
Execution Plan
-----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS(Cost=253K Card=190 Bytes=11K)
1 0 NESTED LOOPS
2 1 NESTED LOOPS (Cost=253K Card=190 Bytes=11K)
3 2 SORT (UNIQUE) (Cost=2K Card=427K Bytes=9M)
4 3 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2K ...)
5 4 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=988 Card=427K)
6 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=190)
7 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=1 ...)
‼️힌트 설명 ‼️
✔️ leading(거래@subq)
- leading 힌트는 조인의 순서를 지정할 때 사용하며 거래 테이블을 subq라는 이름을 가진 서브쿼리 내에서 첫 번째로 처리하도록 지시한다. 이 힌트를 사용하여 거래 테이블을 먼저 접근하게 된다.
✔️ qp_name(subq)
- 서브쿼리의 이름을 명시적으로 지정하는 힌트, 실행계획에서 해당 서브쿼리를 더 쉽게 추적하고 조정 가능
서브쿼리를 그대로 풀어서 조인하면 메인 쿼리 결과집합(고객)이 서브쿼리 M 쪽 집합(거래) 수준으로 확장될 수 있으므로 서브쿼리 집합에 대한 Sort Unique 오퍼레이션부터 수행하게 된다.
서브쿼리 집합에서 고객번호 중복을 제거하기 위해 쿼리가 아래와 같이 변환된 것이다.
select /*+ no_merge(t) leading(t) use_nl(c) */ c.고객번호, c.고객명
from (select distint 고객번호
from 거래
where 거래일시 >= trunc(sysdate, 'mm')) t, 고객 c
where c.가입일시 >= trunc(add_months(sysadte, -1), 'mm')
and c.고객번호 = t.고객번호
아래는 서브쿼리를 unnesting 하고 나서 해시 세미 조인 방식으로 실행되도록 hash_sj 힌트를 사용했을 때 실행계획이다.
< 예시 쿼리 >
select c.고객번호, c.고객명
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
and exists (
select /*+ unnest hash_sj */ 'x'
from 거래
where 고객번호 = c.고객번호
and 거래일시 >= trunc(sysdate, 'mm'))
< 실행 계획 >
Execution Plan
-----------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS(Cost=2K Card=38 Bytes=2K)
1 0 FILTER
2 1 HASH JOIN (SEMI) (Cost=2K Card=38 Bytes=2K)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=38 ...)
4 3 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=38)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2K ...)
6 5 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=988 Card=427K)
이처럼 옵티마이저의 선택 또는 힌트를 사용해서 서브쿼리를 Unnesting 해 메인쿼리와 같은 레벨로 만들면, 다양한 조인 메소드를 선택할 수 있고, 조인 순서도 정할 수 있다. 옵티마이저는 많은 조인 테크닉을 가지므로 조인 형태로 변환했을 때 필터 오퍼레이션보다 더 좋은 실행경로를 찾을 가능성이 높아진다.
⭐ ROWNUM 주의사항
데이터 존재 여부를 확인하는 Exists 서브쿼리에서 rownum <= 1을 사용하면 성능의 문제를 일으킬 수 있다.
rownum을 쓰는 순간 서브쿼리 Unnesting을 방지하여 옵티마이징 기능을 사용하지 못하게 막는 효과가 있다.
이때 /*+ unnest nl_sj*/ 과 같은 힌트를 사용해도 rownum이 그 조건을 막아 Unnesting 되지 못한다.
일부러 Unnesting을 방지하려는 목적이 아니면 서브쿼리에는 rownum을 함부로 쓰지 말자.
⭐ 서브쿼리 Pushing
앞에서 설명한 것처럼 Unnesting 되지 않은 서브쿼리는 항상 필터방식으로 처리되며, 대게 실행계획 상에서 맨 마지막 단계에 처리되는데 서브쿼리 필터링을 먼저 처리함으로써 조인 단계로 넘어가는 로우 수를 크게 줄일 수 있다면 성능을 크게 향상시킬 수 있다. 이때 push_subq 힌트를 사용하여 서브 쿼리 필터링을 먼저 처리하게 할 수 있다.
< 예시 쿼리 >
select /*+ leading(p) use_nl(t) */ count(distinct p.상품번호), sum(t.주문금액)
from 상품 p, 주문 t
where p.등록일시 >= trunc(add_months(sysdate, -3), 'mm')
and t.주문일시 >= trunc(sysdate -7)
and exists (
select /*+ no_unnest push_subq */ 'x'
from 상품분류
where 상품분류코드 = p.상품분류코드
and 상위분류코드 = 'AK')
Rows Row Source Operation
------- --------------------------------------------------------------------------
0 STATEMENT
1 SORT AGGREGATE(cr=1903 pr=0 pw=0 time=128934 us)
3000 NESTED LOOPS (cr=1903 pr=0 pw=0 time=153252 us)
150 TABLE ACCESS FULL 상품 (cr=101 pr=0 pw=0 time=18230 us)
1 TABLE ACCESS BY INDEX ROWID 상품분류 (cr=6 pr=0 pw=0 time=135 us) /**상품 분류 먼저 실행! **/
3 INDEX UNIQUE SCAN 상품분류_PK (cr=3 pr=0 pw=0 time=63 us)
3000 TABLE ACCESS BY INDEX ROWID 주문 (cr=1802 pr=0 pw=0 time=100092 us)
3000 INDEX RANGE SCAN 주문_PK (cr=302 pr=0 pw=0 time=41733 us)
Pushing 서브쿼리는 이처럼 서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 기능으로 push_subq / no_push_subq 힌트로 제어한다. no_push_subq 힌트 사용 시 서브쿼리 필터링을 가능한 한 나중에 처리하도록 지시한다.
단 이 기능은 Unnesting 되지 않은 서브쿼리에만 작동한다.
따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법이다.
4.3 뷰(View)와 조인
옵티마이저가 인라인 뷰(View) 쿼리를 변환하지 않는다면 최적화 단위가 쿼리 블록이므로 뷰 쿼리 블록은 독립적으로 최적화 한다.
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.고객번호
< 실행 계획 >
Execution Plan
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=1M Card=1K Bytes=112K)
1 0 NESTED LOOPS
2 1 NESTED LOOPS (Cost=1M Card=1K Bytes=112K)
3 2 VIEW (Cost=2K Card=427K Bytes=21M)
4 3 HASH (GROUP BY) (Cost=2K Card=427K Bytes=14M)
5 4 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=2K ...)
6 5 INDEX (RANGE SCAN) OF '거래_X01' (INDEX (Cost=988 Card=427K)
7 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=190)
8 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=3 Card=1 ...)
뷰를 독립적으로 최적화 하게 되는 경우로 예를들어 위 쿼리처럼 당월 거래 전체를 읽어 고객번호 수준으로 Group By 한 후 고객 테이블을 조인할 때 인라인 뷰 바깥에서 가입 일시 조건으로 필터링을 하게 된다면 성능이 좋지 않을 수 있다.
이 때 merge 힌트를 이용해 뷰를 메인 쿼리와 머징(Merging)하도록 한다. 반대로 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.고객번호
< 실행 계획 >
Execution Plan
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=27)
1 0 HASH (GROUP BY) (Cost=4 Card=1 Bytes=27)
2 1 NESTED LOOPS (Cost=3 Card=5 Bytes=135)
3 2 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=2 Card=1 ...)
4 3 INDEX (RANGE SCAN) OF '고객_X01' (INDEX (Cost=1 Card=1)
5 2 TABLE ACCESS (BY INDEX ROWID) OF '거래' (TABLE) (Cost=1 Card=5 ...)
6 5 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=0 Card=5)
< 변환된 쿼리 >
select c.고객번호, c.고객명, avg(t.거래금액) 평균거래
, min(t.거래금액) 최소거래, max(t.거래금액) 최대거래
from 고객 c, 거래 t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호
and t.거래일시 >= trunc(sysdate, 'mm') -- 당월 발생한 거래
group by c.고객번호, c.고객명
위 쿼리에서 뷰 머징 시 단점은 조인에 성공한 전체 집합을 Group By 하고서야 데이터를 출력할 수 있다. 즉 부분범위 처리가 불가능하다. 부분 범위처리가 불가능한 상황에선 NL 조인은 좋은 선택이 아니며 이런 상황에선 보통 해시 조인이 빠르다.
😎 조인 조건 Pushdown
11g 이후로 조인 조건 Pushdown 이라는 쿼리 변환 기능이 작동하는데 메인 쿼리를 실행하면서 조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 기능이다.
실행계획에는 'VIEW PUSHED PREDICATE' 오퍼레이션으로 나타난다.
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (select /*+ no_merge push_pred */ 고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm') -- 당월 발생한 거래
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm') -- 전월 이후 가입 고객
and t.고객번호 = c.고객번호
< 실행 계획 >
Execution Plan
--------------------------------------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=4 Card=1 Bytes=61)
1 0 NESTED LOOPS (Cost=4 Card=1 Bytes=61)
2 1 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '고객' (TABLE) (Cost=2 ...)
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=1 Card=1)
4 1 VIEW PUSHED PREDICATE (Cost=2 Card=1 Bytes=41) /* PUSHDOWN! */
5 4 SORT (GROUP BY) (Cost=2 Card=1 Bytes=7)
6 5 TABLE ACCESS (BY INDEX ROWID BATCHED) OF '거래' (TABLE) (Cost=2 ...)
7 6 INDEX (RANGE SCAN) OF '거래_X02' (INDEX) (Cost=1 Card=5)
< 변환된 쿼리 >
select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c
, (select /*+ no_merge push_pred */ 고객번호, avg(거래금액) 평균거래
, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= trunc(sysdate, 'mm') -- 당월 발생한 거래
and 고객번호 = c.고객번호 /* 조인 조건이 밀어넣어짐 */
group by 고객번호) t
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')위 변환된 쿼리는 원래는 허용되지 않는 문법이지만 옵티마이저가 내부적으로 쿼리를 이와 같은 형태로 최적화 해준다.
참고로 직접 해당 쿼리를 실행하게 되면 에러 (ORA-00904) 가 발생한다.
이 방식을 사용하면 전월 이후 가입한 고객 대상으로 '건건이' 당월 거래 데이터만 읽어 조인하고 Group By를 수행한다. 즉 부분 범위 처리가 가능하다. 이 기능을 제어하는 힌트는 push_pred 이며 옵티마이저가 뷰를 머징하면 해당 힌트가 작동하지 않으니 꼭 no_merge 힌트를 함께 사용하도록 한다.
⭐ Lateral 인라인 뷰, Cross/Outer Apply 조인인
원래 인라인 뷰 안에서 메인 쿼리 테이블 컬럼 참시 에러 (ORA-00904) 가 발생하는데 오라클 12c 부터 인라인 뷰를 아래와 같이 선언하면 인라인 뷰 안에서 메인 쿼리 테이블의 컬럼을 참조할 수 있다.
▶️ Lateral 인라인 뷰
select *
from 사원 e, LATERAL (select *
from 조직
where 조직코드 = e.조직코드)
▶️ Lateral 인라인 뷰 (Outer 조인)
select *
from 사원 e, LATERAL (select *
from 조직
where 조직코드 = e.조직코드) (+)
▶️ Outer Apply 조인 (Outer 조인)
select *
from 사원 e
OUTER APPLY (select *
from 조직
where 조직코드 = e.조직코드)
▶️ Cross Apply 조인 ( Lateral 인라인 뷰와 기능적으로 같음)
select *
from 사원 e
CROSS APPLY (select *
from 조직
where 조직코드 = e.조직코드);
‼️기존에 익숙한 구문으로도 충분히 원하는 실행계획을 자유롭게 만들어 낼 수 있기 때문에 튜닝 과정에 알 수 없는 이유로 조인 조건 pushdown 기능이 잘 작동하지 않을 때만 활용하도록 한다.
4.4 스칼라 서브쿼리 조인
사용자 정의 함수가 포함된 쿼리를 실행하게 되면 함수 안에 는 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 조인문처럼 하나의 문장으로 이해하면 된다. 차이가 있다면 스칼라 서브쿼리는 처리 과정에서 캐싱 작용이 일어난다.
select /*+ ordered use_nl(d) */ e.empno, e.ename, e.sal, e.hiredate, d.dname
from emp e, dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000
⭐ 스칼라 서브쿼리 캐싱 효과
스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화 하기 위해 입력 값과 출력 값을 내부 캐시(Query Execution Cache)에 저장해두고 조인할 때마다 캐시에서 '입력값'을 찾아보고 찾으면 저장된 '출력값'을 반환한다.
캐시에서 찾지 못하는 경우만 조인을 수행하며 결과는 버리지 않고 캐시에 저장해둔다.
아래 스칼라 서브쿼리에서 입력 값은 e.deptno 이고 출력값은 d.dname 이다.
, (select d.dname from dept d where d.deptno = e.deptno) as dname
스칼라 서브쿼리 캐싱은 필터 서브쿼리 캐싱과 같은 기능이다. 메인 쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면 조인 수행횟수를 최소화 할 수 있어 성능을 높일 수 있다.
캐싱은 쿼리 단위로 이루어지며, 쿼리를 시작할 때 PGA 메모리 공간을 할당하고 쿼리를 수행하면서 공간을 채워나가며 쿼리를 마치는 순간 공간을 반환한다.
많이 활용되는 튜닝 기법으로 SELECT-LIST에 사용한 함수는 메인 쿼리 결과 건수만큼 반복 수행되는데 아래와 같이 스칼라 서브쿼리를 덧씌우면 캐싱 효과 덕분에 호출 횟수를 최소화 할 수 있다.
select empno, ename, sal, hiredate, (select GET_DNAME(e.deptno) from dual) dname
from emp e
where sal >= 2000
⭐ 스칼라 서브쿼리 캐싱 부작용
스칼라 서브쿼리에 사용하는 캐시도 매우 작은 메모리 공간으로 오라클 8i, 9i 기준으로 256개 엔트리를 캐싱하고, 10g 이후로는 입력과 출력 값 크기, _query_execution_cache_max_size 파라미터에 의해 사이즈를 결정한다.
그래서 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있으며 반대의 경우엔 캐시를 매번 확인하는 비용 때문에 오히려 성능이 나빠지고 CPU 및 메모리 사용률만 높아진다.
또 메인 쿼리 집합이 클수록 캐시 재사용성이 높아 효과가 큰데 반대로 메인 쿼리 집합(소수의 결과 집합) 이 작으면 캐시 재사용성이 낮아진다. 이런 경우 오히려 성능을 떨어트릴 수 있다.
⭐ 두개 이상의 값 반환
스칼라 서브쿼리에는 치명적인 제약이 하나 있는데 두 개 이상의 값을 반환할 수 없다는 제약이다.
< 스칼라 서브쿼리는 두개 이상의 값 반환 안됨 >
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 튜너들이 전통적으로 많이 사용해온 방식은 아래와 같다.
select 고객번호, 고객명
, to_number(substr(거래금액, 1, 10)) 평균거래금액
, to_number(substr(거래금액, 11, 10)) 최소거래금액
, to_number(substr(거래금액, 21)) 평대거래금액
from (
select c.고객번호, c.고객명
, (select lapd(avg(거래금액), 10), lpad(min(거래금액), 10), max(거래금액)
from 거래
where 거래일시 >= trunc(sysdate, 'mm')
and 고객번호 = c.고객번호) 거래금액
from 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
)
두 개 이상의 값을 반환하고 싶을 때, 스칼라 서브쿼리 대신 인라인 뷰를 사용하면 편하다. 하지만 뷰를 사용하면 뷰가 머징되지 않았을 때 group by 때문에 부분범위 처리가 안되는 문제가 있을수 있는데 이는 위에서 배운 11g에서 추가된 '조인조건 pushdown' 으로 해결 가능하다.
⭐ 스칼라 서브쿼리 Unnesting
스칼라 서브쿼리는 NL 방식으로 조인하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다. 특히 병렬(Parallel) 쿼리에서는 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 한다. 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과적이다.
오라클 12c 부터 스칼라 서브쿼리도 Unnesting이 가능해져 옵티마이저가 사용자 대신 자동으로 쿼리를 변환해준다.
이 기능은 _optimizer_unnest_scalar_sq 파라미터를 true/false로 설정하여 활성화 할 수 있는데 이 파라미터를 true로 설정 시 스칼라 서브쿼리를 Unnesting 할지 여부를 옵티마이저가 알아서 결정해주며, false로 설정 시 옵티마이저가 알아서 결정하지는 않지만 사용자가 unnest 힌트를 사용하여 유도할 수 있다.
또는 스칼라 서브쿼리 Unnesting으로 인해 일부 쿼리에 문제 발생 시 파라미터를 false 로 설정하지 않고 no_unnest 힌트를 이용해 부분적으로 문제를 해결할수도 있다.
-- 파라미터 False일 때 unnest힌트를 사용해 해시 outer 조인으로 수행됨
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')
-- 파라미터 True일 때 NL조인으로 수행되도록 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')
'개발 독서 스터디 > 친절한 SQL 튜닝' 카테고리의 다른 글
| 6회차 DML 튜닝 - 1 (0) | 2025.03.30 |
|---|---|
| 5회차 소트 튜닝 (1) | 2025.03.16 |
| 3회차 인덱스 튜닝 - 3 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 2 (0) | 2025.02.08 |
| 3회차 인덱스 튜닝 - 1 (1) | 2025.02.08 |
