메뉴 건너뛰기

bysql.net

6. Sort Area를 적게 사용하도록 SQL 작성

2011.06.14 22:13

오예스 조회 수:4205

06. Sort Area를 적게 사용하도록 sql 작성.

(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)

이 게시물을
목록
목록
번호 제목 글쓴이 날짜 조회 수
35 1. 옵티마이저 file 휘휘 2011.04.18 6064
34 3. 옵티마이저의 한계 - P 휘휘 2011.04.18 3699
33 2. 옵티마이저 행동에 영향을 미치는 요소 balto 2011.04.18 6160
32 3. 옵티마이저의 한계 휘휘 2011.04.19 6698
31 4. 통계정보 Ⅰ file 토시리 2011.04.25 16013
30 6. 히스토그램 오예스 2011.04.25 17389
29 5. 카디널리티 오라클잭 2011.04.27 12918
28 7. 비용 file balto 2011.05.02 4997
27 8. 통계정보 Ⅱ AskZZang 2011.05.04 5913
26 1. 쿼리 변환이란? 운영자 2011.05.16 6258
25 3. 뷰 Merging 오라클잭 2011.05.17 6082
24 2. 서브쿼리 Unnesting 토시리 2011.05.18 2079
23 5. 조건절 이행 file balto 2011.05.30 5465
22 4. 조건절 Pushing 오예스 2011.05.31 17482
21 6. 조인 제거 AskZZang 2011.06.01 5440
20 7. OR-Expansion AskZZang 2011.06.01 8327
19 12. 기타 쿼리 변환 휘휘 2011.06.06 3122
18 10. 실체화 뷰 쿼리로 재작성 오라클잭 2011.06.08 9953
17 11. 집합 연산을 조인으로 변환 오라클잭 2011.06.08 4955
16 2. 소트를 발생시키는 오퍼레이션 file balto 2011.06.12 4847
쓰기
태그