SORT 영역을 적게 사용하도록 SQL 작성

소트 연산이 불가피하다면 메모리 내에서 처리되게 하려고 노력해야 한다.

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

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

특정 기간에 발생한 주문상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 소트 영역을 더 적게 사용할까?

[ 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(=30+30+10+20+15) 바이트(헤더 정보는 제외하고 데이터 값만)로 가공된 결과치를 소트 영역에 담는다. 반면 2번 SQL은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 1번 SQL에 비해 소트 영역을 훨씬 적게 사용한다. 실제 테스트해 보면 소트 영역 사용량에 큰 차이가 나는 것을 관찰할 수 있다. 나. Top-N 쿼리 Top-N 쿼리 형태로 작성하면 소트 연산(=값 비교) 횟수와 소트 영역 사용량을 최소화할 수 있다. 우선 Top-N 쿼리 작성법부터 살펴보자. SQL Server나 Sybase는 Top-N 쿼리를 아래와 같이 손쉽게 작성할 수 있다.

select top 10 
	거래일시, 체결건수, 체결수량, 거래대금 
from 시간별종목거래 
where 종목코드 = 'KR123456' and 거래일시 >= '20080304'

IBM DB2에서도 아래와 같이 쉽게 작성할 수 있다.

select 거래일시, 체결건수, 체결수량, 거래대금 
from 시간별종목거래 
where 종목코드 = 'KR123456' and 거래일시 >= '20080304' 
order by 거래일시 fetch first 10 rows only

Oracle에서는 아래 처럼 인라인 뷰로 한번 감싸야 하는 불편함이 있다.

select * 
from ( 
	select 거래일시, 체결건수, 체결수량, 거래대금 
    from 시간별종목거래 
    where 종목코드 = 'KR123456' and 거래일시 >= '20080304' 
    order by 거래일시 ) 
where rownum = 10

위 쿼리를 수행하는 시점에 [종목코드 + 거래일시] 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를 이용함으로써 order by 연산을 대체할 수 있다. 아래 실행계획에서 ‘SORT ORDER BY’ 오퍼레이션이 나타나지 않은 것을 확인하기 바란다.

Execution Plan 
------------------------------------------------------------- 
0 SELECT STATEMENT Optimizer=ALL_ROWS 
1 0 COUNT (STOPKEY) 
2 1 VIEW 
3 2 TABLE ACCESS (BY INDEX ROWID) OF '시간별종목거래' (TABLE) 
4 3 INDEX (RANGE SCAN) OF ' 시간별종목거래_PK' (INDEX (UNIQUE))

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

■ Top-N 쿼리의 소트 부하 경감 원리

[종목코드 + 거래일시] 순으로 구성된 인덱스가 없을 때는 어떤가?

종목코드만을 선두로 갖는 다른 인덱스를 사용하거나 Full Table Scan 방식으로 처리할 텐데, 이때는 정렬 작업이 불가피하다. 하지만 Top-N 쿼리 알고리즘이 작동해 소트 영역을 최소한으로 사용하는 효과를 얻게 된다.

예를 들어 Top 10 (rownum < = 10)이면, [그림 Ⅲ-5-8]처럼 우선 10개 레코드를 담을 배열을 할당하고 처음 읽은 10개 레코드를 정렬된 상태로 담는다. (위에서 예시한 쿼리는 거래일시 순으로 정렬하고 있지만, 설명을 단순화하려고 숫자로 표현하였다.)

이후 읽는 레코드에 대해서는 맨 우측에 있는 값(=가장 큰 값)과 비교해서 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도한다. 물론 맨 우측에 있던 값은 버린다. 이 방식으로 처리하면 전체 레코드를 정렬하지 않고도 오름차순(ASC)으로 최소값을 갖는 10개 레코드를 정확히 찾아낼 수 있다. 이것이 Top-N 쿼리가 소트 연산 횟수와 소트 영역 사용량을 줄여주는 원리다.

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

앞에서, 앞쪽 일부 페이지만 주로 조회할 때의 가장 표준적인 페이징 처리 구현 방식은 아래와 같다고 설명하였다. 한 페이지에 10개씩 출력한다고 가정하고, 10 페이지를 출력하는 예시다. (설명의 편의를 위해 바인드 변수 대신 상수를 사용하였다.)

select * 
from (
	select rownum no, 거래일시, 체결건수, 체결수량, 거래대금 
    from (
    	select 거래일시, 체결건수, 체결수량, 거래대금 
        from 시간별종목거래 
        where 종목코드 = 'KR123456' and 거래일시 >= '20080304' 
        order by 거래일시 
        ) 
    where rownum < = 100 
    ) 
where no between 91 and 100

[종목코드 + 거래일시] 순으로 구성된 인덱스가 있으면 최적이겠지만, 없더라도 TOP-N 쿼리 알고리즘이 작동해 소트 부하만큼은 최소화할 수 있다고 설명하였다. 쿼리를 아래와 같이 작성하면 where절 하나를 줄이고도 같은 결과집합을 얻을 수 있어 더 효과적인 것처럼 보인다. 하지만 그 순간부터 Top-N 쿼리 알고리즘은 작동하지 않는다.

select * 
from (
	select rownum no, 거래일시, 체결건수, 체결수량, 거래대금 
    from (
    	select 거래일시, 체결건수, 체결수량, 거래대금 
        from 시간별종목거래 
        where 종목코드 = 'KR123456' and 거래일시 >= '20080304' 
        order by 거래일시 
        ) 
    ) 
where no between 91 and 100

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

윈도우 함수를 이용해 마지막 이력 레코드를 찾는 경우를 보자. 아래는 max() 함수를 사용하는 SQL이다.

select 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급 
from (
	select 고객ID, 변경순번 , max(변경순번) over (partition by 고객ID) 마지막변경순번 , 전화번호, 주소, 자녀수, 직업, 고객등급 
    from 고객변경이력
    ) 
where 변경순번 = 마지막변경순번

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

select 고객ID, 변경순번, 전화번호, 주소, 자녀수, 직업, 고객등급 
from (
	select 고객ID, 변경순번 , rank() over (partition by 고객ID order by 변경순번) rnum , 전화번호, 주소, 자녀수, 직업, 고객등급 
    from 고객변경이력
    ) 
where rnum = 1

어디선가 퍼온글인데 출처를 잊어버렸다.

(혹시 아시는분은 알려주시면 수정하도록 하겠습니다.)