[친절한 SQL 4장] 조인튜닝

4.1.1 NL 조인 기본메커니즘

-. 일반적으로 NL조인은 Outer와 Innser 양쪽 테이블 모두 인덱스를 이용한다.

-. 프로그래밍에서 Nested Loop 구조랑 같음

 

4.1.2 NL 조인 샐행계회 제어

-. use_nl 힌트 사용

A->B->C->D 순으로 조인

-. B와C와 조인할때는 NL조인, D와 조인할떄는 Hash조인

C-> A -> D -> B 순으로조인

A,B,C,D NL조인을 하되 순서는 옵티마이저에게 맡긴다.

4.1.3 NL 조인 수행 과정 분석

-. 조인 query 문 실행 순서 예시

각 단계를 모두 완료하고 넘어가는 게 아니라 한 레코드씩 순차적으로 진행한다.

4.1.4 NL 조인 튜닝 포인트 

-, 사원X_1인덱스를 읽고 사원 table에서 엑세스 필터링하는 비율이 높다면 필터링 되는 컬럼을 사원_X1에 추가하는방안 고려

-. 고객_X1인덱스를 일고 고객 table에서 엑세스 필터링하는 비율이 높다명 필터링 되는 컬럼을 고객_X1에 추가하는 방안 고려

-. 맨 처음 액세스 하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다. 

 

4.1.5 NL 조인 특징 요약

-. 대량 데이터 조인할 때, NL조인 불리

-. 조인을 한 레코드씩 순차적으로 진행해서 부분처리가 가능하다. 

 

4.1.7 NL 조인 확장 메커니즘

-. 배치 I/O 기능 '테이블 Prefetch' : 인덱스를 이용해 테이블을 엑세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능.

-. Hint : nlj_prefetch, no_nlj_prefecth 

-. 11g부터는 배치 I/O 메커니즘이 작동하기 때문에 일정 순서로 출력되기를 원한다면 no_nlj_batching(b) 힌트를 추가하거나 맨 바깥쪽에 ORDER BY  절에 정렬 기준을 명시해야한다. 

 

※ 문제

인덱스 구성

 

-. 문제점  1. insert 테이블 alias를 왼쪽에 기술하는 습관이 좋다. 2. 인덱스 구성을 outer table로 만들었는데, inner table에서 row를 access하는 양이 많다면 성능에 문제를 야기한다. 그래서 inner table index구성에 신경써야한다.

 

4.2 소트 머지 조인

 

4.2.1 SGA vs PGA

-. SGA 1. 캐시된 데이터는 어러 프로세스가 공유할 수 있다. 2. 여러프로세스가 공유할 수 있지만 동시에 엑세스할 수는 없다.  3. 동시에 엑세스하려면 프로세스 간 액세스를 직력화하기 위한 Lock 메커니즘으로 래치(Latch)가 존재한다. 4. 데이터 블록과 인덱스 블록을 캐싱하는 DB버퍼캐시는 SGA의 가장 핵심적인 구성요소이며, 여기서 블록을 읽으려면 버퍼 Lock도 얻어야한다.

 

 

-. PGA 1. 오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서, 동시에 자신만의 고유 메모리 영역을 갖는다. 2. 각 오라클 서버 프로세스에 할당된 메모리 영역을 PGA(Process/Program/Private Global Area)라고 부른며 프로세스에 종속적인 고유 데이터를 저장하는 용도로 사용한다. 3. 할당 받은 PGA공간이 작아 데이터를 모두 저장할 수 없을 때는 Temp 테이블스페이스를 이용한다. 4. PGA는 다른 프로세스와 공유하지 않는 독립적은 메모리 공간이므로 래치 메커니즘을 불필요하다.  5. 같은 양의 데이트를 읽더라도 SGA 버퍼캐시에서 읽을 때보다 훨씬 빠르다.

 

4.2.2 기본 메커니즘

 

-. 1. 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬한다.

-. 2. 머지 단계 : 정렬한 양쪽 집합을 서로 머지(Merge) 한다.

-. Hint : use_merge(c)

-. Sort Area(PGA를 이용하고 모자르면 temp tablespace이용-> 독립적인 공간이다. 즉 데이터를 읽을때 래치 획득 과정이 없다.)에 저장된 데이터 자체가 인덱스 역할을 하므로 소트 머지 조인은 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다. 

-. 조인 컬럼에 인덱스가 있어도 NL조인은 대량 데이터 조인할 때 불리하므로 소트 머지 조인을 사용할 수 있다.

 

4.2.4 소트 머지 조인의 주용도

-. 소트 머지 조인 대신 대부분 해시 조인을 쓴다. (더 빠르다.)

-. 하지만 해시조인은 등치(=) 조건이 아닐 떄 사용할 수 없기 때문에 아래 조건일 때 주로 사용한다.

  * 조인 조건이 등치(=) 조건이 아닌 대량 데이터 조인

  * 조인 조건식이 아예 없는 조인(Cross Join, 카테시안곱)

 

4.3 해시조인

 

4.3.1 기본 메커니즘

1. Build 단계 : 작은 쪽 테이블 (Build Input)을 읽어 해시테이블(해시 맵)을 생성한다. 2. Probe 단계 : 큰 쪽 테이블 (Probe Input)을 읽어 해시 테이블을 탐색하면서 조인한다.  3. Hint : use_hash(c) 4. 해시 테이블은 PGA 영역에 할당된 Hash Area에 저장된다. 너무크면 Temp tablespace 이용

 

4.3.2 해시 조인이 빠른 이유

-. PGA 영역을 할당하기 때문에, 래치 획득 과정이 없이 빠르게 데이터 탐색 및 조회

-. 해시 조인도 Build Input, Probe Input 각 테이블을 읽을 떄는 DB버퍼캐시를 공유한다. 

 

4.3.3 대용량 Build Input 처리

-. 두 테이블 둘다 대용량이여서 인메모리 해시 조인이 불가능한 상황일때

-. DBMS는 분할/정복(Drive & Conquer) 방식을 사용한다. 

Temp 공간에 저장

 

4.3.4 해시 조인 실행계획 제어

-. 옵티마니저는 일반적으로 카디널리티(테이블 전체가 아닌 각 테이블 조건절에 대한 카니널리티)가 작은 테이블을 Build Input으로 선택한다.

-. 위와 같이 Plan이 풀렸다고 했을 때, T1(Build Input),T2 Hash join한 결과를 Build Input으로 쓰기 싫다면 no_swap_join_inputs(T2) 하면된다.

 

4.3.5 조인 메서드 선택 기준 ★★★★★

1. 소량 데이터 조인 -> NL조인 2. 대량 데이터 조인 -> 해시 조인 3. 대량 데이터 조인인데 해시 조인으로 처리 할 수 없을 때, 즉 조인 조건식이 등치(=) 조건이 아닐 때(조인 조건식이 아예 없는 카테시안 곱 포함) -> 소트머지조인

-. 같은 성능이라면 해시보다는 NL을 선택하는게 OLTP성 DB에서는 좋다. 

-. 해시조인은 CPU와 메모리 사용이 크게 증가하고, 해시 맵을 만드는 과정에 여러가지 래치 경합도 발생한다.

  1. 수행빈도가 낮고

  2. 쿼리 수행 시간이 오래 걸리는 것

  3. 대량 데이터 조회할 때로 

로 정리할 수 있다.

 

4.4. 서브쿼리 조인

 

4.4.1 서브쿼리 변환이 필요한 이유

 

• 필터 오퍼레이션 : 서브 쿼리를 필터 방식으로 처리하는 방법
  
  

-- no_unnest : 서브쿼리를 쿼리 하나로 판단하여 최적하지 말고 그대로 수행하라는 의미의 힌트. 중첩 해제를 하지 마라.
SELECT 
...
FROM 고객 c
WHERE c.가입일시 >= '19960101'
AND EXISTS (
SELECT /*+ no_unnest */ 
     'x'
FROM 거래
WHERE 고객번호 = c.고객번호
AND 거래일시 >= '20210621'  
)

•  필터 방식이란 마치 NL 조인처럼 동작하도록 하는 방식이다.
• 즉, 두 개의 쿼리를 각각 최적화하지 않고, NL 조인처럼 하나의 쿼리로 판단하여 최적화
• 실행계획은 아래와 같다.

  -- 'FILTER'를 'NESTED LOOP'라고 생각하면 된다.
  FILTER
    	TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE
    		INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)
  	INDEX (RAGNE SCAN) OF '거래_X1' (INDEX)​​

  • NL 조인과 차이점
    • 서브 쿼리 조건절에 해당하는 값이 존재하면 조건절이 참이 true가 되었으므로 바로 break
    • 서브 쿼리 조건절에 따른 결과를 캐싱한다. 동일한 조건의 결과가 올 경우 바로 캐시에서 찾을 수 있으므로 빠름.
    • 항상 메인 쿼리가 Outer, 서브 쿼리가 Inner

 

 

• Unnesting 방식 : 메인과 서브 쿼리 간의 계층구조를 없애고, 서로 같은 레벨로 쿼리를 처리하는 방식이다. 위에서 설명한 필터 방식과 비교해서 이해하면 쉽다.

  	Filter 방식	Unnesting 방식
  힌트	no_unnest	unnest
  Outer와 Inner 쿼리	항상 서브 쿼리가 Inner	힌트를 이용해 선택 가능
  조인 방식	NL 조인 방식	NL조인, 해시 조인 등 다양하게 가능

  • unnest 힌트로 유도 가능
  • 서브 쿼리가 메인 쿼리 보다 먼저 처리될 수 도 있다.
  • 다양한 최적화 기법을 줄 수 있어 필터 방식보다 더 좋은 실행 경로를 찾을 수 있다.
  • 서브 쿼리에 ROWNUM을 사용하면 unnest 힌트를 사용한다고 하여도 서브 쿼리를 최적화하지 않으니 ROWNUM을 서브 쿼리에 쓸 때 조심하자.

 

• 서브 쿼리 Pushing : 서브쿼리 필터링을 가능한 한 앞 단계에서 처리하도록 강제하는 기능
  • 조건절에 존재하는 서브 쿼리에 해당하는 데이터가 적다면 서브 쿼리가 먼저 수행되는 것이 좋다. 하지만 필터 방식의 서브 쿼리는 대게 맨 마지막에 처리되므로, 이를 가장 먼저 처리되도록 강제하는 기능이다.  
  • push_subq 힌트로 유도
  • 필터 방식에서만 사용 가능하기 때문에 no_unnest 힌트와 같이 힌트를 사용한다.
  • 반대로, 가장 나중에 처리하게 하려면 no_push_subq를 사용하면 된다.

 

• 인라인 뷰 서브 쿼리와 조인
  • 이 장 처음에 말했듯이 일반적으로 서브 쿼리가 포함된 쿼리는 옵티마이저는 각각의 쿼리를 나눠서 쿼리 변환을 하여 각각에 대해 더 나은 성능을 내도록 한다.
  • 인라인 뷰도 옵티마이저가 독립적으로 최적화하기 때문에 merge 힌트를 사용하여 다르게 처리할 수 있다.

    -- T에 대한 인라인 뷰 안에서는 조건에 해당하는 모든 데이터를 읽어야 한다.
    -- 모든 데이터를 읽고 결국엔 고객 테이블의 고객번호와 조인되는 값만 출력한다.
    -- 따라서 불필요한 데이터를 조회해서 비효율적이다.
    SELECT 
    	...
    FROM 고객 C
    	,(SELECT ... FROM 거래 WHERE 거래일시 >= trunc(sysdate, 'mm') GROUP BY 고객번호) T
    WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
    	AND T.고객번호 = C.고객번호
        
        
    -- merge : 뷰를 메인 쿼리와 머지하라는 힌트이다.
    -- 머지를 하게되면 고객 테이블을 먼저 읽고 조인할 때는 해당 고객들에 대한 데이터만 읽는다.
    SELECT 
    	...
    FROM 고객 C
    	,(SELECT /*+ merge */ ... FROM 거래 WHERE 거래일시 >= trunc(sysdate, 'mm') GROUP BY 고객번호) T
    WHERE C.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
    	AND T.고객번호 = C.고객번호
        
    
    
        	​

 

• 스칼라 서브 쿼리와 조인
  • 스칼라 서브 쿼리는 NL 조인 방식으로 실행된다.
  • 필터 서브 쿼리처럼 스칼라 서브 쿼리도 캐싱 기능을 사용하여 동일한 입력값에 대해서 캐싱해놓는다.
  • 스칼라 서브 쿼리는 2개 이상의 항목을 조회할 수 없으므로 그럴 경우에는, 해당 쿼리를 인라인 뷰로 사용하는 편이 낫다.
  • 스칼라 서브 쿼리도 unnest 사용이 가능하다