Sort Area를 적게 사용하도록 SQL 작성

운구름·2022년 7월 10일

sql 소트 스터디 튜닝

친절한 SQL 튜닝 스터디

목록 보기

21/27

소트 데이터 줄이기

소트 연산이 불가피 할 경우, 메모리 내에서 처리 완료해야 한다.
sort area 적게 사용하는 방법
1. 소트를 완료하고 나서 데이터 가공
2. Top-N쿼리 : 소트 연산(=값 비교) 횟수 최소화 및 sort area 사용량 감소

예시1

💡 아래 두 SQL 중 어느것이 Sort Area를 적게 사용할까?

-- 1번 쿼리
-- 레코드당 107(30+30+10+20+17) 바이트로 가공한 결과집합을 Sort Area에 담는다.
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번 쿼리
-- 가공하지 않은 상태로 정렬을 완료 한 후 최종 출력할 때, 가공한다.
-- (2번 쿼리가 Sort Area를 훨씬 적게 사용한다.)
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 상품번호
)

예시2

💡 아래 두 SQL 중 어느것이 Sort Area를 적게 사용할까?

-- 1번 쿼리
-- 모든 컬럼을 Sort Area에 저장한다. (716MB / 14.41sec)
select * from 예수금원장 order by 총예수금 desc

-- 2번 쿼리
-- 계좌번호, 총예수금만 Sort Area에 저장한다. (17M / 1.2sec)
select 계좌번호, 총예수금 from 예수금원장 order by 총예수금 desc

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

select *
from (
	select rownum no, a.*
  from (
		select 거래일시, 체결건수, 체결수량, 거래대금
		from 종목거래
		where 종목코드 = 'KR123456'
		  and 거래일시 >= '20180304'
		order by 거래일시
	) a
  where rownum <= (:page * 10)
)
where no >= (:page-1) * 10 + 1

---------------------------------------------------------------
0		| STATEMENT
10		| 	COUNT STOPKEY
10		| 		VIEW
10		| 			SORT ORDER BY STOPKEY
49857	| 				TABLE ACCESS FULL

실행계획에 Sort Order By가 나타남. 그 옆에 Stopkey도 나타남.
소트연산을 피할 수 없어 Sort Order By를 하지만 Top N 소트 알고리즘이 작동한다는 것을 보여줌.
Top N 소트 알고리즘이 작동하면 소트연산 횟수와 Sort Area 사용량을 최소화 해줌.
이 방식으로 처리하면 대상 집합이 아무리커도 많은 메모리 공간이 필요하지 않음.
전체 레코드를 다 정렬하지 않고도 오름차순으로 최소값을 갖는 10개의 레코드를 정확히 찾을 수 있다.

Top N 쿼리가 아닐 때 발생하는 소트 부하

SELECT *
  FROM (
          SELECT rownum no , a.*
           FROM (
                  SELECT 거래일시, 체결건수, 체결수량, 거래대금
                    FROM 종목거래
                   WHERE 종목거래 = ‘KR123456’
                     AND 거래일시 >= ‘20180304’
                   ORDER BY 거래일시
                 ) a
           -- WHERE rownum <= (:page * 10)
       ) 
 WHERE no between (:page-1) * 10 + 1 and (:page * 10)



-----------------------------------------------------
0 		SATEMENT
10 		VIEW
49857  	COUNT 
49857    VIEW
49857     SORT ORDER BY 
49857       TABLE ACCESS FULL 종목거래