6. Sort Area를 적게 사용하도록 SQL 작성
2011.06.14 22:13
(1) 소트를 완료하고 나서 데이터 가공하기
특정 기간에 발생한 주문상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 Sort Area를 적게 사용할까?
1번
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10) || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss') from 주문상품 where 주문일시 between :start and : end order by 상품번호; |
2번
select lpad(상품번호, 30) || lpad(상품명, 30) || lpad(고객ID, 10) || lpad(고객명, 20) || to_char(주문일시, 'yyyymmdd hh24:mi:ss') from ( select 삼품번호, 상품명, 고객ID, 고객명, 주문일시 from 주문상품 where 주문일시 between :start and : end order by 상품번호); |
1번 sql은 레코드당 105바이트(헤더 정보는 제외하고 데이터 값만)로 가공된 결과치를 Sort Area에 담는다.
반면 2번 SQL은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 1번 SQL에 비해 Sort Area를 훨씬 적게 사용한다.
실제 테스트해 보면 Sort Area사용량에 큰 차이가 나는 것을 확인 가능.
(2) TOP-N 쿼리
TOP-N 쿼리 형태로 작성하면 소트 연산(=값 비교) 횟수를 최소화함은 물론 Sort Area 사용량을 줄일 수 있다.
select * from (
select 거래일시, 체결건수, 체결수량, 거래대금
from 시간대별종목거래
where 종목코드 = 'KR123456'
and 거래일시 >= '20080304'
order by 거래일시
)
위 쿼리를 수행하는 시점에[종목코드 + 거래일시] 순으로 구성된 인덱스가 존재한다면 옵티마이져는 그 인덱스를 이용함으로 써 order by 연산을 대체할 수 있다.
TOP-N 쿼리의 소트 부하 경감 원리.
Top-N 쿼리 알고리즘에 대해 간단히 설명하면 rownu <= 10 이면 우선 10개 레코드를 담을 배열을 할당하고, 처음 읽은 10개 레코드를 정렬된 상태로 담는다.
(MAX 값)
8 | 15 | 98 | 136 | 189 | 190 | 233 | 264 | 287 | 337 |
(간단히 설명하기 위해 숫자로 표현.)
이후 읽는 레코드에 대해서는 맨 우측에 있는 값(=가장 큰 값) 과 비교해서 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도한다.
TOP-N 쿼리가 작동할 때와 TOP-N 쿼리가 작동하지 않을 때의 차이는 같은 양의 데이터를 읽고 정렬을 수행시, TOP-N 쿼리가 작동하면 메모리 내에서 정렬을 완료가 가능하다는 점이다.
(3) 분석함수에서의 Top-N 쿼리
window sort 시에도 rank()나 row_number()를 쓰면 Top-N 쿼리 알고리즘이 작동해 max()등 함수를 쓸때 보다 소트 부하를 경감시켜 준다.
마지막 이력 레코드를 찾는 쿼리
select id, seq, owner, object_name, object_type, created, status
from (select id, seq
, max(seq) over (partition by id) last_seq
, owner, object_name, object_type, created, status
from t)
where seq = last_seq;
Call Count CPU Time Elapsed Time Disk Query Current Rows
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Parse 1 0.000 0.009 0 49 0 0
Execute 1 0.000 0.000 0 0 0 0
Fetch 2 0.953 2.944 9632 4850 2 10
------- ------ -------- ------------ ---------- ---------- ---------- ----------
Total 4 0.953 2.953 9632 4899 2 10
Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SCOTT (ID=54)
Rows Row Source Operation
------- ---------------------------------------------------
0 STATEMENT
10 VIEW (cr=4850 pr=9632 pw=4807 time=917854 us)
535890 WINDOW SORT (cr=4850 pr=9632 pw=4807 time=1989604 us)
535890 TABLE ACCESS FULL T (cr=4850 pr=0 pw=0 time=13 us)
max() 함수 대신 rank()함수를 사용해 보자
select id, seq, owner, object_name, object_type, created, status from (select id, seq , rank() over (partition by id order by seq desc) rnum , owner, object_name, object_type, created, status from t) where rnum = 1; |
Call Count CPU Time Elapsed Time Disk Query Current Rows ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Parse 1 0.000 0.002 0 49 0 0 Execute 1 0.000 0.000 0 0 0 0 Fetch 2 1.016 1.016 2 4850 3 10 ------- ------ -------- ------------ ---------- ---------- ---------- ---------- Total 4 1.016 1.018 2 4899 3 10 Misses in library cache during parse: 1 Optimizer goal: ALL_ROWS Parsing user: SCOTT (ID=54) Rows Row Source Operation ------- --------------------------------------------------- 0 STATEMENT 10 VIEW (cr=4850 pr=2 pw=2 time=1015546 us) 24 WINDOW SORT PUSHED RANK (cr=4850 pr=2 pw=2 time=1015539 us) 535890 TABLE ACCESS FULL T (cr=4850 pr=0 pw=0 time=13 us) |
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
55 | 4장._쿼리_변환 | 휘휘 | 2011.09.07 | 2394 |
54 | 진행기록 | 운영자 | 2011.08.23 | 2383 |
53 | 2. 병렬 Order By와 Group By | 휘휘 | 2011.06.30 | 10127 |
52 | 3. 병렬 조인 | 오예스 | 2011.06.29 | 4969 |
51 | 4. PQ_DISTRIBUTE 힌트 | 휘휘 | 2011.06.27 | 15619 |
50 | 5. 병렬 처리에 관한 기타 상식 [1] | balto | 2011.06.26 | 28162 |
49 | 1. 테이블 파티셔닝 | 오라클잭 | 2011.06.21 | 28274 |
48 | 2. 파티션 Pruning | 휘휘 | 2011.06.19 | 11867 |
47 | 7. Sort Area 크기 조정 | 오예스 | 2011.06.16 | 5987 |
46 | 5. 인덱스를 이용한 소트 연산 대체 | AskZZang | 2011.06.16 | 4955 |
45 | 4. 소트가 발생하지 않도록 SQL 작성 | AskZZang | 2011.06.16 | 3109 |
44 | 3. 데이터 모델 측면에서의 검토 | AskZZang | 2011.06.16 | 2724 |
43 | 5장. 소트 튜닝 | balto | 2011.06.16 | 4283 |
» | 6. Sort Area를 적게 사용하도록 SQL 작성 | 오예스 | 2011.06.14 | 4209 |
41 | 1. 소트 수행 원리 | balto | 2011.06.12 | 8025 |
40 | 2. 소트를 발생시키는 오퍼레이션 | balto | 2011.06.12 | 4847 |
39 | 11. 집합 연산을 조인으로 변환 | 오라클잭 | 2011.06.08 | 4956 |
38 | 10. 실체화 뷰 쿼리로 재작성 | 오라클잭 | 2011.06.08 | 9953 |
37 | 12. 기타 쿼리 변환 | 휘휘 | 2011.06.06 | 3122 |
36 | 7. OR-Expansion | AskZZang | 2011.06.01 | 8334 |