[SQL 튜닝 실습 2-1] 중첩 루프 조인 튜닝 개념

1) 중첩 루프 조인

프로그래밍에서 중첩 반복문이라고 생각하면 되는데, 한건 한건씩 찾아나가기 때문에 대용량 테이블을 중첩 루프 조인한다면 성능에 큰 부하를 주게된다.

 

NOTE : 중첩 루프 조인의 원리

반복 : A테이블에서 조건에 맞는 행을 스캔						
	반복 : B테이블에서 조건에 맞는 행을 스캔					
		A 테이블과 B 테이블의 조인 조건이 일치한다면				
			해당 행을 결과 집합에 포함

 

---

2) Outer 테이블과 Inner 테이블

 

Outer 테이블

중첩 루프 조인에서 제일 먼저 스캔하는 테이블로, Driving 테이블이라고도 한다. 
Outer 테이블의 스캔 건수가 적을수록 중첩 루프 조인 시 유리하다
Outer 테이블의 스캔 결과 건수만큼 Inner 테이블에 대한 스캔을 반복하기 때문이다.

Inner 테이블

중첩 루프 조인에서 두번째로 스캔하는 테이블로, Driven 테이블이라고도 한다. 
Inner 테이블은 Outer 테이블보다 결과건수가 많아야 중첩 루프 조인 시 유리하다. 
또한, Inner 테이블을 스캔할 때 효율적인 인덱스 스캔을 못한다면,
Outer 테이블의 결과 건수만큼 Inner 테이블을 테이블 풀 스캔하게 되서
DBMS 전체 시스템에 큰 부하를 주게 된다. 

 

---

3) 인라인 뷰

From 절 내에 소괄호 () 로 감싸져 있는 SELECT 문을 뜻한다.

인라인 뷰 내에 있는 SELECT문의 결과는 마치 하나의 테이블과 같은 개념이다.

 

SELECT *			
FROM			
	DEPT A,		
	(		
		SELECT DEPTNO	
		FROM EMP	
	) B		
WHERE			
	A.DEPTNO = B.DEPTNO;		

 

B로 감싸져 있는 SELECT문을 인라인 뷰 내에 존재하는 SELECT문이라고 부른다.
즉, 집합 B를 하나의 테이블로 취급한다.

이 SQL에서 옵티마이저가 집합 B를 DEPT 테이블과 같은 레벨로 올라가도록 병합하는 것을 
View Merging 이라고 하며, 집합 B가 DEPT 테이블과 같이 올라가지 않게 막는 것을
View No Merging 이라고 한다
비용기반 옵티마이저는 일반적으로 인라인 뷰를 View Merging해서 DEPT와 같은 레벨로 취급하는 특성이 있다.

 

---

4) 중첩 루프 조인 튜닝

다음 2가지 사항을 지키지 않는 SQL문을 튜닝하는 모든 일련의 활동을 중첩 루프 조인 튜닝이라고 한다.

* Outer 테이블의 결과 집합이 작아야 한다.

* Inner 테이블 스캔 시 반드시 효율적인 인덱스 스캔이 이뤄져야 한다.

 

---

5) 중첩 루프 조인 튜닝 관련 힌트

 

LEADING

2개 이상의 테이블 조인 시 첫 번째로 스캔할 테이블을 지정하는 힌트
제일 먼저 스캔할 테이블을 지정

 

SELECT /*+ LEADING(테이블) */ 

LEADING 힌트를 이용해서 DEPT 테이블을 선행 테이블로 지정
이 SQL문이 어떤 조인 방식으로 동작할지는 오라클의 옵티마이저가 정한다.

 

SELECT /*+ LEADING(A) */		
*		
FROM DEPT A,		
EMP B		
WHERE A.DEPTNO = 10		
AND A.DEPTNO = B.DEPTNO;		

 

USE_NL

 

중첩 루프 조인을 유도하는 힌트로, 해당 힌트의 인자 값은 Inner 테이블을 지정
입력한 테이블을 Inner 테이블로 지정해서 중첩 루프 조인을 수행한다.

SELECT /*+ USE_NL(테이블) */ 

LEADING 힌트를 이용해서 ACCOUNTS 테이블을 Outer 테이블로 지정하고 
CUSTOMERS 테이블을 Inner 테이블로 지정해서 중첩 루프 조인 연산을 수행하게 하는 SQL이다.

 

SELECT /*+ LEADING(ACCOUNTS) USE_NL(CUSTOMERS) */					
	ACCOUNTS.BALANCE,				
	CUSTOMERS.LAST_NAME,				
	CUSTOMERS.FIRST_NAME				
FROM					
	ACCOUNTS, CUSTOMERS				
WHERE					
	ACCOUNTS.CUSTNO = CUSTOMERS.CUSTNO;				

 

MERGE

 

인라인 뷰로 감싸져 있는 SQL을 메인쿼리와 같은 레벨로 병합하는 역할을 한다
View Merging 함으로써 오라클의 옵티마이저는 더 많은 접근 경로를 갖게 된다

지정한 뷰를 메인쿼리와 같은 레벨로 한다

SELECT /*+ MERGE(뷰) */ 

MERGE힌트를 사용함으로써 인라인 뷰인 V를 View Merging 해서 E1 테이블과 E2 테이블이 같은 레벨이 된다. 
그 후 오라클의 옵티마이저는 다양한 접근 경로를 도출하게 된다.

 

SELECT /*+ MERGE(V) */				
	E1.ENAME, E1.SAL, V.AVG_SAL			
FROM				
	EMP E1, 			
		(SELECT		
		DEPTNO, AVG(SAL) AVG_SAL		
		FROM EMP E2		
		GROUP BY DEPTNO		
		) V		
WHERE 				
	E1.DEPTNO = V.DEPTNO AND			
	E1.SAL > V.AVG_SAL;			

 

NO_MERGE

인라인 뷰로 감싸져 있는 SQL이 메인쿼리와 같은 레벨로 병합되는 것을 방지한다.
No Merging 됨으로써 인라인 뷰에 있는 SQL은 독립적으로 수행된다.

지정한 뷰가 메인쿼리와 같은 레벨로 병합되는 것을 방지한다.

SELECT /*+ NO_MERGE(뷰) */ 

NO_MERGE 힌트를 사용함으로써 DALLASDEPT 뷰가 인라인 뷰 내에서 수행되도록 한다.
인라인 뷰인 DALLASDEPT 뷰가 수행 완료되면 DALLASDEPT 뷰의 결과가 E1 테이블을 조인으로 처리하게 된다.

 

SELECT /*+ NO_MERGE(DALLASDEPT) */				
	E1.ENAME,			
	DALLASDEPT.DNAME			
FROM				
	EMP E1,			
		(SELECT		
			DEPTNO, DNAME	
		FROM DEPT		
		WHERE		
			LOC = 'DALLAS'	
		) DALLASDEPT		
WHERE E1.DEPTNO = DALLASDEPT.DEPTNO;