<오라클 성능 고도화 원리와 해법2> Ch05-06 Sort Area를 적게 사용하도록 SQL 작성
오라클 성능 고도화 원리와 해법2 - Ch05-06 Sort Area를 적게 사용하도록 SQL 작성
소트 연산이 불가피하다면 메모리 내에서 처리를 완료할 수 있도록 노력해야 한다. Sort Area 크기를 늘리는 방법도 있지만, 그전에 Sort Area를 적게 사용하는 방법부터 찾는 것이 순서다.
(1) 소트를 완료하고 나서 데이터 가공하기
특정 기간에 발생한 주문 상품 목록을 파일로 내리고자 한다. 아래 두 SQL 중 어느 쪽이 Sort Area를 더 적게 사용할까?
[1번]
1
2
3
4
5
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번]
1
2
3
4
5
6
7
8
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) 바이트(헤더 정보는 제외하고 데이터 값만)로 가공된 결과치를 Sort Area에 담는다. 반면 2번 SQL은 가공되지 않은 상태로 정렬을 완료하고 나서 최종 출력할 때 가공하므로 1번 SQL에 비해 Sort Area를 훨씬 적게 사용한다. 실제 테스트해보면 Sort Area 사용량에 큰 차이가 나는 것을 관찰할 수 있다.
(2) Top-N 쿼리
Top-N 쿼리 형태로 작성하면 소트 연산(=값 비교) 횟수를 최소화함은 물론 Sort Area 사용량을 줄일 수 있다. 우선 Top-N 쿼리 작성법부터 살펴보자.
SQL Server와 Sybase는 Top-N 쿼리를 아래와 같이 손쉽게 작성할 수 있다.
1
2
3
4
SELECT TOP 10 거래일시, 체결건수, 체결수량, 거래대금
FROM 시간대별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304'
IBM DB2에서도 아래와 같이 쉽게 작성할 수 있다.
1
2
3
4
5
6
SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 시간대별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304'
ORDER BY 거래일시
FETCH FIRST 10 ROWS ONLY ;
오라클에서는 아래처럼 인라인 뷰로 한 번 감싸야 하는 불편함이 있다.
1
2
3
4
5
6
7
8
SELECT * FROM (
SELECT 거래일시, 체결건수, 체결수량, 거래대금
FROM 시간대별종목거래
WHERE 종목코드 = 'KR123456'
AND 거래일시 >= '20080304'
ORDER BY 거래일시
)
WHERE ROWNUM <= 10
위 쿼리를 수행하는 시점에 [종목코드+거래일시] 순으로 구성된 인덱스가 존재한다면 옵티마이저는 그 인덱스를 이용함으로써 ORDER BY 연산을 대체할 수 있다. 아래 실행 계획에서 SORT ORDER BY 오퍼레이션이 나타나지 않은 것을 확인하기 바란다.
그뿐만 아니라 ROWNUM 조건을 사용해 N건에서 멈추도록 했으므로 조건절에 부합하는 레코드가 아무리 많아도 매우 빠른 수행 속도를 낼 수 있다. 실행 계획에 표시된 COUNT STOPKEY가 그것을 의미한다.
Top-N 쿼리의 소트 부하 경감 원리
[종목코드+거래일시] 순으로 구성된 인덱스가 없을 때는 어떻게 될까? 종목코드만을 선두로 갖는 다른 인덱스를 사용하거나 Full Table Scan 방식으로 처리하게 되는데, 이때는 정렬 작업이 불가피하다. 하지만 Top-N 쿼리 알고리즘이 효과를 발휘해 Sort Order By 부하를 경감시켜준다.
Top-N 쿼리 알고리즘에 대해 간단히 설명하면, rownum <= 10이면 우선 10개 레코드를 담을 배열을 할당하고, 처음 읽은 10개 레코드를 정렬된 상태로 담는다. (위에서 예시한 쿼리는 거래일시 순으로 정렬하고 있지만, 설명을 단순화하려고 숫자로 표현하였다.)
이후 읽는 레코드에 대해서는 맨 우측에 있는 값(가장 큰 값)과 비교해서 그보다 작은 값이 나타날 때만 배열 내에서 다시 정렬을 시도한다. 물론 맨 우측에 있던 값은 버린다. 이 방식으로 처리하면 전체 레코드를 정렬하지 않고도 오름차순(ASC)으로 최소값을 갖는 10개 레코드를 정확히 찾아낼 수 있다. 이것이 Top-N 쿼리가 소트 연산 횟수와 Sort Area 사용량을 줄여주는 원리이다.
(3) 분석 함수에서의 Top-N 쿼리
윈도우 함수 시에도 rank()나 row_number()를 쓰면 Top-N 쿼리 알고리즘이 작동해 max() 등 함수를 쓸 때보다 소트 부하를 경감시켜준다.