5.4장 Sort Area 효율적으로 쓰는 SQL 작성법
친절한 SQL 튜닝 5.4장
스터디하면서 알게 된 정렬 관련 실전 팁을 정리했다. 불필요한 sort area 사용을 줄이기 위한 실제 쿼리 튜닝 사례 위주다.
1. Sort 데이터 줄이기
다음 두 쿼리는 결과는 같지만, 정렬 대상과 시점이 다르다.
-- (1) 가공 후 정렬
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, 'yyyymmdd hh24:mi:ss')
FROM 주문상품
WHERE 주문일시 BETWEEN :START AND :END
ORDER BY 상품번호;-- (2) 정렬 후 가공
SELECT LPAD(상품번호, 30) || LPAD(상품명, 30) || LPAD(고객ID, 30) || LPAD(고객명, 30) || TO_CHAR(주문일시, 'yyyymmdd hh24:mi:ss')
FROM (
SELECT 상품번호, 상품명, 고객ID, 고객명, 주문일시
FROM 주문상품
WHERE 주문일시 BETWEEN :START AND :END
ORDER BY 상품번호
);첫 번째 쿼리는 LPAD와 TO_CHAR로 가공한 후 정렬하기 때문에, 가공된 긴 문자열 전체를 sort area에 적재하게 된다.
두 번째 쿼리는 정렬 후 가공하므로, 정렬 시점의 데이터 양이 작아져 sort area 사용량이 줄어든다.
단순 예시로 다음도 마찬가지다.
-- 전체 컬럼 정렬
SELECT * FROM 예수금원장 ORDER BY 총예수금 DESC;
-- 필요한 컬럼만 정렬
SELECT 계좌번호, 총예수금 FROM 예수금원장 ORDER BY 총예수금 DESC;→ 정렬 시 불필요한 컬럼을 빼는 것만으로도 sort I/O를 줄일 수 있다.
2. Top-N 쿼리는 정렬을 ‘다’ 하지 않는다
Top-N 쿼리에서는 Oracle이 일반 정렬이 아닌, Top-N Sort 알고리즘을 사용한다.
작동 방식은 다음과 같다
처음 N개의 레코드를 읽어 정렬
이후 레코드를 하나씩 읽으면서 N등보다 크면 삽입, 가장 작은 값은 제거
최종적으로 N개의 레코드만 남긴다
-- Oracle 12c 이상
SELECT * FROM 주문상품 ORDER BY 주문금액 DESC FETCH FIRST 5 ROWS ONLY;
-- Oracle 11g 이하
SELECT * FROM (
SELECT * FROM 주문상품 ORDER BY 주문금액 DESC
) WHERE ROWNUM <= 5;장점
전체 정렬이 아니므로 메모리 사용량이 적다
인덱스로 소트 연산을 생략할 수 없어 Table Full Scan 방식 시
SORT ORDER BY STOPKEY를 사용한다.
3. Top-N 조건이 없으면 정렬은 끝까지 수행된다
ROWNUM 또는 FETCH FIRST 조건이 없는 경우, 전체 데이터를 정렬해야 하므로 sort 부하가 커질 수 있다.
-- 정렬 대상이 많아질수록 부하 커짐
SELECT * FROM 주문상품 ORDER BY 주문금액 DESC;또한 다음과 같은 윈도우 함수는 MAX()보다 효율적인 정렬을 할 수 있다.
RANK(),ROW_NUMBER()→ 내부적으로 Top-N 정렬 사용
Last updated