소트가 발생하지 않도록 SQL 작성
- 데이터 모델 측면에서는 이상이 없으나, 불필요한 소트가 발생하도록 SQL을 작성하는 경우
UNION ALL VS UNION
- union all : 중복을 확인하지 않고 두 집합을 단순히 결합하므로 소트 부하가 없음
- union : 중복을 제거하므로 정렬(SORT) 발생
- union 쿼리
EMP_DEPTNO_IDX : DEPTNO
EXPLAIN PLAN FOR
SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 10
UNION
SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 20;
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------
Plan hash value: 3552824833
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 6 (67)| 00:00:01 |
| 1 | SORT UNIQUE | | 10 | 150 | 6 (67)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 75 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 75 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("DEPTNO"=10)
6 - access("DEPTNO"=20)
- union all 쿼리
EMP_DEPTNO_IDX : DEPTNO
EXPLAIN PLAN FOR
SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 10
UNION ALL
SELECT EMPNO, JOB, MGR FROM EMP WHERE DEPTNO = 20;
@/u01/app/oracle/product/10.2.0/db_1/rdbms/admin/utlxpls
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------
Plan hash value: 907018170
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 150 | 4 (50)| 00:00:01 |
| 1 | UNION-ALL | | | | | |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 75 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
| 4 | TABLE ACCESS BY INDEX ROWID| EMP | 5 | 75 | 2 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPTNO_IDX | 5 | | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("DEPTNO"=10)
5 - access("DEPTNO"=20)
DISTINCT
- DISTINCT : exists 서브쿼리로 대체함으로써 sort 연산을 없앨 수 있음
- exists 서브쿼리의 특징 : 메인 쿼리로부터 건건이 입력 받은 값에 대한 조건을 만족하는 첫 번째 레코드를 만나는 순간 true를 반환하고 서브쿼리 수행 마침
아래 쿼리의 경우 "과금 테이블에 과금연월 + 지역 순으로 인덱스 구성하면 최적으로 수행됨
==> sort 발생시키지 않고 더 적은 블록을 읽고 수행시간도 짧음 - 튜닝 전 쿼리
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)
- 튜닝 후 쿼리
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.