[친절한 SQL 튜닝 스터디] 5장. 소트 연산 튜닝

5.1 소트 연산에 대한 이해

소트 수행 과정

소트는 PGA에 할당한 Sort Area에서 수행하며 PGA가 부족하면 Temp 테이블 스페이스를 사용

메모리 소트 : PGA 내에서 소트 완료할 경우

디스크 소트 : Temp 테이블 스페이스까지 사용하여 소트를 완료할 경우

소트는 최대한 발생하지 않도록 SQL을 작성해야 하고, 소트가 불가피하다면 메모리 내에서 수행을 완료할 수 있도록 해야 함

 

 

아래는 소트 오퍼레이션 종류에 대해 설명한다.

 

 

Sort Aggregate : 전체 로우를 대상으로 집계를 수행할 때 나타난다.

-- Sort Area에 변수를 하나 만들어 데이터를 읽으면서 최대값 비교하며 최대값을 찾아 리턴 

SELECT MAX(SAL) FROM 직원;​​

 

MAX, MIN, SUM, AVG 함수를 사용할 때 나타남

실제로 데이터를 정렬하진 않고 Sort Area 사용하는 정도

 

 

Sort Order By : 데이터를 정렬할 때 나타나는 기본적인 소트 오퍼레이션.

SELECT * FROM 직원 ORDER BY SAL DESC;​​

 

 

Sort Group By : 그룹별 집계에 나타나는 오퍼레이션

-- 부서번호를 기준으로 ORDER BY 하므로 SORT GROUP BY 오퍼레이션 사용

SELECT DEPT_NO, MAX(SAL) FROM 직원 GROUP BY DEPT_NO ORDER BY DEPT_NO;

 

-- ORDER BY가 없으므로 HASH GROUP BY 오퍼레이션 사용

SELECT DEPT_NO, MAX(SAL) FROM 직원 GROUP BY DEPT_NO;​​

각각의 그룹별로 위 Sort Aggregate에서 했던 방식대로 사용

각각의 그룹을 대표하는 값에 대해 ORDER BY가 없다면 Hash Group By라는 오퍼레이션을 사용함.

ORDER BY가 없을 경우, 그룹을 대표하는 값에 따라 소트 하지 않는다는 것을 명심

 

 

Sort Unique : 중복 레코드를 제거하는 소트 오퍼레이션

-- 서브쿼리의 중복을 제거하기 위해 발생

SELECT /*+ ordered use_nl(dept) */

 *

FROM DEPT

WHERE

 DEPT_NO IN (SELECT /*+ unnest */ DEPT_NO FROM EMP WHERE JOB = '개발자');

 

 

-- UNION 하여 중복을 제거하기 위해 발생

SELECT JOB FROM EMP WHERE DEPT_NO = 10

UNION

SELECT JOB FROM EMP WHERE DEPT_NO = 20​

만약 유일성이 보장된다면 (PK 또는 유니크 인덱스) 이 오퍼레이션은 생략됨.

Distinct 연산자에서도 사용됨.

 

 

Sort Join : 4.2장에서 배운 소트 머지 조인을 수행할 때 나타남

MERGE JOIN

 SORT (JOIN)

  TABLE ACCESS (BY INDEX ROWID) OF '사원' TABLE

   INDEX (RAGNE SCAN) OF '사원_X1' (INDEX)

 SORT (JOIN)

  TABLE ACCESS (BY INDEX ROWID) OF '고객' TABLE

   INDEX (RAGNE SCAN) OF '고객_X1' (INDEX)​​

 

 

Window Sort : 윈도우 함수(분석 함수)를 수행할 때 나타남

SELECT MGR , ENAME , SAL , SUM(SAL) OVER (PARTITION BY MGR) MGR_SUM FROM EMP;​​

 

윈도우 함수 : 행과 행간의 관계를 쉽게 정의 및 분석할 수 있는 함수

RANK, SUM, MAX, MIN , AVG 등등 

 

 

5.2 소트가 발생하지 않도록 SQL 작성

Union vs Union All

Union은 중복을 제거하기 위해 소트 연산을 함

Union All은 중복 제거를 하지 않으므로 소트를 하지 않음

중복일 수 없는 데이터들의 집합을 Union으로 처리하면 낭비이므로 데이터가 중복되지 않는다면 Union All을 사용해야 함

소트 연산이 일어나지 않도록 Union All을 사용하며 중복을 피하는 방법도 있음

SELECT

 ..

WHERE 결제일자 = '20180316'

UNION ALL

SELECT

 ..

WHERE 주문일자 = '20180316'

AND 결제일자 <> '20180316' -- 또는 LNNVL(결제일자 = '20180316')​

Exists 활용

DISTINCT는 중복을 제거하기 위해 소트 연산을 함

-- 이 경우 B 데이터를 모두 읽고, 중복을 제거하는 작업이 필요

SELECT DISTINCT A.*

FROM A, B

WHERE A.x = :x

AND B.y = A.y

AND B.z between :dt1 and :dt2

 

-- 이 경우, B 데이터가 한건이라도 존재하는지만 확인하므로 효율적임

SELECT A.*

FROM A

WHERE A.x = :x

AND EXISTS(

 SELECT 'x' FROM B

 WHERE B.y = A.y

 AND B.z between :dt1 and :dt2

)​

해시 조인은 소트가 발생하므로 NL 조인으로 변경해주는 것이 효과적일 수 있다.

 

728x90

 

 

5.3 인덱스를 이용한 소트 연산 생략

인덱스는 항상 키 컬럼 순으로 정렬된 상태를 유지하므로, 인덱스를 이용해서 소트 연산이 생략 가능

 

 

Top N 쿼리 : 전체 결과 집합 중 상위 N개 레코드만 선택하는 쿼리 

인덱스를 탄다면, 부분 범위 처리로 딱 10개만 만나면 멈춘다.

실행 계획에는 COUNT(STOPKEY)라고 표현되며 이를 Top N Stopkey 알고리즘이라고 칭함.

페이징 처리를 할 때 Top N 쿼리를 사용하는데 반드시 아래에 ROWNUM을 사용해야 한다.

 

 

인덱스를 이용한 최대/최솟값 구하기

조건절 컬럼 + MIN/MAX 함수 인자의 컬럼이 모두 인덱스에 포함되어 있어야지 소트를 하지 않는다.

이런 경우를 First Row Stopkey 알고리즘이 사용되었다고 표현한다.

 

 

Top N 쿼리를 이용한 최대/최솟값 구하기

인덱스가 모두 포함되지 않을 경우는 Top N 쿼리로 사용하면 된다. ROWNUM ≤ 1;

 

 

이력 조회 : 다른 포스팅으로 대신합니다. [SQL] 변경 이력 테이블 생성 및 조회 방법

 

 

Sort Group By 생략

-- 인덱스 : REGION

SELECT REGION, AVG(AGE)

FROM 고객

GROUP BY REGION

 

GROUP BY에 선두 컬럼 인덱스가 있다면, 인덱스를 통해, Sort Group By 연산을 생략 가능.

Sort Group By Nosort라고 표현됨

 

 

 

 

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

소트 연산이 불가피하다면 메모리 내에서 처리를 완료하도록 노력해야 함.

 

 

소트 데이터 줄이기 : Sort Area에 저장을 적게 해야 하므로, SELECT 절에 필요한 항목만 넣어야 한다.

-- 모든 컬럼을 SORT AREA에 담으므로 아래보다 느리다.

SELECT * FROM 직원 ORDER BY 나이;

 

-- 이름만 SORT AREA에 담으므로 위보다 빠르다.

SELECT 이름 FROM 직원 OERDER BY 나이;​

​

 

 

TOP-N 쿼리가 인덱스를 사용하지 못해도, Sort Area를 적게 사용한다. 그러니 TOP-N은 좋다!

 

출처: 친절한 SQL 튜닝 (저자 : 조시형)