대부분의 TOP SQL 은 ROWNUM 을 이용하여 부분범위처리 하는 개념임을 알것이다.
하지만 RANK 등의 분석함수를 이용하여 TOP 처리를 하는경우가 많이 있다.
이런경우에 대부분의 개발자및 DBA 들이 범하는 오류가 있으므로 오늘은 그부분을 바로잡고자 한다.
환경은 Oracle 10.2.0.4 이며 모든 예제는 오라클의 SH 스키마의 SALES 테이블을 이용한다.

들어가며
  ROWNUM 을 이용한 TOP SQL과 분석함수를 이용한 것의 다른점이 무엇인가?.
분석함수를 이용하면 1등이 여러명일때도 처리가 가능 하지만 ROWNUM 을 이용할경우 단한건만 가져오므로 처리가 불가능하다는 것이다.  아래는 그러한 경우에 분석함수를 하여 간단하게 해결한 경우이다.

SELECT /*+ gather_plan_statistics */
       *
  FROM (SELECT cust_id, prod_id, time_id, amount_sold,
               RANK () OVER (ORDER BY amount_sold DESC) top_sales
          FROM sh.sales a)
 WHERE top_sales <= :V_RANK;   --> 숫자 1 대입


먼저 매출액기준으로 순위를 만든후에 인라인뷰의 밖에서 FILTER 하는 형태의 SQL 이다.   
금액기준으로 가장 많이 팔린 상품과 해당고객 그리고 해당매출일자 등을 SELECT 하고 있다.

----------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Starts | A-Rows |   A-Time   | Buffers | Used-Mem |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |      1 |    108 |00:00:01.09 |    1718 |          |
|*  1 |  VIEW                    |       |      1 |    108 |00:00:01.09 |    1718 |          |
|*  2 |   WINDOW SORT PUSHED RANK|       |      1 |    109 |00:00:01.09 |    1718 |   45M (0)|
|   3 |    PARTITION RANGE ALL   |       |      1 |    918K|00:00:00.01 |    1718 |          |
|   4 |     TABLE ACCESS FULL    | SALES |     28 |    918K|00:00:00.01 |    1718 |          |
----------------------------------------------------------------------------------------------
 
A-Rows 항목을 보면 1등이 108 건임을 알수 있다.
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)
 
울며 겨자먹기
  약 91만건을 full scan 하였으며 일량(Buffers)과 메모리 사용량(Used-Mem)이 엄청나다.
분명히 문제가 있다.
분석함수로 TOP SQL 을 구현할경우 이와 관련된 일반적인 3가지 미신(오류)이 있다.

첫번째 오류로는 일반적으로 금액컬럼에 인덱스를 만들지 않는다는 것이다.

두번째 오류로는 금액컬럼에  인덱스를 만들어도 분석함수를 사용하였으므로 인덱스를 타지 않는다는 것이다.
(참고로 분석함수로는 Function Based Index를 만들수 없다.)

세번째 오류가 가장 심각하다.
인덱스를 사용하지 못할 바에야 전체건을 sort 해야하므로 울며 겨자먹기로 순위컬럼을 모델에 추가하고 그컬럼에 인덱스를 생성하며 데이터가 바뀔때마다 실시간으로 순위컬럼에 UPDATE 하는방식을 많이 사용하고 있다.
이런것을 추출속성이라고 하는데 정합성을 해칠수 있으므로 물리모델링시에 최소화 시켜야 한다.  
그렇다면 이러한 문제를 어떻게 풀수 있을까?

먼저 AMOUNT_SOLD에 인덱스를 하나 만든다.

CREATE INDEX SH.SALES_IDX01 ON SH.SALES
(AMOUNT_SOLD);

SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
       *
  FROM (SELECT prod_id, amount_sold,
               RANK () OVER (ORDER BY amount_sold DESC) top_sales
          FROM sh.sales a)
 WHERE top_sales <= :V_RANK;  --> 숫자 1 대입

-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------|
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      11 |
|*  1 |  VIEW                                |             |      1 |    108 |00:00:00.01 |      11 |
|*  2 |   WINDOW NOSORT STOPKEY              |             |      1 |    108 |00:00:00.01 |      11 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    109 |00:00:00.01 |      11 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    109 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<=:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<=:V_RANK)
 
 
해결책은 인덱스 생성
  금액 컬럼으로 인덱스를 하나 만들었을 뿐인데 성능과 정합성이라는 두가지 이슈를 모조리 해결 하였다.
일량이(Buffers ) 170 배정도 차이가 나며 메모리 사용은 하지도 않는다.
분석함수 사용시 인라인뷰의 바깥에서 filtering 시의 비밀은 3가지로 요약할수 있다.

1.해당컬럼 컬럼에 인덱스가 있을경우 rownum 과 같이 Stop Key Operation 이 발생 한다는 것이다.

2.Predicate Information 을 보면 뷰를 만들기 전에 filter 하는 것을 볼수 있다.(id 기준으로 2번)
  이때 인덱스를 사용하는 것이다.

3.id 기준으로 2번 Operation 을 보면 WINDOW NOSORT ~ 가 있다.
이것은 인덱스가 이미 정렬이 되어있기 때문에 Sort 가 필요없다는 뜻이다.

마치 Sort Group By 시에 해당컬럼이 인덱스로 생성되어 있을경우 NOSORT 가 나오는 원리와 같다.  
하지만 인덱스가 없으면 Full Table Scan + filter 만 일어난다. 이때 WINDOW SORT PUSHED RANK Operation 이 발생하게 된다.(가장 위의 인덱스가 없을때의 plan 을 참조 할것)


ROWNUM VS RANK

  WINDOW NOSORT STOPKEY 사용시 PLAN 을 보면 1건 더 SCAN했음을 알수 있다.
즉 1위그룹은 108 건  이지만 109 건을 scan 하였고 WINDOW FILTER 과정에서 1건이 제거되었다.
하지만 ROWNUM 을 이용한다면 어떻게 될까?
아래는 ROWNUM 으로 108 건(1위그룹의 전체건수)을 가져오는 예제이다.

SELECT /*+ GATHER_PLAN_STATISTICS FIRST_ROWS(1) */ *
  FROM (SELECT prod_id, amount_sold
          FROM sh.sales a
         ORDER BY amount_sold DESC )
WHERE ROWNUM <= :V_RANK --> 108 대입;        


-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      10 |
|*  1 |  COUNT STOPKEY                       |             |      1 |    108 |00:00:00.01 |      10 |
|   2 |   VIEW                               |             |      1 |    108 |00:00:00.01 |      10 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    108 |00:00:00.01 |      10 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    108 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<:V_RANK)
  
정확히 108 건만 SCAN 했음을 알수 있다.
이것이 ROWNUM 과의 차이이다. ROWNUM 은 정확히 건수를 잘라내지만 WINDOW STOP KEY 는 한건을 더읽어야만 한다.
하지만 이정도(1건)의 비효율은 눈감아 줄수 있다. 하지만 아래의 경우는 비효율이 심하므로 반드시 피해야 한다.

SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
       *
  FROM (SELECT prod_id, amount_sold,
               RANK () OVER (ORDER BY amount_sold DESC) top_sales
          FROM sh.sales a)
 WHERE top_sales < :V_RANK;  --> 숫자 2 대입


-----------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name        | Starts | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |             |      1 |    108 |00:00:00.01 |      13 |
|*  1 |  VIEW                                |             |      1 |    108 |00:00:00.01 |      13 |
|*  2 |   WINDOW NOSORT STOPKEY              |             |      1 |    115 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID| SALES       |      1 |    116 |00:00:00.01 |      13 |
|   4 |     INDEX FULL SCAN DESCENDING       | SALES_IDX01 |      1 |    116 |00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("TOP_SALES"<:V_RANK)
   2 - filter(RANK() OVER ( ORDER BY INTERNAL_FUNCTION("AMOUNT_SOLD") DESC )<:V_RANK)

RANK <= 1과 RANK < 2 는 결과는 같다. 하지만 성능은?
  WHERE 절에서 = 을 제거하고 < 로 바꾸었으므로 :V_RANK 변수에 2 를 대입 해야 같은결과를 넏을수 있다.
결과는 <= 를 사용한 것과 같지만 일량(Buffers)이 11 에서 13 으로 늘었다.
이것또한 분석함수를 이용한 TOP SQL 처리시 기억해야할 특징중의 하나인데 :V_RANK 변수에 2를 대입 하면 2위그룹까지 SCAN 한다.(정확히 말하면 3위그룹의 첫번째 ROW 까지 SCAN 한다.)
따라서 WINDOW STOPKEY 사용시에 = 를 생략하면 안된다.
 
결론:
여러 사이트에서 분석함수에 관한 몇가지 오해 때문에 성능이 나빠짐은 물론이고 정합성이 위배될수도 있는 일들이 벌어지고 있다.
두가지만 기억하자.
1.분석함수로 TOP SQL 처리시 반드시 인덱스를 만들것. (빠른성능을 원한다면)
2.RANKING 비교시 <= 를 사용할것.

편집후기 : 주의사항이 있으므로 반드시 댓글을 참고할것.

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of https://ukja.tistory.com BlogIcon 욱짜 2009.05.22 12:03 신고  댓글주소  수정/삭제  댓글쓰기

    좋은데요? ^^

    이 Post를 보는 다른 분들을 위해 한가지 주의점만 추가하면 위의 예제에서 amount_sold 컬럼이 반드시 NOT NULL이어야 한다는 것입니다. (그리고 sh.sales.amount_sold 컬럼은 당연히 NOT NULL로 선언되어 있구요)

    NOT NULL이 보장이 안되면 Index Scan만으로 Data를 다 가져온다는 것이 보장이 안되기 때문에 Full Scan 외에는 대안이 없기 때문입니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.05.22 13:35 신고  댓글주소  수정/삭제

      말씀하신데로 Full scan 을 피하려면 아래의 2가지 방법으로 하면 되겠습니다.
      1.NULL 을 허용하는 컬럼인 경우 NOT NULL 로 바꾸는 방법
      2.아래처럼 WHERE 절에 NOT NULL 을 명시하는 방법.

      SELECT /*+ gather_plan_statistics FIRST_ROWS(1) */
      *
      FROM (SELECT /*+ INDEX_DESC(A SALES_IDX01) */
      cust_id, prod_id, time_id, amount_sold,
      RANK () OVER (ORDER BY amount_sold DESC) top_sales
      FROM sh.sales a
      WHERE amount_sold IS NOT NULL)
      WHERE top_sales <= :V_RANK ;

      또한 현실적으로 볼때 null 인 경우 순위를 정할필요가 없을거 같습니다.
      좋은정보 감사합니다.

  2. feelie 2010.02.19 16:39  댓글주소  수정/삭제  댓글쓰기

    한번 읽어본 내용인데 다시 한번 보고, 실제로 테스트를 해보니 직접하는거하고 많이 다르네요.
    실제로 제가 테스트를 해봐야 겠다는 생각이 듭니다.

    내용중에 궁금한점이 있어서요.
    1. 인덱스를 만들고, first_rows(1) 힌트를 사용하셨는데요. index 힌트를 사용하는거하고 다른점이 있나요
    (first_rows, index)
    2. 9i부터로 기억되는데요. 9i 이후부터는 인덱스에 desc, asc 을 사용하지 않아도 옵티마이저가 알아서 min/max을
    사용해서 원하는 데이터만 스캔하는것으로 알고 있습니다.
    위의 테스트에서 first_rows 대신에 index 힌트를 사용했더니 인덱스를 역순으로 읽어주지 못해서, index_desc 힌트를
    사용했고, 그랬더니 정상처리됬습니다.
    분석함수에서는 min/max 을 옵티마이저가 처리를 못하는것인지 궁금합니다.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.02.19 17:02 신고  댓글주소  수정/삭제

      잘 아시겠지만 First_rows(n) 힌트와 INDEX 힌트는 다릅니다. 하지만 위의 예제에서 INDEX_DESC 힌트를 사용한다면 같은 효과를 볼 수 있습니다.

      그리고 분석함수에서는 min/max operation이 나타나지 않습니다. 수동으로 처리해 주어야 합니다. 물론 First_rows(N) 이 있기는 하지만 최악의 경우 FULL SCAN으로 처리될 수도 있기 때문에 INDEX_DESC 만이 완벽한 방법입니다.

  3. Favicon of http://tslee58@gmail.com BlogIcon 이태수 2019.03.27 14:14  댓글주소  수정/삭제  댓글쓰기

    늦게 늦게 인사드립니다.
    위의 샘플을 테스트 해보다 사소하지만 중요한 점을 찾았네요.
    SQL Developer에서 테스트 하는데 해당툴은 바인딩변수의 데이터 타입을 설정하는 기능이 없어 기본적으로 문자형으로
    인식하는듯 합니다. 따라서 바인딩 변수를 TO_NUMBER(:B1)과 같이 형변환을 하는데 이렇게 되었을때
    "WINDOW NOSORT STOPKEY"에서 STOPKEY가 적용이 되지 아니하여 부분범위처리를 하지 않고 전체범위처리를 하게되어
    최초보다 성능이 더 안좋게 나오네요.
    바인딩변수에 데이터형을 지정할 수 있는 다른 툴에서 숫자형으로 지정하니 위의 예제와 같이 정확히 나오구요.

    좋은 글 감사드립니다.


    -----------------------------------------------------------------------------------------------------
    | Id | Operation | Name | Starts | A-Rows | A-Time | Buffers |
    -----------------------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 108 |00:00:00.01 | 20972 |
    |* 1 | VIEW | | 1 | 108 |00:00:00.01 | 20972 |
    | 2 | WINDOW NOSORT | | 1 | 918K|00:00:00.91 | 20972 |
    | 3 | TABLE ACCESS BY GLOBAL INDEX ROWID| SALES | 1 | 918K|00:00:00.56 | 20972 |
    | 4 | INDEX FULL SCAN DESCENDING | SALES_IDX01 | 1 | 918K|00:00:00.13 | 2455 |
    -----------------------------------------------------------------------------------------------------

    Predicate Information (identified by operation id):
    ---------------------------------------------------

    1 - filter("TOP_SALES"<=TO_NUMBER(:V_RANK))