[오라클] Join 원리, 개념 파악하기(loop join, sort_merge join, hash join)

안녕하세요.

차근차근 개념부터 잡아가며 정리하기위해 책들을 읽고 정리하고자 합니다.

오늘은 오라클, mysql 등 DB에서 사용하는 join의 원리를 파악하고자 합니다.

대부분... 같은 컬럼을 연결해서 무심코 사용하고 있지 않나요?

저는 오라클을 공부하고있기 때문에 오라클을 기준으로 하겠습니다.

먼저, DBMS의 작동 원리 입니다.

평소에 데이터는 하드 디스크의 데이터 파일에 저장해 두었다가

필요한 시점에 메모리로 복사 해 옵니다.(이때의 메모리 = 데이터베이스 버퍼 캐시)

여기서 중요한 것은 100개의 칼럼을 가진 테이블에 

Select를 통해 1개의 컬럼을 수행할 경우에도 100개의 컬럼을 모두 접근하게 됨으로 조심해야하며,

정규화가 중요합니다.

이제 여기서 자주 들었던 Join의 종류가 나오기 시작합니다.

1) Neste Loop Join(가장 기본적인 Join 기법)

2)Sort_Merge join

3) Hash Join(CBE에서만 가능)

하나씩 정리해 보겠습니다.

1. Nested Loop Join

먼저 예시 쿼리를 적어보겠습니다.

select 

e.ename, d.dname

from 

emp e, dept d

where

e.deptno = d.deptno

위의 SQL을 실행하면 순서는 다음과 같습니다.

1) 사원·부서 테이블을 메모리에 복사

2) 사원 테이블에서 사원이름 꺼내서 임시 작업공간으로 가져 가져감(인덱스 상황이나 다른 요소에 따라 순서가 변경 될 수도 있음)

3) 부서 테이블에서 해당 부서명을 찾으러 가는데 그때 위 SQL의 where조건을 보고 

    해당 조건에 맞는 데이터를 찾아서 부서명을 가져옴

4) 한 행의 작업이 끝나면 다시 처음 테이블로 돌아가서 두번째 행의 이름을 PGA(메모리 영역으로 생각)로 가져옴

5) 다시 부서 테이블에 가서 두번째 부서번호와 동일한 부서번호를 가진 부서명을 꺼내옴

이와같은 과정이 먼저 읽었던 사원 테이블의 데이터가 끝이 날때까지 작업이 반복됩니다.(LOOP)

그래서 이 Join을 Nested Loop Join이라고 합니다.

먼저 읽은 테이블의 행의 수만큼 Join이 수행됩니다.(중요)

먼저 읽는 테이블이 Join의 성능을 결정 합니다.

그래서 Driving Table(선행 테이블)이라 하고 나중에 읽는 테이블은 Driven Table(후행 테이블) 이라 합니다.

그러므로, 여러 개의 테이블을 Join해야 할 경우 어떤 테이블을 선행 테이블로 설정하는가가 아주 중요합니다.

이것을 결정해주는 아로클 내부 구성요소를 옵티마이저라고 한다.

(음...쿼리가 실행될때 내부적으로 이루어 지는것을...옵티마이저라고 하는데...자세한건 공부가 필요할 것 같습니다.)

2. Sort-Merge join

select 

e.ename, d.dname

from 

emp e, dept d

where

e.deptno = d.deptno

다시한번 위의 쿼리를 확인해 보겠습니다.

emp 테이블의 ename의 값을 가져오고 이를 통해 dept 테이블의 dname의 값을 찾게 됩니다.

이때 where 절에 있는 조건을 보고 그 조건에 맞는 dname을 가져 오게 됩니다.

즉, where절에 잘못된 조건을 줄 경우나 조건을 안 줄 경우에 올바른 데이터를 가져오지 못합니다.

만약 dept 테이블의 데이터가 1억건 이라면 

emp의 ename 값을 찾기위해 1억건을 읽어야 합니다.

(emp 테이블에 데이터가 10개가 있다면 10억건의 데이터를 읽어야 합니다.)

그래서 join과 더불어 필수적으로 언급되는 것이 인덱스 입니다.

인덱스가 존재한다면 해당 테이블을 전부 읽지 않고도 데이터를 찾은 후 테이블로 가서 바로 데이터를 읽을 수 있습니다.

이때 인덱스가 없을 경우에도 빨리 해당 데이터를 찾아서 결과를 출력해야 하는 경우에 Sort-Merge join 방법을 사용합니다.

Sort-Merge 방법은 

where 조건을 기준으로(deptno) 정렬합니다.

그리고 서로 같은 값(deptno)를 비교하여 값을 가져 옵니다.

말그대로 Sort 한 후 그 결과를 Merge 해서 데이터를 찾게 됩니다.

단점은 Sort 할 때 시간이 너무 오래 걸린다는 점 입니다. 그래서 이를 보완하기 위하여 Hash Join을 사용합니다.

Hash Join

Hash Join은 양쪽 테이블 모두 Join 컬럼에 인덱스가 없을 경우에 사용 합니다.

(Sort-Merge Join이 시간이 너무 오래 걸리기 때문)

방법

1) 두 테이블 중에서 범위가 좁은 테이블을 메모리로 가져옵니다.

2) Join 조건 칼럼의 데이터를 Hash 함수에 넣어서 나온 Hash Value 값으로 Hash Table을 생성합니다.

3) 후행 테이블의 Join 조건을 Hash 함수에 넣어서 Hash Value를 생성하고 이 값을 선행 테이블의 Hash Table의 값과 비교하여 같은 값을 찾아 매칭합니다.

Sort-Merge Join과 Hash Join은 둘 다 모든 테이블을 다 읽는다는 부분은 동일하지만 Sort-Merge Join에서 실행하는 정렬은 실행하지 않습니다.

이 차이는 약 2배 이상의 성능차이가 나게 됩니다.

마무리로 각 Join이 방법을 개념적으로 설명하였습니다.

저도 아직 실제로 제대로 사용해 본적은 없습니다.

기본으로 개념만 알고 계셨으면 좋겠습니다.

고생하세요.