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
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
40 | 10. 실체화 뷰 쿼리로 재작성 | suspace | 2011.06.07 | 5531 |
39 | 9. Outer 조인을 Inner 조인으로 변환 | darkbeom | 2011.06.07 | 7834 |
38 | 8. 공통 표현식 제거 | darkbeom | 2011.06.07 | 5223 |
37 | 12. 기타 쿼리 변환 [3] | 실천하자 | 2011.06.03 | 6646 |
36 | 4. 조건절 Pushing | 실천하자 | 2011.05.31 | 7025 |
35 | 7. OR-Expansion | 멋진넘 | 2011.05.31 | 8720 |
34 | 6. 조인 제거 | 멋진넘 | 2011.05.30 | 4596 |
33 | 5. 조건절 이행 | 휘휘 | 2011.05.30 | 5409 |
32 | 2. 서브쿼리 Unnesting | darkbeom | 2011.05.16 | 19705 |
31 | 3. 뷰 Merging | 실천하자 | 2011.05.15 | 23390 |
30 | 7. 비용 | 휘휘 | 2011.05.03 | 6173 |
29 | 1. 쿼리 변환이란? | 실천하자 | 2011.05.02 | 4930 |
28 | 8. 통계정보 Ⅱ [1] | 멋진넘 | 2011.04.30 | 31102 |
27 | 5. 카디널리티 | suspace | 2011.04.26 | 5954 |
26 | 4. 통계정보 Ⅰ | darkbeom | 2011.04.26 | 18100 |
25 | 6. 히스토그램 | 실천하자 | 2011.04.25 | 10923 |
24 | 3. 옵티마이저의 한계 | 멋진넘 | 2011.04.19 | 7859 |
23 | 1. 옵티마이저 | 실천하자 | 2011.04.18 | 11213 |
22 | 2. 옵티마이저 행동에 영향을 미치는 요소 | 휘휘 | 2011.04.18 | 5096 |
21 | 8. 고급 조인 테크닉-2 | suspace | 2011.04.05 | 7018 |