4. 소트가 발생하지 않도록 SQL 작성
2011.06.13 02:17
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 ) ;
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
20 | 5. 인덱스를 이용한 소트 연산 대체 | 멋진넘 | 2011.06.13 | 3360 |
19 | 6. IOT, 클러스터 테이블 활용 | 휘휘 | 2011.02.26 | 2763 |
18 | 6. 히스토그램 | 실천하자 | 2011.04.25 | 10950 |
17 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4605 |
16 | 6. Sort Area를 적게 사용하도록 SQL 작성 | 실천하자 | 2011.06.14 | 8874 |
15 | 7. 인덱스 스캔 효율 [1] | 휘휘 | 2011.03.09 | 16954 |
14 | 7. 비용 | 휘휘 | 2011.05.03 | 6181 |
13 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 8726 |
12 | 7. Sort Area 크기 조정 | 실천하자 | 2011.06.14 | 15108 |
11 | 8. 인덱스 설계 | 멋진넘 | 2011.03.07 | 8354 |
10 | 7. 조인을 내포한 DML 튜닝 | 실천하자 | 2011.04.04 | 7187 |
9 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.30 | 31443 |
8 | 8. 공통 표현식 제거 | darkbeom | 2011.06.07 | 5230 |
7 | 9. 비트맵 인덱스 | 실천하자 | 2011.03.06 | 12373 |
6 | 8. 고급 조인 테크닉-1 [1] | darkbeom | 2011.04.04 | 13292 |
5 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.07 | 7852 |
4 | 8. 고급 조인 테크닉-2 | suspace | 2011.04.05 | 7027 |
3 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5540 |
2 | 11. 집합 연산을 조인으로 변환 | suspace | 2011.06.08 | 6002 |
1 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.03 | 6658 |