조인튜닝

• 앞선 포스팅들에서 인덱스와 관련된 내용들을 정리했다.
• 조인은 테이블간의 관계를 이용해서 우리가 원하는 쿼리를 반환하는 것을 의미한다.
• 일반적으로 카타시안곱을 사용하기 때문에, 대량의 데이터의 경우 문제가 생길 수 있다.
• 이를 해결하기 위해 조인에 관해서 튜닝을 할 줄 알아야 한다.
• 이번 포스팅에서는 NL조인과 Sort Merge Join에 관해서 포스팅 하고자 한다.

NL 조인

기본 메커니즘

• Nested Loop Join 의 약자를 따서, NL 조인이라고 부른다.
• 2중 반복문이란 소리다.
• 카타시안 곱의 형태로 SCAN을 진행하기 때문에 인덱스를 전략적으로 구성해야 성능이 잘 나온다는 것을 눈치 챌 수 있다.
• Outer 테이블은 먼저 확인하는 기준 테이블, Inner Table은 Outer를 기준으로 조건절에 해당하는 내용들을 스캔하기 위한 테이블을 의미한다. 2중 포문을 기준으로 생각해보면 이해가 쉽다.
• 인덱스를 활용해서 scan을 최소화 하는 것을 목적으로 둔다는 것을 잊지 말자.

인덱스 실행계획 제어

• 아래와 같이 쿼리 사용자가 직접 제어하여 실행계획을 짤 수 있다.

select /* use_nl(column_name) */ ...
select /* leading(column_a, column_b) */ ...
select /* use_hash(column_name) */ ...

NL 조인 수행 과정 분석

• where 절의 순서는 중요치 않고, 옵티마이저가 인덱스 구성에 따라서 실행계획을 작성하여 조건절의 순서를 바꾼다.
• nl조인의 특징은 dfs처럼 조건에 맞는 결과물에 따라 완전탐색을 진행한다는 것이다.
• Inner Table까지 탐색하여 Range Scan을 진행 후, 다시 Outer Table의 검색으로 돌아와서 진행한다는 뜻이다.

튜닝 포인트

• NL 조인 튜닝 포인트를 발견하는 것이 우선이다.
• 조건절에서 BETWEEN 또는 비교 연산자가 들어간다면 인덱스에 정렬순서를 고려해서 선행, 후행 칼럼을 선정해야 한다는 것을 잊지말자(앞 포스팅에서 설명했다.()
• NL 조인 특성상 Inner Table에서 비교연산이 들어가는 쪾에 인덱스를 추가하는 것이 빠른 경우가 좋다. 잊지말아야 할 것은 인덱스는 칼럼들을 기준으로 정렬된다는 사실이다.

NL 조인 특징 요약

• 랜덤 액세스 위주의 조인방식이라는 것이다. 즉 DFS 처럼 풀스캔이 일어날 수 있다.
• 한 레코드씩 순차적으로 진행된다.
• 인덱스 구성 전략이 특히 중요하다.

NL 조인 확장 메커니즘

• 오라클의 경우 버전이 올라가면서 조인 성능을 높이기 위해 테이블의 Prefecth, Batch I/O 기능이 도입되었다.
• Prefetch란, 디스크 I/O가 필요해지는 경우에 이어서 곧 읽게될 블록까지 미리 읽어서 버퍼캐시에 적용하는 것을 의미한다.
• 배치 I/O란, 디스크 I/O 콜을 여러개 모아놨다가 한번에 접근하는 것을 의미한다.

SORT MERGE JOIN

• SGA와 PGA

  1. SGA란 shared global area의 약자로 데이터베이스 프로세스가 공통으로 관리하는 메모리이다.
  2. PGA란 데이터베이스 프로세스별로 따로 관리하는 메모리이다.

• SGA의 경우 프로세스별 접근이 lock 제한이 존재하기 때문에, 래치 메커니즘(기다리는 작업) 이 필요하다.

• PGA는 독립적인 공간이므로 래치 메커니즘이 필요가 없다는 것을 기억하자.

• 소트머지란, 조인되는 칼럼을 기준으로 정렬작업 이후, 머지하는 것을 의미한다.

  1. A 테이블을 조인 칼럼 기준으로 정렬한다. 이때 PGA에 저장되고, 모자르면 TEMP 메모리에 저장한다.
  2. B 테이블을 조인 칼럼 기준으로 정렬한다. 이때 PGA에 저장되고, 모자르면 TEMP 메모리에 저장한다.
  3. 머지해서 최종적으로 정렬을 수행한다. 여기서 특이점은 이미 정렬되어 있기 때문에 full scan이 일어나서 정렬을 하지 않는 다는 점이다. 아마 이분탐색이나 해시알고리즘이 들어가지 않을까 싶은데 이것은 추측이다.

• 소트머지가 빠른이유는 O(n^2) -> O(n log(n)) 의 복잡도를 가지기 때문이다. 그치만 상황에 따라 소량의 데이터셋의 경우에는 큰 차이가 없을 수도 있다.

• 주된 용도로는 조건절에서 (=) 조건이 아닌 그 이외의 조건의 경우(like, between)이 사용되거나, 카테시안 곱이 발생하는 경우에 자주 사용한다.

• 하지만 최근에는 해시조인이 더 빠른 경우가 대다수이다.