[SQLP 친절한 SQL 튜닝 정리] 5장- 소트튜닝(Sort Area를 적게 사용하는 SQL 작성)

SQLP를 준비하며 "친절한 sql 튜닝" 책 5장 소트튜닝 Sort Area를 적게 사용하는 SQL 작성 부분을 정리한 내용 입니다.
친절한SQL튜닝 도서

5장-소트튜닝

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

5.4.1 소트 데이터 줄이기

-- 1번SQL

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

• 1번 SQL은 모든 컬럼은 Sort Area에 저장한다
• 2번 SQL은 계좌번호와 총 예수금만 Sort Area에 저장한다.
• 즉, 2번이 Sort Area를 적게 사용한다.

 

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

 

 

전교생 1,000명 중 가장 큰 학생 열 명을 선발 할 경우 Top N 쿼리 작동 방식

• 인덱스로 소트 연산을 생략할 수 있을 경우
  • 전교생이 키 순서대로 정렬되어 있기 때문에 가장 위쪽에 있는 열명을 선발
• 인덱스로 소트 연산을 생략할 수 없을때
  1. 전교생을 운동장에 집합 시킨다.
  2. 맨 앞줄 맨 윗쪽에 있는 학생 열명을 단상 앞으로 불러 키 순서대로 세운다.
  3. 나머지 990명을 한 명씩 교실로 들여보내면서 현재 Top 10 위치에 있는 학생과 키를 비교한다.
     더 큰 학생이 나타나면, 현재 Top 10위치에 있는 학생을 교실로 들여 보낸다.
  4. Top 10에 새로 진입한 학생 키에 맞춰 자리를 재배치 한다.

 

• 인덱스로 소트 연산을 생략할 수 없어 Table Full Scan 방식으로 처리할 때의 SQL 트레이스
• Sort Orde By 옆의 'StopKey'는 소트 연산을 피할 수 없어 Sort Order By 오퍼레이션을 수행하지만 "Top N 소트 알고리즘이 작동한다는 것을 표현
• 소트 연산(=값 비교) 횟수와 SortArea 사용량을 최소해 준다. page 변수에 1을 입력하면 열 개 원소를 담을 배열 공간만 있으면 된다.
• 처음 읽은 열 개 레코드를 거래일시 오름차순으로 정렬해서 배열에 담고, 이후 읽는 레코드에 대해서는 배열 맨 끝에 있는 값과 비교해서 그보다 작은 값이 나타날 때만 배열내에서 다시 정렬한다. 기존에 맨 끝에 있던 값은 버린다.

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

• 실행 계획에서 Stopkey가 사라진 것으로 'Top N 소트' 알고리즘이 작동하지 않았음을 알 수 있다.
• 결과로 physical Read(pr=698)와 Physical Write(pw=698) 발생
• sorts(disk) 항목이 1이므로 정렬 과정에 Temp 테이블스페이스를 이용헀다는 사실을 알 수 있음

5.4.4 분석함수에서의 Top N 소트

• 윈도우 함수 중 rank, row_number 함수는 Top N 소트 알고리즘이 작용하기 때문에 max 함수보다 소트 부하가 적다.
• max 함수를 사용 했을 때에는 Window Sort 단계에서 13,456의 physical read(pr)와 8,960개 physical write(pw)이 발생
• rank 함수를 사용 헀을 때에는 40개의 physical read(pr)과 physical write(pw) 발생