01 Nested Loops 조인
(1) 기본 메커니즘
(2) 힌트를 이용해 NL 조인을 제어하는 방법
ordered 힌트는 from 절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용
use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용
10g 부터는 leading 힌트에 2개이상 테이블을 기술할 수 있도록 기능이 개선돼,
from 절을 바꾸지않고도 마음껏 순서를 제어할 수 있게 되었다.
(3) NL 조인 수행 과정 분석
실행계획을 해석할 때, 형제 노드 간에는 위에서 아래로, 부모 자식 노드 간에는 안쪽에서 바깥쪽으로 자식노드부터 읽는다.
각 단계를 완료하고 나서 다음 단계로 넘어가는게 아니라 한 레코드씩 순차적으로 진행한다.
단, order by 는 전체 집합을 대상으로 정렬해야 하므로 작업을 모두 완료한 후에 오퍼레이션을 진행한다.
OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서다.
(4) NL 조인의 특징
1. Random 액세스 위주의 조인 방식
인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다.
2. 한 레코드씩 순차적으로 진행
먼저 액세스되는 테이블의 처리범위에 의해 전체 일량이 결정된다.
(5) NL 조인 튜닝 실습
트레이스에서 9i R2 부터는 각 처리단계별 논리적인 블록 요청 횟수 (cr), 디스크에서 읽은 블록수 (pr), 디스크에 쓴 블록 수 (pw) 표시
(6) 테이블 Prefetch
NL 조인에서 새 포맷의 실행계획이 나타나는 경우
- Inner 쪽 Non-Unique 인덱스를 Range Scan 할 때는 테이블 Prefetch 실행계획이 항상 나타난다.
- Inner 쪽 Unique 인덱스를 Non-Unique 조건 (모든 인덱스 구성컬럼이 '=' 조건이 아닐 때) 으로 Range Scan 할 때도 테이블 Prefetch 실행 계획이 항상 나타난다.
- Inner 쪽 Unique 인덱스를 Unique 조건 (모든 인덱스 구성컬럼이 '=' 조건) 으로 액세스할 때도 테이블 Prefetch 실행계획이 나타날 수 있다.
이 때 인덱스는 Range Scan 으로 액세스 한다. 테이블 Prefetch 실행계획이 안 나타날 때는 Unique Scan 으로 액세스한다.
(7) 배치 I/O
1. 드라이빙 테이블에서 일정량의 레코드를 읽어 Inner 쪽 인덱스와 조인하면서 중간 결과집합을 만든다.
2. 중간 결과집합이 일정량 쌓이면 Inner 쪽 테이블 레코드를 액세스한다.
이 때 테이블 블록을 버퍼캐시에서 찾으면 바로 최종결과집합에 담고, 못찾으면 중간 집합에 남겨둔다.
3. 2번 과정에서 남겨진 중간 집합에 대한 Inner 쪽 테이블 블록을 디스크로부터 읽는다.
이 때 Multiple Single Block I/O 방식을 사용한다.
4. 버퍼 캐시에 올라오면 테이블 레코드를 읽어 최종 결과집합에 담는다.
5. 모든 레코드를 처리하거나 사용자가 Fetch Call 을 중단할 때까지 1~4번 과정을 반복한다.
위 방식을 사용할 때 Inner 쪽 테이블 블록이 모두 버퍼 캐시에서 찾아지지 않으면 (버퍼캐시 히트율 < 100%)
즉, 실제 배치 I/O 가 작동한다면 데이터 정렬 순서가 달라질 수 있다.
(8) 버퍼 Pinning 효과
* 8i 에서 나타난 버퍼 Pinning 효과
테이블 블록에 대한 버퍼 Pinning 기능이 작동하기 시작
다른 레코드를 읽기 위해 Outer 쪽으로 돌아오는 순간 Pin을 해제
* 9i 에서 버퍼 Pinning 효과
Inner 쪽 인덱스 루트 블록에 대한 버퍼 Pinning 효과가 나타나기 시작
* 10g 에서 버퍼 Pinning 효과
하나의 Outer 레코드에 대한 Inner 쪽과의 조인을 마치고 Outer 쪽으로 돌아오더라도 테이블 블록에 대한 Pinning 상태를 유지
* 11g 에서 나타난 버퍼 Pinning 효과
루트 아래 인덱스 블록들도 Pinning 하기 시작
02 소트 머지 조인
(1) 기본 메커니즘
두 테이블을 각각 정렬한 다음에 두 집합을 머지 (Merge) 하면서 조인을 수행한다.
1. 소트단계: 양쪽 집합을 조인 컬럼 기준으로 정렬
2. 머지단계: 정렬된 양쪽 집합을 서로 머지
(2) 소트 머지 조인의 특징
소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다.
NL 조인은 조인 컬럼에 대한 인덱스 유무에 따라 크게 영향을 받지만 소트 머지 조인은 영향을 받지 않는다.
양쪽 집합을 개별적으로 읽고 나서 조인한다는 것도 특징이다.
스캔 (Scan) 위주의 액세스 방식을 사용한다는점
* 소트 머지 조인이 유용한 상황
- First 테이블에 소트 연산을 대체할 인덱스가 있을 때
- 조인할 First 집합이 이미 정렬돼 있을 때
- 조인 조건식이 등치 (=) 조건이 아닐 때
(3) First 테이블에 소트 연산을 대체할 인덱스가 있을 때
무조건 전체 범위 처리 방식이라고 알려졌지만 항상 그렇지는 않다.
해시 조인과 마찬가지로, 한쪽 집합 (Second 테이블) 은 전체 범위를 처리하고 다른 한쪽 (First 테이블) 은 일부만 읽고 멈추도록 할 수 있다.
* 소트 머지 조인에서의 부분범위 처리 활용
(4) 조인할 First 집합이 이미 정렬돼 있을 때
group by, order by, distinct 연산 등을 먼저 수행한 경우인데, 그때는 조인을 위해 다시 정렬하지 않아도 되므로 소트 머지 조인이 유리하다.
(5) 조인 조건식이 등치 (=) 조건이 아닐 때
03 해시 조인
(1) 기본 메커니즘
둘 중 작은 집합 (Build Input) 을 읽어 Hash Area 에 해시 테이블을 생성하고,
반대쪽 큰 집합 (Probe Input) 을 읽어 해시 테이블을 탐색하면서 조인하는 방식
PGA 메모리에 할당 되는 Hash Area (hash_area_size 참조) 에 담길 정도로 충분히 작아야 한다.
(2) 힌트를 이용한 조인 순서 및 Build Input 조정
(3) 두가지 해시 조인 알고리즘
(4) Build Input 이 Hash Area 를 초과할 때 처리 방식
In-Memory 해시 조인이 불가능 할 때
* Grace 해시 조인
1) 파티션 단계 - 조인되는 양쪽 집합 모두 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝을 실시한다.
2) 조인 단계 - 파티션 단계가 완료되면 파티션 짝에 대해 하나씩 조인을 수행한다.
* Hybrid 해시 조인
* Recursive 해시 조인 (=Nested-loops 해시 조인)
* 비트-벡터 필터링
(5) Build Input 해시 키 값에 중복이 많을 때 발생하는 비효율
(6) 해시 조인 사용 기준
* 해시 조인 성능 키포인트
- 한쪽 테이블이 Hash Area 에 담길 정도로 충분히 작아야 함
- Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함
* 해시 조인 언제 사용하는 것이 효과적인지
- 조인 컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때
- 조인 컬럼에 인덱스가 있더라도 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
- 소트 머지 조인하기에는 두 테이블이 너무 커 소트 부하가 심할 때
- 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때
04 조인 순서의 중요성
* 필터 조건이 없을 때
다른 필터 조건이 없는 상황에서는 작은쪽 집합을 드라이빙 하는 것이 유리함
* 필터 조건이 있을 때
인덱스 구성에 따라 유,불리가 결정되며, 비효율이 없게끔 인덱스를 잘 구성해 주기만 한다면 역시 작은쪽 집합을 드라이빙 하는 것이 유리하다.
* 소트 머지 조인과 해시 조인의 경우
소트 머지 조인은 디스크 소트가 발생할 정도의 큰 테이블을 포함할 때는 큰 테이블을 드라이빙하는 것이 더 빠르지만
메모리 소트 방식으로 조인할 때는 작은쪽 테이블을 드라이빙하는 것이 조검 더 빠르다.
해시 조인은 작은 쪽 테이블을 드라이빙 하는 것이 유리하다.
05 Outer 조인
(1) Outer NL 조인
(+) 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다. leading 힌트를 이용해도 소용없다.
최대한 안쓰는게 좋다.
* ERD 표기를 따르는 SQL 개발의 중요성
각 속성의 Null 값 허용 여부를 반드시 확인해야 하고, 엔티티 간 관계를 해석할 때도 카디널리티만 보지 말고 Optionality를 반드시 따져봐야 한다.
(2) Outer 소트 머지 조인
Outer 소트 머지 조인도 처리 방향이 한족으로 고정되며, Outer 기호가 붙지 않은 테이블이 항상 First 테이블로 선택된다.
leading 힌트를 사용해도 소용없다.
(3) Outer 해시 조인
해시 조인은 특히 대용량 테이블을 조인할 때 자주 사용되는데, Outer 조인할 때 조인 순서가 고정되다 보니 자주 성능 문제를 일으키곤 했다.
* Right Outer 해시 조인 탄생 배경
* 9i 이전 버전에서 Outer 해시 조인 튜닝
(4) Full Outer 조인
'Left Outer 조인 + Union All + Anti 조인 (Not Exists 필터)' 이용
ANSI Full Outer 조인
Native Hash Full Outer 조인
Union All 을 이용한 Full Outer 조인
06 스칼라 서브쿼리를 이용한 조인
(1) 스칼라 서브쿼리
함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 '스칼라 서브쿼리' 라고 한다.
(2) 스칼라 서브쿼리의 캐싱 효과
스칼라 서브쿼리의 해싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적은 때라야 효과가 있으며,
반대의 경우라면 캐시를 확인하는 비용 때문에 오히려 성능은 저하되고 CPU 사용률만 높게 만든다.
게다가 스칼라 서브쿼리를 사용하면 NL 조인에서 Inner 쪽 인덱스와 테이블에 나타나는 버퍼 Pinning 효과도 사라진다는 사실을 기억할 필요가 있다.
(3) 두 개 이상의 값을 리턴하고 싶을 때
07 조인을 내포한 DML
(1) 수정 가능 조인 뷰 활용
* 전통적인 방식의 UPDATE
* 수정 가능 조인 뷰
* 키 보존 테이블이란?
* _UPDATABLE_COLUMNS 뷰 참조
* 수정 가능 조인 뷰 제약 회피
(2) Merge 문 활용
(3) 다중 테이블 Insert 활용
08 고급 조인 테크닉
(1) 누적 매출 구하기
select 지점, 판매월, 매출, sun(매출) over (partition by 지점 order by 판매월 range between unbounded preceding and current row) 누적매출
from 월별지점매출
분석함수가 지원되지 않는다면
select t1.지점, t1.판매월, min(t1.매출) 매출, sum(t2.매출) 누적매출
from 월별지점매출 t1, 월별지점매출 t2
where t2.지점 = t1.지점
and t2.판매월 <= t1.판매월
group by t1.지점, t1.판매월
order by t1.지점, t1.판매월 ;
(2) 선분 이력 끊기
(3) 데이터 복제를 통한 소계 구하기
(4) 상호배타적 관계의 조인
(5) 최종 출력 건에 대해서만 조인하기
* 반정규화는 성능을 위한 최후의 수단
(6) 징검다리 테이블 조인을 이용한 튜닝
* 인조 식별자 사용에 의한 조인 성능 이슈
* 인조 식별자를 둘 때 주의사항
업무적으로 이미 통용되는 식별자이거나 유연성/확장성을 고려해 인조 식별자를 설계하는 경우를 제외하면 논리적인 데이터 모델링 단계에서는 가급적 식별자를 두지 않는 것이 좋다. 의미상 주어에 해당하는
(7) 점 이력 조회
* 정해진 시점 기준으로 조회
(8) 선분이력 조인
* 과거/현재/미래의 임의 시점 조회
* 현재 시점 조회
* Between 조인
(9) 선분이력과 조인 튜닝
* 정해진 시점을 기준으로 선분이력과 단순 조인할 때
* Between 조인 튜닝 - 조회 대상이 많지 않을 때
* Between 조인 튜닝 - 조회 대상이 많지만 대상별 이력 레코드가 많지 않을 때
* Between 조인 튜닝 - 대상별 이력 레코드가 많을 때
* Between 조인 튜닝 요약
(10) 조인에 실패한 레코드 읽기