Oralce 조인에는 inner join, outer join과 같은 SQL 작성에 사용하는 조인을 떠올리기 쉽지만 이번 주제는 oracle의 optimizer가 어떤방식으로 조인을 실행을 하는지 다루어보겠습니다.
SQL 조인 구문을 작성하더라도 조인 방식에 대해 알고 있다면 조금 더 좋은 query를 작성할 수 있다고 생각합니다.
1. Nested Loop Join
순차적 루프에 의한 접근 방식
Nested Loop Join(NL 조인)은 가장 흔하게 접할 수 있는 오라클 조인 방식입니다. OLTP(Online Transaction Processing) 쿼리에서 가장 일반적이고 흔한 조인 방식입니다. 소량의 데이터를 처리하거나 부분범위 처리에 적합합니다. NL 조인의 방식은 For문을 중첩으로 사용해 join 한다고 생각하시면 이해하기 쉽습니다.
WHERE 절에 있는 조건에 따라 한 테이블을 기준으로 다른 테이블과 join을 시도하는데 기준이 되는 테이블을 드라이빙 테이블(Driving Table) 혹은 Outer 테이블이라고 부르며, 나머지 하나의 테이블은 드라이븐 테이블(Driven Table) 혹은 Inner 테이블이라고 부릅니다.
Optimzer가 드라이빙 테이블의 한 로우를 읽어 해당 로우를 찾고 이 값을 기준으로 드라이븐 테이블에서 같은 값을 검색합니다. 검색이 완료되면 다음 로우를 진행하고 이러한 방식으로 조인을 하는 것을 NLJ라고 합니다.
- 첫 번째 로우를 받는 시간은 빠르지만, 전체 결과를 받기까지 시간이 걸린다.
- 메모리가 필요없다.
- 드라이빙 테이블의 선택이 중요하다(가장 적은 데이터를 가지고 있는 테이블을 드라이빙 테이블로 지정)
- inner 테이블 검색시 인덱스를 사용하기 때문에 inner 테이블의 index 효율이 좋아야 한다.
- 드라이빙 테이블 검색에는 full table scan 혹은 index scan 방식을 사용한다.
- 쿼리 수행결과가 전체 로우의 15% 이하 일 때 사용한다.
- 조인 조건에 "=" 연산자가 사용될 경우 optimizer는 NLJ를 선택한다.
2. Sort Merge Join
정렬을 통한 접근 방식
Sort Merge 조인은 메모리를 사용합니다. 조인 실행시 조건에 해당하는 두 테이블을 각각 정렬을 시켜 만들어 놓은 후 한 번에 합치는 조인 방법입니다. 조인 절에 index가 없을 때 자주 사용되며 주로 대량의 데이터를 조회할 때 적합합니다.
- NLJ와 다르게 드라이빙 테이블이 필요없다. 두 테이블 모두 동등 레벨
- 첫 번째 로우를 받는 시간은 느리지만, 전체 로우가 반환되는 시간은 빠르다.
- 정렬을 위해 추가 메모리를 사용한다. SORT_AREA_SIZE 파라미터에 명시. 명시된 값 이상의 메모리 사용시 디스크 I/O 발생
- NLJ보다 많은 양의 데이터 처리시 유리하다.
- 메모리만 이용해 정렬 작업할 경우 넓은 범위의 값을 검색하는데 유리
- 정렬 작업이 전체 성능에 영향을 미치므로 SELECT 절에서 불필요한 컬럼은 제거해 정렬 작업에 부하를 줄이는 게 좋다.
- 조인 조건에 >, >=, <=, <와 같은 비동등 연산자가 사용될 경우 optimizer는 SMJ를 선택한다.
3. Hash Join
해시 함수를 이용한 접근 방식
두 테이블 중 WHERE 조건에 의해 필터링 된 로우수가 적은 테이블을 대상으로 해시 테이블을 생성한 후, 조인 조건에 따라 다른 하나의 테이블의 데이터를 검색하는 방식입니다.
메모리에 해시 테이블을 생성하고, 해시 함수를 이용해 연산 조인을 하기 때문에 CPU 사용이 증가할 수 있으므로 조회 빈도가 높은 온라인 프로그램에는 적합하지 않는 조인 방식입니다.
- 해시 테이블을 만든다는 것을 제외하면 NLJ와 비슷하다.
- 해시 테이블이 드라이빙 테이블이 되어 NLJ를 수행한다고 보면 된다.
- HASH_AREA_SIZE에 지정된 메모리 값 내에서 해시 테이블이 생성되므로 테이블의 크기가 이 값 내에 있을 경우에 유리하다.(일반적으로 HASH_AREA_SIZE는 SORT_AREA_SIZE의 두 배)
- 해시 대상의 테이블을 드라이빙 테이블(driving table) 또는 빌드 테이블(build table)이라고 한다.
- 조인 조건에서 동등조건 "="이 사용되었을 때 사용이 가능하다.
- 반환되는 로우 수가 적은 테이블(이 테이블을 대상으로 해서 해시 테이블을 생성)과 이에 반해 반환되는 로우수가 상당히 많은 테이블을 조인시 유리하다.
댓글