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

• 데이터 모델 측면에서는 이상이 없으나, 불필요한 소트가 발생하도록 SQL을 작성하는 경우

UNION ALL VS UNION

1. union all : 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 부하가 없음
2. union : 중복을 제거하므로 정렬(SORT) 발생
3. union 쿼리

SQL> SELECT empno, job, mgr FROM emp WHERE deptno = 10
  2   UNION
  3  SELECT empno, job, mgr FROM emp WHERE deptno = 20
  4  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 3774834881

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

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("DEPTNO"=10)
   4 - filter("DEPTNO"=20)

1. union all 쿼리

SQL> SELECT empno, job, mgr FROM emp WHERE deptno = 10
  2   UNION ALL
  3  SELECT empno, job, mgr FROM emp WHERE deptno = 20
  4  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1301082189

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

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("DEPTNO"=10)
   3 - filter("DEPTNO"=20)

DISTINCT

1. DISTINCT : exists 서브쿼리로 대체함으로써 sort 연산을 없앨 수 있음
2. exists 서브쿼리의 특징 : 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행 마침
   아래 쿼리의 경우 "과금 테이블에 과금연월 + 지역 순으로 인덱스 구성하면 최적으로 수행됨
   ==> sort 발생시키지 않고 더 적은 블록을 읽고 수행시간도 짧음
3. 튜닝 전 쿼리

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)

1. 튜닝 후 쿼리

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)

사례

• App.의 과금연월 콤보박스에서 과금이 발생했던 연월만 보여지게 할 때, 아래의 방법을 활용하면 성능 저하는 예방 가능

SQL> CREATE TABLE day_tb AS
SELECT TO_CHAR( ymd , 'yyyymmdd' ) ymd ,
       TO_CHAR( ymd , 'yyyy' ) year ,
       TO_CHAR( ymd , 'mm' ) month ,
       TO_CHAR( ymd , 'dd' ) day ,
       TO_CHAR( ymd , 'dy' ) weekday ,
       TO_CHAR(next_day(ymd,'MONDAY')-7,'w') week_monthly,
       TO_NUMBER( TO_CHAR( NEXT_DAY( ymd , 'MONDAY' ) - 7 , 'ww' ) ) week_yearly
FROM   (
        SELECT TO_DATE( '19691231' , 'yyyymmdd' ) + ROWNUM ymd
        FROM   dual
        CONNECT BY LEVEL <= 365*100
       ) ;

Table created.

SQL> CREATE TABLE yyyymm_tb AS
SELECT SUBSTR( ymd , 1 , 6 ) yyyymm ,
       MIN( ymd ) first_day ,
       MAX( ymd ) LAST_DAY ,
       MIN( year ) year ,
       MIN( month ) month
FROM   day_tb
GROUP  BY SUBSTR( ymd , 1 , 6 ) ;
Table created.