Oracle Data Access Pattern

Partition Access Pattern

Oracle 조인 방법 25가지

 

필자는 위의 글들을 이미 게시하였다. 그래서 Access Path Join Method는 정리가 되었고, 블로그로 배포되었으므로 그것으로 끝이라고 생각했다. 하지만 어느 개발자와 대화를 해보니 그런 생각이 짧았음을 알게 되었다.

 

 

개발자 : 위의 3가지 문서는 잘 보고 있습니다. 하지만 Access Path에 관해서는 큰 구멍이 있습니다.


필자 : ? 무슨 말인가요?


개발자 : 분석함수는 Access Path를 변경시킵니다. 즉 분석함수를 어떻게 사용하느냐에 따라 실행계획의 Operation이 변경되므로 여타의 함수와는 엄연히 다릅니다. 그러므로 수석님께서 분석함수와 관련된 Access Path를 정리해 주셨으면 합니다.


필자 : …. 프로젝트도 있고, 블로그도 관리해야 하고, 책도 써야 하고, 개인적인 공부도 해야 하는데요. 시간이 나질 않습니다. (! 말을 하자마자 실수였다는 것을 알아차렸다. 블로그 관리라니……)


개발자 : 지금 하시고 계신 네 가지 이외의 일을 해달라는 게 아닙니다. 분석함수의 실행계획을 정리하셔서 블로그에 올리시면, 그게 블로그 관리 아닌가요?


필자 : …..

 

 

이번에는 공짜가 아니다

이렇게 해서 이 글을 쓰게 되었다. 해당개발자는 언제 나에게 맥주 한잔 사기 바란다. 이런 식으로 말려들어서 글을 작성한 적이 한두번이 아니다. ^^

 

분석함수의 내부

분석함수를 이용하여 답을 잘 내었으므로 그것으로 만족하는 사람들이 있다. 답을 내는 것은 표면적인 것(문법)을 이해한 것이다. 하지만 그것만 알아서는 내부적인 매커니즘을 이해할 수 없다. 즉 분석함수를 사용함에 따라 Access Path(Operation)가 어떻게 변경되는지도 알아야 모두를 아는 것이다. 분석함수의 내부적인 Operation을 모른다면 SQL 작성은 할지 몰라도, 성능을 향상시킬 수는 없다. 오늘의 목표는 분석함수의 내부를 정복함으로써 튜닝을 가능하게 하는 것이다.

 

분석함수의 실행계획은 아래와 같이 총 8가지로 예상된다. 만약, 아래의 List 이외의 것을 발견하면 이 글을 보는 독자들을 위해 알려주기 바란다. 글이 너무 길어지므로, 이번 시간에는 8개중에 위의 4개를 알아보고자 한다. 어떤 경우에 아래의 4가지 실행계획이 발생하는지 알아보자.

 

WINDOW (SORT)  

WINDOW (SORT PUSHED RANK)

WINDOW (NOSORT)

WINDOW (NOSORT STOPKEY)   <-- 이번 시간에는 여기까지

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (CHILD PUSHED RANK)

WINDOW (IN SQL MODEL) SORT

 

환경: Oracle 11.2.0.1

테스트를 위해 테이블과 인덱스를 만든다.

 

CREATE TABLE SALES_T NOLOGGING AS SELECT * FROM SALES;

 

CREATE INDEX IX_PROD ON SALES_T( PROD_ID );

 

1. WINDOW SORT

분석함수를 사용하면 기본적으로 SORT가 발생한다. 이때 SORT를 대체할 수 있는 적절한 인덱스가 없다면 WINDOW SORT가 발생한다. 아래의 경우가 대표적이다.

 

-인덱스와 분석함수 OVER절의 컬럼이 일치하지 않을 때 발생한다.

-분석함수를 사용하고 Full Table Scan을 사용할 때 발생한다.

 

먼저 인덱스와 OVER 절의 컬럼이 일치하지 않는 경우를 살펴보자.

 

SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN

  FROM SALES_T

 WHERE PROD_ID = 30;

 

-------------------------------------------------------------------------------------------

| Id  | Operation                    | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

-------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |         |  29282 |00:00:00.18 |     424 |          |

|   1 |  WINDOW SORT                 |         |  29282 |00:00:00.18 |     424 | 1558K (0)|

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T |  29282 |00:00:00.09 |     424 |          |

|*  3 |    INDEX RANGE SCAN          | IX_PROD |  29282 |00:00:00.03 |      60 |          |

-------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("PROD_ID"=30)

 

 인덱스는 PROD_ID 로 구성되었지만, OVER절은 CUST_ID로 되어 서로 다르므로 실행계획의 Id 2번에 WINDOW SORT가 발생하였다.

 

이제 분석함수를 사용하면서 FTS(Full Table Scan)이 발생하는 경우를 보자.

 

SELECT PROD_ID,

       SUM(AMOUNT_SOLD) OVER() AS SUM_AVG

  FROM SALES_T

WHERE PROMO_ID = 33

ORDER BY CUST_ID;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |   2074 |00:00:00.04 |    4440 |          |

|   1 |  WINDOW SORT       |         |   2074 |00:00:00.04 |    4440 |  102K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |   2074 |00:00:00.04 |    4440 |          |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("PROMO_ID"=33)

 

WHERE 절이 PROD_ID가 아니라 PROMO_ID이므로 FTS가 발생하였다. 이때 분석함수를 사용하면 WINDOW SORT가 발생된다.

이때 ORDER BY절이 있더라도 별도의 SORT ORDER BY operation이 발생하지 않는다. 분석함수가 SORT를 대신하기 때문이다.  이때 SORT의 일량은 동일하다. 즉 분석함수와 ORDER BY절이 동시에 존재한다면, SORT의 일량은 분석함수가 없고 ORDER BY만 존재하는 SQL과 동일 하다. 아래의 SQL이 이 사실을 증명한다.

 

SELECT PROD_ID, AMOUNT_SOLD

  FROM SALES_T

WHERE PROMO_ID = 33

ORDER BY CUST_ID;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |   2074 |00:00:00.04 |    4440 |          |

|   1 |  SORT ORDER BY     |         |   2074 |00:00:00.04 |    4440 |  102K (0)|

|*  2 |   TABLE ACCESS FULL| SALES_T |   2074 |00:00:00.04 |    4440 |          |

---------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   2 - filter("PROMO_ID"=33)

 

위에서 설명된 SQL의 분석함수 + ORDER BY SORT 일량과 ORDER BY만 사용한 SORT의 일량은 102K로 동일함을 알 수 있다. 즉 분석함수와 ORDER BY를 같이 사용하던지, 아니면 ORDER BY만 사용하던지 간에 SORT의 부하는 동일하다는 이야기 이다.

 

2.WINDOW SORT PUSHED RANK

위에서 적당한 인덱스가 없을 때, 분석함수를 사용하면 WINDOW SORT가 발생한다고 하였다. 이번에는 WINDOW SORT에 대해 좀더 깊이 들어가 보자.

 

SELECT *

  FROM ( SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN

           FROM SALES_T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10;

 

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |     10 |00:00:00.13 |     424 |          |

|*  1 |  VIEW                         |         |     10 |00:00:00.13 |     424 |          |

|*  2 |   WINDOW SORT PUSHED RANK     |         |     11 |00:00:00.13 |     424 | 2048  (0)|

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T |  29282 |00:00:00.10 |     424 |          |

|*  4 |     INDEX RANGE SCAN          | IX_PROD |  29282 |00:00:00.03 |      60 |          |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "CUST_ID")<=10)

   4 - access("PROD_ID"=30)

 

이 글에서 처음 소개된 SQL과 같지만, 인라인뷰 외부에서 분석함수 컬럼을 filter 조건으로 사용하였다. 즉 상위 10건만 보자고 한 것이다. 이런 경우 WINDOW SORT PUSHED RANK operation이 발생하며 SORT의 부하는 제한된다. 다시 말해 RN <= 10 조건에 의해 전체를 SORT할 필요가 없이 10건의 배열을 만들고, 그 배열만 SORT 하면 된다. 이 글에서 최초로 설명된 SQL SORT 일량과 WINDOW SORT PUSHED RANK의 일량을 비교해보면 1558K 2K 로 천지차이이다. 분석함수 중에 순위를 구하는 것(RANK, DENSE_RANK, ROW_NUMBER)을 인라인뷰 외수에서 filter로 사용하면 적절한 인덱스가 없더라도 Sort의 부하는 최소화 된다는 것을 기억하자.

 

이런 사실은 분석함수를 사용하지 않더라도 동일하게 발생한다. 즉 위에서 실행된 SQL SORT량과 아래의 SORT이 동일하다. 아래는 분석함수를 사용하지 않고 ROWNUM을 사용하였다.

 

SELECT *

  FROM ( SELECT /*+ INDEX(T IX_PROD) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD

           FROM SALES_T T

          WHERE PROD_ID = 30

          ORDER BY CUST_ID)

 WHERE ROWNUM <= 10;

 

---------------------------------------------------------------------------------------------

| Id  | Operation                      | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT               |         |     10 |00:00:00.13 |     424 |          |

|*  1 |  COUNT STOPKEY                 |         |     10 |00:00:00.13 |     424 |          |

|   2 |   VIEW                         |         |     10 |00:00:00.13 |     424 |          |

|*  3 |    SORT ORDER BY STOPKEY       |         |     10 |00:00:00.13 |     424 | 2048  (0)|

|   4 |     TABLE ACCESS BY INDEX ROWID| SALES_T |  29282 |00:00:00.09 |     424 |          |

|*  5 |      INDEX RANGE SCAN          | IX_PROD |  29282 |00:00:00.03 |      60 |          |

---------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

   1 - filter(ROWNUM<=10)

   3 - filter(ROWNUM<=10)

   5 - access("PROD_ID"=30)

 

WINDOW SORT PUSHED RANK 대신에 SORT ORDER BY STOPKEY operation이 사용되었지만 하는 일과 원리는 동일하므로 SORT량도 동일하다.

 

3.WINDOW NOSORT

적절한 인덱스가 없을 때 분석함수를 사용하면 WINDOW SORT가 발생한다고 하였다. 이번에는 동일한 SQL을 실행하되 적절한 인덱스를 만들고 실행해보자.

 

CREATE INDEX PK_SALES_T ON SALES_T(PROD_ID, CUST_ID, CHANNEL_ID, TIME_ID);

 

SELECT /*+ INDEX(T PK_SALES_T) */ PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       ROW_NUMBER() OVER(ORDER BY CUST_ID) AS RN

  FROM SALES_T T

 WHERE PROD_ID = 30;

 

----------------------------------------------------------------------------------------------

| Id  | Operation                    | Name       | A-Rows |   A-Time   | Buffers | Used-Mem |

----------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT             |            |  29282 |00:00:00.34 |   28352 |          |

|   1 |  WINDOW NOSORT             |            |  29282 |00:00:00.34 |   28352 |          |

|   2 |   TABLE ACCESS BY INDEX ROWID| SALES_T    |  29282 |00:00:00.24 |   28352 |          |

|*  3 |    INDEX RANGE SCAN          | PK_SALES_T |  29282 |00:00:00.07 |     133 |          |

----------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   3 - access("PROD_ID"=30)

 

WHERE절과 OVER절의 ORDER BY에 만족하는 인덱스를 생성하였더니 SORT가 전혀 발생하지 않는다. 또한 OPERATIONWINDOW SORT에서 WINDOW NOSORT로 바뀌었다. 적절한 인덱스가 왜 필요한지 알 수 있는 대목이다. 참고로 OVER 절의 ORDER BY 구문은 PROD_ID , CUST_ID 로 바꾸어도 동일한 결과를 얻을 수 있다.

 

또 다른 경우를 보자. 분석함수를 사용하고, Full Table Scan을 사용하더라도 WINDOW NOSORT가 발생할 수 있다.

 

SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

       ROW_NUMBER() OVER(ORDER BY NULL) AS RN

  FROM SALES_T ;

 

---------------------------------------------------------------------------------

| Id  | Operation          | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

---------------------------------------------------------------------------------

|   0 | SELECT STATEMENT   |         |    918K|00:00:04.81 |    4898 |          |

|   1 |  WINDOW NOSORT   |         |    918K|00:00:04.81 |    4898 |          |

|   2 |   TABLE ACCESS FULL| SALES_T |    918K|00:00:01.99 |    4898 |          |

---------------------------------------------------------------------------------

 

FTS를 사용했지만 분석함수에 ORDER BY절이 없으므로 SORT는 발생하지 않는다. 무작위로 10건을 추출하는 경우에 사용할 수 있다. 물론 ROWNUM을 사용해도 동일한 효과를 낼 것이다.

 

4.WINDOW NOSORT STOPKEY

인덱스의 컬럼순서와 분석함수 OVER절에 존재하는 ORDER BY 컬럼순서가 동일하며, Ranking Family 함수를 인라인뷰 외부에서 filter 조건으로 사용할 때 발생한다.

 

SELECT *

  FROM ( SELECT PROD_ID, CUST_ID, TIME_ID, CHANNEL_ID, AMOUNT_SOLD,

                ROW_NUMBER() OVER(ORDER BY PROD_ID) AS RN

           FROM SALES_T

          WHERE PROD_ID = 30 )

 WHERE RN <= 10;

 

--------------------------------------------------------------------------------------------

| Id  | Operation                     | Name    | A-Rows |   A-Time   | Buffers | Used-Mem |

--------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT              |         |     10 |00:00:00.01 |       6 |          |

|*  1 |  VIEW                         |         |     10 |00:00:00.01 |       6 |          |

|*  2 |   WINDOW NOSORT STOPKEY    |         |     10 |00:00:00.01 |       6 |          |

|   3 |    TABLE ACCESS BY INDEX ROWID| SALES_T |     11 |00:00:00.01 |       6 |          |

|*  4 |     INDEX RANGE SCAN          | IX_PROD |     11 |00:00:00.01 |       4 |          |

--------------------------------------------------------------------------------------------

 

Predicate Information (identified by operation id):

---------------------------------------------------

 

   1 - filter("RN"<=10)

   2 - filter(ROW_NUMBER() OVER ( ORDER BY "PROD_ID")<=10)

   4 - access("PROD_ID"=30)

 

WINDOW NOSORT STOPKEY는 부분범위처리에 최적화되어 있다

분석함수를 Rownum처럼 사용하였기 때문에 WINDOW NOSORT STOPKEY가 발생하였다. 1건을 더 읽었지만 비효율은 느낄 수 없는 정도이다. WINDOW NOSORT STOPKEY와 이미 위에서 설명된 WINDOW SORT PUSHED RANK와의 차이는 단순히 SORT의 유무뿐만이 아니다. Block I/O량도 차이가 난다. WINDOW SORT PUSHED RANK는 전체 건을 읽은 후에 다 버리고 10건만 출력한다. 이에 반해 WINDOW NOSORT STOPKEY 11건을 읽고 한 건만 버리므로 부분범위처리의 효율이 좋다. 2번과 4번의 Block I/O량을 비교해보기 바란다. 몇 십 배나 차이가 난다..    

 

참고:  Ranking Family 분석함수란 RANK, DENSE_RANK, ROW_NUMBER를 말한다.

 

결론


WINDOW (SORT) :

-FTS를 사용하거나 인덱스가 적절하지 않은 경우 발생 

WINDOW (SORT PUSHED RANK) :

-위와 같으나 Ranking Family를 사용해야 하며, 인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

WINDOW (NOSORT) :

-인덱스가 적절한 경우나, OVER절에 ORDER BY가 없는 Ranking Family를 사용한 경우 발생

WINDOW (NOSORT STOPKEY) :

-위와 같으나 Ranking Family를 사용해야 하며, 인라인뷰 외부에서 분석함수를 filter로 사용하는 경우 발생

 

실행계획만 보고도 SQL을 어느 정도 짐작 할 수 있다라고 주장하는 튜닝의 고수가 있다. 필자는 때에 따라 그럴 수도, 아닐 수도 있다고 생각한다. 하지만 최소한 분석함수에 관해서는 어느 정도 가능하다고 생각한다. 분석함수의 내부를 이해했다면 충분히 그럴 수 있다. 이제 위의 결론을 이용하여, 실행계획만 보고도 어떤 분석함수를 어떤 상황에서 사용한 것인지 짐작 할 수 있겠는가? 대충 짐작 할 수 있다면 성공한 것이다.

 

다음 글(2 )에서 소개될 내용

WINDOW (BUFFER)

WINDOW (BUFFER PUSHED RANK)

WINDOW (CHILD PUSHED RANK)

WINDOW (IN SQL MODEL) SORT

Posted by extremedb

댓글을 달아 주세요

  1. salvationism 2011.02.07 10:22  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 보고 갑니다. 새해 복 많이 받으세요 ^^

  2. 혈기린 2011.02.07 15:42  댓글주소  수정/삭제  댓글쓰기

    그 개발자한테 블로그 독자들이 술한잔 사야 할거 같네요 ㅋㅋ
    책도 다시 집필하신다니 기대 됩니다 ㅎㅎ

  3. 라튜니 2011.02.07 17:37  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 포스팅 감사합니다. 벌써부터 2편이 기대되네요~
    새해 복 많이 받으세요~

  4. Favicon of https://scidb.tistory.com BlogIcon extremedb 2011.02.08 08:58 신고  댓글주소  수정/삭제  댓글쓰기

    모두들 새해 복많이 받으시고, 소원성취하세요.

  5. Ejql 2011.02.09 00:53  댓글주소  수정/삭제  댓글쓰기

    정말 유용한 글입니다. 감사합니다.

  6. greenluck 2011.02.09 15:56  댓글주소  수정/삭제  댓글쓰기

    좋은 글 보면서 많은 것을 배우고 있어 항상 감사드립니다. ^^

    그런데 "3. WINDOW NOSORT"부분에서 적절한 인덱스를 생성한 후에 WINDOW NOSORT이 발생하는 부분을 설명해 주셨는데 WHERE조건절이 equal이 아닌 range로 하게 되면 결합인덱스인 경우에 OVER절에는 맨 처음 칼럼이 있는 경우에만 WINDOW NOSORT로 되는 것 같습니다.

    create table test_1
    as
    select lpad(level, 2, '0') rn1
    , lpad(mod(level, round(dbms_random.value(1,3))), 2, '0') rn2
    from dual
    connect by level <= 10
    ;
    create index ix_test_1 on test_1 (rn1, rn2)
    ;

    select rn1
    , rn2
    , rank() over (order by rn2, rn1) t_rank
    from test_1
    where rn1 = '00'
    ;

    Execution Plan

    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=1 Bytes=8)
    1 0 WINDOW (NOSORT) (Cost=2 Card=1 Bytes=8)
    2 1 INDEX (RANGE SCAN) OF 'IX_TEST_1' (INDEX) (Cost=1 Card=1 Bytes=8)

    select rn1
    , rn2
    , rank() over (order by rn2, rn1) t_rank
    from test_1
    where rn1 > '00'
    ;

    Execution Plan

    --------------------------------------------------------------------------------
    0 SELECT STATEMENT Optimizer=FIRST_ROWS (Cost=2 Card=4 Bytes=32)
    1 0 WINDOW (SORT) (Cost=2 Card=4 Bytes=32)
    2 1 INDEX (RANGE SCAN) OF 'IX_TEST_1' (INDEX) (Cost=1 Card=4 Bytes=32)

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

      greenluck님 반갑습니다.

      첫번째 SQL의 경우 인덱스의 컬럼순서와 over절의 order by 컬럼순서가 일치하지 않음에도 NOSORT 가 나옵니다. 그 이유는 order by rn2, rn1 라고 정의했더라도 ,실제로는 order by rn2 로 한것과 같기 때문입니다. 즉 where 조건때문에 rn1을 sort할 필요가 없는 것이죠. 그래서 nosort가 나온것 입니다.

      두번째 SQL에서 sort가 발생한 이유는 인덱스의 컬럼순서와 order by절의 컬럼순서가 맞지 않기 때문입니다. order by rn1, rn2 로 순서를 맞춰주시면 nosort로 바뀔 것 입니다.

      감사합니다.

  7. 야함나르 2011.02.09 16:24  댓글주소  수정/삭제  댓글쓰기

    좋은 글 잘 보고 갑니다 ^^....

    그런데 nosort stopkey 가 오라클 버전 언제부터 지원하는지 궁금하네요.

    저 같은경우 9.2.0.7 이라서 그러는지 그냥 nosort로 풀려버리네요. 흠..

    index 는 parent_prd_no, prd_no 로 생성했고
    아래 쿼리에서

    SELECT PRD_NO, parent_prd_no
    FROM (
    select
    PRD_NO, parent_prd_no, prd_nm
    --, sum(prd_no) over ()
    , row_number() over (order by parent_prd_no, prd_no) rn
    From temp_1 a
    where parent_prd_no = 111111
    )
    where rn <= 5


    그냥
    WINDOW (NOSORT)
    로 풀리는군요 ..

  8. Favicon of http://www.topreplicawatchesstore.com/ BlogIcon replica dolce gabbana 2011.09.20 12:42  댓글주소  수정/삭제  댓글쓰기

    제 기억으로 Oracle 10.1에서도 nosort stopkey 가 안되었던 것으로 기억합니다. 즉 10.2부터 가능합니다.

    감사합니다.

  9. Favicon of http://www.hotefashion.com/louis-vuitton-c-18.html BlogIcon louis vuitton shoulder bags 2011.09.20 12:42  댓글주소  수정/삭제  댓글쓰기

    그냥
    WINDOW (NOSORT)
    로 풀리는군요 ..