세미 조인 튜닝

세미 조인 튜닝

개발자를 위한 오라클 SQL 튜닝 내용에서 참고한 내용입니다.

Intro

세미 조인(Semi Join)

• 세미 조인(Semi Join) 이란 조인 시 특정 조건에 부합된다면 더는 연산을 수행하지 않는 것을 뜻한다.

• SQL문에서 'EXISTS'또는 'NOT EXISTS'를 사용하면 옵티마이저는 세미 조인 사용 여부를 판단하게 된다.

• 세미 조인은 조인 방식에 따라 중첩 루프 세미 조인과 해시 세미 조인으로 나누어진다.

  • 특정 조건에 맞으면 더는 반복하지 않고 멈추기 때문에 성능상 매우 유리하다.

EXISTS 문과 NOT EXISTS문

• EXISTS 문과 NOT EXISTS문은 각각 특정 데이터가 존재하거나 존재하지 않는 데이터를 추출할 때 사용한다.

• 세미 조인은 이 두 가지 유형의 SQL로 처리할 때 유용하게 사용할 수 있다.

Join Type	SQL	Hint
중첩 루프 세미 조인	EXISTS	NS_SJ
해시 세미 조인	EXISTS	HASH_SJ
중첩 루프 안티 세미 조인	NOT EXISTS	NL_AJ
해시 안티 세미 조인	NOT EXISTS	HASH_AJ

세미 조인 튜닝

• 세미 조인 튜닝으로 사용되는 두 가지 방법

  • EXISTS 또는 NOT EXISTS 문이 사용된 SQL을 세미 조인으로 유도

  • UNION 또는 MINUS 집합 연산자가 사용된 SQL을 세미 조인으로 유도

서브쿼리 Unnesting

• 서브쿼리는 소괄호 '()'로 감싸져 있다.

• 옵티마이저는 '()'로 감싸진 서브 쿼리를 중첩되어 있다고 판단한다.

• 중첩된 서브쿼리를 풀어서 메인 쿼리와 똑같은 레벨로 위치하게 하는 작업을 '서브쿼리 Unnesting'이라 한다.

• 즉, 옵티마이저는 서브쿼리를 메인 쿼리와 똑같은 레벨로 위치하게 하는 이유는 쿼리 변환을 수행하게 된다.

• 옵티마이저가 서브쿼리 Unnesting 하는 이유는 메인 쿼리의 테이블과 서브쿼리의 테이블을 같은 레벨로 위치시키면 더 많은 접근 경로를 통한 다양한 실행 계획을 도출할 수 있기 때문이다.

• 이와 반대로 서브쿼리 Unnesting을 하지 않도록 하여 무조건 필터 조건으로 서브쿼리의 연산이 처리되게 하는 것을 '서브쿼리 No Unnesting'이라 부른다.

세미 조인 튜닝 관련 힌트

NS_SJ와 HASH_SJ

• EXISTS문을 쓴 서브쿼리를 사용하는 경우 해당 힌트를 사용할 수 있다.

• NL_SJ 힌트는 중첩 루프 세미 조인을, HASH_SJ는 해시 세미 조인을 유도한다.

• 사용법

  • 서브쿼리를 세미 조인으로 유도한다.

SELECT /*+ NL_SJ 또는 HASH_SJ */

NL_AJ와 HASH_AJ

• NOT EXISTS문을 쓴 서브쿼리를 사용하는 경우, 해당 힌트를 사용할 수 있다.

• NL_AJ 힌트는 중첩 루프 안티 세미 조인(Nested Loop Anti Semi Join) 을, HASH_SJ는 해시 안티 세미 조인(Hash Anti Semi Join) 을 유도한다.

• 사용법

SELECT /*+ NL_AJ 또는 HASH_AJ */

UNNEST

• UNNEST 힌트는 서브쿼리 Unnesting을 유도하는 힌트

• 서브쿼리의 내용을 메인 쿼리와 같은 레벨로 위치하게 하여 옵티마이저가 더 많은 접근 경로를 갖게 한다.

• 사용법

SELECT /*+ UNNEST */

NO_UNNEST

• NO_UNNEST 힌트는 서브쿼리 Unnesting을 방지하는 힌트

• 해당 힌트를 사용함으로써 오라클의 옵티마이저는 메인쿼리를 읽으면서 서브쿼리의 조건을 필터 처리하게 된다.

• 사용법

SELECT /*+ NO_UNNEST */