메뉴 건너뛰기

bysql.net

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

2011.06.14 13:13

오예스 조회 수:4274

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)

이 게시물을
목록
목록
쓰기
태그