[SQLP] 스칼라 서브 쿼리, 고급 조인 기법

스칼라 서브 쿼리

스칼라 서브 쿼리의 캐싱 효과

• 스칼라 서브 쿼리를 사용하면 내부적으로 캐시를 생성하고, 여기에 서브 쿼리에 대한 입력 값과 출력 값을 저장한다.
• 메인 쿼리로부터 같은 입력 값이 들어오면 서브쿼리를 실행하는 대신 캐시된 출력값을 리턴한다.
• 스칼라 서브 쿼리를 수행할 때 빠르게 입,출력 값을 찾기 위해 해싱 알고리즘을 사용하며, 입력 값의 카티널리티가 높을수록 효과가 좋다.

고급 조인 기법

부등호 조인과 Between 조인은 생략함

1. 인라인 뷰 활용

요약해서 말하면 필터링 할 것을 미리 걸러주고 목적을 수행하면 된다는 것이다.

아래는 집계함수 사용시 인라인 뷰 사용 전과 후 예시이다,

사용 전

select 
    min(t2.상품명) 상품명, sum(t1.판매수량) 판매수량, sum(t1.판매금액) 판매금액 
from 일별상품판매 t1, 상품 t2 
where t1.판매일자 between '20090101' and '20091231' 
    and t1.상품코드 = t2.상품코드 
group by t2.상품코드 


Call    | Count | CPU Time | Elapsed Time | Disk | Query  | Current | Rows
--------|-------|----------|--------------|------|--------|---------|-----
Parse   | 1     | 0.000    | 0.000        | 0    | 0      | 0       | 0
Execute | 1     | 0.000    | 0.000        | 0    | 0      | 0       | 0
Fetch   | 101   | 5.109    | 13.805       | 52744| 782160 | 0       | 1000
--------|-------|----------|--------------|------|--------|---------|-----
Total   | 103   | 5.109    | 13.805       | 52744| 782160 | 0       | 1000

Rows | Row Source Operation
-----|---------------------------------------------------
1000 | SORT GROUP BY (cr=782160 pr=52744 pw=0 time=13804391 us)
365000 | NESTED LOOPS (cr=782160 pr=52744 pw=0 time=2734163731004 us)
365000 |   TABLE ACCESS FULL 일별상품판매 (cr=52158 pr=51800 pw=0 time=456175026878 us)
365000 |   TABLE ACCESS BY INDEX ROWID 상품 (cr=730002 pr=944 pw=0 time=872397482545 us)
365000 |     INDEX UNIQUE SCAN 상품_PK (cr=365002 pr=4 pw=0 time=416615350685 us)

사용 후

SELECT 
    t2.상품명, t1.판매수량, t1.판매금액 
FROM (SELECT 상품코드, SUM(판매수량) 판매수량, SUM(판매금액) 판매금액 
      FROM 일별상품판매 
      WHERE 판매일자 BETWEEN '20090101' AND '20091231' 
      GROUP BY 상품코드) t1, 
     상품 t2 
WHERE t1.상품코드 = t2.상품코드

성능 통계
----------
Call     | Count | CPU Time | Elapsed Time | Disk  | Query | Current | Rows
---------|-------|----------|--------------|-------|-------|---------|-----
Parse    | 1     | 0.000    | 0.000        | 0     | 0     | 0       | 0
Execute  | 1     | 0.000    | 0.000        | 0     | 0     | 0       | 0
Fetch    | 101   | 1.422    | 5.540        | 51339 | 54259 | 0       | 1000
---------|-------|----------|--------------|-------|-------|---------|-----
Total    | 103   | 1.422    | 5.540        | 51339 | 54259 | 0       | 1000


Rows | Row Source Operation
-----|---------------------------------------------------
1000 | NESTED LOOPS (cr=54259 pr=51339 pw=0 time=5540320 us)
1000 |   VIEW (cr=52158 pr=51339 pw=0 time=5531294 us)
1000 |   SORT GROUP BY (cr=52158 pr=51339 pw=0 time=5530293 us)
365000 |   TABLE ACCESS FULL 일별상품판매 (cr=52158 pr=51339 pw=0 time=2920041 us)
1000 |   TABLE ACCESS BY INDEX ROWID 상품 (cr=2101 pr=0 pw=0 time=8337 us)
1000 |   INDEX UNIQUE SCAN 상품_PK (cr=1101 pr=0 pw=0 time=3747 us)

2. 배타적 관계의 조인

• 어떤 엔티티가 2개 이상의 엔티티와 합집합과 관계를 갖는 것을 상호배타적이라고 한다.
• 아래와 같은 예시인데, 개통신청도 작업지시를 1:N의 관계를 가질 수 있고, 장애접수도 마찬가지다.
• 그럼 아래와 같은 상황일 때 설계를 2가지 방식으로 할 수 있다.
  1. 개통신청 pk, 장애접수 pk의 컬럼을 따로 2개 두고 레코드별로 둘 중 하나의 컬럼만 입력
  2. 작업구분, 접수번호 컬럼을 두는 방식

1번 방식일 때 쿼리

select 
    /*+ ordered use_nl(b) use_nl(c) */ 
    a.작업일련번호, a.작업자ID, a.작업상태코드 , nvl(b.고객번호, c.고객번호) 고객번호 , nvl(b.주소, c.주소) 주소,...
from 작업지시 a, 개통신청 b, 장애접수 c 
where a.방문예정일시 = :방문예정일시 
    and b.개통신청번호(+) = a.개통신청번호 
    and c.장애접수번호(+) = a.장애접수번호

2번 방식일 때 쿼리

select 
    x.작업일련번호, x.작업자ID, x.작업상태코드, y.고객번호, y.주소, ... 
from 작업지시 x, 개통신청 y 
where x.방문예정일시 = :방문예정일시 
    and x.작업구분 = '1' 
    and y.개통신청번호 = x.접수번호 
union all 
select 
    x.작업일련번호, x.작업자ID, x.작업상태코드, y.고객번호, y.주소, ... 
from 작업지시 x, 장애접수 y 
where x.방문예정일시 = :방문예정일시 
    and x.작업구분 = '2' 
    and y.장애접수번호 = x.접수번호

• 해당 방식에서는 인덱스에 관해 여러 고민 상황이 생긴다.
• 만약 인덱스가 [작업구분 + 방문예정일시] 이면 읽는 범위에 중복이 없을 것이다.
• 근데 [방문예정일시 + 작업구분] 일 경우 중복 읽기 발생할 가능성이 높다.
• 그래서 아래와 같이 비효율을 최소화할 수 있다.

select 
    /*+ ordered use_nl(b) use_nl(c) */ a.작업일련번호, a.작업자ID, a.작업상태코드 , nvl(b.고객번호, c.고객번호) 고객번호 , nvl(b.주소, c.주소) 주소, ... 
from 작업지시 a, 개통신청 b, 장애접수 c 
where a.방문예정일시 = :방문예정일시 
    and b.개통신청번호(+) = decode(a.작업구분, '1', a.접수번호) 
    and c.장애접수번호(+) = decode(a.작업구분, '2', a.접수번호)

3. ROWID 활용

• 예를 들어 아래 쿼리는 서비스 만료일 보다 앞선 변경일자 중 가장 마지막 레코드를 찾는 것이다.
• 실행계획을 보면 고객별 연체이력이라는 테이블을 두 번 엑세스 하고 있다.
  • 서브쿼리의 내용의 인덱스를 거꾸로 스캔하면서 가장 큰 값 하나만을 찾는 실행계획을 수립

SELECT 
    a.고객명, 
    a.거주지역, 
    a.주소, 
    a.연락처, 
    b.연체금액, 
    b.연체개월수 
FROM 
    고객 a, 
    고객별연체이력 b 
WHERE 
    a.가입회사 = 'C70' 
    AND b.고객번호 = a.고객번호 
    AND b.변경일자 = (
        SELECT MAX(변경일자) 
        FROM 고객별연체이력 
        WHERE 고객번호 = a.고객번호 
              AND 변경일자 <= a.서비스만료일
    )

Execution Plan
-------------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=845 Card=10 Bytes=600)
1 0   TABLE ACCESS (BY INDEX ROWID) OF '고객별연체이력' (Cost=2 Card=1 Bytes=19)
2 1     NESTED LOOPS (Cost=845 Card=10 Bytes=600)
3 2       TABLE ACCESS (BY INDEX ROWID) OF '고객' (Cost=825 Card=10 Bytes=410)
4 3         INDEX (RANGE SCAN) OF '고객_IDX01' (NON-UNIQUE) (Cost=25 Card=10)
5 2       INDEX (RANGE SCAN) OF '고객별연체이력_IDX01' (NON-UNIQUE) (Cost=1 Card=1)
6 5         SORT (AGGREGATE) (Card=1 Bytes=13)
7 6           FIRST ROW (Cost=2 Card=5K Bytes=63K)
8 7             INDEX (RANGE SCAN (MIN/MAX)) OF '고객별연체이력_IDX01' (NON-UNIQUE)

• 좀 더 블록 엑세스를 줄여야 하는 상황이면 ROWID를 이용해 조인하는 다음과 같은 튜닝 기법을 적용해볼 수 있다.
  • SQL Server에서는 rowid로 테이블 엑세스하는 방식을 지원하지 않는다고 한다.
• 아래는 a,b 테이블 굳이 직접 조인하지 않아도 되는 것을 보여준다.
  • a 테이블의 고객번호로 서브 쿼리의 고객별연체이력 테이블을 조인하고 그를 통해서 rowid 를 얻고 그를 통해서 바로 테이블 엑세스를 한다.
  • 물론 위 방법은 굳이 안써도 first row(min/max) 알고리즘이 작동한다면 충분히 성능을 낸다고 한다. 그러니 정말 해결이 되지 않을 때만 적용해보자

SELECT /*+ ORDERED USE_NL(b) ROWID(b) */ 
    a.고객명, 
    a.거주지역, 
    a.주소, 
    a.연락처, 
    b.연체금액, 
    b.연체개월수 
FROM 
    고객 a, 
    고객별연체이력 b 
WHERE 
    a.가입회사 = 'C70' 
    AND b.rowid = (
        SELECT /*+ INDEX(c 고객별연체이력_idx01) */ 
            rowid 
        FROM 
            고객별연체이력 c 
        WHERE 
            c.고객번호 = a.고객번호 
            AND c.변경일자 <= a.서비스만료일 
            AND rownum <= 1
    )

Execution Plan
-------------------------------------------------------------
0   SELECT STATEMENT Optimizer=CHOOSE (Cost=835 Card=100K Bytes=5M)
1 0   NESTED LOOPS (Cost=835 Card=100K Bytes=5M)
2 1     TABLE ACCESS (BY INDEX ROWID) OF '고객' (Cost=825 Card=10 Bytes=410)
3 2         INDEX (RANGE SCAN) OF '고객_IDX01' (NON-UNIQUE) (Cost=25 Card=10)
4 1     TABLE ACCESS (BY USER ROWID) OF '고객별연체이력' (Cost=1 Card=10K Bytes=137K)
5 4         COUNT (STOPKEY)
6 5             INDEX (RANGE SCAN) OF '고객별연체이력_IDX01' (NON-UNIQUE) (Cost=2 Card=5K)