소트 영역을 적게 사용하도록 SQL 작성 - SQL전문가 가이드 p.695~p.706

목적: 소트 영역 크기를 늘리는 방법도 있지만 그전에 소트 영역을 적게 사용할 방법부터 찾는 것이 순서다.

 

1. 소트 완료 후 데이터 가공

- '1차 단계'는 레코드당 105(=30+30+10+20+15) 바이트(헤어 정보는 제외하고 데이터 값만)로 가공된 결과치를 소트 영역에 담는다. 반면 '2차 단계'는 가공되지 않는 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 '1차 단계'에 비해 소트 영역을 휠씬 적게 사용한다. 

1) 1차 단계

SELECT LPAD(상품번호, 30) || LPAD(상품명,30) || LPAD(고객ID, 10)

|| LPAD(고객명,20) || LPAD(주문일시, 'YYYYMMDD HH24:MI:SS')

FROM 주문상품

WHERE 주문일시 BETWEEN :start ADN :end

ORDER BY 상품번호

;

2) 2차 단계

SELECT LPAD(상품번호, 30) || LPAD(상품명,30) || LPAD(고객ID, 10)

|| LPAD(고객명,20) || LPAD(주문일시, 'YYYYMMDD HH24:MI:SS')

FROM

(

SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시

FROM 주문상품

WHERE 주문일시 BETWEEN :start ADN :end

ORDER BY 상품번호

)

;

 

 

2. Top-N 쿼리

- ROWNUM 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 빠른 수행 속도를 낼 수 있다. 실행계획에 'COUNT (STOPKEY)'가 그것을 의미한다.

- Top-N 쿼리 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻게 된다.

 

SELECT * FROM (

SELECT 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 ='A0T100M'

AND 거래일시 >= '200120301'

ORDER BY 거래일시

 )

 WHERE ROWNUM <= 10

 ;

 

*Top-N 쿼리 알고리즘이 작동하지 못하는 경우

 목표: 한 페이지에 10개씩 출력한다고 가정하고, 10 페이지를 출력하는 예시다.

 1) Top-N 쿼리가 작동하는 예제

  SELECT *

  FROM (SELECT ROWNUM NO, 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 'A0T100M'

AND 거래일시 >= '20080304'

ORDER BY 거래일시

)

WHERE ROWNUM <= 100

       )

 WHERE NO BETWEEN 91 AND 100

 ;

 

2) Top-N 쿼리가 작동하지 않는 예제: WHERE절 하나를 줄이고도 같은 결과집합을 얻을 수 있어 더 효과적인 것처럼 보이지만 그 순간 부터 Top-N 쿼리 알고리즘은 작동하지 않는다.

 

SELECT *

FROM (SELECT ROWNUM NO, 거래일시, 체결건수, 체결수량, 거래대금

FROM 시간별종목거래

WHERE 종목코드 'A0T100M'

AND 거래일시 >= '20080304'

ORDER BY 거래일시

)

-- WHERE ROWNUM <= 100 -- 주석처리

)

WHERE NO BETWEEN 91 AND 100

;

 

3) 윈도우 함수에서의 Top-N 쿼리

- 윈도우 함수를 사용할 때도 max() 함수보다 rank()나 row_number() 함수를 사용하는 것이 유리한데, 이것 역시 Top-N 쿼리 알고리즘이 작동하기 때문이다.

  

 (가) Top-N 쿼리 작동(X)

 SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급

     FROM (SELECT 고객ID, 변경순번

, MAX(변경순번) OVER (PARTITION BY 고객ID) 마지막변경순번

, 전화번호, 주소, 자녀수, 직업, 고객등급

          FROM 고객변경이력) A

 WHERE 변경순번 = 마지막변경순번

  ; 

 

 (나) Top-N 쿼리 작동(O)

 SELECT 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급

 FROM (SELECT 고객ID, 변경순번

 , RANK() OVER (PARTITION BY 고객ID ORDER BY 변경순번) RNUM

 , 전화번호, 주소, 자녀수, 직업, 고객등급

 FROM 고객변경이력) A

 WHERE RNUM

  ;