조인의 기본은 NL조인이다. NL조인은 인덱스를 이용한 조인이기 때문에 조인 원리도 그대로 적용할 수 있다.
NL조인은 Outer와 Inner 양쪽 테이블 모두 인덱스를 이용한다. Outer쪽 테이블은 사이즈가 크지 않으면 인덱스를 이용하지 않을 수 있다. 반면 Inner 테이블은 인덱스를 사용해야한다.
select /*+ ordered use_nl(c) */
e.사원명, e.고객명, e.전화번호
from e.입사일자 >= '19960101'
and c.관리사원번호 = e.사원번호
ordered 힌트는 FROM 절에서 기술한 순서대로 조인하라고 옵티마이저에 지시할 때 사용한다. use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용한다. 이 힌트를 사용하면 FROM 절을 바꾸지 않고도 마음껏 순서를 제어할 수 있다. ordered를 기술하지 않는 경우 조인 순서는 옵티마이저가 스스로 정한다.
--인덱스 구성:사원pk(사원번호), 사원_X1(입사일자), 고객pk(고객번호), 고객_X1(관리사원번호), 고객_X2(최종주문금액)
select /*+ ordered use_nl(c) index(e) index(c) */
e.사원번호, e.사원명, e.입사일자,
c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리번호 = 사원번호 --1
and e.입사일자 >= '19960101' --2
and e.부서코드 >= 'Z123' --3
and c.최종주문금액 >= 20000 --4
조건절 비교 순서는 2->3->1->4 순이다.
264p
올바른 조인 메소드 선택 : OLTP 시스템에서 튜닝할 때는 일차적으로 NL조인부터 고려하는 것이 올바른 순서다. 성능이 느리다면 과도한 랜덤 액세스가 발생하는 지점을 우선 파악하고, 조인 순서를 변경해서 랜덤 액세스 발생량을 줄일 수 있는지 더 효과적인 다른 인덱스가 있는지 등을 검토한다. 만약 NL조인이 좋은 성능을 내기 어렵다고 판단될 때는 소트 머지 조인이나 해시 조인을 검토한다.
- NL조인은 랜덤 액세스 위주의 조인 방식이다.(대량 데이터 조인시 불리함)
- 조인을 한 레코드 씩 순차적으로 진행한다.
- 다른 조인 방식과 비교시 인덱스 구성 전략이 특히 중요하다. 조인컬럼에 대한 인덱스가 있느냐 없느냐, 있다면 구성이 어떻게 되어있는냐에 따라 조인 효율이 크게 달라진다.
1번의 특징 때문에 대량 처리시 치명적인 한계를 드러내지만 2번 특징으로 인하여 큰 테이블을 조인하더라도 빠른 응답속도를 낸다.
정리) NL조인은 소량 데이터를 주로 처리하거나 부분 범위 처리가 가능한 온라인 트랜잭션처리(OLTP)시스템에 적합한 조인 방식이다.
266~269p
버전이 올라가면서 오라클은 NL조인 성능을 높이기 위해 테이블 Prefetch, 배치 I/O 기능을 도입했다. Prefetch는 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능이다. 배치 I/O는 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다.
조인 컬럼에 인덱스가 없을 때, 대량 데이터 조인이어서 인덱스가 효과적이지 않을 때, 옵티마이저는 NL 조인 대신 소트 머지 조인이나 해시 조인을 선택한다.
공유 메모리 영역인 SGA에 캐시된 데이터는 여러 프로세스가 공유할 수 있다. 하지만 동시에 액세스할 수는 없다. 동시에 액세스하려는 프로세스 간 액세스를 직렬화하기 위한 Lock 매커니즘으로서 래치(Latch)가 존재한다.
오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역을 갖는다. 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA라고 부르며, 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다. 할당받은 PGA 공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블 스페이스를 이용한다.
PGA는 다른 프로세스와 공유하지 않은 독립적인 메모리 공간이므로 래치 매커니즘이 불필요하다. 따라서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.
소트머지조인은 1)소트단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다. 2)머지단계 : 정렬한 양쪽 집합을 서로 머지한다.
*소트머지 조인은 use_merge() 힌트로 유도한다.
테이블의 데이터를 읽을때 정렬을 하고, 정렬된 결과 집합을 PGA 영역에 할당된 Sort Area에 저장한다. Sort Area에 저장한 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인방식이다.
NL 조인은 인덱스를 이용한 조인으로 조인 과정에서 액세스하는 모든 블록을 랜덤 액세스 방식으로 건건이 DB버퍼캐시를 경유해서 읽는다. 읽는 모든 블록에 래치 획득 및 캐시버퍼 체인 스캔과정을 거친다. 그리고 버퍼캐시에서 찾지 못한 블록은 건건이 디스크에서 읽어 들인다. 인덱스를 이용하기 때문에 인덱스 손익분기점 한계를 그대로 드러낸다.
반면 소트 머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장한 후 조인한다. PGA는 프로세스만을 위한 독립적인 메모리 공간이므로 데이터를 읽을 때 래치 획득 과정이 없다. 이것이 소트 머지 조인이 대량 데이터 조인에 유리한 이유다.
- 조인 조건식이 등치 조건이 아닌 대량 데이터 조인
- 조인 조건식이 아예 없는 조인(Cross Join)
use_merge힌트를 사용하여 제어
소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다. 소트 머지조인은 인덱스 유무에 영향을 받지 않고, 양쪽 집합을 개별적으로 읽고 나서 조인을 시작한다. 따라서 조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 각각 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리하다.
NL 조인은 인덱스를 이용한 조인 방식으로 인덱스 구성에 따른 성능 차이가 심하다. 소트 머지 조인과 해시 조인은 조인 과정에 인덱스를 이용하지 않기 때문에 대량 데이터 조인할 때 NL 조인보다 훨씬 빠르고, 일정한 성능을 보인다. 소트 머지 조인은 항상 양쪽 테이블을 정렬하는 부담이 있는데 해시 조인은 그런 부담도 없다.
- Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블을 생성한다. (조건에 해당하는 데이터를 읽어 해시 테이블 생성 -> 해시 체인에 데이터를 연결 -> 해시 테이블은 PGA 영역에 할당된 Hash Area에 저장)
- Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하면서 조인한다. (1단계에서 생성한 해시 테이블을 탐색 -> 해시 체인을 찾음 -> 해시 체인을 스캔해서 값이 같은 값을 찾는다. 찾으면 조인에 성공, 못 찾으면 실패)
해시 조인은 use_hash 힌트로 유도한다.
Hash Area에 생성한 해시 테이블을 이용한다는 점만 다를 뿐 해시 조인도 조인 프로세싱 자체는 NL 조인과 같다. 해시 조인이 NL 조인보다 빠른 이유는 소트 머지조인과 같다. 해시 테이블을 PGA 영역에 할당하기 때문이다. 해시 조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색하고 조인한다.
대량 데이터에서 소트 머지 조인 보다 해시 조인이 더 빠른 이유는 두 조인 메소드의 조인 오퍼레이션을 시작하기 전, 사전 준비작업에 있다. 소트 머지 조인의 사전 준비작업은 양쪽 집합을 모두 정렬해서 PGA에 담는 작업이다. 해시 조인에서 사전 준비작업은 양쪽 집합 중 어느 한쪽을 읽어 해시 맵을 만드는 작업이다.
해시 조인은 NL 조인처럼 조인 과정에서 발생하는 랜덤 액세스 부하가 없고, 소트 머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없다. 해시 테이블을 생성하는 비용이 수반되지만 둘 중 작은 집합을 Build Input으로 선택하므로 대개는 부담이 크지 않다.
두 테이블 모두 대용량 테이블인 경우 인 메모리 해시 조인이 불가능하다 이때 해시 조인 처리 방식은 다음과 같이 두 단계로 나눠서 진행된다.
- 파티션 단계 : 조인하는 양쪽 집합의 조인 컬럼에 해시 함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝한다. 양쪽 집합을 읽어 Temp 공간에 저장해야 하므로 인메모리 해시 조인보다 성능이 많이 떨어진다.
- 조인 단계 : 파티션 단계를 완료하면 각 파티션 짝에 대해 하나씩 조인을 수행한다. 이때 각각에 대한 Build Input과 Probe Input은 독립적으로 결정된다.
해시 조인 실행계획을 제어할 때 use_hash 힌트를 사용한다. Build Input을 사용자가 직접 선택하고 싶은 경우 조인 대상 테이블이 두 개뿐이라면 leading, ordered 힌트를 사용하면 된다. 오라클은 이들 힌트를 지시한 순서에 따라 가장 먼저 읽는 테이블을 Build Input으로 선택한다. 또는 swap_join_inputs 힌트로 Build Input을 명시적으로 선택할 수도 있다.
291~296p
- 소량 데이터 조인시 : NL 조인
- 대량 데이터 조인시 : 해시 조인
- 대량 데이터 조인인데 해시 조인으로 처리할 수 없을 때, 즉 조인 조건식이 등치 조건이 아닐때 : 소트 머지 조인
여기서 소량과 대량의 기준은 단순히 데이터량의 많고 적음이 아닌 NL 조인 기준으로 최적화했는데도 랜덤 액세스가 많아 만족할만한 성능을 낼 수 없다면 대량 데이터 조인에 해당한다.
수행빈도가 매우 높은 쿼리에 대해서는 다음과 같은 기준을 가진다.
- (최적화된)NL 조인과 해시 조인 성능이 같으면 NL 조인
- 해시 조인이 약간 더 빨라도 NL조인
- NL 조인보다 해시 조인이 매우 빠른 경우, 해시 조인
조인 메소드를 선택시 NL 조인을 가장 먼저 고려해야하는 이유는 NL 조인에 사용하는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조다. 반면 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다. 따라서 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리를 해시 조인으로 처리하면 CPU와 메모리 사용률이 크게 증가한다. 또 해시 맵을 만드는 과정에 여러가지 래치 경합도 발생한다.
결론적으로 해시조인은 수행빈도가 낮고, 쿼리 수행시간이 오래 걸리는, 대량 데이터를 조인할 때 주로 사용한다.
옵티마이저는 비용을 평가하고 실행계획을 생성하기에 앞서 사용자로부터 전달받은 SQL을 최적화에 유리한 형태로 변환하는 작업(쿼리 변환)부터 진행한다.
쿼리변환은 옵티마이저가 SQL을 분석해 의미적으로 동일하면서도 더 나은 성능이 기대되는 형태로 재작성하는 것을 말한다.
[서브쿼리 분류]
- 인라인 뷰 : FROM 절에서 사용한 서브쿼리
- 중첩된 서브쿼리 : WHERE 절에서 사용한 서브쿼리, 서브쿼리가 메인쿼리 컬럼을 참조하는 형태를 상관관계 있는 서브쿼리라고 부른다.
- 스칼라 서브쿼리 : 한 레코드당 하나의 값을 반환하는 서브쿼리, 주로 SELECT-LIST에서 사용하고, 예외사항을 제외하면 컬럼이 올 수 있는 대부분 위치에 사용할 수 있다.
옵티마이저는 쿼리블록 단위로 최적화를 수행한다.
메인쿼리와 서브쿼리 간에는 부모와 자식이라는 종속적이고 계층적인 관계가 존재한다. 서브쿼리는 메인쿼리에 종속되므로 단독으로 실행할 수 없다.
필터 오퍼레이션 : no_unnest 힌트 사용, 필터 오퍼레이션은 기본적으로 NL조인과 처리 루틴이 같으나 차이점으로는
- 필터는 메인쿼리의 한 로우가 서브쿼리의 한 로우와 조인에 성공하는 순간 진행을 멈추고, 메인쿼리의 다음 로우를 계속 처리한다는 점이다.
- 필터는 캐싱기능을 갖는다. 서브쿼리 입력 값에 따른 반환 값을 캐싱하는 기능으로 서브쿼리를 수행하기 전에 항상 캐시부터 확인한다. 캐시에서 true/false 여부를 확인할 수 있다면 서브쿼리를 수행하지 않아도 되므로 성능을 높이는데 도움이 된다.
- 필터 서브쿼리는 메인쿼리에 종속되므로 조인 순서가 고정된다.(항상 메인쿼리가 드라이빙 집합)
서브쿼리 Unnesting : unnest 힌트를 사용, 서브쿼리 Unnesting은 메인과 서브쿼리 간의 계층구조를 풀어 서로 같은 레벨로 만들어 준다는 의미로 서브쿼리 Flattening이라고 부르기도 한다. 서브쿼리를 그대로 두면 필터 방식을 사용하지만 Unnesting 하고 나면 일반 조인문처럼 다양한 최적화 기법을 사용할 수 있다. Unnesting된 서브쿼리는 필터방식과 다르게 메인 쿼리 집합보다 먼저 처리될 수 있다.
*ROWNUM : 서브쿼리에 ROWNUM을 잘못 사용하면 서브쿼리 Unnesting이 기능하지 않을 수 있다. ex) exists에 사용하는 경우 exists는 매칭되는 데이터 존재 여부를 확인하는 연산자이므로 조건절을 만족하는 레코드를 만나는 순간 멈추는 기능을 가지고 있는데 거기에 ROWNUM을 사용하면 의미가 중복이 되고 Unnesting이 동작하지 않는다.
서브쿼리 Pushing : Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능이며 push_subq/no_push_subq 힌트로 제어한다. 이 기능은 Unnesting 되지 않은 서브쿼리에만 작동한다. 서브쿼리가 Unnesting 되면 필터가 아닌 다양한 조인 방식으로 실행된다. Unnesting 되는 순간 push_subq 힌트는 무용지물이다. 따라서 push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법이다.
311~316p 최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 반환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.
- 스칼라 서브쿼리 특징 : 스칼라 서브 쿼리는 메인 쿼리 레코드마다 정확히 하나의 값만 반환한다. 함수적으로 재귀적으로 실행하는 구조는 아니다. 컨텍스트 스위칭 없이 메인쿼리와 서브쿼리를 한 몸체처럼 실행한다.
- 스칼라 서브쿼리 캐싱 효과 : 스칼라 서브쿼리로 조인시 오라클에서는 조인 횟수 최소화를 위해 입력 값과 출력 값을 내부 캐시에 저장해둔다. 일단 캐시에서 입력 값을 찾아보고, 찾으면 저장된 출력값을 반환한다. (캐시에서 찾지 못할 때만 조인을 수행)
- 스칼라 서브쿼리 캐싱 부작용 : 캐시 공간은 늘 부족하기 때문에 스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 효과가 있다. 또 서브쿼리 캐싱이 성능에 도움을 주지 못하는 경우로 메인 쿼리 집합이 매우 작은 경우다. 캐싱은 쿼리 단위로 이루어지기 때문에 쿼리 단위로 쓰고 버린다. 따라서 메인 쿼리 집합이 클수록 재사용성이 높아 효과도 크다.
- 두 개 이상의 값 반환 : 스칼라 서브쿼리의 치명적인 단점은 두 개 이상의 값을 반환할 수 없다는 제약이다. 따라서 두 개 이상의 값을 반환하고 싶을 때는 인라인 뷰를 사용하자
- 스칼라 서브쿼리 Unnestiong : 스칼라 서브쿼리는 NL 방식으로 조인 하므로 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있다. 그래서 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환하고 싶은 경우 오라클12부터 Unnesting이 가능해졌다. 옵티마이저가 자동으로 쿼리를 변환해주어 다른 조인으로 실행할 수 있게 해준다.