# Join(Semi, Outer)
## Intro
## Join
* **조인**이란 2개 이상의 테이블에서 특정 조건에 의해 데이터를 검색하는 방법을 말한다.
* 오라클에서 제공하는 **조인 방식**은 크게 3가지 이다.
* **중첩 루프 조인(Nested Loop Join)**
* **해시 조인(Hash Join)**
* **소트 머지 조인(Sort Merge Join)**
* 이 중 널리 사용하는 방식은 **중첩 루프 조인**과 **해시 조인**이며 **소트 머지 조인**은 거의 사용하지 않는다.
* 추가적인 조인 방식
* **세미 조인(Semi Join)**
* **아우터 조인(Outer Join)**
## 세미 조인(Semi Join)
> **세미 조인이란**
* **세미 조인(Semi Join)** 이란 조인 시 특정 조건에 부합된다면 더는 연산을 수행하지 않는 것을 뜻한다.
* `SQL`문에서 '**EXISTS**'또는 '**NOT EXISTS**'를 사용하면 옵티마이저는 세미 조인 사용 여부를 판단하게 된다.
* **세미 조인**은 조인 방식에 따라 **중첩 루프 세미 조인**과 **해시 세미 조인**으로 나누어진다.
* `특정 조건에 맞으면 더는 반복하지 않고 멈추기 때문에 성능상 매우 유리하다.`
> **세미 조인을 사용하는 상황**
* **세미 조인 튜닝**으로 사용되는 두 가지 방법
* **EXISTS** 또는 **NOT EXISTS** 문이 사용된 SQL을 세미 조인으로 유도
* **UNION** 또는 **MINUS** 집합 연산자가 사용된 SQL을 세미 조인으로 유도
> **서브쿼리 Unnesting**
* 서브쿼리는 소괄호 '**()**'로 감싸져 있다.
* 옵티마이저는 **'()'로 감싸진 서브 쿼리**를 중첩되어 있다고 판단한다.
* **중첩된 서브쿼리**를 풀어서 **메인 쿼리와 똑같은 레벨로 위치**하게 하는 작업을 '**서브쿼리 UnNesting**'이라 한다.
* 즉, 옵티마이저는 **서브쿼리를 메인 쿼리와 똑같은 레벨로 위치하게 하는 이유**는 **쿼리 변환**을 수행하게 된다.
* 옵티마이저가 **서브쿼리 Unnesting** 하는 이유는 `메인 쿼리의 테이블`과 `서브쿼리의 테이블`을 **같은 레벨로 위치**시키면 **더 많은 접근 경로를 통한 다양한 실행 계획을 도출**할 수 있기 때문이다.
* 이와 반대로 **서브쿼리 Unnesting**을 하지 않도록 하여 무조건 필터 조건으로 서브쿼리의 연산이 처리되게 하는 것을 '**서브쿼리 No Unnesting**'이라 부른다.
> **세미 조인과 관련된 힌트**
| **Join Type** | **SQL** | **Hint** | **DESC** |
| ------------------ | :------------: | :----------: | --------------------------------------------------------------- |
| 중첩 루프 **세미 조인** | **EXISTS** | **NS\_SJ** | `EXISTS` 문을 쓴 서브쿼리를 사용 시 `NL_SJ` 힌트는 **중첩 루프 세미 조인**을 유도 |
| 해시 **세미 조인** | **EXISTS** | **HASH\_SJ** | `EXISTS` 문을 쓴 서브쿼리를 사용 시 `HASH_SJ` 힌트는 **해시 세미 조인**을 유도 |
| 중첩 루프 **안티 세미 조인** | **NOT EXISTS** | **NL\_AJ** | `NOT EXISTS` 문을 쓴 서브쿼리를 사용 시 `NL_AJ` 힌트는 **중첩 루프 안티 세미 조인**을 유도 |
| 해시 **안티 세미 조인** | **NOT EXISTS** | **HASH\_AJ** | `NOT EXISTS` 문을 쓴 서브쿼리를 사용 시 `HASH_AJ` 힌트는 **해시 안티 세미 조인**을 유도 |
### 세미 조인 튜닝 전
> **ERD 및 쿼리**
> **쿼리 및 실행 계획**
* **SQL 분석**
* **LEADING 힌트**를 사용하여 `TB_CUST 테이블`을 **가장 먼저 스캔** 하여 **테이블 풀스캔**으로 유도
* **USE\_NL 힌트**를 이용하여 `TB_CUST_DTL 테이블`과 **중첩 루프 조인 연산** 후 `TB_ORD 테이블`과 **중첩 루프 조인**을 유도
* **SQL의 문제점**
* `TB_ORD 테이블`이 가지고 있는 컬럼은 SELECT 절에 존재하지 않는다.
* `TB_ORD 테이블`은 **EXISTS** 유무만 판단해도 결과 집합에 영향을 주지 않는다.
* **즉, 해당 테이블은 세미 조인으로 튜닝할 수 있다.**
### 세미 조인 튜닝 후
* **SQL 분석**
* LEADING 힌트를 이용하여 가장 작은 테이블인 TB\_CUST 테이블을 Outer 테이블로 지정하고 FULL 힌트로 Outer 테이블을 테이블 풀 스캔한다.
* USE\_NL 힌트를 사용하여 TB\_CUST\_DTL을 Inner 테이블로 지정하고 중첩 루프 조인을 수행한다.
* EXISTS문을 이용하여 TB\_ORD 테이블의 존재 여부를 확인한다.
* NL\_SJ 힌트를 이용하여 중첩 루프 세미 조인으로 유도한다.
* INDEX 힌트를 이용하여 TB\_ORD\_IDX01 인덱스를 사용하도록 한다.
### 안티 세미 조인
* **EXISTS**문을 **NOT EXISTS**로 바꾸면 **안티 세미 조인**이 성립된다.
* **SQL 분석**
* **NOT EXISTS**문을 이용하여 `TB_CUST 테이블`과 `TB_CUST_DTL 테이블`의 **조인 결과** 중 `CUST_ID 컬럼`을 기준으로 `TB_ORD 테이블` 내에 존재하지 않는 행을 검색
* **NL\_AJ** 힌트를 이용하여 **중첩 루프 안티 세미 조인**으로 유도한다.
* **INDEX** 힌트를 이용하여 `TB_ORD_IDX01 인덱스`를 사용하도록 한다.
## 아우터 조인
> **아우터 조인**
* 테이블 `A`와 `B`가 있을 때 `A 테이블`을 **기준**으로 `B 테이블`이 **조인에 성공**하면 B 테이블의 **데이터를 보여주고**,
**조인에 실패**하면 B 테이블의 **데이터를 보여주지 않는 조인 방식**이다.
> **아우터 조인의 2가지 방식**
* **Left Outer Join**
* 왼쪽에 명시한 테이블을 기준이 되는 Outer 조인 방식
* 오른쪽에 명시한 테이블은 조인에 성공한 데이터만 보여주는 방식
* **Right Outer Join**
* 오른쪽에 명시한 테이블이 기준이 되는 Outer 조인 방식
* 왼쪽에 명시한 테이블은 조인에 성공한 데이터만 보여주는 방식
> **아우터 조인을 이용하여 테이블 스캔 최소화**
* 테이블 A, B, C이 있다고 가정
* A = B + C의 관계가 성립하는 경우 테이블 B와 C는 테이블 A에 대하여 배타적 관계에 있다고 할 수 있다.
* 테이블 A를 기준으로 테이블 B와 C를 각각 조인할 때 **UNION ALL**문을 사용하여 두 개의 SELECT 문의 합집합을 구하게 된다.
* 이러한 경우 **UNION ALL** 문을 아우터 조인으로 변환하여 성능을 개선할 수 있다. \
(가장 큰 테이블인 A를 단 한번만 스캔하는 것이 핵심)
> **아우터 조인을 스칼라 서브쿼리로 변환**
* 아우터 조인으로 구현된 SQL 문은 스칼라 서브쿼리로 변환할 수 있다.
* 오라클에서는 스칼라 서브쿼리로 한 번 이상 호출된 Input/Ouput 값을 멀티 버퍼에 저장해 둔 후 동일한 Input으로 호출되면 기존에 가지고 있던 Output 값을 바로 리턴하는 스칼라 서브쿼리 캐싱 기능이 있다.
* 이러한 오라클의 기능을 잘 활용하여 아우터 조인을 스칼라 서브쿼리로 변환하면 성능을 극대화할 수 있다.
### 아우터 조인 튜닝 전
> **ERD 및 쿼리**
* **SQL 분석**
* `TB_JOB_ORDER 테이블`과 `TB_OPEN_REQ 테이블`을 조인
* `TB_JOB_ORDER 테이블`과 `TB_DISABLE_REQ 테이블`을 조인
* 각각의 SELECT 문을 **UNION ALL** 하여 결과 집합을 도출
* **SQL 문제점**
* **가장 큰 용량의 테이블**인 `TB_JOB_ORDER 테이블`을 **두 번이나 스캔**하는 문제
* 해당 테이블을 단 한 번만 스캔하여 결과 집합을 도출해야 한다.
> **쿼리 및 실행 계획**
### 아우터 조인 튜닝 후
* **SQL 분석**
* **LEADING 힌트**를 사용하여 `TB_JOB_ORDER 테이블`을 **Outer 테이블**로 지정, **USE\_NL 힌트**를 이용하여 `TB_OPEN_REQ 테이블`을 **Inner 테이블**로 지정
* `TB_ORD_ORDER 테이블`과 `TB_OPEN_REQ 테이블`의 **중첩 루프 조인** 결과가 나오면 해당 결과를 **Outer 테이블**로 하고, `TB_DISABLE_REQ 테이블`을 **Inner 테이블**로 하여 중첩 루프 조인
* `TB_ORD_ORDER 테이블`의 `JOB_GUBUN`의 값이 **'1'** 인 경우 `TB_OPEN_REQ 테이블`과 **LEFT OUTER JOIN**
* `TB_ORD_ORDER 테이블`의 `JOB_GUBUN`의 값이 **'2'** 인 경우 `TB_DISABLE_REQ 테이블`과 **LEFT OUTER JOIN**
### 아우터 조인 스칼라 서브 쿼리 튜닝 전
> **ERD 및 쿼리**
* **SQL 분석**
* **LEADING 힌트**를 이용하여 `TB_TRD_DAY 테이블`을 **Outer 테이블**로 지정, **USE\_NL 힌트**를 이용하여 `TB_EXPORTER 테이블`을 **Inner 테이블**로 지정하고 **중첩 루프 조인** 처리가 되도록 유도
* **INDEX 힌트**를 이용하여 `TB_DRD_DAY_PK 인덱스`를 사용
* `TB_TRD_DAY 테이블`을 기준으로 **LEFT OUTER JOIN** 처리
* `TB_EXPORTER 테이블`은 **Outer 테이블**로 설정
* **SQL 문제점**
* **Outer 테이블**인 `TB_EXPORTER 테이블` 내에 EXPORTER\_NM 컬럼만 SELECT절에 있다.
* 이런 경우 `TB_EXPORTER 테이블`을 반드시 **OUTER JOIN** 할 필요가 없으므로 **스칼라 서브쿼리**로 변환하면 성능 향상을 기대할 수 있다.
> **쿼리 및 실행 계획**
### 아우터 조인 스칼라 서브 쿼리 튜닝 후
* **SQL 분석**
* `TB_TRD_DAY 테이블`을 **인라인 뷰**를 이용하여 원하는 데이터를 조회
* **스칼라 서브쿼리**를 이용하여 **TB\_EXPORTER 테이블**을 스캔하여 `EXPORTER_NM`을 조회, **스칼라 서브쿼리**의 캐싱 효과로 인하여 성능 향상
## 추가 정리
> 세 가지 조인 방식의 장 단점
| 이름 | 장점 | 단점 |
| :----------: | ------------------------------------------------------------------------------------------------------------------------- | ------------------------------------------------------------------------------------------ |
| Nested Loops | - 작은 구동 테이블 + 내부 테이블의 인덱스라는 전제조건을 갖추는 경우 굉장히 빠르다.
- 메모리 또는 디스크 소비가 적으므로 OLTP에 적합
- non-equi join에서도 사용가능
| - 대규모 테이블들의 결합에는 부적합
- 내부 테이블의 인덱스가 사용되지 않거나, 내부 테이블의 선택률이 높으면 느리다.
|
| Hash | - 대규모 테이블들을 결합할 때 적합 | - 메모리 소비량이 큰 OLTP에는 부적합
- 메모리 부족이 일어나면 TEMP 탈락 발생
- equi join에서만 사용가능
|
| Sort Merge | - 대규모 테이블들을 결합할 때 적합
- non-equi join에서도 사용가능
| - 메모리 소비량이 큰 OLTP에는 부적합
- 메모리 부족이 일어나면 TEMP 탈락 발생
- 데이터가 정렬되어 있지 않다면 비효율적
|
* 조인과 서브 쿼리의 트레이드 오프 (feat. 윈도우 함수)
---
# Agent Instructions: Querying This Documentation
If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question.
Perform an HTTP GET request on the current page URL with the `ask` query parameter:
```
GET https://seokrae.gitbook.io/sr/database/database/2021-07-17-db-toc/2021-08-07-db-join2.md?ask=
```
The question should be specific, self-contained, and written in natural language.
The response will contain a direct answer to the question and relevant excerpts and sources from the documentation.
Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.