[DB 기술면접 질문 리스트] : JOIN

- JOIN이란?

두개이상의 테이블이나 데이터베이스를 연결하여 데이터를 검색하는 방법입니다. 자신이 검색하고 싶은 컬럼이 다른 테이블에 있을경우 주로 사용하며 여러개의 테이블을 마치 하나의 테이블인 것처럼 활용하는 방법입니다. 보통 Primary key혹은 Foreign key로 두 테이블을 연결합니다

 

- JOIN 종류는( 논리적 JOIN ) ? 

1. INNER JOIN

-  기준테이블과 Join한 테이블의 중복된 값을 보여줌

- 교집합

 

2. LEFT OUTER JOIN 

- JOIN 문 기준 왼쪽 테이블(A)의 전체 데이터와, A테이블과 B테이블의 중복 데이터를 보여줌

 

3.RIGHT OUTER JOIN

- JOIN문 기준 오른쪽 테이블(B)의 전체 데이터와, A테이블과 B테이블의 중복 데이터를 보여줌

 

4.FULL OUTER JOIN

- A테이블과 B테이블 데이터 모두를 보여줌( 사실상 모든 데이터 출력)

- 합집합

 

5. CROSS JOIN

- 모든 경우의 수를 표현해줌

- 기준 테이블이 A일 경우, A 데이터의 ROW를 B테이블 전체와 JOIN하는 방식 

- 결과값은 A 레코드 수 * B 레코드 수

 

6. SELFT JOIN

- 자기 자신과 자기 자신을 조인

 

- JOIN 방식은( 물리적 JOIN )? 

1. Nested Loop Join ( 중첩 반복 조인 ) 

 

1) 정의 

: 2개 이상 테이블에서 하나의 테이블을 기준으로 순차적으로 상대방 ROW를 결합하여 원하는 결과를 추출하는 방식

 

 2) 처리 방식

 : Driving Table의 처리 범위를 하나씩 액세스 하면서 추출된 값으로 Driven Table을 조인하는 방식으로 동작

( 바깥 테이블의 처리 범위를 하나씩 접근하면서 추출된 값으로 테이블을 조인하는 방식)

 

3) 해당 조인을 사용하는 경우

- 한쪽 입력이 작고(Driving Table), 다른 한쪽 입력이 크면서 join열에 인덱스가 있는 경우(Driven Table)

- I/O 연산과 비교 연산이 가장 적게 필요하기 때문

 

 

3) Driving / Driven Table이란

 :  선행 테이블(Driving Table) : 조인 시 먼저 액세스 되는 테이블

                                                           WHERE 절로 최대한 데이터를 거를 수 있는 테이블 / 데이터 양이 적은 테이블로 선정

 

    후행 테이블(Driven Table) : 조인 시 나중에 액세스 되는 테이블 (Driving이 아닌 나머지 테이블)

                                                          조인을 위한 인덱스가 생성되어 있는 것이 좋다

                                                        ( 없다면 Driving Table에서 도출된 결과와 맞는지 매번 FULL TABLE SCAN으로 일일이 비교해야 하기 때문)

 

 

4) 특징 

- 선행 테이블의 결과를 통해 후행 테이블을 액세스 할 때 랜덤 I/O가 발생한다. (두 테이블의 랜덤 I/O가 높게 나옴)

- 인덱스에 의한 랜덤 액세스에 기반하므로, 대량 데이터 처리 시 적합하지 않다.

- 메모리 사용량이 가장 적다.

- 순차적으로 처리한다.

- 순차적으로 처리하기 때문에 안쪽 테이블에 인덱스가 필요하다.

 

5) 드라이빙 테이블 유도 방법

- 힌트 사용

/*+ORDERED*/ -- FROM절에 기술한 테이블 순서대로 제어
/*+LEADING (table명)*/ -- 힌트 내에 제시된 테이블이 드라이빙으로 처리됨

- 뷰 사용 : 뷰를 통해 데이터를 먼저 읽어내고, 뷰로 읽은 결과로 다음 데이터를 연결하는 방식으로 시도

 

 6) 코드 예시

SELECT * FROM [스키마명].[테이블명] AS 별명1
INNER LOOP JOIN [후행테이블] AS 별명2 ON 별명1.키 = 별명2.


SELECT * FROM [스키마명].[테이블명] AS 별명1
INNER JOIN [후행테이블] AS 별명2 ON 별명1.키 = 별명2.키
OPTION (LOOP JOIN)

 

 

2. Merge Join / Sort Merge Join ( 정렬 병합)

 

1)정의

 양 테이블을 각각 접근하여  결과를 정렬하고, 정렬한 결과를 Scan해가면서 연결 조건으로 Merge하는 방식

 

2) 동작 방식

1. 각 테이블에 대해 동시에 독립적으로 데이터를 먼저 읽어 들인다.

2. 읽혀진 각 테이블의 데이터를 조인을 위한 연결고리에 대하여 정렬을 수행한다.

3. 정렬이 모두 끝난 후에 조인 작업이 수행한다.

 

3) 해당 조인을 사용하는 경우

1. 두 join 열을 미리 정렬된 상태로 가져올 수 있는 경우

2. 연결 고리에 인덱스가 전혀 없는 경우

3. 대용량의 자료를 조인할때 유리한 경우

4. 조인 조건으로 <, >, <=, >=와 같은 범위 비교 연산자가 사용된 경우

5. 인덱스 사용에 따른 랜덤 액세스의 오버헤드가 많은 경우

( 두 입력의 크기가 서로 비슷할 경우에는 Merge Join과 Hash Join 성능이 비슷하지만, 두 입력의 크기가 서로 많이 다를 경우 Hash Join 성능이 더 좋다.)

 

4) 특징

 - 동시 처리 ( 양 테이블을 동시에 읽고 양 테이블이 join 준비가 되었을 때 join 수행 )

 - 독립적 ( 처리 범위를 줄일 수 있는 수단은 각 테이블의 while 조건 )

 - 인덱스 유무는 중요하지X ( 정렬된 양쪽 결과를 스캔하는 방식이므로)

 - 정렬에 따라 메모리 사용량이 증가

 

5) 성능 개선 방법

- 양쪽 테이블을 Access하는 과정에서 적절한 Scan을 사용하여 Access 속도를 빠르게 해준다.(적절한 Scan 사용)

- 양쪽 테이블에서 조인 컬럼이 이미 정렬되어있다면 속도가 향상됨

- 두 테이블 ACCESS속도와 정렬 속도를 최대한 비슷하게 맞춘다 ( 양쪽 테이블을 ACCESS하고 조회한 데이터들을 정렬할때 어느 한쪽이라도 정렬 작업이 종료되지 않으면 조인이 시작되지 않는다)

- SORT_AREA_SIZE를 적당한 크기로 최적화 시킨다( SORT_AREA_SIZE란 두 테이블이 정렬 작업을 위해 사용되는 정렬 공간에서 할당받을 수 있는 메모리 사이즈. 만약 이 사이즈가 부족하다면 Temporary Table Space를 사용하게 되면서 딜레이가 생김)

 

6) 코드 예시

SELECT * FROM [스키마명].[테이블명] AS 별명1
INNER MERGE JOIN [후행테이블] AS 별명2 ON 별명1.키 = 별명2.키


SELECT * FROM [스키마명].[테이블명] AS 별명1
INNER JOIN [후행테이블] AS 별명2 ON 별명1.키 = 별명2.키
OPTION (MERGE JOIN)

 

3. HASH JOIN

1) 정의

- 두 테이블 중 하나를 Hash Table로 선정하여, 테이블의 key 값을 Hash 알고리즘으로 비교하여 조인을 수행하는 방식

- Sort-Merge 조인은 소트의 부하가 많이 발생하여, 이를 보완하기 위한 방법으로 Sort 대신 해쉬값을 이용하는 조인
- Build Input을 읽어 해쉬 영역(Hash Area)에 해쉬 테이블(=해쉬 맵)을 생성하고, Probe Input을 읽어 해쉬 테이블을 탐색하면서 조인하는 방식

 

2) 동작 방식

- 둘 중 작은 집합(Build Input)을 읽어 해쉬 영역(Hash Area)에 해시 테이블(또는 Hash Map)을 생성

(해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인에 엔트리를 연결)

- 반대쪽 큰 집합(Probe Input)을 읽어 해시 테이블을 탐색하면서 JOIN

- 해시 함수에서 리턴 받은 버킷 주소로 찾아가 해시 체인을 스캔하면서 데이터를 찾는다.

 

3) 사용되는 경우

1. JOIN 컬럼에 적당한 인덱스가 없어 NL JOIN이 비효율적일 때

2. JOIN Access량이 많아 Random Access 부하가 심하여 NL JOIN이 비효율적일 때

3. join 입력 크기가 크고, 정렬되지 않았을 때 (대용량 데이터를 조인할 때)

4. 수행빈도가 낮고 쿼리 수행 시간이 오래 걸리는 대용량 테이블을 JOIN 할 때

5. '=' 비교를 수행할 때

6. 집합 일치 연산'(inner/outer/semi join, intersection, union, difference 등), '중복 제거', '그룹핑'

7. 비용 기반 옵티마이저를 사용할 때

 

4) 해쉬 함수

- 같은 입력값에 대해 같은 출력값을 보장하는 함수

- 만약 다른 입력값에 대한 출력값이 같은 경우, "해쉬 충돌"이라고 하며 이 때 입력값이 다른 엔트리가 해쉬 버킷(Hash Bucket)에 들어간다. (해쉬 값이 같은 입력값들은 hash bucket에 linked list로 연결된다.)

 

5)해쉬 조인 종류

• In-Memory Hash Join (인 메모리 해쉬 조인) : 해쉬 테이블을 메모리에 유지할 수 있는 경우의 조인 방법이다.
  •   Build Input을 읽어 해쉬 테이블을 생성한다. 이 때, 해쉬 함수를 이용한다.해쉬 테이블에는 해쉬 키와 결과에 출력될 컬럼들이 저장된다.
  •   Probe Input을 스캔하여 읽은 데이터로 해쉬 테이블 탐색한다. 여기서도 해쉬 함수를 이용한다.즉, 해쉬 값으로 버킷을 찾아가 해쉬 체인을 스캔하며 데이터를 탐색한다.

 

• Grace Hash Join (유예 해쉬 조인) : 같은 해쉬 함수를 적용했기 때문에 양쪽 테이블은 같은 해쉬 키를 기반으로 파티셔닝한다. (같은 해쉬 키를 가진 두 파티션을 파티션 pair라고 한다.)
  • 1. 파티션 단계
    • WHERE 조건으로 양쪽 테이블을 필터링한다.
    • 해쉬 함수 적용해서 해쉬 값에 따라 파티셔닝 후 각각의 파티션 별로 Tempdb에 임시 파일로 저장한다.
    • 같은 해쉬 함수를 적용했기 때문에 양쪽 테이블은 같은 해쉬 키를 기반으로 파티셔닝한다. (같은 해쉬 키를 가진 두 파티션을 파티션 pair라고 한다.)
  • 2. 조인 단계
    • 파티션 pair에서 한 쪽의 파티션 파일에 대해 해쉬 함수를 적용해 해쉬 테이블을 생성한다. 이 때, 파티션 pair에 대해 어떤 파티션을 Build Input으로 할지는 독립적으로 결정된다.
    • 파티션 pair에서 반대 쪽 파티션 파일의 row를 하나씩 읽으면서 해쉬 테이블을 탐색한다.
    • 모든 파티션 pair에 대한 처리가 완료될 때 까지 이 과정을 반복한다.
    • -> 파티션 단계에서 양쪽 집합을 모두 읽어 Tempdb에 저장해야하므로 In-Memory Hash Join보다 성능이 크게 떨어진다.

• Recursive Hash Join (= Nested Loops Hash Join) (재귀 해쉬 조인)
  • Tempdb에 있는 파티션 pair끼리 조인을 수행하려고 '작은 파티션'을 메모리에 로드하는 과정에서 다시 가용 메모리를 초과하는 경우, 추가적인 파티셔닝 단계를 수행한다.
• Hybrid Hash Join : Build Input이 가용 메모리보다 조금 밖에 크지 않다면 In-Memory Hash Join과 Grace Hash Join요소가 결합되어 조인한다.

 

 

6) 성능 개선 방법

1. HASH TABLE을 만드는 과정을 효율화 : Build Input이 Hash Area에 담길 정도로 충분히 작아야 하며 Build Input 해시 키 칼럼에 중복 값이 거의 없어야 좋다.

 

2. CPU 성능 향상 : HASH BUCKET이 조인 집합에 구성되어 해시 함수 결과를 저장해야 하는데 기본적으로 HASH_AREA_SIZE에 지정된 크기만큼의 메모리가 할당되어 사용됨. 이 과정에서 CPU 와 많은 메모리를 소모하므로 CPU를 향상 시킬 것

 

3. 충분한 PGA 메모리 확보 : Hash Area는 PGA 메모리에 할당되는데 Build Input이 HASH_AREA_SIZE를 초과하게 되면 가장 큰 순서대로 Hash Bucket이 Temporary Table Space로 내려가서 구성됨.  디스크로 내려간 Hash Bucket에 변경이 일어날 때마다 디스크 I/O가 발생하게 되어 성능이 현저하게 저하됨

 

 

7) 코드 예시

SELECT * FROM [스키마명].[테이블명] AS 별명1
INNER HASH JOIN [후행테이블] AS 별명2 ON 별명1.키 = 별명2.키



SELECT * FROM [스키마명].[테이블명] AS 별명1
INNER JOIN [후행테이블] AS 별명2 ON 별명1.키 = 별명2.키
OPTION (HASH JOIN)

 

 

 

-----------------------------------출처--------------------------

https://s2choco.tistory.com/32

https://coding-factory.tistory.com/758?category=990785 

https://in-harvey-it.tistory.com/8

 https://needjarvis.tistory.com/162 

https://coding-factory.tistory.com/87

[MSSQL] JOIN의 종류설명 및 사용법 & 예제