조인 튜닝

1. NL 조인

• 첫 번째 포인트는 outer나 inner 테이블에서 인덱스 스캔을 통해 테이블 랜덤 액세스를 수행하고 나서 필터링되는 비율이 높다면 인덱스에 필터링되는 칼럼을 추가해 주면 선택도를 높일 수 있다. 두 번째 포인트는 조인 액세스를 줄이기 위해서 테이블의 처리 범위가 적은 테이블을 outer 테이블로 정하는 것이다. 부분범위처리가 가능하기 때문에 OLTP 환경에 적합한 조인 방식이다.
• 오라클은 NL 조인 성능을 높이기 위해 테이블 액세스하다가 디스크 I/O가 필요해지면 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 테이블 Prefetch와 디스크 I/O Call을 미뤘다가 읽을 블록이 일정량 쌓이면 한꺼번에 처리하는 배치 I/O 기능을 제공한다. 
• /*+ ordered use_nl(a) */ , option (force order) - FROM 절에 기술한 순서대로 NL 조인 수행
• /*+ leading(a) use_nl(b) */ , option (force order, loop join) - a 테이블이 먼저 수행되고 NL 조인 수행

2. 소트 머지 조인

• 두 테이블을 각각 정렬한 다음에 두 집합을 머지하면서 조인을 수행하며 부분범위처리가 가능하다.
• 일반 인덱스나 클러스터형 인덱스와 같이 미리 정렬된 오브젝트에 사용하면 정렬작업이 필요 없기 때문에 효율이 좋다. 하지만 정렬해야 할 집합이 많은 대용량 테이블이면 효율이 떨어진다.
• 테이블별 검색 조건에 의해 전체 일 량이 좌우되며 스캔 위주의 조인 방식이다.
• 오라클은 조인 연산자가 부등호이거나 조인 조건이 없어도 소트 머지 조인이 가능하지만 SQL Server는 = 일 때만 수행된다.
• /*+ ordered use_merge(a) */ , option(force order, merge join) - FROM 절에 기술한 순서대로 머지 조인 수행

3. 해시 조인

• 둘 중 작은 집합(Build Input)을 읽어 해시 영역에 해시 테이블을 생성하고 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 조인하는 방식이다. Build Input의 데이터가 가용 메모리에 담길 정도로 충분히 작고 해시 키 값으로 사용되는 칼럼에 중복 값이 거의 없을 때 효과적이다. Build Input은 전체범위처리지만 Probe Input은 부분범위처리가 가능하다.
• CPU와 메모리를 많이 사용함
• 조인칼럼에 적당한 인덱스가 없어 NL 조인이 비효율적일 때 사용
• 조인칼럼에 인덱스가 있지만 NL 조인에서 outer 테이블에서 inner 테이블로 조인 액세스량이 많아 랜덤 액세스 부하가 심할 때 사용
• 소트 머지 조인 하기에는 두 테이블이 너무 커 소트 부하가 심할 때 사용
• 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 조인할 때 사용(OLAP 환경)
• /*+ ordered use_hash(a) */ - FROM 절에 기술한 순서대로 해시 조인 수행

4. 스칼라 서브 쿼리

• 오라클은 스칼라 서브 쿼리를 수행할때 해싱 알고리즘을 사용하여 캐싱하는데 입력값이 적어야 해시 충돌이 적어 효과적이다.
• 스칼라 서브 쿼리를 이용해서 두개 이상의 값을 리턴하고 싶을때는 서브 쿼리에서 여러 결과 출력을 concat 등으로 합치고 메인 쿼리에서 substr 함수를 이용해서 분리시켜 사용하면 된다.
• /*+ unnest */ - 서브쿼리를 풀어서 메인 쿼리에 포함되도록 수행(스칼라 서브 쿼리의 캐시기능은 이때 사용 불가능)

5.  기타 조인 기법

• 1:M 조인에서 그룹핑한 결과가 필요하다면 인라인뷰로 M테이블을 먼저 그룹핑하고 조인을 하게 되면 조인 횟수를 감소 시켜 성능을 향상시킬 수 있다.
• 상호배타적 관계의 조인에서는 데이터 모델링에 따라서 outer 조인과 union all을 사용하자.
• 월별 누적 매출을 구해야할때 오라클에서는 윈도우 함수를 사용하면 되지만 불가능할때 부등호 조인을 이용해 같은 결과를 만들 수 있다.
• 선분이력에서 조회할때 특정 시점일 경우 between 조건을 사용하면 되고 현제 시점일 경우 = 조건으로 마지막날 상수값으로 조회하면 된다.

 

유사 내용 ( https://twosharkbaby.tistory.com/127 )

조인 원리와 활용

 

참고 서적 - SQL 전문가 가이드