친절한 SQL 튜닝 4장 정리( 조인 튜닝 )

조인 튜닝

1. NL조인

조인의 기본은 NL조인이다.

NL조인은 인덱스를 이용한 조인이다.

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

일반적으로 NL조인은 OUTER와 INNER 양쪽 테이블 모두 인덱스를 이용한다. OUTER테이블 사이즈가 크지 않다면 인덱스를 이용하지 않을 수 있다. ( TABLE FULL SCAN 한 번에 처리 )

NL조인 순서
1. 1번 테이블 인덱스에서 첫 번째 레코드를 찾는다.
2. ROWID로 1번 테이블 레코드를 찾아간다.
3. 1번 테이블 인덱스에서 읽은 레코드로 2번 테이블 인덱스를 탐색한다.
4. ROWID로 2번 테이블 레코드를 찾는다.
5. 2번 인덱스에서 2번 테이블을 한 건 더 스캔한다.
6. 원하는 결과를 모두 찾으면 스캔 종료

기본적인 조인 프로세싱은 전부 위와 같다.( NL, 해쉬, 소트 머지 )

NL조인 힌트 => use_nl

FROM절에 기술한 순서대로 조인 힌트 => ordered

직접 테이블 읽기 순서 지정 힌트=> leading

index를 지정하지 않으면 옵티마이저가 결정

맨 처음 액세스하는 인덱스에서 얻은 결과 건수에 따라 전체 일량의 차이가 난다.

올바른 조인 메소드 선택 방법
1. OLTP 시스템에선 1차적으로 NL조인부터 고려
2. 랜덤 액세스 발생 지점 파악
3. 조인 순서 변경
4. 효과적인 인덱스 있는지 확인
5. 인덱스 추가 또는 구성 변경
6. NL조인으로 성능 개선 불가 시 소트 머지 OR 해시 조인 검토

NL조인 특징
1. 랜덤 액세스 위주의 조인 방식
2. 인덱스 구성이 완벽해도 대량 데이터 조인할 때 NL조인이 불리하다.
3. 조인을 한 레코드씩 순차적으로 진행
4. 부분범위 처리 활용이 필요하다.
5. 소량 데이터 처리 OR 부분범위 처리하는 OLTP 시스템에 적합한 조인 방식이다.

테이블 조인 시 효율이 좋지 않은 경우 조인 순서 변경을 고려해보는 것도 좋다.

조인 순서를 바꿔도 별 소득이 없다면, 소트 머지 조인이나 해시 조인을 검토해야 한다.

---

2. 소트 머지 조인

1. 조인 컬럼에 인덱스가 없을 때
2.  대량 데이터 조인이어서 인덱스가 효과적이지 않을 때
3. 해시 조인을 사용할 수 없는 상황에서 대량 데이터를 조인하고자 할 때

SGA
1. 캐시된 데이터는 여러 프로세스가 공유한다.
2. 동시에 액세스할 수 없다.
3. LOCK매커니즘으로서 래치가 존재한다.
4. DB버퍼캐시가 핵심적인 구성요소이다.
5. 블록을 읽기위해 버퍼 LOCK도 얻어야 한다.

PGA
1. 오라클 서버 프로세스에 할당된 메모리 영역
2. PGA공간이 작아 데이터를 모두 저장하지 못할 경우 TEMP 테이블 스페이스를 이용한다.
3. 다른 프로레스와 공유하지 않는 독립적인 메모리 공간이다.
4. 래치 매커니즘이 불필요하다.
5. 같은 양일 경우 SGA버퍼캐시에서 읽을 때보다 빠르다.

소트 머지 조인의 기본 매커니즘
1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지한다.

소트 머지 조인 힌트 => use_merge

소트 머지 조인은 SORT AREA에 저장한 뎅터 자체가 인덱스 역할을 하므로 인덱스가 없어도 사용할 수 있는 조인 방식이다.

NL조인과 다른 점은 SORT AREA에 미리 정렬해 둔 자료구조를 이용한다는 점만 다르다.

소트 머지 조인 정리
1. 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽는다.
2. PGA 또는 테이블 스페이스에 저장 후 조인한다.
3. 독립적인 메모리 공간이므로 래치 흭득이 필요없다.
4. DB버퍼캐시를 경유한다.
5. 조인 조건식이 등치(=)조건이 아닌 대량 데이터 조인일 때 사용한다.
6. 조인 조건식이 아예 없는 조인일 때 사용한다.
7. 조인 컬럼 인덱스가 없는 상황에서 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리하다.

---

3. 해시 조인

해시 조인의 기본 매커니즘
1. BUILD단계 : 작은쪽 테이블을 읽어 해시 테이블에 생성한다.
2. PROBE단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인한다.( NL조인이랑 같다 )

해시 조인 힌트 => use_hash

HASH AREA에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 NL조인과 같다.

해시 조인이 빠른 이유 2가지
1. 해시 테이블을 PGA 영역에 할당한다.
2. 래치 흭득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.

해시 테이블에는 조인 키값뿐만 아니라 SQL에 사용한 컬럼을 모두 저장한다.

소트 머지 조인과 다른 점
1. PGA공간을 다 쓸 경우 테이블 스페이스를 이용하는데 이 때 디스크에 쓰는 작업을 반드시 수행한다. ( 소트 머지 조인은 양쪽 테이블을 정렬하기 때문에 대량인 경우 PGA만으로는 부족한 경우가 많다. )
2. 해시 조인은 작은 집합을 HASH AREA에 담기 때문에 엄청나게 큰 경우가 아니라면 디스크에 쓰는 작업은 일어나지 않는다.
3. HASH AREA 크기를 초과한다고 다른 조인을 쓸 필요는 없고 테이블스페이스를 사용한다 하더라도 해시 조인이 가장 빠르다.

양쪽 테이블 모두 대용량일 경우 해시 조인이 불가능하기 때문에 분할/정복 방식을 사용한다. ( 파티션 짝 )

use_hash(a, b) 이런식으로 힌트를 사용하면 옵티마이저가 해쉬 테이블에 담을 작은 테이블을 알아서 선택하는데, 사용자가 직접 선택하고 싶다면 leading 이나 ordered 힌트를 사용하면 된다.

옵티마이저는 힌트로 지시한 순서에 따라 가장 먼저 읽는 테이블을 HASH AREA에 Build Input(카디널리티가 작은 테이블)으로 선택한다.

leading 힌트 첫 번째 파라미터로 지정한 테이블은 무조건 Build Input으로 선택된다.

그래도 다른 테이블로 지정하고 싶다면 swap_join_inputs(테이블)  힌트를 사용하면 된다.

반대로 테이블이 여러개(a,b,c)일 경우 a와 b의 결과 집합과 조인할 테이블을 선택하고 싶다면 no_swap_join_inputs(테이블명) 힌트를 사용하면 된다.

해시 조인 사용법 정리
1. leading 힌트 기술
2. Build input으로 선택하고 싶은 테이블을 swap_join_inputs()힌트 사용 or no_swap_join_inputs() 힌트 사용


상황에 따른 조인 사용법
1. 소량 데이터 조인 => NL조인
2. 대량 데이터 조인 => 해쉬 조인
3. 대량 데이터 조인이지만 해시 조인으로 못할 경우 => 소트 머지 조인
4. NL조인과 해시조인 성능이 같으면 => NL조인
5. 해시 조인이 약간 더 빨라도 => NL조인
6. NL조인보다 해시 조인이 매우 빠른 경우 => 해시 조인

소량과 대량의 기준은 NL조인으로 최적화했는데도 느리면 대량 데이터 조인이다.

NL조인에 사용하는 인덱스는 영구적으로 다양한 쿼리를 위해 공유 및 재사용하는 자료구조이다.

해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조이다.

해시 조인을 사용해야 하는 쿼리
1. 수행 빈도가 낮음
2. 쿼리 수행 시간이 오래 걸린다.
3. 대량 데이터를 조인한다.

해시 조인 => 배치, DW, OLAP성 쿼리에 적용

---

4. 서브쿼리 조인

SELECT 절 조인문 => 스칼라
FROM 절 조인문 => 인라인 뷰
WHERE 절 조인문 => 중첩된 서브쿼리

옵티마이저가 나무가 아닌 숲 전체를 바라보는 관점에서 쿼리를 이해하려면 먼저 서브쿼리를 풀어내야 한다.

메인쿼리와 서브쿼리 간에는 종속적이고 계층적인 관계가 존재한다.

서브쿼리를 풀지말라는 힌트 -> no_unnest 대게 옵티마이저는 이 방식을 사용한다.

FILTER => LESTED LOOPS같은 해석

항상 메인쿼리가 드라이빙 집합이다.

unnest => 중첩된 상태를 풀어내라라는 뜻

서로 같은 레벨로 만들어준다는 의미에서 서브쿼리 Flattening 이라고도 한다.

필터방식은 항상 메인쿼리가 드라이빙 집합이지만, unnest된 서브쿼리는 메인 쿼리 집합보다 먼저 처리될 수 있다.

서브쿼리를 메인쿼리와 같은 레벨로 만들면 다양한 조인 메소드를 선택할 수 있고 조인 순서도 마음껏 정할 수 있다는게 장점.

Exists 서브쿼리에 rownum조건을 사용하면 중복이고 성능에 문제가 생길 수 있다.

rownum을 쓰면 옵티마이저에게 "이 서브쿼리 블록은 손대지 말라"고 선언하는 것과 다름없다.

unnest 되지 않은 서브쿼리는 항상 필터 방식이므로 실행계획 상에서 맨 마지막 단계에 처리된다.

서브쿼리 필터링을 가능한 앞 단계에서 처리하도록 강제하는 힌트 => push_subq/no_push_subq (unnest 되지 않은 서브쿼리에만 작동한다.)

따라서 push_seq와 no_unnset 힌트는 한 세트다.

반대로 서브쿼리를 마지막에 처리하게 하려면 no_push_seq, no_unnest를 같이 쓰면된다.

메인 쿼리와 인라인 뷰를 머지하기 위한 힌트 => merge

조인 조건절 값을 건건이 뷰 안으로 밀어 넣는 힌트 => no_merge , push_pred 두 개가 한 세트임.

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하기 위해 입력 값과 출력 값을 캐시에 저장한다.

메인쿼리 집합이 작으면 캐시 재사용성도 낮다.

스칼라 서브쿼리는 두 개 이상의 값을 반환할 수 없다는 제약이 있다.

11g 부터는 조인 조건 pushdown 기능이 작동하므로 인라인 뷰로 맘편히 써도 됨.

12c 부터는 _optimizer_unnest_scalar_sq 파라미터를 true로 설정하면 옵티마이저가 스칼라 서브쿼리를 unnest할지 여부를 정한다.

false로 설정하면 사용자가 unnest 힌트로 유도하면 된다.


4장 끝~