13. 친절한 SQL 튜닝

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

TOP N 쿼리

TOP N 쿼리는 아래와 같이 간단하게 구현할 수 있다.

SELECT TOP10 
       거래일자
      ,체결건수
      ,체결수량
      ,거래대금
  FROM 거래
  
  WHERE 종목코드 > 'KR123456'
    AND 거래일시 >= '20180304'
  
  ORDER BY 거래일시
 
 SELECT * 
   FROM (
         SELECT TOP10 
                거래일자
               ,체결건수
               ,체결수량
               ,거래대금
           FROM 거래
           WHERE 종목코드 > 'KR123456'
             AND 거래일시 >= '20180304'
           ORDER BY 거래일시
        )
   WHERE ROWNUM <= 10

DBMS의 옵티마지어는 레코드가 아무리 많아도 그 중 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 거기서 바로 멈춘다. 지금부터 이 기능을 'Top N Stopkey' 알고리즘이라고 부르자.

 

페이징 처리

3-Tier 환경에서는 대량 데이터를 조회할 경우 페이징 처리 기법을 활용한다. 일반적으로 사용하는 표준은 아래와 같다.

SELECT *
  FROM (
        SELECT ROWNUM NO
             , A.*
          FROM
          (
           /* SQL BODY */
              
          ) AS A
  		  WHERE ROWNUM <= (:PAGE * 10)
       )
  WHERE NO >= (:PAGE-1)*10 + 1

3-Tier 환경에서 부분범위를 처리하기 위한 방법은 아래와 같다.

• 부분범위 처리 가능하도록 SQL을 작성한다. 부분범위 처리가 잘 작동하는지 토드, 오렌지와 같은 툴을 사용하여 확인한다.
• 작성한 SQL 문을 페이징 처리용 표준 패턴 SQL Body 부분에 붙여 넣어준다.

인덱스를 이용해 최소/최대값 구하기 위한 조건

• 전체 데이터를 읽지 않고 인덱스를 이용해 최소 또는 최대값을 구하려면, 조건절 컬럼과 MIN/MAX 함수 인자 컬럼이 모두 인덱스에 포함돼 있어야 한다.
• 테이블 액세스가 발생하지 않아야 한다. 이렇게 인덱스를 이용해 최소값, 최대값을 가져오는 알고리즘을 'First Row StopKey' 알고리즘 이라고 부른다.

INDEX_DESC 힌트 활용

• 단순하게 쿼리의 성능을 높이기 위해 SQL 튜닝 전문가들이 전통적으로 사용해온 방식이 있다.

SELECT  /*+ INDEX_DESC(X 상태변경이력) */
        거래일자
       ,체결건수
       ,체결수량
       ,거래대금
 WHERE 종목코드 > 'KR123456'
   AND 거래일시 >= '20180304'
   AND ROWNUM <= 1

• 이 방식은 확실히 성능이 좋은데 문제는 인덱스 구성이 완벽해야만 잘 작동하며 인덱스 구성이 바뀌면 잘못된 값을 가져올 가능성이 있는 단점이 있다.