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 알고리즘을 사용한다.

작동 방식은 다음과 같다

1. 처음 N개의 레코드를 읽어 정렬

2. 이후 레코드를 하나씩 읽으면서 N등보다 크면 삽입, 가장 작은 값은 제거

3. 최종적으로 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 정렬 사용