오라클 조인(Nested Loop Join, Sort Merge Join, Hash Join)

오라클 조인(Nested Loop Join, Sort Merge Join, Hash Join)

조인(join)이란 연결한다. 하나가 되도록 합쳐진다 의미를 가지고 있습니다. 해석 그대로 분리되어 있는 데이터를 연결해 합치는 작업을 말합니다. 두개 이상의 데이터를 연결할때 최적의 방법으로 연결해야 성능이 좋아지므로 오라클에서는 여러가지 조인 방법을 제공합니다. 그 중 대표적인 조인 메소드에 대해서 알아보도록 하겠습니다.

| Nested Loop Join

> 선행 테이블 조건에 맞는 데이터와 같은 값을 다른 테이블에서 참조합니다. 선행 테이블의 처리 범위가 처리량을 결정합니다. 선행 테이블의 값을 받아서 후행 테이블의 처리 범위가 결정됩니다. 주로 랜덤 액세스 방식으로 처리됩니다. 후행 테이블의 조인 컬럼의 인덱스의 유무 및 조건의 인텍스 참여의 정도에 따라 수행 속도가 많이 차이가 날수 있습니다. 부분 범위 처리 및 처리량이 적은 경우에 유리합니다. 

EMP 테이블과 DEPT 테이블의 NL조인 플랜 내용입니다. EMP테이블의 EMP_IDX01인덱스로 데이터를 액세스 후 후행 테이블인 DEPT테이블의 DEPT_XPK인덱스로 데이터를 찾아 결과를 리턴합니다. 자바의 2중 포문과 동일한 방식이라고 생각하시면 됩니다. 힌트는 /*+ USE_NL(A B) */ 사용합니다.

| Sort Merge Join

> 양쪽 테이블을 각자 액세스하여 처리 범위를 줄이고, 조인 컬럼 순으로 데이터를 정렬 후에 조인하는 방식입니다. 특징으로는 상대 테이블로부터 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리 범위를 결정합니다. 조인의 순서에 상관이 없습니다. 인덱스가 아닌 컬럼도 Merge할 작업 대상을 줄이므로 중요한 의미를 가집니다. 처리량이 많거나 전체 범위 처리 시에 유용합니다. 연결고리 이상 상태에 영향을 받지 않으므로 연결고리 컬럼을 위한 인덱스를 생성하지 않고도 유용하게 사용할 수 있습니다.

EMP 테이블과 DEPT 테이블의 MERGE JOIN 플랜 내용입니다. EMP 테이블 및 DEPT 테이블을 액세스 후 조건 컬럼 순으로 데이터를 정렬 합니다. 정렬을 함으로써 데이터를 처음부터 끝까지 확인 하지 않아도 되므로 인덱스와 동일한 효과를 볼 수 있습니다. 힌트는 /*+ USE_MERGE(A B)*/ 사용합니다.

| Hash join

> NL 조인의 랜덤 액세스 문제점과 Sort Merge 조인의 정렬 작업의 부담을 해결하기 위한 대안으로 등장하였습니다. Hash 함수 기법을 활용하여 조인을 수행하는 방식으로 대량의 데이터를 조인하는데 주로 사용되며 CBO의 동치조인(동등, Equal, =) 에서만 가능합니다. 둘 중 작은 집합을 읽어 해지 영역에 해지 테이블을 생성하고, 반대쪽 큰 집합을 읽어 해시 테이블을 탐색하면서 조인하는 방식입니다. 다른 테이블의 결과 값을 제공받지 않고, 자신에게 주어진 조건으로만 처리 범위를 결정합니다. 메모리 영역만으로 Hash Table을 생성할 때 최적의 효과를 낼 수 있으므로 적은 테이블이 선행 테이블로 선택됩니다. 대량의 데이터를 처리하는 배치 처리에 유용합니다. Full Table Scan 하면서 조인해야 할 때 유용합니다. 비용이 많이 들지만 수행 속도를 보장해야 하는 작업에 유용합니다.

EMP 테이블과 DEPT 테이블의 HASH 플랜 내용입니다. EMP 테이블에서 주어진 조건을 만족하는 행을 찾아 해쉬 테이블을 생성합니다. DEPT 테이블에 주어진 조건을 만족하는 행을 찾아 해쉬 함수를 적용하여 해당 버킷을 찾음 조인에 성공하면 데이터 추출 합니다.

| 조인 함수 비교

온라인 프로그램에서는 되도록 Nested Loop 조인이 적합하며, 배치 처리에는 Sort Merge 및 Hash 조인이 적합하다고 판단됩니다. 하지만, 데이터 와 인덱스 범위에 따라 다를 수도 있습니다. 오라클은 매우 똑똑한 옵티마이져가 있고 진화하고 있습니다. 그래도 비정상적으로 수행하는 일이 발생하니 데이터와 인덱스를 확인하시어 적절한 조인 방법을 사용해보시기 바라겠습니다.

그럼 모두 즐프하세요~