SQL튜닝이론 - TOP-N 쿼리 활용방법 (이력조회)

SQL튜닝이론 - TOP-N 쿼리 활용방법 (이력조회)

 

 

업무에 대한 이력 테이블을 조회할 때, 성능을 위해 FISRT ROW STOPKEY 또는 TOP-N STOPKEY 알고리즘이 작동할 수 있게 인덱스 설계 및 SQL을 구현해야 합니다. 성능을 고려하지 않은 가장 단순한 조회 패턴은 다음과 같습니다.

 

-- 장비구분코드가 A0001인 장비의 최종변경일자를 조회하는 쿼리입니다. 변경이력은 상태변경이력 테이블에 저장됩니다.

SELECT 장비번호, 장비명, 상태코드
,(
    SELECT MAX(변경일자)
    FROM 상태변경이력
    WHERE 장비번호 = P.장비번호
) as 최종변경일자
FROM 장비 P
WHERE 장비구분코드 = 'A0001'

---EXECUTION PLAN
-- SELECT STATEMETN
--    SORT AGGREGATE
--        FIRST ROW
--            INDEX RANGE SCAN(MIN/MAX) OF IDX_상태변경이력
--    TABLE ACCESS BY INDEX ROWID OF 장비
--        INDEX RANGE SCAN OF 장비_N1

 

상태변경이력을 조회하는 스칼라 쿼리 부분에 FIRST ROW STOPKEY 알고리즘이 작동합니다. 상태변경이력_PK 인덱스가 [장비번호 + 변경일자 + 변경순번] 순으로 구성돼 있기 때문입니다.

 

복잡한 이력조회

 

-- 장비구분코드가 A0001인 장비의 최종 변경일자와 최종변경순번을 조회하는 쿼리입니다. 변경이력은 상태변경이력 테이블에 저장됩니다.

SELECT 장비번호, 장비명, 상태코드
, SUBSTR(최종변경이력 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9,4)) 최종변경순번
FROM (
    SELECT 장비번호, 장비명, 상태코드
    ,(
        SELECT MAX(H.변경일자 || LPAD(H.변경순번, 4)) 
        FROM 상태변경이력 H
        WHERE 장비번호 = P.장비번호
    ) as 최종이력
    FROM 장비 P
    WHERE 장비구분코드 = 'A0001'
)
-- EXECUTION PLAN
SELECT STATEMENT
  SORT AGGREGATE
    INDEX RANGE SCAN OF 상태변경이력_PK
  TABLE ACCESS BY INDEX ROWID OF 장비
    INDEX RANGE SCAN OF 장비_N1

 

위 SQL은 인덱스 컬럼을 가공했기 때문에 FIRST ROW STOPKEY 가 작동할 수 없습니다. 장비별로 상태변경이력 레코드가 많으면 성능문제가 발생합니다. 아래 SQL로 변경하면 FIRST ROW STOPKEY 알고리즘이 잘 동작합니다. 하지만 동일한 테이블을 세 번 읽는 비효율이 있습니다.

 

SELECT 장비번호, 장비명, 상태코드
,(
    SELECT MAX(변경일자)
    FROM 상태변경이력
    WHERE 장비번호 = P.장비번호
) as 최종변경일자
,(
    SELECT MAX(변경순번)
    FROM 상태변경이력 H
    AND 변경일자 = (
      SELECT MAX(변경일자)
      FROM 상태변경이력
      WHERE 장비번호 = P.장비번호
    )
) as 최종변경순번
FROM 장비 P
WHERE 장비구분코드 = 'A001'

-- EXECUTION PLAN
-- SELECT STATEMENT
--  SORT AGGREGATE
--    FIRST ROW
--      INDEX RANGE SCAN OF (MIN/MAX) 상태변경이력01
--  SORT AGGREGATE
--    INDEX RANGE SCAN
--      SORT AGGREGATE
--        FIRST ROW
--          INDEX RANGE SCAN (MIN/MAX) 상태변경이력02
--  TABLE ACCESS BY INDEX ROWID OF 장비
--  INDEX RANGE SCAN 장비 Of 장비_N1

 

상태변경이력 테이블에서 읽어야할 칼럼이 많으면 성능에 문제가 생깁니다. 예를 들어 상태코드가 추가되면 같은 테이블을 3번 더 조회하게 됩니다.

 

SELECT 장비번호, 장비명
, (
   SELECT MAX(H.변경일자)
    FROM 상태변경이력 H
    WHERE 장비번호 = P.장비번호
) 최종변경일자
, (
   SELECT MAX(H1.변경순번)
    FROM 상태변경이력 H1
    WHERE 장비번호 = P.장비번호
    AND 변경일자 = (
      SELECT MAX(H2.변경일자)
      FROM 상태변경이력 H2
      WHERE 장비번호 = P.장비번호
    )
) 최종변경순번
, (
   SELECT H1.상태코드
    FROM 상태변경이력 H1
    WHERE 장비번호 = P.장비번호
    AND 변경일자 = (
      SELECT MAX(H2.변경일자)
      FROM 상태변경이력 H2
      WHERE 장비번호 = P.장비번호
    )
    AND 변경순번 = (
     SELECT MAX(H3.변경순번)
      FROM 상태변경이력 H3
      WHERE 장비번호 = P.장비번호
      AND 변경일자 = (
        SELECT MAX(H4.변경일자)
        FROM 상태변경이력 H4
        WHERE 장비번호 = P.장비번호
      )
    )
) 최종상태코드
FROM 장비 P
WHERE 장비구분코드 = 'A001'

 

PREDICATED PUSING 쿼리변환 활용

 

아래의 쿼리는 메인 쿼리의 컬럼을 서브쿼리 내 인라인 뷰에서 참조했기 때문에 파싱 에러가 발생합니다.

 

SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9,4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM
(
  SELECT 장비번호, 장비명
  ,(
    SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
    FROM (
      SELECT 변경일자, 변경순번, 상태코드
      FROM 상태변경이력
      WHERE 장비번호 = P.장비번호 -- 부적합한 식별자오류
      ORDER BY 변경일자 DESC, 변경순번 DESC
    )
    WHERE  ROWNUM <= 1
  ) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

 

에러를 피하기 위해 아래와 같이 쿼링 할 수 있습니다. 인라인뷰가 아닌 스칼라 쿼리이므로 메인 쿼리의 칼럼 사용이 가능합니다.

 

SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9,4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM
(
  SELECT 장비번호, 장비명
  ,(
    SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
    FROM (
      SELECT 변경일자, 변경순번, 상태코드
      FROM 상태변경이력
      ORDER BY 변경일자 DESC, 변경순번 DESC
    )
    WHERE 장비번호 = P.장비번호
    AND  ROWNUM <= 1
  ) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

-- OPERATION
-- SELECT STATEMENT
--   COUNT STOPKEY
--     VIEW
--       TABLE ACCESS BY INDEX ROWID OF 상태변경이력
--        INDEX RANGE ROW SCAN DESCENDING OF 상태변경이력
--   TABLE ACCESS BY INDEX ROWID OF 장비
--     INDEX RANGE SCAN 장비_N1

 

쿼리의 형태만 놓고보면, 스칼라 쿼리 내부에서 인라인 뷰로 정의한 집합을 우선 만들고 장비번호와 ROWNUM 조건으로 필터링하고 있습니다. 하지만 실제 수행해 보면 장비번호 조건이 인라인 뷰 내부에서 필터링됩니다. 이런 쿼리변환을 PREDICATE PUSHING 이라고 합니다. 이 방식을 사용하면 인덱스 구성이 변경됐을 때 TOP N STOPKEY 가 작동하지 않을 수는 있지만 쿼리 결과집합은 보장됩니다. 12C부터는 서브쿼리의 인라인뷰에서 메인쿼리 칼럼을 이용해도 파싱에러 없이 PREDICATE PUSHING 이 작동합니다.

 

SELECT 장비번호, 장비명
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력, 9,4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM
(
  SELECT 장비번호, 장비명
  ,(
    SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
    FROM (
      SELECT 변경일자, 변경순번, 상태코드
      FROM 상태변경이력
      WHERE 장비번호 = P.장비번호
      ORDER BY 변경일자 DESC, 변경순번 DESC
    )
    WHERE ROWNUM <= 1
  ) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

 

윈도우 함수와 ROW LIMITING을 활용한 이력조회

 

윈도우 함수를 사용하면 TOP N STOPKEY 알고리즘이 작동하지 않습니다. 따라서 인덱스로 소트를 생략할 수 있는 상황에서는 사용해선 안됩니다.

 

이력조회

 

이력 조회를 위해 서브쿼리에 아래와 같이 윈도 함수를 사용할 수 있지만 TOP N STOPKEY 알고리즘이 작동하지 않습니다. 따라서 인덱스로 소트를 생략할 수 있을 때는 사용해선 안됩니다.

 

SELECT 장비번호, 장비명, 
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력,9,4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
  SELECT 장비번호, 장비명,
  (
      SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
      FROM (
        SELECT 변경일자, 변경순번, 상태코드, 
        ROW_NUMBER() OVER(ORDER BY 변경일자 DESC, 변경순번 DESC) NO
        FROM 상태변경이력
        WHERE 장비번호 = P.장비번호
      )
      WHERE NO = 1    
  ) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

 

ROW LIMITING 절을 이용해 구현할 수도 있습니다. 마찬가지로 TOP-N STOPKEY 는 작동하지 않습니다. 옵티마이저가 동일하게 윈도 함수를 사용하는 형태로 변경하기 때문에 실행계획도 같습니다.

 

SELECT 장비번호, 장비명, 
, SUBSTR(최종이력, 1, 8) 최종변경일자
, TO_NUMBER(SUBSTR(최종이력,9,4)) 최종변경순번
, SUBSTR(최종이력, 13) 최종상태코드
FROM (
  SELECT 장비번호, 장비명,
  (
    SELECT 변경일자 || LPAD(변경순번, 4) || 상태코드
    FROM 상태변경이력
    WHERE 장비번호 = P.장비번호
    ORDER BY 변경일자 DESC, 변경순번 DESC
    FETCH FIRST 1 ROWS ONLY
   ) 최종이력
  FROM 장비 P
  WHERE 장비구분코드 = 'A001'
)

 

페이징 처리

 

윈도우 함수를 페이징 처리에 활용하는 경우 TOP-N STOPKEY 알고리즘이 작동할 수 있습니다. 하지만 카디널리티와 비용계산이 불완전하여 소트를 생략할 수 있는 인덱스를 사용하지 않는 경우가 발생합니다. 따라서 페이징 처리에 이 방식을 사용하면 INDEX/INDEX_DESC 힌트를 명시해야 합니다.

 

SELECT 변경일자, 변경 순번, 상태코드
FROM (
  SELECT 변경일자, 변경순번, 상태코드
  , ROW_NUMER() OVER (ORDER BY 변경일자, 변경순번) NO
  FROM 상태변경이력
  WHERE 장비번호 = :enp_no
)
WHERE NO BETWEEN 1 AND 10;

소트연산을 생략 가능한 인덱스가 없이 TOP-N 알고리즘이 작동할 때는, 기존 TOP-N 쿼리보다 소트 공간을 더 많이 사용해야 합니다.

SELECT 변경일자, 변경 순번, 상태코드
FROM (
  SELECT 변경일자, 변경순번, 상태코드
  FROM 상태변경이력
  ORDER BY 변경일자, 변경순번
  FETCH FIRST 10 ROWS ONLY
)
WHERE NO >= 1;

 

12C 이후 버전부터 페이징 처리에 ROW-LIMITTING을 사용할 수 있지만 윈도 함수와 성능이 동일합니다.

 

상황에 따라 달라지는 이력 조회 패턴

 

전체 정비 이력을 조회할 때는 인덱스를 이용한 STOPKEY 기능 작동여부가 튜닝의 핵심요소가 아닙니다. 인덱스 활용 패턴의 성능은 랜덤 I/O 발생량에 비례하므로 대량데이터에는 좋은 해법이 아닙니다. 전체 장비이력을 조회할 때는 아래와 같이 윈도 함수를 이용하는 것이 효과적입니다.

 

SELECT P.장비번호, P.장비명
, H.변경일자 AS 최종변경일자
, H.변경순번 AS 최종변경순번
, H.상태코드 AS 최종상태코드
FROM 장비 P,
(
  SELECT 장비번호, 변경일자, 변경순번, 상태코드
  , ROW_NUMBER() OVER( ORDER BY 변경일자 DESC, 변경순번 DESC) RNUM
  FROM 상태변경이력
)H
WHERE H.장비번호 = P.장비번호
AND H.RNUM = 1

--OPERATION
-- SELECT STATEMENT
--    HASH JOIN
--        TABLE ACEESS FULL OF 장비 TABLE
--        VIEW
--            WINDOW SORT PUSHED RANK
--                TABLE ACCESS FULL OF 상태변경이력

 

FULL SCAN과 해시조인을 이용해 전체 이력까지 모두 얻지만 인덱스를 이용하는 방식보다 빠릅니다. KEEP 절을 이용할 수도 있습니다.

 

SELECT P.장비번호, P.장비명
, H.변경일자 AS 최종변경일자
, H.변경순번 AS 최종변경순번
, H.상태코드 AS 최종상태코드
FROM 장비 P,
(
  SELECT 장비번호, 
  max(변경일자) 변경일자, 
  max(변경순번) KEEP (DENSE_RANK LAST ORDER BY 변경일자) as 변경순번,
  max(상태코드) KEEP (DENSE_RANK LAST ORDER BY 변경일자, 변경순번) as 상태코드
  FROM 상태변경이력
)H
WHERE H.장비번호 = P.장비번호
AND H.RNUM = 1

--OPERATION
-- SELECT STATEMENT
--    HASH JOIN
--        TABLE ACEESS FULL OF 장비 TABLE
--        VIEW
--            WINDOW SORT PUSHED RANK
--                TABLE ACCESS FULL OF 상태변경이력

SORT GROUP BY 생략

 

그룹핑 연산에서도 인덱스를 활용해 SORT GROUP BY 연산을 생략할 수 있습니다. 아래 SQL에서 REGISON 이 선두인 인덱스를 활용해 SORT GROUP BY 를 생략할 수 있습니다. 실행계획에 SORT GROUP BY NOSORT라고 표시됩니다.

 

 select region, avg(age), count(*)
 from customer
 group by region

 -- OPERATION
 SELECT STATEMENT
  SORT GROUP BY NOSORT
    TABLE ACCESS BY INDEX ROWID OF CUSTOMER TABLE
      INDEX FULL SCAN OF CUSTOMER_X01

 

인덱스에서 region을 기준으로 스캔중 값이 바뀔 때마다 그때까지 집계한 값을 저장합니다. 인덱스를 이용해 NOSORT 방식으로 GROUP BY를 처리하면 부분 범위 처리가 가능합니다.