union을 사용하면 옵티마이저는 상단과 하단의 두 집합 간 중복을 제거하려고 sort unique를 수행한다.
SQL> EXPLAIN PLAN
2 FOR
3 SELECT empno ,
4 job ,
5 mgr
6 FROM emp
7 WHERE deptno = 10
8 UNION
9 SELECT empno ,
10 job ,
11 mgr
12 FROM emp
13 WHERE deptno = 20 ;
해석되었습니다.
SQL> @?\rdbms\admin\utlxpls
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 3774834881
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 152 | 8 (63)| 00:00:01 |
| 1 | SORT UNIQUE | | 8 | 152 | 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 | 5 | 95 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("DEPTNO"=10)
4 - filter("DEPTNO"=20)
17 개의 행이 선택되었습니다.
하지만 PK 컬럼인 empno를 select-list에 포함하므로 두 집합간에는 중복 가능성이 전혀 없으므로 union all을 사용해야 한다. union all은 중복을 확인하지 ?고 두 집합을 단순히 결합하므로 소트 부하가 없기 때문이다.(select-list에 empno가 없다면 10번과 20번 부서에 job, mgr이 같은 사원이 있을 수 있어 union과 union all은 의미가 달라진다.)
distinct를 사용하는 경우도 매우 흔한데, 대부분 exists 서브쿼리로 대체함으로써 소트 연산을 없앨 수 있다.
아래는 특정 지역(:reg)에서 특정월(:yyyymm) 이전에 과금이 발생했던 연월을 조회하는 쿼리다. 야간 배치 프로그램에서 발췌한 것으로서, 이 쿼리 결과를 이용해 다른 많은 작업을 수행한다.
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)
입력한 과금연월(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 서브쿼리의 가장 큰 특징은, 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행을 마친다는 점이다. 따라서 과금 테이블에 과금연월 + 지역 순으로 인덱스를 구성해 주기만 하면 가장 최적으로 수행될 수 있다.
그 결과 소트를 발생시키지 않았음을 물론 82개 블록만 읽고 0.01초 만에 수행을 완료했다. 물론 연월(yyyymm)을 관리하는 테이블이 따로 있을 때 적용 가능한 기법이다. 아래와 같이 일자 및 연월 테이블을 미리 생성해 두면 여러모로 활용가치가 높다.
SQL> CREATE TABLE 일자테이블 AS
2 SELECT TO_CHAR( ymd , 'yyyymmdd' ) ymd ,
3 TO_CHAR( ymd , 'yyyy' ) year ,
4 TO_CHAR( ymd , 'mm' ) month ,
5 TO_CHAR( ymd , 'dd' ) day ,
6 TO_CHAR( ymd , 'dy' ) weekday ,
7 TO_NUMBER( TO_CHAR( NEXT_DAY( ymd , '일' ) - 7 , 'ww' ) ) week_yearl
y
8 FROM (
9 SELECT TO_DATE( '19691231' , 'yyyymmdd' ) + ROWNUM ymd
10 FROM dual
11 CONNECT BY LEVEL <= 365*100
12 ) ;
테이블이 생성되었습니다.
SQL> CREATE TABLE 연월테이블 AS
2 SELECT SUBSTR( ymd , 1 , 6 ) yyyymm ,
3 MIN( ymd ) first_day ,
4 MAX( ymd ) LAST_DAY ,
5 MIN( year ) year ,
6 MIN( month ) month
7 FROM 일자테이블
8 GROUP BY SUBSTR( ymd , 1 , 6 ) ;
테이블이 생성되었습니다.
그림 5-8처럼 과금연월 콤보(combo) 박스에 과금이 발생했던 연월만 보여지도록 하고 싶을 때, 만약 위와 같은 튜닝 기법을 적용하지 않는다면 어떤 일이 벌어질까?
그림 5-8
조회 버튼을 누르기도 전, 화면이 열리는 단계에서 이미 성능 때문에 고생할 것이고, 아주 빈번히 조회되는 화면이라면 이 때문에 시스템은 몸살을 앓게 될 것이다.