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 | Front Page | 운영자 | 2011.02.16 | 114629 |
54 | 2. 인덱스 기본 원리 | balto | 2011.02.18 | 18844 |
53 | 1장. 인덱스 원리와 활용 | 휘휘 | 2011.02.21 | 6796 |
52 | 4. 테이블 Random 액세스 부하 | 휘휘 | 2011.02.26 | 6942 |
51 | 5. 테이블 Random 액세스 최소화 튜닝 | 휘휘 | 2011.02.26 | 9503 |
50 | 6. IOT, 클러스터 테이블 활용 | 오예스 | 2011.02.26 | 15597 |
49 | 7. 인덱스 스캔 효율 | 휘휘 | 2011.03.06 | 8449 |
48 | 9. 비트맵 인덱스 | 휘휘 | 2011.03.07 | 5014 |
47 | 8. 인덱스 설계 | AskZZang | 2011.03.09 | 5726 |
46 | 2. 소트 머지 조인 | 오예스 | 2011.03.21 | 7912 |
45 | 3. 해시 조인 | 휘휘 | 2011.03.21 | 6486 |
44 | 2장. 조인 원리와 활용 | 운영자 | 2011.03.23 | 3307 |
43 | 1. Nested Loops 조인 | 오라클잭 | 2011.03.23 | 23030 |
42 | 6. 스칼라 서브쿼리를 이용한 조인 | balto | 2011.03.27 | 18958 |
41 | 5. Outer 조인 | 휘휘 | 2011.03.28 | 17669 |
40 | 1. 인덱스 구조 | 운영자 | 2011.03.30 | 15951 |
39 | 4. 조인 순서의 중요성 | AskZZang | 2011.03.30 | 5328 |
38 | 7. 조인을 내포한 DML 튜닝 | 오예스 | 2011.04.04 | 9946 |
37 | 8. 고급 조인 테크닉-2 | 오라클잭 | 2011.04.05 | 12560 |
36 | 8. 고급 조인 테크닉-1 | 휘휘 | 2011.04.05 | 6460 |