[친절한 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 조인으로 변경해주는 것이 효과적일 수 있다.

 

 

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은 좋다!
  • Top N의 자세한 설명은 다음 포스팅을 참고. [SQL] Top N을 파헤쳐 보자.