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

▶UNION 사용시 SORT UNIQUE 발생
 UNION 의 사용목적 : 상단과 하단의 두 집합 간의 중복을 제거시 사용
 SORT 제거방안 : 상황에 따라 UNION ALL로 대체 가능할 경우 대체함
 
 SELECT EMPNO, JOB, MGR
   FROM EMP
  WHERE DEPTNO = 10
  UNION
 SELECT EMPNO, JOB, MGR
   FROM EMP
  WHERE DEPTNO = 20;

 ----------------------------------------------------------------------------
 | Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------
 |   0 | SELECT STATEMENT    |      |    10 |   190 |    10  (60)| 00:00:01 |
 |   1 |  SORT UNIQUE        |      |    10 |   190 |    10  (60)| 00:00:01 |
 |   2 |   UNION-ALL         |      |       |       |            |          |
 |*  3 |    TABLE ACCESS FULL| EMP  |     5 |    95 |     4   (0)| 00:00:01 |
 |*  4 |    TABLE ACCESS FULL| EMP  |     5 |    95 |     4   (0)| 00:00:01 |
 ----------------------------------------------------------------------------

 Predicate Information (identified by operation id):
 ---------------------------------------------------
    3 - filter("DEPTNO"=10)
    4 - filter("DEPTNO"=20)

 위의 SQL은 PK 컬럼인 EMPNO를 SELECT-LIST에 포함하므로 두 집합간에는 중복 가능성이 전혀 없으므로
 UNION 대신 UNION ALL을 사용해야 한다. UNION ALL은 중복을 확인하지 않고 두 집합을 단순히 결합하므로
 SORT 부하가 없기 때문이다.
 
 만약, SELECT-LIST에 EMPNO가 없다면 10번과 20번 부서에 JOB, MGR이 같은 사원이 있을 수 있어
 UNION과 UNION ALL은 의미가 달라진다.

▶DISTINCT 사용시 SORT UNIQUE 발생
 DISTINCT 의 사용목적 : 중복 리스트의 제거시 사용
 SORT 제거방안 : 대부분 EXISTS 서브쿼리로 대체함으로써 소트 연산을 없앨 수 있다.
 (교재내용 - SCRIPT가 없구. 환경 구성하기기 힘들어 교재내용을 SCAN했다가.. 실패해서 직접 스크립트 작성)
 
 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=1586208 pr=32648 pw=0 time=70155864 us)

 위의 SQL은 특정 지역(:reg)에서 특정월(:yyyymm) 이전에 과금이 발생했던 연월을 조회하는 쿼리임
 야간 배치 프로그램에서 사용하며, 이 쿼리 결과를 이용해 다른 많은 작업을 수행한다고 함
 
 총 1,586,208개 블록을 읽었고 1,000만 건에 가까운 레코드에서 중복 값을 제거하고 최종적으로 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=32648 pw=0 time=19568 us)                     
      36   TABLE ACCESS 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)                  

 EXISTS 서브쿼리는 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째
 ROW를 만나는 순간 TRUE를 반환하고 서브쿼리 수행을 마친다는 점이다.
 따라서 과금 테이블에 [과금연월 + 지역] 순으로 인덱스를 구성해 주기만 하면 가장 최적으로
 수행되며 그 결과 소트를 발생시키지 않고 82개 블록만 읽고 0.01초 만에 수행을 완료함

▶기타 활용방안(날짜와 관련해서...많이 사용되는 형태 - 과거 COPY_T 확장)
 CREATE TABLE YHN_일자테이블
 AS
 SELECT TO_CHAR(YMD, 'YYYYMMDD') AS YMD     -- 년월일
      , TO_CHAR(YMD, 'YYYY'    ) AS YEAR    -- 년
      , TO_CHAR(YMD, 'MM'      ) AS MONTH   -- 월
      , TO_CHAR(YMD, 'DD'      ) AS DAY     -- 일
      , TO_CHAR(YMD, 'Q'       ) AS QTR     -- 분기
      , TO_CHAR(YMD, 'DY'      ) AS WEEKDAY -- 요일
      , TO_NUMBER(TO_CHAR(NEXT_DAY(YMD , '일') - 7, 'WW')) AS WEEK_YEARLY -- 주
   FROM (
        SELECT TO_DATE( '19691231' , 'YYYYMMDD' ) + ROWNUM YMD
          FROM DUAL
        CONNECT BY LEVEL <= 365
        ) ;
 
 CREATE TABLE YHN_연월테이블
 AS
 SELECT SUBSTR(YMD , 1 , 6 ) AS YYYYMM
      , MIN(YMD  )           AS FIRST_DAY
      , MAX(YMD  )           AS LAST_DAY
      , MIN(YEAR )           AS YEAR
      , MIN(MONTH)           AS MONTH
      , MIN(QTR  )           AS QTR
   FROM YHN_일자테이블
  GROUP BY SUBSTR( ymd , 1 , 6 ) ;