[SQLD 학습 자료 요약] SQL 기본 및 활용 3.3. 조인 수행 원리

본 문서의 내용은 한국데이터산업진흥원에서 펴낸 SQL 전문가 가이드를 기반으로 자격증 취득에 도움이 될 개념을 정리한 것입니다.

SQL 전문가 가이드 국내도서 저자 : 한국데이터산업진흥원 출판 : 한국데이터산업진흥원 2020.05.29 상세보기

 

3. 조인 수행 원리

테이블 또는 조인 결과를 이용하여 조인을 수행할 때 조인 단계별로 다른 조인 기법을 사용할 수 있다.

 

 

---

1. NL Join

NL Join 은 프로그래밍에서 사용하는 중첩된 반복문과 유사한 방식으로 조인을 수행한다. 반복문의 외부에 있는 테이블을 선행 테이블 또는 외부 테이블(Outer Table)이라고 하고, 반복문의 내부에 있는 테이블을 후행 테이블 또는 내부 테이블(Inner Table)이라고 한다.

 

먼저 선행 테이블의 조건을 만족하는 행을 추출하여 후행 테이블을 읽으면서 조인을 수행한다. 이 작업은 선행 테이블의 조건을 만족하는 모든 행의 수만큼 반복 수행한다. NL Join 에서는 선행 테이블의 조건을 만족하는 행의 수가 많으면(처리 주관 범위가 넓으면), 그 만큼 후행 테이블의 조인 작업은 반복 수행된다. 따라서 결과 행의 수가 적은(처리 주관 범위가 좁은) 테이블을 조인 순서상 선행 테이블로 선택하는 것이 전체 일량을 줄일 수 있다. NL Join 은 랜덤 방식으로 데이터를 액세스하기 때문에 처리 범위가 좁은 것이 유리하다.

 

 

NL Join 작업 방법

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키 값을 가지고 후행 테이블에서 조인 수행
3. 선행 테이블 조건을 만족하는 모든 행에 대해 위 작업을 반복 수행

Nested Loop Join 흐름도 (출처: SQL 전문가 가이드)

 

추출버퍼는 SQL 문의 실행결과를 보관하는 버퍼로서 일정 크기를 설정하여 추출버퍼에 결과가 모두 차거나 더 이상 결과가 없어서 추출버퍼를 채울 것이 없으면 결과를 사용자에게 반환한다. 추출 버퍼는 운반단위, Array Size, Prefetch Size 라고도 한다.

 

NL Join 기법은 조인이 성공하면 바로 조인 결과를 사용자에게 보여 줄 수 있다. 그래서 결과를 가능한 빨리 화면에 보여줘야 하는 온라인 프로그램에 적당한 조인 기법이다.

 

 

NL Join 특징

• 조인 컬럼에 적당한 인덱스가 있어서 자연조인이 효율적일 때 유용하다.
• Driving Table의 조인 데이터 양이 큰 영향을 주는 조인 방식이다.
• 유니크 인덱스를 활용하여 수행시간이 적게 걸리는 소량 테이블을 온라인 조회하는 경우 유용하다.

 

 

---

2. Sort Merge Join

Sort Merge Join 은 조인 칼럼을 기준으로 데이터를 정렬하여 조인을 수행한다. Sort Merge Join 은 주로 스캔 방식으로 데이터를 읽는다. 이 방법은 정렬할 데이터가 많아 메모리에서 모든 정렬 작업을 수행하기 어려운 경우에는 임시 영역(디스크)을 사용하기 때문에 성능이 떨어질 수 있다.

 

일반적으로 대량의 조인 작업에서 정렬 작업을 필요로 하는 Sort Merge Join 보다는 CPU 작업 위주로 처리하는 Hash Join 이 성능상 유리하다. 그러나 Sort Merge Join 은 Hash Join 과는 달리 동등 조인 뿐만 아니라 비동등 조인에 대해서도 조인 작업이 가능하다는 장점이 있다.

 

 

Sort Merger Join 흐름도 (출처: SQL 전문가 가이드)

Sort Merge Join 작업 방법

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 정렬 작업을 수행
3. 1-2 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
4. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
5. 후행 테이블의 조인 키를 기준으로 정렬 작업 수행
6. 4-5 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
7. 정렬된 결과를 이용하여 조인을 수행하고, 성공하면 추출버퍼에 넣음

 

Sort Merge Join 은 조인 칼럼의 인덱스를 사용하지 않기 때문에 조인 칼럼의 인덱스가 존재하지 않을 경우에도 사용할 수 있는 조인 기법이다. Sort Merge Join 에서 조인 작업을 위해 항상 정렬 작업이 발생하는 것은 아니다. 예를 들어, 조인할 테이블 중에서 이미 앞 단계의 작업을 수행하는 도중에 정렬 작업이 미리 수행되었다면 조인을 위한 정렬 작업은 발생하지 않을 수 있다.

 

 

Sort Merge Join 특징

• 조인 컬럼에 적당한 인덱스가 없어서 NL Join이 비효율적일 때 사용할 수 있다.
• Driving Table의 개념이 중요하지 않은 조인 방식이다.
• 조인 조건의 인덱스 유무에 영향을 받지 않는다.

 

 

---

3. Hash Join

Hash Join 은 해슁 기법을 이용하여 조인을 수행한다. 조인을 수행할 테이블의 조인 칼럼을 기준으로 해쉬 함수를 수행하여 서로 동일한 해쉬 값을 갖는 것들 사이에서 실제 값이 같은지를 비교하면서 조인을 수행한다. Hash Join 은 NL Join 의 랜덤 액세스 문제점과 Sort Merge Join 의 문제점인 정렬 작업의 부담을 해결 위한 대안으로 등장하였다.

 

 

Hash Join 특징

• 한쪽 테이블이 주 메모리의 가용 메모리에 담길 정도로 충분히 작고 해시 키 속성에 중복 값이 적을 때 효과적이다.
• 조인 컬럼에 적당한 인덱스가 없어서 자연조인이 비효율적일 때 더 효과적이다.
• 자연조인 시 드라이빙 집합 쪽으로 조인 액세스량이 많아 Random 액세스 부하가 심할 때 효과적이다.
• 소트 머지 조인을 하기에는 두 테이블이 너무 커서 소트 부하가 심할 때 효과적이다.

 

 

Hash Join 흐름도 (출처: SQL 전문가 가이드)

Hash Join 작업 방법

1. 선행 테이블에서 주어진 조건을 만족하는 행을 찾음
2. 선행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해쉬 테이블 생성
3. 1-2 작업을 선행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행
4. 후행 테이블에서 주어진 조건을 만족하는 행을 찾음
5. 후행 테이블의 조인 키를 기준으로 해쉬 함수를 적용하여 해당 버킷을 찾음
6. 조인에 성공하면 추출버퍼에 넣음
7. 4-6 작업을 후행 테이블의 조건을 만족하는 모든 행에 대해 반복 수행

 

Hash Join 은 해쉬 함수를 이용하여 조인을 수행하기 때문에 '='로 수행하는 조인 즉, 동등 조인에서만 사용할 수 있다. 해쉬 함수를 적용한 값은 어떤 값으로 해슁될 지 알 수 없다. 해쉬 함수가 적용될 때 동일한 값은 항상 같은 값으로 해슁됨이 보장된다. 그러나 해쉬 함수를 적용할 때 보다 큰 값이 항상 큰 값으로 해슁되고 작은 값이 항상 작은 값으로 해슁된다는 보장은 없다. 그렇기 때문에 Hash Join 은 동등 조인에서만 사용할 수 있다.

 

생성된 해쉬 테이블의 크기가 메모리에 적재할 수 있는 크기보다 더 커지면 임시 영역(디스크)에 해쉬 테이블을 저장한다. 그러면 추가적인 작업이 필요해 진다. 그렇기 때문에 Hash Join 을 할 때는 결과 행의 수가 적은 테이블을 선행 테이블로 사용하는 것이 좋다. 선행 테이블의 결과를 완전히 메모리에 저장할 수 있다면 임시 영역에 저장하는 작업이 발생하지 않기 때문이다.

 

Hash Join 에서는 선행 테이블을 이용하여 먼저 해쉬 테이블을 생성한다고 해서 선행 테이블을 Build Input 이라고도 하며, 후행 테이블은 만들어진 해쉬 테이블에 대해 해쉬 값의 존재여부를 검사한다고 해서 Prove Input 이라고도 한다.

 

NL Join은 데이터를 집계하는 업무 보다는 OLTP의 목록 처리 업무에 많이 사용된다. DW 등의 데이터 집계 업무에서 많이 사용되는 기법은 Hash Join 또는 Sort Merge Join 이다.

 

 

---

↓SQL 전문가 가이드 요약 목록

1장. 데이터 모델링의 이해

Part 1. 데이터 모델링의 이해

1.1. 데이터 모델링의 이해

1.2. 엔터티

1.3. 속성

1.4. 관계

1.5. 식별자

 

Part 2. 데이터 모델과 성능

2.1. 성능 데이터 모델링 개요

2.2. 정규화와 성능

2.3. 반정규화와 성능

2.4. 대량 데이터에 따른 성능

2.5. 데이터베이스 구조와 성능

2.6. 분산 데이터베이스와 성능

 

2장. SQL 기본 및 활용

Part 1. SQL 기본

1.1. 관계형 데이터베이스 개요

1.2. DDL

1.3. DML

1.4. TCL

1.5. WHERE 절

1.6. 함수 (FUNCTION)

1.7. GROUP BY, HAVING 절

1.8. ORDER BY 절

1.9. 조인 (JOIN)

 

Part 2. SQL 활용

2.1. 표준 조인

2.2. 집합 연산자

2.3. 계층형 질의와 셀프 조인

2.4. 서브쿼리

2.5. 그룹 함수

2.6. 윈도우 함수

2.7. DCL

2.8. 절차형 SQL

 

Part 3. SQL 최적화 기본원리

3.1. 옵티마이저와 실행계획

3.2. 인덱스 기본

3.3. 조인 수행 원리

 

따로 PDF 파일이 필요하신 분은 댓글을 통해 메일 주소 적어주시기 바랍니다.