[SQL 튜닝 실습 2-6] 세미 조인 튜닝 개념

1) 세미 조인
조인 시 특정 조건에 부합되면 더 이상 연산 수행하지 않는 것을 뜻함
SQL문에서 EXISTS 또는 NOT EXISTS 를 사용하면 옵티마이저는 세미 조인 사용 여부를 판단하게 된다
세미 조인은 조인방식에 따라 중첩 루프 세미 조인과 해시 세미 조인으로 나뉜다
특정 조건에 맞으면 더 이상 반복하지 않고 멈추기때문에 성능상 아주 유리하다

 

2) EXISTS문과 NOT EXISTS문
각각 특정 데이터가 존재하거나 존재하지 않는 데이터를 추출할 때 사용한다
세미 조인은 EXISTS문과 NOT EXISTS문 이 두가지 유형의 SQL로 처리할 때 유용하게 사용할 수 있다.

 

조인 유형	SQL문	힌트
중첩 루프 세미 조인	EXISTS	NL_SJ
해시 세미 조인	EXISTS	HASH_SJ
중첩 루프 안티 세미 조인	NOT EXISTS	NL_AJ
해시 안티 세미 조인	NOT EXISTS	HASH_AJ

 

3) 세미 조인 튜닝
* EXISTS 또는 NOT EXISTS 문이 사용된 SQL을 세미 조인으로 유도한다
* UNION 또는 MINUS 집합 연산자가 사용된 SQL문을 세미 조인으로 유도한다

4) 서브쿼리 Unnesting
서브쿼리는 소괄호 ( ) 로 감싸져 있다
옵티마이저는 ( )로 감싸진 서브쿼리를 중첩되어 있다고 판단한다
중첩 서브쿼리를 풀어서 메인쿼리와 똑같은 레벨로 위치하게 하는 작업을 서브쿼리 Unnesting 이라고 부른다

즉, 옵티마이저는 서브쿼리를 메인쿼리와 똑같은 레벨로 위치하게 하는 쿼리 변환을 수행하게 된다
옵티마이저가 서브쿼리 Unnesting 하는 이유는 메인쿼리의 테이블과 서브쿼리의 테이블을 같은 레벨로 위치시키면
더 많은 접근 경로를 통한 다양한 실행 계획을 도출할 수 있기 때문이다.
반대로 서브쿼리 Unnesting 을 하지 않게 해서 무조건 필터 조건으로 서브쿼리의 연산이 처리되게 하는 것을 서브쿼리 No Unnesting 이라고 한다

 

5) 세미 조인 튜닝 관련 힌트
NL_SJ 와 HASH_SJ
EXISTS문을 쓴 서브쿼리를 사용할 경우 해당 힌트를 사용할 수 있다.
NL_SJ 힌트는 중첩 루프 세미 조인을 유도한다
HASH_SJ 는 해시 세미 조인을 유도한다

해당 서브쿼리를 세미 조인으로 유도 

SELECT /*+ NL_SJ 또는 HASH_SJ */

 

서브쿼리가 Unnesting 된 후 메인쿼리의 테이블인 DEPT 테이블과 서브쿼리의 테이블인 EMP 테이블을 해시 세미조인으로 유도한다

SELECT *				
FROM DEPT				
WHERE EXISTS				
	(SELECT /*+ HASH_SJ */ *			
	FROM EMP			
	WHERE EMP.DEPTNO = DEPT.DEPTNO			
	AND SAL > 200000			
	);			

 

NL_AJ와 HASH_AJ

NOT EXISTS 문을 쓴 서브쿼리를 사용할 경우 해당 힌트를 사용할 수 있습니다.
NL_AJ 힌트는 중첩 루프 안티 세미 조인을 유도한다
HASH_AJ 힌트는 해시 안티 세미 조인을 유도한다

해당 서브쿼리를 안티 세미 조인으로 유도한다

SELECT /*+ NL_AJ 또는 HASH_AJ */

 

서브쿼리가 Unnesting 된 후 메인쿼리의 테이블인 DEPT 테이블과 서브쿼리의 테이블인
EMP 테이블을 해시 안티 세미 조인으로 유도한다 

SELECT *				
FROM DEPT				
WHERE NOT EXISTS				
	(SELECT /*+ HASH_AJ*/ *			
	FROM EMP			
	WHERE EMP.DEPTNO = DEPT.DEPTNO			
	AND SAL > 200000			
	);			

 

UNNEST

서브쿼리 Unnesting 을 유도하는 힌트이다.
서브쿼리의 내용을 메인쿼리와 같은 레벨로 위치함으로써 옵티마이저는 더 많은 접근 경로를 갖게 된다

 

해당 서브쿼리 Unnesting

SELECT /*+ UNNEST */

 

UNNEST 힌트를 사용해서 서브쿼리 Unnesting 을 유도한다. 
이제 옵티마이저는 더 많은 접근 경로를 갖게 되서 더 다양한 실행 계획을 평가하게 된다

SELECT *	
FROM EMP A	
WHERE A.SAL > 2000	
AND EXISTS	
	(SELECT /*+ UNNEST */ '1'
	FROM DEPT B
	WHERE B.DEPTNO = A.DEPTNO
	AND B.LOC LIKE 'C%'
	);

 

NO_UNNEST

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

SELECT /*+ NO_UNNEST */

 

NO_UNNEST 힌트를 사용해서 서브쿼리 Unnesting 방지한다.
EMP 테이블을 읽으면서 EXISTS문의 조건을 필터 처리하게 된다

SELECT *	
FROM EMP A	
WHERE A.SAL > 2000	
AND EXISTS	
	(SELECT /*+ NO_UNNEST */ '1'
	FROM DEPT B
	WHERE B.DEPTNO = A.DEPTNO
	AND B.LOC LIKE 'C%'
	);