[친절한 SQL 튜닝 스터디] 조인 튜닝

https://velog.io/@wooncloud/series/%EC%B9%9C%EC%A0%88%ED%95%9C-SQL-%ED%8A%9C%EB%8B%9D-%EC%8A%A4%ED%84%B0%EB%94%94

시리즈 | 친절한 SQL 튜닝 스터디 - wooncloud.log

 

NL 조인

• 조인은 기본은 NL조인이다.
• NL조인은 인덱스를 이용한 조인이다.
• 소트 머지 조인, 해시 조인도 NL조인과 프로세싱 과정은 다르지 않다.

기본 메커니즘

아래 사원과 고객 테이블이있다. 이 두 테이블에서 1996년 1월 1일 이후 입사한 사원이 관리하는 고객 테이터를 추출하는 프로그램을 작성해보자

select e.사원, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
    and c.관리사원번호 = e.사원번호

• NL조인은 Outer(선행)와 Inner(후행) 양쪽 테이블 모두 인덱스를 이용
• Outer 테이블은 사이즈가 크지 않으면, Table Full Scan을 할수도 있음. Table Full Scan을 하더라도 한번에 그치기 때문.
• Inner 쪽 테이블은 인덱스를 사용해야 함. Inner 루프에서는 관리사원번호 INDEX를 읽어야한다. 그렇지 않을시 Outer루프에서 읽은 건수만큼의 Table Full Scan을 반복함.

NL 조인 실행계획 제어

NL 조인을 제어할 때, 아래와 같이 use_nl을 사용한다.

select/*+ ordered use_nl(c) */
    e.사원명, c.고객명, c.전화번호
from 사원 e, 고객 c
where e.입사일자 >= '19960101'
    and c.관리자사원번호 = e.사원번호

ordered 힌트는 옵티마이저에게 FROM 절에 기술한 수선대로 조인하라고 지시할때 사용한다.

위의 힌트는 사원테이블기준으로 고객테이블과 NL방식으로 조인하라는 뜻.

3개 이상 테이블을 조인할 때는 아래와 같이 사용.

select /*+ ordered use_nl(B) use_nl(C) use_hash(D) */
from A, B, C, D
where ....

• B와 조인할 때 NL조인, C와 조인할 때 NL조인, D와 조인할때 해시조인

아래는 leading 힌트 써서 FROM 절을 사용하지 않고 순서 제어 가능

select /*+ leading(C, A, D, B) use_nl(A) use_nl(D) use_hash(B) */
from A, B, C, D
where ....

NL조인을 사용하되 옵티마이저가 순서는 스스로 정하도록 맡김

select /*+ use_nl(A, B, C, D) */
from A, B, C, D
where ....

NL 조인 수행 과정 분석

select /*+ ordered use_nl(c) index(e) index(c) */
    e.사원번호, e.사원명, e.입사일자, 
    c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
    and e.입사일자 >= '19960101'
    and e.부서코드 = 'Z123'
    and c.최종주문금액 >= 20000

* 사원_PK : 사원번호
* 사원_X1 : 입사일자
* 고객_PK : 고객번호
* 고객_X1 : 관리사원번호 
* 고객_X2 : 최종주문금액 

1. 입사일자 ≥ ‘19960101’ 조건을 만족하는 레코드를 찾으려고 사원_X1 인덱스를 Range 스캔한다.
2. 사원_X1 인덱스에서 읽은 ROWID로 사원 테이블을 액세스해서 부서코드 = ‘Z123’필터 조건을 만족하는지 확인한다.
3. 사원테이블에거 읽은 사원번호 값으로 조인 조건을 만족하는 고객 쪽 레코드를 찾으려고 고객_X1 인덱스를 Range 스캔한다.
4. 고객_X1 인덱스에서 읽은 ROWID로 고객 테이블을 액세스해서 최종주문금액 ≥ 20000 필터 조건을 만족하는지 확인한다.

NL 조인 튜닝 포인트

1. 첫번째 튜닝 포인트
   1. 위의 그림에서 사원_X1 인덱스를 읽고나서 사원 테이블을 액세스 하는 부분.
2. 두번째 튜닝 포인트
   1. 고객_X1 인덱스를 탐색하는 부분
3. 세번째 튜닝 포인트
   1. 고객_X1 인덱스를 읽고 나서 고객 테이블을 액세스 하는 부분
4. 마지막 튜닝 포인트
   1. 맨 처음 액세스하는 사원_X1 인덱스에서 얻은 결과 건수에 의해 전체 일량이 좌우된다는 사실을 기억하자!

올바른 조인 메소드 선택

OLTP에서 튜닝할 때는 일차적으로 NL조인부터 고려하는 것이 올바른 순서이다.

• NL조인 튜닝 포인트에 따라 각 단계 수행 일량을 분석해서 과도한 랜덤 액세스가 발생하는 지점부터 파악
• 조인 순서 변경해서 개선
• 더 좋은 인덱스 있는지 파악
• 인덱스 추가 또는 구성 변경 고려
• NL조인으로 효과내기 어려우면, 소트 머지 조인이나 해시 조인을 검토해본다.

NL 조인 특징 요약

• 랜덤 액세스 위주의 조인 방식
  • 대량의 데이터 조인할 때 불리함.
• 한 레코드씩 순차적 진행
  • 아무리 큰 테이블을 조인해도 매우 빠른 응답 속도를 낼 수 있음.
• 다른 조인방식과 다르게 인덱스 구성 전략이 특히 중요
• OLTP 시스템에 적합함.

NL 조인 확장 메커니즘

• 오라클의 경우 버전이 올라가면서 조인 성능을 높이기 위해 테이블의 Prefecth, Batch I/O 기능이 도입되었다.
• 테이블 Prefetch
  • 인덱스를 이용해 테이블을 액세스하다가 디스크 I/O가 필요해지면, 이어서 곧 읽게 될 블록까지 미리 읽어서 버퍼캐시에 적재하는 기능.
  • 실행계획에 인덱스 rowid에 의한 Inner 테이블 액세스가 Nested Loops 위쪽에 표시되면, Prefetch 기능이 활성화됨을 의미한다.
• 배치 I/O
  • 디스크 I/O Call을 미뤘다가 일정 블록이 일정량 쌓이면 한꺼번에 처리하는 기능이다.
  • Inner쪽 인덱스만으로 조인을 하고나서 테이블과의 조인은 나중에 일괄처리하는 메커니즘으로 테이블 액세스는 나중에 하지만 부분범위처리는 정상적으로 작동한다.
  • 인덱스와의 조인을 모두 완료하고 나서 테이블을 액세스하는 것이 아니라 일정량씩 나누어 처리한다. (부분범위처리)

소트 머지 조인

SGA vs PGA

SGA는 여러 프로세스가 공유할 수 있지만 동시에 액세스 안된다. 그래서 프로세스간 액세스를 직렬화 하려고 Lock매커니즘인 래치가 존재한다. SGA에 있는 DB 버퍼캐시의 블록을 읽으려면 버퍼 Lock도 얻어야 한다.

PGA

오라클 서버 프로세스는 SGA에 공유된 데이터를 읽고 쓰면서 자신만의 공유 메모리 영역을 가지는데 그걸 PGA(Process/Program/Private Global Area) 라고 함.

할당 받은 PGA 공간이 작아 데이터를 저장할 수 없으면 Temp 테이블 스페이스를 이용한다.

PGA는 독립 메모리 공간이라 래치 매커니즘이 필요없다. 그래서 같은 양의 데이터를 읽더라도 SGA 버퍼캐시보다 훨 빠르다.

기본 메커니즘

소트 머지 조인은 두 단계로 진행한다

• 소트 단계 : 양쪽 집합을 조인 컬럼 기준으로 정렬
• 머지 단계 : 정렬한 양쪽 집합을 서로 머지 함.

소트머지 조인은 use_merge 힌트로 유도한다.

select /*+ ordered use_merge(c) */
    e.사원번호, e.사원병, e.입사일자, 
    c.고객번호, c.고객명, c.전화번호, c.최종주문금액
from 사원 e, 고객 c
where c.관리사원번호 = e.사원번호
    and e.입사일자 >= '19960101'
    and e.부서코드 = 'Z123'
    and c.최종주문금액 >= 20000

1. 아래 조건에 해당하는 사원 데이터를 읽어 사원 번호 순으로 정렬.
   1. 정렬한 결과 집합은 PGA영역에 할당된 Sort Area에 저장. 공간 없으면 Temp 테이블 스페이스 사용함.
   2. select e.사원번호, e.사원병, e.입사일자 from 사원 e where e.입사일자 >= '19960101' and e.부서코드 = 'Z123' order by e.사원번호
2. 아래 조건에 해당하는 고객 데이터를 읽어 조인 컬럼인 관리사원번호 순으로 정렬
   1. 정렬한 결과 집합은 PGA영역에 할당된 Sort Area에 저장. 공간 없으면 Temp 테이블 스페이스 사용함.
   2. select c.고객번호, c.고객명, c.전화번호, c.최종주문금액 from 고객 c where c.최종주문금액 >= 20000 order by c.관리사원번호
3. PGA에 저장한 사원데이터를 스캔하면서 PGA에 저장한 고객 데이터 조인.

1, 2 단계는 소트단계 3은 머지 단계이다. 3번 머지 단계는 NL 조인과 다르지 않다.

소트머지 조인은 Sort Area에 저장한 데이터 자체가 인덱스 역할을 해서, 조인 컬럼에 인덱스가 없어도 사용할 수 있는 조인 방식이다.

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

소트 머지 조인이 빠른이유

NL조인은 대량의 데이터를 조인할때 성능이 좋지 않아 소트 머지조인과 해시 조인이 개발되었다.

소트 머지 조인은 Sort Area에 미리 정렬해둔 자료구조를 사용하는 것 외 NL조인과 같다.

하지만 소트머지 조인은 양쪽 테이블로부터 조인 대상 집합을 일괄적으로 읽어 PGA에 저장후 조인한다. SGA처럼 래치 획득 과정이 없어 대량 데이터 조인에 유리하다.

소트 머지 조인도 양쪽 테이블로부터 조인 대상 집합을 읽을 때 DB 버퍼캐시를 경유하는데, 그때 인덱스를 사용한다. 그 과정에 생기는 “버퍼캐시 탐색 비용”, “랜덤 엑세스 부하”는 소트 머지 조인도 피할 수 없다.

소트 머지 조인의 주용도

소트 머지 조인은 아래와 같은 상황에서 주로 사용한다.

• 조인 조건식이 등치조건이 아닌 대량 데이터 조인
• 조인 조건식이 아예 없는 조인

소트 머지 조인 제어하기

소트머지 조인은 use_merge 힌트로 유도한다.

소트 머지 조인 특징 요약

• 실시간으로 인덱스를 생성하는 조인이다.
• NL조인과 같은 방식이지만 PGA 영역을 사용해서 소트 부하만 감수하면 NL보다 빠르다.
• NL조인은 인덱스 유무에 크게 영향받으나, 소트 머지 조인은 영향받지 않는다.
• 양쪽 집합을 개별적으로 읽고 조인을 시작한다.
• 조인컬럼에 인덱스가 없는 상황에서 두 테이블을을 각각 읽어 조인 대상 집합을 줄일 수 있을때 유리하다.
• 스캔 위주 액세스 방식을 사용한다.
• 양쪽 소스 집합으로부터 조인 대상 레코드를 찾을때는 인덱스를 이용할 수도 있다.

해시 조인

기본 매커니즘

두 단계로 진행된다.

1. Build 단계 : 작은 쪽 테이블을 읽어 해시 테이블을 생성한다.
2. Probe 단계 : 큰 쪽 테이블을 읽어 해시 테이블을 탐색하며 조인한다.

해시 조인이 빠른 이유

• Hash Area에 생성한 테이블을 이용, 조인 프로세싱은 NL조인과 같음.
• 소트 머지 조인처럼 해시 테이블을 PGA 영역에 할당함
• 해시 조인은 NL 조인처럼 조인 과정에서 발생하는 랜덤 액세스 부하가 없고, 소트 머지 조인처럼 양쪽 집합을 미리 정렬하는 부하도 없다.
• 해시 테이블을 생성하는 비용이 수반되지만, 둘 중 작은 집합을 Build Input으로 선택하므로 대개는 부담이 크지 않다.
• Build Input이 PGA 메모리에 담길 때, 인메모리(In-Memory) 해시 조인일 때 가장 효과적인 이유가 바로 여기에 있다.

대용량 Build Input 처리

두 테이블이 모두 대용량 테이블이라 인메모리 해시조인이 불가능하다면?

이럴때 2단계로 나눠 분할-정복 (Divide & Conquer) 방식을 사용한다.

파티션 단계

조인하는 양쪽 집합의 조인 컬럼에 해시함수를 적용하고, 반환된 해시 값에 따라 동적으로 파티셔닝 한다.

독립적으로 처리할 수 있는 여러개의 작은 서브 집합으로 분할함으로서 파티션짝을 생성하는 단계

조인 단계

• 파티션 단계 완료시 파티션짝에 대해 하나씩 조인 수행.
• 각각 Build Input과 Probe Input 은 독립적으로 결정된다.
• 즉, 파티션하기 전 어느쪽이 작은 테이블이었는지에 상관없이 각 파티션 짝별로 작은 쪽을 Build Input으로 선택! 해시 테이블 생성!
• 해시테이블 생성후 반대쪽 파티션 로우를 하나씩 읽으며 해시테이블 탐색. 모든 파티션 짝에 대한 처리를 마칠때까지 반복.

해시 조인 실행계획 제어

해시 조인 실행계획을 제어할 때 아래와 같이 use_hash 힌트를 사용한다.

SELECT /*+ use_hash(e c) */
       e.사원번호, e.사원명, e.입사일자
     , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE c.관리사원번호 = e.사원번호
   AND e.입사일자 >= ‘19960101’
   AND e.부서코드 = ‘Z123’
   AND c.최종주문금액 >= 2000

• use_hash 힌트만 사용하면 Build Input을 옵티마이저가 선택, 일반적으로 둘 중 카디널리티가 작은 테이블을 선택.
• Build Input을 사용자가 직접 선택하려면 leading이나 ordered 힌트를 사용하면 됨.
• 이들 힌트로 지시한 순서에 따라 먼저 읽는 테이블을 Build Input으로 선택한다.

SELECT /*+ leading(e) use_hash(c) */ -- 또는 ordred use_hash(c)
       e.사원번호, e.사원명, e.입사일자
     , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE c.관리사원번호 = e.사원번호
   AND e.입사일자 >= ‘19960101’
   AND e.부서코드 = ‘Z123’
   AND c.최종주문금액 >= 2000

아래와 같이 swap_join_inputs 힌트로 Build Input을 직접 선택하는 방법도 있다.

SELECT /*+ use_hash(e c) swap_join_inputs(e) */
       e.사원번호, e.사원명, e.입사일자
     , c.고객번호, c.고객명, c.전화번호, c.최종주문금액
  FROM 사원 e, 고객 c
 WHERE c.관리사원번호 = e.사원번호
   AND e.입사일자 >= ‘19960101’
   AND e.부서코드 = ‘Z123’
   AND c.최종주문금액 >= 2000

조인 메소드 선택 기준

해시조인이 빨라도 단점이 있다. 수행빈도가 높은 쿼리에 대해 위험하다.

• 소량 데이터 조인 ⇒ NL 조인
• 대량 데이터 조인 ⇒ 해시 조인
• 대량 데이터 조인인데, 해시조인으로 처리불가시 (조인 조건식이 =이 아닐때) ⇒ 소트머지 조인

수행빈도가 매우 높은 쿼리에 대한 기준

1. NL 조인과 해시 조인이 성능이 같으면 ⇒ NL 조인
2. 해시 조인이 약간 빨라도 ⇒ NL 조인
3. NL조인보다 해시 조인이 매우 빠른 경우 ⇒ 해시 조인

왜 2번처럼 해시 조인이 NL 보다 빨라도 NL 조인을 써야 하는가?

• NL조인에 사용되는 인덱스는 영구적으로 유지하면서 다양한 쿼리를 위해 공유 및 재사용하는 자료구조이다.
• 해시 테이블은 단 하나의 쿼리를 위해 생성하고 사용이 끝나면 소멸하는 자료구조이다.
• 해시 조인을 사용하면 CPU와 메모리 사용률을 크게 증가시킨다.
• 해시 맵을 만드는 과정에서 여러 래치 경합도 발생!

해시 조인이 사용되는 3가지 조건

• 수행 빈도가 낮고
• 쿼리 수행 시간이 오래 걸리는
• 대량의 데이터를 조인할 때.

서브쿼리 조인

💡 옵티마이저는 서브쿼리에 대해 다양한 형태로 쿼리 변환을 시도해서 쿼리 변환을 알아야 한다.

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

옵티마이저는 제일 먼저 사용자로부터 받은 SQL을 최적화에 유리한 형태로 변환작업을 한다 ⇒ 쿼리변환 진행

쿼리변환은 옵티마이저가 SQL을 분석하여 의미적으로 동일하지만 더 나은 성능이 기대되는 형태로 재작성 하는 것을 말한다.

• 서브쿼리는 하나의 SQL문 안에 괄호로 묶은 별도의 쿼리 블록을 말한다.
• 쿼리에 내장된 또 다른 쿼리다.
• 서브쿼리를 DBMS마다 조금씩 다르게 분류하는데 오라클은 세가지로 분류한다.

1. 인라인 뷰 : FROM 절에 사용한 서브쿼리
2. 중첩 서브쿼리 : 결과 집합을 한정하기 위해 WHERE 절에 사용한 서브쿼리를 말한다.
   특히 서브쿼리가 메인 컬럼을 참조하는 형태를 상관관계 있는 서브쿼리라고 한다.
3. 스칼라 서브쿼리 : 한 레코드당 정확히 하나의 값을 반환하는 서브쿼리이다.
   주로 SELECT-LIST에서 사용하지만 몇가지 예외상황을 제외하면 컬럼이 올수 있는 대부분 위치에서 사용할 수 있다.

예시1

SELECT c.고객번호, c.고객명
  FROM 고객 c
 WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), 'mm')
   AND EXISTS (SELECT /*+ no_unnest */
                 FROM 거래
                WHERE 고객번호 = c.고객번호
                  AND 거래일시 >= TRUNC(SYSDATE, 'mm'))

SELECT c.고객번호, c.고객명
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), 'mm')

select 'x'
from 거래
where 고객번호 = :cust_no
    and 거래일시 >= TRUNC(SYSDATE, 'mm'))

예시2

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c, (
    **select  고객번호, 
                    avg(거래금액) 평균거래, 
                    min(거래금액) 최소거래, 
                    max(거래금액) 최대거래
    from 거래
    where 거래일시 >= turnc(sysdate, 'mm')
    group by 고객번호**
) t
where c.가입일시>= turnc(add_months(sysdate, -1), 'mm')
    and t.고객번호= c.고객번호

select c.고객번호, c.고객명, t.평균거래, t.최소거래, t.최대거래
from 고객 c, SYS_VW_TEMP t
where c.가입일시>= turnc(add_months(sysdate, -1), 'mm')
    and t.고객번호= c.고객번호

select 고객번호, avg(거래금액) 평균거래, min(거래금액) 최소거래, max(거래금액) 최대거래
from 거래
where 거래일시 >= turnc(sysdate, 'mm')
group by 고객번호

서브쿼리와 조인

메인쿼리와 서브쿼리간에는 부모와 자식같은 종속적이고 계층적인 관계가 형성된다.

서브가 메인에 종속되기 때문에 단독으로 실행할 수 없고 메인에서 값을 받아서 반복적으로 필터링하는 방식을 사용한다.

필터 오퍼레이션

서브쿼리를 필터방식으로 처리하려면 no_unnset 힌트를 사용 (unnest(풀어내지) 말고 그대로 수행하라는 뜻)

select c.고객번호, c.고객명, 
from 고객 c 
where c.가입일시 > trunc(add_months(sysdate, -1), 'mm') 
    and exists (
                select /*+ no_unnest */ 'x'
                from 거래 
                where 고객번호 = c.고객번호 
                    and 거래일시 >= trunc(sysdate, 'mm') 
            )

Execution Plan ------------------------------------------------------------
0 SELECT STATEMENT Optimizer=ALL_ROWS (Cost=289 Card=1 Bytes=39) 
1 0 FILTER 
2 1 TABLE ACCESS (BY INDEX ROWID) OF '고객' (TABLE) (Cost=4 Card=190 ... )
3 2 INDEX (RANGE SCAN) OF '고객_X01' (INDEX) (Cost=2 Card=190) 
4 1 INDEX (RANGE SCAN) OF '거래_X01' (INDEX) (Cost=3 Card=4K Bytes=92K)

• 필터 오퍼레이션은 NL 조인과 처리 루틴이 같다.

차이

• 조인에 성공하는 순간(exist) 진행을 멈추고 다음 로우 진행
• 필터는 캐싱기능을 가짐.
  • 서브쿼리 입력값에 따른 반환값을 캐싱하는 기능
  • 필터가 각 서브쿼리 수행에 대해서 캐싱
  • 캐싱은 쿼리단위로 이루어짐
  • 쿼리를 시작할 때 PGA 메모리에 공간 할당, 쿼리 수행하면서 채우고 다 쓰면 반환.
• 필터 서브쿼리는 일반 NL 조인과 달리 메인 쿼리에 종속되므로 조인 순서가 고정된다.
  • 메인 쿼리가 드라이빙 집합

서브쿼리 Unnesting

Unnesting 하려면 unnest 힌트 사용

select c.고객번호, c.고객명
from 고객 c 
where c.기입일시 >= trunc(add_months(sysdate, -1), 'mm') 
    and exists ( 
        select /*+ unnest nl_sj */ 'x' 
        from 거래
        where 고객번호 = c.고객번호
            and 거래일시 >= trunc(Sysdate, 'mm') )

nest의 사전적 의미 : 차곡차곡 포개넣다. ⇒ 중첩

nl_sj (NL 세미조인) : 기본적으로 NL 조인과 같은 프로세스이나, 조인에 성공하는 순간 진행을 멈추고 메인 쿼리의 다음 로우를 계속 처리한다는 점만 다름. (필터 오퍼레이션 기능이기도 함)

• unnest하면 서브쿼리는 NL 세미조인 외 다양한 방식으로 실행될 수 있다.
• Unnesting 된 서브쿼리는 메인 쿼리보다 먼저 처리될 수 있다.

leading 힌트

select /*+ leading(거래@subq) use_nl(c) */ c.고객번호, c.고객명
from 고객 c 
where c.기입일시 >= trunc(add_months(sysdate, -1), 'mm') 
    and exists ( 
        select /*+ qb_name(subq) unnest */ 'x' 
        from 거래
        where 고객번호 = c.고객번호
            and 거래일시 >= trunc(sysdate, 'mm') )

서브쿼리 집합에서 고객번호 중복을 제거하기 위해 쿼리를 아래처럼 변환

select /*+ no_merge(t) leading(t) use_nl(c) */ c.고객번호, c.고객명
from (
    select distinct 고객번호
    from 거래 
    where 거래일시 trunc(sysdate, 'mm')) t, 고객 c
where c.가입일시 >= trunc(add_months(sysdate, -1), 'mm')
    and c.고객번호 = t.고객번호

해시 조인

힌트는 unnsest hash_sj

select c.고객번호, c.고객명
from 고객 c 
where c.기입일시 >= trunc(add_months(sysdate, -1), 'mm') 
    and exists ( 
        select /*+ unnsest hash_sj */ 'x' 
        from 거래
        where 고객번호 = c.고객번호
            and 거래일시 >= trunc(sysdate, 'mm') )

서브쿼리를 Unnesting 해서 메인쿼리와 같은 레벨로 만들면 할수 있는 일

• 다양한 조인 메소드 선택가능
• 조인 순서 지정 사용

서브쿼리 Pushing

필터방식에서 서브쿼리는 순서가 정해지고 맨 마지막 단계에 처리된다.

하지만, push_subq 힌트를 사용하여 서브쿼리 필터링을 먼저 처리하게 해서 처리량을 줄일 수 있다.

push는 필터링 상태에서만 적용이 가능하다.

• Pushing 서브쿼리는 서브쿼리 필터링을 가능한 한 앞단계에서 처리하도록 강제하는 기능
• Unnesting 되지 않은 서브쿼리에서만 작동
• push_subq 힌트는 항상 no_unnest 힌트와 같이 기술하는 것이 올바른 사용법

뷰와 조인

최적화 단위가 쿼리 블록이므로 옵티마이저가 뷰 쿼리를 변환하지 않으면 뷰 쿼리 블록을 독립적으로 최적화한다.

전월 이후 가입 고객 필터 조건이 뷰 바깥에 있기 때문에 뷰 안에서는 전체 고객에 대한 데이터를 읽어야 한다.

merge 힌트

merge 힌트를 사용해서 메인쿼리와 뷰를 머징한다. 머징하지 않을땐 no_merge

)

merging을 했을때 단점

• Group By 하고 데이터를 출력할 수 있다.
• 부분범위 처리가 불가능하다.

조인조건 PushDown

11g 버전 이후 사용가능.

메인쿼리를 실행하면 조인조건절 값을 건건이 뷰 안으로 밀어넣는 기능.

)

스칼라 서브쿼리

스칼라 서브쿼리 특징

create or replace function GET_DNAME(p_deptno number) return varchar2
is
    l_dname dept.dname%TYPE;
begin
    select dname into l_dname from dept where deptno = p_deptno;
    return l_dname;
exception
    where others then
    return null;
end;

위처럼 GET_DNAME 함수를 만들었는데 이 함수를 사용하면 SELECT 쿼리를 메인쿼리 건수만큼 재귀적으로 반복함.

SELECT EMPNO, ENAME, SAL, HIREDATE, GET_DNAME(E.DEPTNO) AS DNAME
 FROM EMP E
WHERE SAL >= 2000

아래의 서브쿼리는 메인 쿼리 레코드마다 하나의 값만 반환한다. 즉, 재귀적으로 실행하는 구조가 아니다.

SELECT EMPNO, ENAME, SAL, HIREDATE
      , (SELECT D.DNAME, FROM DEPT D WHERE D.DEPTNO = E.DEPTNO) AS DNAME
  FROM EMP E
 WHERE SAL >= 2000

서브쿼리를 사용한 위 쿼리문 아래 Outer 조인문처럼 NL조인을 사용한다. DEPT와 조인에 실패하는 EMP 레코드는 DNAME에 NULL 값을 출력하는 점도 같다.

SELECT E.EMPNO, E.ENAME, E.SAL, E.HIREDATE, D.DNAME
FROM EMP E, DEPT D
WHERE D.DEPTNO = E.DEPTNO
    AND SAL >= 2000

스칼라 서브쿼리 캐싱 효과

스칼라 서브쿼리로 조인하면 오라클은 조인 횟수를 최소화하려고 입력 값과 출력 값을 내부 캐시(Query Execution Cashe)에 저장함.

조인할 때마다 일단 캐시에서 ‘입력 값’을 찾아보고, 찾으면 저장된 ‘출력 값’을 반환한다

캐시에서 찾지 못할 때만 조인을 수행하며, 결과는 버리지 않고 캐시에 저장해 둔다.

SELECT EMPNO, ENAME, SAL, HIREDATE,
    (SELECT D.DNAME --> 출력 값 : D.DNAME
        FROM DEPT D
        WHERE D.DEPTNO = E.EMPNO --> 입력 값 : E.EMPNO
    ) 
FROM EMP E
WHERE SAL >= 2000

(스칼라 서브쿼리 캐싱 = 필터 서브쿼리 캐싱)

메인쿼리 집합이 아무리 커도 조인할 데이터를 대부분 캐시에서 찾는다면, 조인 수행횟수를 최소화할 수 있어, 캐싱 메커니즘은 조인 성능을 높이는 데 큰 도움이 된다.

캐싱은 쿼리 단위로 이루어짐.

• 쿼리를 시작할 때 PGA 메모리에 공간을 할당
• 쿼리를 수행하면서 공간을 채움
• 쿼리를 마치는 순간 공간을 반환

캐싱을 위한 좋은 튜닝기법

SELECT-LIST에 사용한 함수는 메인쿼리 건수만큼 반복 수행되서 안좋음.

근데 스칼라 서브쿼리 덧씌우면 스칼라 서브쿼리 캐싱 효과로 성능이 좋아짐.

그래서 함수에 내장된 SELECT 쿼리도 캐싱효과로 덜 수행됨.

SELECT EMPNO, ENAME, SAL, HIREDATE, **(SELECT GET_DNAME(e.deptno) FROM dual)**
FROM EMP E
WHERE SAL >= 2000

스칼라 서브쿼리 캐싱 부작용

스칼라 서브쿼리 캐싱 효과는 입력 값의 종류가 소수여서 해시 충돌 가능성이 작을 때 좋음.
(입력값의 도메인이 적어야 한다?)

입력 값의 종류가 많으면 캐시를 매번 확인하는 비용 때매 오히려 꾸졌음. CPU랑 메모리를 와구와구 잡아묵음

두 개 이상의 값 반환

스칼라 서브쿼리에는 치명적인 제약이 하나 있다. 고거슨 바로 두 개 이상의 값을 반환할 수 없다는 제약이다.

즉, 쿼리를 아래와 같이 작성할 수 없다. 부분범위 처리 가능하다는 스칼라 서브쿼리의 장점을 이용하고 싶을 때 고민이 생기게 마련이다.

SELECT c.고객번호, c.고객명
     , (SELECT **AVG(거래금액), MIN(거래금액), MAX(거래금액)**
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
          AND 고객번호 = c.고객번호)
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

그렇다고 아래처럼 쓰면 같은 데이터 반복해서 읽는 비효율이 있음

SELECT c.고객번호, c.고객명
     , (SELECT MIN(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
     , (SELECT AVG(거래금액), MIN(거래금액), MAX(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
     , (SELECT MAX(거래금액)
        FROM 거래
        WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
           AND 고객번호 = c.고객번호)) 
FROM 고객 c
WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)

고래서 우짤것인가! 튜닝 장인들이 걸어온 역사속에서 대대손손 물려내려온 전통적인 비법이 있었으니

~

! 고것이 아래의 방법이렸다!

SELECT 고객번호, 고객명
     , TO_NUMBER(SUBSTR(거래금액, 1, 10)) 평균거래금액
     , TO_NUMBER(SUBSTR(거래금액, 11, 10)) 최소거래금액
     , TO_NUMBER(SUBSTR(거래금액, 21)) 최대거래금액
FROM (SELECT C.고객번호, C.고객명
             , (SELECT LPAD(AVG(거래금액), 10 || LPAD(MIN(거래금액), 10 || LPAD(MAX(거래금액), 10
                FROM 거래
                WHERE 거래일시 >= TRUNC(SYSDATE, ‘mm’
                   AND 고객번호 = c.고객번호) 거래금액
      FROM 고객 c
      WHERE c.가입일시 >= TRUNC(add_months(sysdate, -1), ‘mm’)
)

구하는 값들을 문자열로 모두 결합하고, 바깥쪽 액세스 쿼리에서 substr 함수로 다시 분리하는 방식이다.

type을 사용하는 방법도 있다는데 타이핑 치기 귀찮고, 사람들도 귀찮아서 잘 안쓴다하니 책을보시길

그런데 그냥 인라인뷰 쓰자. 요즘 11g 버전에서 조인조건 Pushdown 나와서 좋답니다.

스칼라 서브쿼리 Unnesting

스칼라 서브쿼리도 NL 방식으로 조인해서 캐싱 효과가 크지 않으면 랜덤 I/O 부담이 있음

그래서 다른 조인 방식을 선택하기 위해 스칼라 서브쿼리를 일반 조인문으로 변환하고 싶을 때가 있다.

특히, 병렬(Parallel) 쿼리에선 될 수 있으면 스칼라 서브쿼리를 사용하지 않아야 한다. 대량 데이터를 처리하는 병렬 쿼리는 해시 조인으로 처리해야 효과만점!

근데 요즘 세상이 좋아져가지구 12c 버전부터 스칼라 서브쿼리도 Unnesting이 가능하다 하는구마이.
세팅이 안 되어있어도 옵티마이저 힌트로 Unnesting를 유도할 수 있음.

힌트는 스칼라 서브쿼리에 unnest 사용!