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

UNION 과 UNION ALL

# union은 두 집합 간의 중복을 제거 하기 위해 sort unique 연산 수행

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


     EMPNO JOB              MGR
---------- --------- ----------
      7369 CLERK           7902
      7566 MANAGER         7839
      7782 MANAGER         7839
      7839 PRESIDENT
      7902 ANALYST         7566
      7934 CLERK           7782

6 개의 행이 선택되었습니다.


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

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

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

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


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         14  consistent gets
          0  physical reads
          0  redo size
        659  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          6  rows processed

# union all은 중복을 확인하지 않고 단순히 두 집합을 결합함. sort 부하는 없다.

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

     EMPNO JOB              MGR
---------- --------- ----------
      7782 MANAGER         7839
      7839 PRESIDENT
      7934 CLERK           7782
      7369 CLERK           7902
      7566 MANAGER         7839
      7902 ANALYST         7566

6 개의 행이 선택되었습니다.


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

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

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

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


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         15  consistent gets
          0  physical reads
          0  redo size
        665  bytes sent via SQL*Net to client
        400  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          6  rows processed

• 동일한 결과를 얻고 중복 가능성이 전혀 없을때는 union all 사용

DISTINCT 와 EXIST 서브쿼리

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

h5.# distinct 사용

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,586,208개 block을 읽고 35건 출력

h5.# distinct 대신 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)

• 82개 block을 읽고 35건 출력