SQL튜닝이론 - 인덱스를 활용한 소트 오퍼레이션 생략 방법 과 TOP-N 쿼리에 응용하기

SQL튜닝이론 - 인덱스를 활용한 소트 오퍼레이션 생략 방법 과 TOP-N 쿼리에 응용하기

 

 

인덱스는 항상 구성 칼럼을 기준으로 정렬된 상태를 유지합니다. 인덱스의 이런 특징을 활용하면 SQL에 ORDER BY 또는 GROUP BY 절이 있더라도 실행 중 소트 오퍼레이션을 생략할 수 있습니다. 소트 오퍼레이션이 생략된 TOP-N 쿼리는 OLTP 시스템에서 대량의 데이터를 조회할 때 매우 빠른 응답속도를 냅니다. 특정 조건을 만족하는 최솟값 또는 최댓값도 빨리 찾을 수 있어 이력 데이터 조회에 유용합니다.

 

SORT ORDER BY 오퍼레이션 생략

 

인덱스 선두 컬럼이 [종목코드 + 거래일시] 일 때, 아래 쿼리 실행 시, 소트오퍼레이션을 생략할 수 있습니다. SQL문에 ORDER BY 절이 있음에도 옵티마이저가 SORT ORDER BY 오퍼레이션을 생략합니다.

 

select 거래일시, 체결건수, 체결수량, 거래대금
from 종목거래
where 종목코드 = 'KR123456'
ORDER BY 거래일시

-- SELECT STATEMENT
--    TABLE ACCESS BY INDEX ROWID OF 종목
--      INDEX RANGE SCAN OF 종목거래_pk

-- PREDICATE INFORMATION
-- ACCESS("종목코드" = "KR123456")

 

소트 오퍼레이션의 생략효과는 종목코드 = 'KR123456' 을 만족하는 전체 레코드를 읽지 않고 바로 결과 집합을 출력할 수 있습니다. 이는 부분범위처리를 가능케 합니다. 인덱스의 정렬을 이용하면 소트해야 할 대상 레코드가 무수히 많은 상황에서 극적인 성능 개선효과를 얻을 수 있습니다.

 

부분범위 처리를 활용한 튜닝 기법

 

부분범위 처리 원리는 TOP-N 쿼리를 통해 3-TIER 아키텍쳐 환경에서도 유효하게 사용됩니다.

3-TIER 아키텍처

클라이언트(APP, 시스템)와 DB서버 사이에 WAS, AP 등이 존재하는 서버 아키택쳐. DB서버 리소스를 수많은 클라이언트가 공유하는 구조이므로 클라이언트가 특정 DB 커넥션을 독점할 수 없고 단위 작업을 마친 후 커넥션풀에 DB 커넥션을 반납해야 합니다.

 

TOP-N 쿼리

 

TOP-N 쿼리는 전체 결과집합 중 상위 N개 레코드만 선택하는 쿼리입니다.

 

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

-- EXECUTION PLAN
-- SELECT STATEMETN
--    COUNT (STOPKEY)
--        VIEW
--            TABLE ACCESS BY INDEX ROWID OF 종목거래 TABLE
--                INDEX RANGE SCAN OF 종목거래_PK

 

인라인 뷰로 정의한 데이터를 모두 읽어 거래일시 순으로 정렬한 중간집합을 만들고, 중간집합으로부터 상위 열 개 레코드를 취하는 형태입니다. 중간집합을 구할 때는 소트 오퍼레이션을 생략할 수 있도록 인덱스를 구성해야 합니다. 인덱스를 [종목코드 + 거래일시] 순으로 구성하면 옵이마이저는 소트연산을 생략하며, 인덱스를 스캔하다가 열 개 레코드를 읽는 순간 바로 멉춥니다.

 

실행 계획을 확인해 보면 SORT ORDER BY 오퍼레이션 대신 COUNT(STOPKEY) 오퍼레이션이 찍힌 것을 확인할 수 있습니다. COUNT(STOPKEY) 오퍼레이션은 조건절에 부합하는 레코드가 아무리 많아도 ROWNUM으로 지정한 건수만큼 레코드를 읽고 탐색을 멈춘다는 의미입니다.

 

TOP_N 쿼리를 이용한 페이징 처리

 

3-tier 환경에서는 대량의 결과집합을 조회하기 위해 페이징 처리 기법을 활용합니다.

 

    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

 

인라인 뷰가 TOP-N 쿼리이므로 ROWNUM으로 지정한 건수만큼 결과 레코드를 얻으면 탐색을 멈춥니다. :page가 커질수록 즉, 뒤쪽 페이지로 이동할수록 인라인 뷰에서 읽어오는 레코드의 양이 많아지지만 보통 OLTP 시스템에서는 앞쪽을 일부 데이터만 확인하므로 큰 문제가 되지 않습니다. 3-TIER 환경에서 부분범위 처리를 활용하기 위해 할 일은 다음과 같습니다.\

 

1. 부분범위 처리 가능한 TOP-N 쿼리를 작성합니다.
2. TOP-N 쿼리를 인라인 뷰로 하는 페이징 처리용 표준 패턴 SQL을 작성합니다.

 

'부분범위 처리 가능하도록 SQL을 작성한다'는 의미는 다음과 같습니다.

1. 인덱스 사용 가능 조건절을 구성하고 조인은 NL 조인 위주로 처리합니다
2. ORDER BY 절이 있더라도 소트 연산을 생략할 수 있도록 인덱스를 구성합니다.

아래의 페이징 전용 쿼리는 실행계획에 소트 연산이 없고 COUNT STOP 키만 나타납니다.

 

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

--EXECUTION PLAN
-- SELECT STATEMENT
--   VIEW
--     COUNT (STOPKEY) -- NO SORT + STOPKEY
--       VIEW -- INLINE VIEW
--         TABLE ACCESS BY INDEX ROWID OF '종목거래'
--           INDEX RANGE SCAN OF '종목거래_PK'

 

페이징 처리에 ROWNUM을 사용하지 않으면 발생하는 문제점

 

아래의 쿼리로 페이징처리하는 것이 더 간결해 보입니다. 하지만 아래 쿼리는 STOP KEY가 발생하지 않는 쿼리 패턴입니다.

 

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

--EXECUTION PLAN
-- SELECT STATEMENT
--   FILTER
--       VIEW
--         COUNT -> NO SORT + NO STOP
--           VIEW
--             TABLE ACCESS BY INDEX ROWID OF '종목거래'
--               INDEX RANGE SCAN OF '종목거래_PK'

 

ROWNUM은 단순한 조건절이 아닙니다. TOP-N STOPKEY 알고리즘이 작동하게 하는 열쇠 역할을 합니다. STOPKEY 오퍼레이션이 발생하지 않으면 부분범위가 아닌 전체범위를 처리합니다.

 

부분범위 처리가 가능한 SQL 작성법

 

아래 쿼리는 STOPKEY가 작동하긴 했지만 적절한 인덱스의 부재로 소트 연산을 생략할 수 없습니다. 실행계획에도 SORT ORDER BY 오퍼레이션이 나타납니다. 화면에 일부만을 출력하고 싶어도, 거래일자 조건에 해당하는 데이터를 모두 읽어 정렬을 마친 후 일부만 읽어옵니다.

 

-- 거래\_PK : 거래일자 + 계좌번호 + 거래순번
-- 거래\_X01: 계좌번호 + 거래순번 + 결제구분코드

select *
from (
  select 계좌번호, 거래순번, 주문금액, 결제구분코드, 주무매체구분코드
  from 거래
  where 거래일자 = :ord_dt
  order by 계좌번호, 거래순번, 결제구분코드
)
where rownum <= 50

-- EXECUTION PLAN
-- SELECT STATEMENT
--   COUNT (STOPKEY)
--     VIEW
--       SORT(ORDER BY STOPKEY)
--         TABLE ACCESS BY INDEX ROWID OF '거래' TABLE
--           INDEX RANGE SCAN OF '거래_PK' INDEX

 

인라인뷰의 ORDER BY절에서 결제구분코드를 빼면 거래_PK 인덱스를 활용해 부분범위처리 할 수 있습니다. 계좌번호와 거래순번 만으로도 중복 레코드가 전혀 없기 때문에 결제구분코드를 정렬 칼럼으로 추가했을 때와 정렬상태가 같습니다. 이에 결제구분코드 칼럼을 제거하고 부분범위처리의 이점을 누리는 것이 낫습니다.