01 Nested Loops 조인

(1) 기본 메커니즘

begin
	for outer in (select deptno, empno, rpad(enmae, 10) ename from emp)
    loop -- outer 루프
      for inner in (select dname from dept where deptno = outer.deptno)
      loop -- inner loop
      	dbms_output.put_line(outer.empno||' : '||outer.ename|| ' : '||inner.dname);
      end loop;
	end loop;
end;

위 PL/SQL문은 아래 쿼리와 100% 같은 순서로 데이터를 액세스하고, 데이터 출력순서도 같다.

select /*+ ordered use_nl(d) */ e.empno, e.ename, d.name
from emp e, dept d
where d.deptno = e.deptno

(2) 힌트를 이용해 NL 조인을 제어하는 방법

select /*+ ordered use_nl(e) */ *
from dept d, emp e
where e.deptno = d.deptno

ordered 힌트는 from절에 기술된 순서대로 조인하라고 옵티마이저에게 지시할 때 사용, use_nl 힌트는 NL 방식으로 조인하라고 지시할 때 사용한다.

-> dept 테이블을 기준으로 emp 테이블과 조인할 때 NL 방식으로 조인하라!

Outer, Inner 테이블

Nested Loops
	TABLE ACCESS (FULL) ~   -> Outer / Driving
    TABLE ACCESS (FULL) ~   -> Inner / Driven
    
MERGE JOIN
	SORT (JOIN)
    	TABLE ACCESS (FULL) ~ -> Outer / First
    SORT (JOIN)
		TABLE ACCESS (FULL) ~ -> Inner / Second
        
HASH JOIN 
	TABLE ACCESS (FULL) ~ -> Outer / Build Input
    TABLE ACCESS (FULL) ~ -> Inner / Probe Input 

세 개 이상을 조인할 때는 힌트를 아래처럼 사용하는 것이 올바른 사용법이다.

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와 조인할 때는 해시 방식으로 조인하라

leading 힌트

select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_nl(B) */ *
from A, B, C, D

leading 힌트만으로 조인 순서를 세밀하게 제어할 수 있다.

(3) NL 조인 수행 과정 분석

OLTP 시스템에서 조인을 튜닝할 때는 일차적으로 NL 조인부터 고려하는 것이 올바른 순서다. 우선, NL 조인 메커니즘을 따라 각 단계의 수행 일량을 분석해 과도한 Random 액세스가 발생하는 지점을 파악한다.

조인 순서를 변경해 Random 액세스 발생량을 줄일 수 있는 경우가 있고, 그렇지 못할 때는 인덱스 컬럼 구성을 변경하거나 다른 인덱스의 사용을 고려해야 한다.

여러 가지 방안을 검토한 결과 NL 조인이 효과적이지 못하다고 판단될 때 해시 조인이나 소트 머지 조인을 검토한다.

(4) NL 조인의 특징

오라클은 블록 단위로 I/O를 수행하며, 하나의 레코드를 읽으려고 블록을 통째로 읽는 Random 액세스 방식은 메모리 버퍼에서 빠르게 읽더라도 비효율이 존재한다. 그런데 NL 조인의 첫 번째 특징이 Random 액세스 위주의 조인 방식이라는 점이다.

따라서 인덱스 구성이 아무리 완벽하더라도 대량의 데이터를 조인할 때 매우 비효율적이다.

두 번째 특징은, 조인을 한 레코드씩 순차적으로 진행한다는 점이다.

첫 번째 특징 때문에 대용량 데이터 처리 시 치명적인 한계를 드러내지만, 반대로 이 두 번째 특징 때문에 아무리 대용량 집합이더라도 매우 극적인 응답 속도를 낼 수 있다.

부분범위처리가 가능한 상황!

또한 인덱스 구성 전략이 특히 중요하다.
조인 컬럼에 대한 인덱스가 있으냐 없느냐, 컬럼이 어떻게 구성됐느냐에 따라 조인 효율이 크게 달라진다.

NL 조인은 소량의 데이터를 주로 처리하거나 부분범위처리가 가능한 온라인 트랜잭션 환경에 적합한 조인 방식이다.

(6) 테이블 Prefetch (222~224P) 패스

(7) 배치 I/O 패스

(8) 버퍼 Pinning 효과 (226~234P) 패스

---

02 소트 머지 조인

(1) 기본 메커니즘

두 테이블을 각각 정렬한 다음에 두 집합을 머지하면서 조인을 수행한다.

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

소트 머지 조인은 outer 루프와 inner 루프가 Sort Area에 미리 정렬해 둔 자료구조를 이용한다는 점만 다를 뿐, 실제 조인 오퍼레이션을 수행하는 과정은 NL조인과 다르지 않다.

Sort Area는 PGA 영역에 할당되므로 SGA를 경유해 인덱스와 테이블을 액세스할 때보다 훨씬 빠르다. PGA는 프로세스만을 위한 독립적인 메모리 공간이어서 데이터를 읽을 때 래치 획득 과정이 없기 때문이다.

select /*+ ordered use_merge(e) */ d.deptno, d.dname, e.empno, e.ename
from dept d, emp e
where d.deptno = e.deptno

수행할 때의 처리과정

1. Outer(First) 테이블인 dept를 deptno 기준으로 정렬한다.
2. Inner(Second) 테이블인 emp를 deptno 기준으로 정렬한다.
3. Sort Area에 정렬된 dept 테이블을 스캔하면서, 정렬된 emp 테이블과 조인한다.

emp 테이블이 정렬돼 있기 때문에 조인에 실패하는 레코드를 만나는 순간 멈출 수 있다. 또한 정렬된 emp에서 스캔 시작점을 찾으려고 매번 탐색하지 않아도 된다.

(2) 소트 머지 조인의 특징

소트 머지 조인은 조인을 위해 실시간으로 인덱스를 생성하는 것과 다름없다. 양쪽 집합을 정렬한 다음에는 NL 조인과 같은 방식으로 진행하지만 PGA 영역에 저장된 데이터를 이용하기 때문에 빠르다.

소트 머지 조인은 인덱스 유무에 영향을 받지 않는다.

양쪽 집합을 개별적으로 읽고 나서 조인한다! (조인 컬럼에 인덱스가 없는 상황에서 두 테이블을 독립적으로 읽어 조인 대상 집합을 줄일 수 있을 때 아주 유리)

스캔 위주의 액세스 방식을 사용한다. (양쪽 소스 집합에서 정렬 대상 레코드를 찾는 작업만큼은 인덱스를 이용해 Random 액세스 방식으로 처리될 수 있고, 그 때 액세스량이 많다면 소트 머지 조인의 이점이 사라질 수도 있다)

• First 테이블에 소트 연산을 대체할 인덱스가 있을 때
• 조인할 First 집합이 이미 정렬돼 있을 때
• 조인 조건식이 등치(=) 조건이 아닐 때

(3) First 테이블에 소트 연산을 대체할 인덱스가 있을 때

First 테이블 조인 컬럼에 인덱스가 있을 때 한쪽 집합(Second 테이블)은 전체범위를 처리하고 다른 한쪽(First 테이블)은 일부만 읽고 멈추도록 할 수 있다.

OLTP성 업무에서 소량의 테이블과 대량의 테이블을 조인할 때 소트 머지 조인을 유용하게 사용할 수 있다.

create index dept_idx on dept(loc, deptno);
create index emp_idx on emp(job, deptno);

select /*+ ordered use_merge(e) */ *
from dept d, emp e
where d.deptno = e.deptno
and d.loc = 'CHICAGO'
and e.job = 'SALESMAN'
order by e.deptno

dept_idx는 [loc + deptno] 순으로 정렬돼 있다.
따라서 이 인덱스를 loc = 'CHICAGO' 조건으로 스캔하면서 얻어진 결과집합은 이미 deptno 순으로 정렬돼 있다. 따라서 dept 테이블이 소트 머지 조인을 위한 First 집합으로 선택된다면 소트 연산이 생략될 수 있다.

소트 머지 조인에서의 부분범위 처리 활용

select /*+ ordered use_merge(e) index (d dept_pk) */ 
d.dname, e.empno, e.ename
from dept d, emp e
where e.deptno = d.deptno;

Second 테이블은 항상 정렬을 수행하므로 전체범위처리가 불가피하지만 First 테이블만큼은 중간에 읽다가 멈출 수 있다.

(4) 조인할 First 집합이 이미 정렬돼 있을 때

소트 머지 조인할 때, Fisrt쪽 집합이 조인 컬럼 기준으로 이미 정렬된 상태일 수 있다. group by, order by, distinct 연산 등을 수행한 경우인데, 그때는 조인을 위해 다시 정렬하지 않아도 되므로 소트 머지 조인이 유리하다.

(5) 조인 조건식이 등치(=) 조건이 아닐 때

해시 조인은 조인 조건식이 등치(=) 조건일 때만 사용할 수 있지만 소트 머지 조인은 등치 조건이 아닐 때도 사용될 수 있다.

---

03 해시 조인

(1) 기본 매커니즘

해시 조인은 둘 중 작은 집합을 읽어 Hash Area에 해시 테이블을 생성하고, 반대쪽 큰 집합을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다.

해시 테이블을 생성할 때 해시 함수를 사용한다. 즉, 해시 함수에서 리턴받은 버킷 주소로 찾아가 해시 체인에 엔트리를 연결한다.

해시 테이블을 탐색할 때도 해시 함수를 사용한다. 즉, 해시 함수에서 리턴받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾는다.

해시 조인은 조인과정에서 발생하는 Random 액세스 부하가 없고, 미리 양쪽 집합을 정렬하는 부담도 없다. 다만 해시 테이블을 생성하는 비용이 수반된다. 따라서 Build Input이 작을 때 효과적이다.

구체적으로, PGA 메모리에 할당되는 Hash Area에 담길 정도로 충분히 작아야 한다. 만약 Build Input이 Hash Area 크기를 초과한다면 디스크에 썼다가 다시 읽어 들이는 과정을 거치기 때문에 성능이 많이 저하된다.

Build Input으로 선택되는 테이블이 작은 것도 중요하지만 해시 키 값으로 사용되는 컬럼에 중복값이 거의 없을 때 효과적이다.

해시 조인이 인덱스 기반의 NL 조인보다 빠른 결정적인 이유는, 해시 테이블이 PGA 영역에 할당된다는 데에 있다. 
NL 조인은 Outer 테이블에서 읽히는 레코드마다 Inner 쪽 테이블 버퍼 캐시 탐색을 위한 래치 획득을 반복하지만, 
해시 조인은 래치 획득 과정 없이 PGA에서 빠르게 데이터를 탐색한다. 

(2) 힌트를 이용한 조인 순서 및 Build Input 조정

Build Input을 사용자가 직접 선택하고자 할 때는 swap_join_inputs 힌트를 사용하면 되지만, 단 2개 테이블을 해시 조인할 때는 ordered나 leading 힌트를 사용해도 된다.

(3) 두 가지 해시 조인 알고리즘

첫 번째 알고리즘

(4) Build Input이 Hash Area를 초과할 때 처리 방식

(5) Build Input 해시 키 값에 중복이 많을 때 발생하는 비효율

해시 알고리즘의 성능은 해시 충돌을 얼마나 최소화할 수 있느냐에 달렸으며, 이를 방지하려면 그만큼 많은 해시 버킷을 할당해야만 한다. 오라클은 가능하면 충분히 많은 개수의 버킷을 할당함으로써 버킷 하나당 하나의 키 값만 갖게 하려고 노력한다.

그런데 해시 버킷을 아무리 많이 할당하더라도 해시 테이블에 저장할 키 컬럼에 중복 값이 많다면 하나의 버킷에 많은 엔트리가 달릴 수 밖에 없다.

그러면 해시 버킷을 아무리 빨리 찾더라도 해시 버킷을 스캔하는 단계에서 많은 시간을 허비하기 때문에 탐색 속도가 현저히 저하된다.

Build Input의 해시 키 컬럼에는 중복 값이 (거의) 없어야 해시 조인이 빠르게 수행될 수 있다.

(6) 해시 조인 사용기준

해시 조인 성능을 좌우하는 두 가지 키 포인트는 다음과 같다.

• 한 쪽 테이블이 Hash Area에 담길 정도로 충분히 작아야 함
• Build Input 해시 키 컬럼에 중복 값이 거의 없어야 함

위 두 가지 조건을 만족할 때 해시 조인이 가장 극적인 성능 효과를 낼 수 있다.

그럼, 해시 조인을 언제 사용하는 것이 효과적인가?

• 조인 컬럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때
• 조인 컬럼에 인덱스가 있더라도 NL 조인 드라이빙 집합에서 Inner 쪽 집합으로의 조인 액세스량이 많아 Random 액세스 부하가 심할 때
• 소트 머지 조인하기에는 두 테이블이 너무 커 소트 부하가 심할 때
• 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때

if) 수행시간이 짧으면서 수행빈도가 매우 높은 쿼리를 해시 조인으로 처리한다면?
-> 해시 테이블은 단 하나의 쿼리를 위해 생성하고 조인이 끝나면 곧바로 소멸하는 자료구조다.

따라서 수행빈도가 높은 쿼리에 해시 조인을 사용하면 CPU와 메모리 사용률을 크게 증가시킴은 물론, SQL memory Manager workarea, row cache objects, simulator hash latch, cache buffers chains 같은 래치 경합이 발생해 시스템 동시성을 떨어뜨리게 된다.

따라서 해시 조인은 수행 빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때 주로 사용해야 한다.

---

04 조인 순서의 중요성

---

05 Outer 조인

(1) Outer NL 조인

NL 조인은 Outer 조인할 때 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블이 항상 드라이빙 테이블로 선택된다.

(2) Outer 소트 머지 조인

소트 머지 조인은 소트된 중간 집합을 이용한다는 점만 다를 뿐 처리루틴이 NL 조인과 다르지 않다. 따라서 Outer 소트 머지 조인도 처리 방향이 한쪽으로 고정되며, Outer 기호(+)가 붙지 않은 테이블(Outer 테이블)이 항상 First 테이블로 선택된다.

(3) Outer 해시 조인

Outer 해시 조인도 9i까지는 방향이 고정돼었다. 9i에서 Outer 해시 조인을 수행해 보면, Outer 기호(+)가 붙지 않은 테이블(Outer 테이블)이 항상 Build Input으로 선택된다.

select /*+ use_hash(d e) */ d.dname, e.ename
from dept d, emp e
where e.deptno(+) = d.deptno

1. Outer 집합인 dept 테이블을 해시 테이블로 빌드한다.
2. Inner 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색한다.
3. 조인에 성공한 레코드는 곧바로 결과집합에 삽입하고, 조인에 성공했음을 해시 엔트리에 표시해 둔다.
4. Probe 단계가 끝나면 Inner 조인과 동일한 결과집합이 만들어진 상태다. 이제 조인에 실패했던 레코드를 결과집합에 포함시켜야 하므로 해시 테이블을 스캔하면서 체크가 없는 dept 엔트리를 결과집합에 삽입한다. 그럼으로써 Outer 조인을 완성한다.

해시 조인은 특히 대용량 테이블을 조인할 때 자주 사용되는데, Outer 조인할 때 조인 순서가 고정되다 보니 자주 성능 문제를 일으키곤 했다.
-> Hash Area가 부족해 디스크 쓰기와 읽기가 발생할 뿐만 아니라 주문 건수가 많은 고객일수록 해시 버킷 당 엔트리 개수가 많아져 해시 테이블을 탐색하는 효율이 크게 저하된다.

오라클은 이 문제를 해결하려고 10g에서 Right Outer 해시 조인을 도입하게 되었다.

select /*+ use_hash(d e) swap_join_inputs(d) */ d.dname, e.ename
from dept d, emp e
where e.deptno = d.deptno(+)

-> HASH JOIN (RIGHT OUTER)

1. Inner 집합인 dept 테이블을 해시 테이블로 빌드(Build)한다.
2. Outer 집합인 emp 테이블을 읽으면서 해시 테이블을 탐색(Probe)한다.
3. Outer 조인이므로 조인 성공 여부에 상관없이 결과집합에 삽입한다.

(4) Full Outer 조인 -> 패스

---

06 스칼라 서브쿼리를 이용한 조인

(1) 스칼라 서브쿼리

쿼리에 내장된 또 다른 쿼리 블록을 서브쿼리라고 하는데, 그 중에서 함수처럼 한 레코드당 정확히 하나의 값만을 리턴하는 서브쿼리를 '스칼라 서브쿼리'라고 한다.

select ~, (select d.dname from dept d where d.deptno = e.deptno) dname
from emp e
where sal >= 2000

select /*+ ordered use_nl(d) */ ~
from emp e, dept d
where d.deptno(+) = e.deptno
and e.sal >= 2000

(2) 스칼라 서브쿼리의 캐싱 효과

오라클은 스칼라 서브쿼리 수행횟수를 최소화하려고 그 입력 값과 출력 값을 내부 캐시에 저장해 둔다. 스칼라 서브쿼리가 수행될 때면 일단 '입력 값'을 캐시에서 찾아 보고 거기 있으면 저장된 '출력 값'을 리턴한다. 캐시에서 차지 못할 때만 쿼리를 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다. (입력 값: 참조하는 메인 쿼리의 컬럼 값)

select ~, 
	(
    	select d.dname -> 출력 값: d.dname
        from dept d
        where d.deptno = e.empno -> 입력 값: e.empno
    )
from emp e
where sal >= 2000

결론적으로, 스칼라 서브쿼리의 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 적을 때 효과가 있으며, 반대의 경우라면 캐시를 확인하는 비용 때문에 오히려 성능은 저하되고 CPU 사용률만 높게 만든다. 게다가 스칼라 서브쿼리를 사용하면 NL 조인에서 Inner 쪽 인덱스와 테이블에 나타나는 버퍼 Pinning 효과도 사라진다.

(3) 두 개 이상의 값을 리턴하고 싶을 때 -> Pass