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. 윈도우 함수)

PreviousJoin(Nested Loop, Hash)NextPartial Range Processing

Last updated

Was this helpful?