SQL :: 옵티마이저 조인(Optimizer Join)

Nested Loop 조인

- 하나의 테이블에서 데이터를 먼저 찾고, 그 다음 테이블은 조인하는 방식

- 먼저 조회 되는 테이블을 Outer Table 이라고 하고, 그 다음 조회되는 테이블을 Inner Table.

   코딩 형태로 설정해보자면...

 for(i=0; i<dept.length; i++) { // Outer table
    for(j=0; j<emp.length; j++) { // Inner table
       // Search
    } 
}

 

사용법 예시

select /* ordered use_nl(b) */ *
from EMP a, DEPT b
where a.DEPTNO = b.DEPTNO
    and a.DEPTNO = 10;

 

장단점

1. 인덱스에 의한 Random access 에 기반하고 있기 때문에 대량의 데이터 처리 시 적합하지 않음.

2. Driving Table로는 데이터가 적거나, where 절 조건으로 row를 줄일 수 있는 테이블이어야 함.

3. Driven Table에는 조인을 위한 적절한 인덱스가 생성되어 있어야 함.

4. 선행 테이블의 결과를 통해 후행 테이블을 엑세스할 때 Random I/O가 일어남 : 이것을 줄여야 성능이 향상됨.

 

성능 향상을 위한 Driving Table(Outer Table) 유도 방법

1. 힌트(Hint) 사용 

/*+ORDERED*/				-- from절에 기술한 테이블 순서대로 제어
/*+LEADING (table명)*/		-- 힌트 내에 제시된 테이블이 드라이빙으로 처리됨

둘 중에 하나를 사용하되, 2가지를 동시에 사용하게 되면 LEADING 힌트는 적용 안됨.

 

2. 뷰 사용

 

Sort Merge 조인

- 두 개의 테이블을 SORT_AREA 라는 메모리 공간에 모두 로딩(loading)하고 Sort를 수행

- 두 개의 테이블에서 SORT가 완료되면 두 개의 테이블을 병합(Merge)함

- 조회 범위가 많을 때 사용하는 조인 방법론. 주로 조인 조건 칼럼에 인덱스가 없거나, 출력해야 할 결과 값이 많을 때 사용됨.

- Nest Loop Join의 조인 방식과 장단점 면에서 상반된 매력을 가짐.

- 정렬 데이터 양이 너무 많으면, 정렬은 임시 영역에서 수행됨. 임시 영역은 디스크에 있기 때문에 성능이 급격히 떨어짐.

 

사용법 예시

select /* ordered use_merge(A B)*/ A.Color, B.SIZE,...
from TABLE_A A,TABLE_B B
where a.joinkey_a = b.joinkey_b -- join key에 대한 인덱스가 테이블 둘 모두 다 없음
and a.color = 'RED' --인덱스 있음
and b.size = 'MED'; --인덱스 없음

 

사용하는 케이스

1. 연결 고리에 인덱스가 전혀 없는 경우

2. 대용량의 자료를 조인할 때

3. 조인 조건으로 >, <, <=, >= 와 같은 범위 비교 연산자가 사용된 경우

4. 인덱스 사용에 따른 랜덤 엑세스의 오버헤드가 많은 경우

 

성능 개선 포인트

1. Access 하는 속도를 향상 시킨다.

테이블을 Access 할 때 Full table scan이냐 Index Range Scan이냐 하는 등의 테이블 엑세스 하는 방법을 통하여 최적화 시킨다면 Sort Merge Join의 속도도 자연스럽게 최적화할 수 있음

2. 정렬 속도의 향상

3. 양쪽의 정렬까지 완료되는 속도를 맞춰준다.

양쪽 테이블을 access 하고 조회된 데이터들을 정렬할 때에, 어느 한 쪽이라도 정렬 작업이 종료되지 않으면 한 쪽이 대기 상태가 되고, 다른 한쪽의 정렬이 완전히 끝날 때까지 조인이 시작될 수 없음. 비교해야 할 테이블의 데이터 양이나 정렬 속도를 최대한 맞춰주는 것이 효율적.

4. SORT_AREA_SIZE 최적화

 

Hash 조인

- 두 개의 테이블 중 작은 테이블을 Hash 메모리에 로딩하고, 두 개의 테이블의 조인키를 사용해서 해시 테이블을 생성함.

- 해시 함수를 사용하여 주소를 계산하고, 해당 주소를 사용해서 테이블을 조인하기 때문에 CPU 연산을 많이 함.

- 주로 많은 양의 데이터를 조인해야 하는 경우 사용됨.

 

사용법 예시

select /* + ordered use_hash(b) */ * 
from emp a, dept b
where a.DEPTNO = b.DEPTNO
    and a.DEPTNO = 10 ;

 

사용하는 케이스

1. Join 컬럼에 적당한 인덱스가 없어 nested loop 조인 거는 게 비효율적일 때

2. Join Access 양이 많이 Random Access 부하가 심하여 nested loop가 비효율적일 때

3. Sort Merge Join을 하기에는 두 테이블이 너무 커 Sort 부하가 심할 때

4. 수행 빈도가 낮고 쿼리 수행 기간이 오래 걸리는 대용량 테이블을 JOIN할 때 

 

성능 개선 포인트

1. Hash Table을 만드는 과정을 효율화한다.

Hash Table로 만들 Build Input이 Hash Area에 담길 정도로 충분히 작아야 하며, Build Input 해시 키 컬럼에 중복 값이 거의 없어야 효율적인 동작을 기대할 수 있음.

2. CPU의 성능을 향상한다.

3. 충분한 PGA 메모리 확보