h1.04.소트가 발생하지 않도록 SQL 작성

• 데이터 모델 측면에선 이상이 없는데, 불필요한 소트가 발생하도록 SQL을 작성하는 경우가 있다.
  예를 들어, 아래처럼 union을 사용하면 옵티마이저는 상단과 하단 두 집합 간 중복을 제거하려고 sort unique 연산을 수행한다.

select empno, job, mgr from emp where deptno = 10
union
select empno, job, mgr from emp where deptno = 20

--------------------------------------------------------------------------------------------
| Id  | Operation                     | Name           | E-Rows |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |        |       |       |          |
|   1 |  SORT UNIQUE                  |                |    115 |  9216 |  9216 | 8192  (0)|
|   2 |   UNION-ALL                   |                |        |       |       |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP            |    114 |       |       |          |
|*  4 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |    114 |       |       |          |
|   5 |    TABLE ACCESS BY INDEX ROWID| EMP            |      1 |       |       |          |
|*  6 |     INDEX RANGE SCAN          | EMP_DEPTNO_IDX |      1 |       |       |          |
--------------------------------------------------------------------------------------------

하지만 PK 컬럼인 empno를 select-list에 포함하므로 두 집합간에는 중복 가능성이 전혀 없다.
따라서 union all을 사용해야 한다.

• distinct를 사용하는 경우도 매우 흔한데, 대부분 exists 서브쿼리로 대체함으로써 소트 연산을 없앨 수 있다.
  아래는 특정 지역에서 특정월 이전에 과금이 발생했던 연월을 조회하는 쿼리다.
  야간 배치 프로그램에서 발췌한 것으로서, 이 쿼리 결과를 이용해 다른 많은 작업을 수행한다.

select distinct 과금연월
from  과금
where 과금연월 <= :yyyymm
and   지역 like :reg || '%'

call    count       cpu   elapsed      disk     query   current      rows
------ ------  -------- --------- --------- --------- --------- ---------
Parse       1      0.00      0.00         0         0         0         0
Execute     1      0.00      0.00         0         0         0         0
Fetch       4     27.65     98.38     32648   1586208         0        35
------ ------  -------- --------- --------- --------- --------- ---------
total       6     27.65     98.38     32648   1586208         0        35

Rows     Row Source Operation
-------  -----------------------------------------------
     35 HASH UNIQUE (cr=1586208 pr=32648 pw=0 time=98704640 us)
9845517  PARTITION RANGE ITERATOR PARTITION: 1 KEY (cr=1586208 pr=32648 ...)
9845517   TABLE ACCESS FULL 과금 (cr=158608  pw=0 time=70155864 us)

입력한 과금연월 이전에 발생한 과금 데이터를 모두 스캔하는 동안 1,586,208개 블록을 읽었고, 무려 1000만 건에 가까운 레코드에서 중복 값을
제거하고 고작 35건을 출력하였다. 매우 비효율적인 방식으로 수행되었고, 쿼리 소요시간은 1분 38초다.
각 월별로 과금이 발생한 적이 있는지 여부만 확인하면 되므로 쿼리를 아래처럼 바꿀 수 있다.
소량의 데이터만을 갖는 연월테이블을 먼저 드리이빙해 과금 테이블을 exists 서브쿼리로 필터링 하는 방식이다.

select 연월
from   연월테이블 a
where  연월 <= :yyyymm
and    exists (
       select 'x'
       from   과금
       where  과금연월 = a.연월
       and    지역 like :reg || '%'
       )

call    count       cpu   elapsed      disk     query   current      rows
------ ------  -------- --------- --------- --------- --------- ---------
Parse       1      0.00      0.00         0         0         0         0
Execute     1      0.00      0.00         0         0         0         0
Fetch       4      0.00      0.01         0        82         0        35
------ ------  -------- --------- --------- --------- --------- ---------
total       6      0.00      0.01         0        82         0        35

Rows  Row Source Operation
----  -----------------------------------------------
  35  NESTED LOOPS SEMI (cr=82 pr=0 time=19568 us)
  36   TABLE ACESS FULL 연월테이블 (cr=6 pr=0 pw=0 time=557 us)
  35   PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=76 pr=0 pw=0 time=853 us)
  35    INDEX RANGE SCAN 과금_N1 (cr=76 pr=0 pw=0 time=683 us)