부제 : min/max값을 안전하고 빠르게 구하는 방법


최종일자, 최종순번을 구하기 위한 전통적인 방법은 index desc 힌트와 rownum = 1 조합이었다. 하지만 이것은 대단히 위험한 방법이다. 왜냐하면 튜닝을 하기 전에 값이 맞아야 하며, 성능튜닝은 그 이후의 문제이기 때문이다. 위의 방법은 인덱스의 구성컬럼이 변경 혹은 삭제되거나, 인덱스명이 바뀌면 max 값을 구하지 못한다. 즉 성능을 향상시키기 위해 값이 틀릴 수 있는 가능성을 열어놓은 것이다. 이런 방법은 어떤 이유로도 받아들여져서는 안 된다. 나 또한 예전에 이런 방법을 사용했지만 이는 필자의 명백한 잘못이었다.

 

올바른 값을 얻어야 하고 성능도 충족해야 하므로 오라클은 first_row(min/max) operation을 내놓았다. 따라서 우리는 index_desc + rownum 대신에 first_row(min/max)을 사용해야 한다. 그런데 항상 first_row(min/max)를 사용해야 할까? first_row(min/max)가 비효율적인 경우는 index_desc + rownum 조합을 생각해 볼 수 있다. 하지만 인덱스가 변경 및 삭제될 때 성능이 느려질지언정 답이 틀리면 안 된다. 만약 max가 아닌 잘못된 값으로 update 되었다고 상상해보라. 큰일이다. 원복시키기도 어렵다. update 가 여러번 되었을 수 있기 때문이다.

 

환경 :Oracle11g R2

 

CREATE INDEX ix_cust_channel_time ON SALES (CUST_ID, CHANNEL_ID, TIME_ID)  ;

CREATE INDEX ix_cust_time_channel ON SALES (CUST_ID, TIME_ID, CHANNEL_ID)  ;
CREATE INDEX ix_time_cust_channel ON SALES (TIME_ID, CUST_ID, CHANNEL_ID)  ;

 

인덱스와 where 조건이 완벽할 때

 

SELECT /*+ gather_plan_statistics INDEX(s ix_cust_channel_time) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust   --30777 대입

   AND channel_id = 2;

 

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

| Id  | Operation                    | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT             |                      |      1 |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE              |                      |      1 |      1 |00:00:00.01 |       3 |

|   2 |   FIRST ROW                  |                      |      1 |      1 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CUST_CHANNEL_TIME |      1 |      1 |00:00:00.01 |       3 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

 

인덱스가 CUST_ID + CHANNEL_ID + TIME_ID로 되어 있는 경우는 문제가 없다. first_row(min/max) operation을 사용할 수 있고 비효율이 없기 때문에 값이 틀려질 수 있는 index_desc + rownum을 사용해선 안 된다.

 

where 조건에 인덱스의 중간 컬럼이 빠졌을 때  

 

SELECT /*+ gather_plan_statistics INDEX(S IX_CUST_TIME_CHANNEL) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust   --30777 대입

   AND channel_id = 2;

 

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

| Id  | Operation                    | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT             |                      |      1 |      1 |00:00:00.01 |       3 |

|   1 |  SORT AGGREGATE              |                      |      1 |      1 |00:00:00.01 |       3 |

|   2 |   FIRST ROW                  |                      |      1 |      1 |00:00:00.01 |       3 |

|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_CUST_TIME_CHANNEL |      1 |      1 |00:00:00.01 |       3 |

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

 

Predicate Information (identified by operation id):

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

   3 - access("CUST_ID"=:V_CUST)

       filter("CHANNEL_ID"=2)

 

인덱스가 CUST_ID + TIME_ID + CHANNEL_ID 로 되어 있는 경우를 보자. 인덱스의 중간컬럼이 where절에 빠져있지만  CUST_ID의 선택도가 워낙 좋으므로 문제가 되지 않는다. first_row(min/max) operation을 그대로 사용하면 된다.

 

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우

 

SELECT /*+ gather_plan_statistics INDEX(S IX_TIME_CUST_CHANNEL) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust  --30777 대입

   AND channel_id = 2;

 

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

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.02 |     755 |

|   1 |  SORT AGGREGATE             |                      |      1 |      1 |00:00:00.02 |     755 |

|   2 |   FIRST ROW                 |                      |      1 |      1 |00:00:00.02 |     755 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.02 |     755 |

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

 

Predicate Information (identified by operation id):

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

   3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

인덱스가 TIME_ID + CUST_ID + CHANNEL_ID로 구성되어 있을때 인덱스의 선두 컬럼이 where 조건에서 빠졌다. 그로 인해 Index full scan이 발생하여 쓸모 없는 752블록을 Scan하였다. 즉 인덱스를 끝부분부터 계속 scan하다가 운 좋게 755 블록을 scan해보니 cust_id = 30777 channel_id  = 2을 만족하는 값을 모두 처리한 것이다. 운이 나쁘면 인덱스를 모조리 읽어야 할 수도 있다.

 

서브쿼리나 인라인뷰를 이용하여 집합을 추가하자는 의견에 대해

인덱스의 선두 컬럼이 where 조건에서 빠지는 경우는 강제로 집합을 추가하자는 의견이 있다. 아래의 SQL이 그것이다.

 

SELECT  TIME_ID

  FROM  ( SELECT /*+ LEADING(C) INDEX_DESC(S IX_TIME_CUST_CHANNEL)  */  S.time_id

            FROM sales S,

                 (SELECT TRUNC(SYSDATE) - LEVEL + 1 AS time_id

                   FROM DUAL

                CONNECT BY LEVEL <= 7300 ) C

           WHERE S.cust_id = :v_cust   --30777

             AND S.channel_id = 2

             AND S.time_id = C.time_id )

 WHERE ROWNUM = 1;

 

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

| Id  | Operation                       | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT                |                      |      1 |      1 |00:00:00.02 |     512 |

|*  1 |  COUNT STOPKEY                  |                      |      1 |      1 |00:00:00.02 |     512 |

|   2 |   NESTED LOOPS                  |                      |      1 |      1 |00:00:00.02 |     512 |

|   3 |    VIEW                         |                      |      1 |   3484 |00:00:00.02 |       0 |

|   4 |     CONNECT BY WITHOUT FILTERING|                      |      1 |   3484 |00:00:00.01 |       0 |

|   5 |      FAST DUAL                  |                      |      1 |      1 |00:00:00.01 |       0 |

|*  6 |    INDEX RANGE SCAN DESCENDING  | IX_TIME_CUST_CHANNEL |   3484 |      1 |00:00:00.01 |     512 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter(ROWNUM=1)

   6 - access("S"."TIME_ID"=INTERNAL_FUNCTION("C"."TIME_ID") AND "S"."CUST_ID"=:V_CUST AND

              "S"."CHANNEL_ID"=2)

 

이렇게 하니 Scan한 블럭수가 1/3 정도 줄어들었다. 하지만 불필요한 조인이 3484번이나 발생하였다. 이것이 최적은 아니다. 또한 명시적으로 max값을 보장하게 작성된 SQL도 아니다.

 

Index_ss 힌트를 사용했다. 하지만……

위의 예에서 보듯이 인덱스의 선두 컬럼이 조건절에 없을때 이빨이 빠진 집합을 추가하는 것과 first_row(min/max)를 사용하는 것은 둘다 비효율적이다. 그러므로 index_desc + rownum을 사용하되 값이 바뀌지 않도록 해야 한다. 그런데 인덱스의 첫 번째 컬럼이 조건 절에서 빠졌으므로 index_ss_desc + rownum을 사용해야 한다. 이것이 가능할까? SQL을 바꾸지 않으면 불가능하다. 아래의 SQL을 보자.

 

SELECT /*+ gather_plan_statistics INDEX_SS(s ix_time_cust_channel) */

       MAX (time_id)

  FROM sales s

 WHERE cust_id = :v_cust     --30777

   AND channel_id = 2;

 

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

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.02 |     755 |

|   1 |  SORT AGGREGATE             |                      |      1 |      1 |00:00:00.02 |     755 |

|   2 |   FIRST ROW                 |                      |      1 |      1 |00:00:00.02 |     755 |

|*  3 |    INDEX FULL SCAN (MIN/MAX)| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.02 |     755 |

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

 

Predicate Information (identified by operation id):

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

   3 - filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

Min/Max Index Skip Scan을 동시에 사용할 수 없다

min 혹은 max 함수를 사용했을 때 Oracle9i 버전과는 달리 10g 11g에서는 index skip scan을 사용할 수 없다. 힌트를 추가해도 마찬가지이다. 아래의 10053 trace를 보자.

 

***************************************

SINGLE TABLE ACCESS PATH

  Single Table Cardinality Estimation for SALES[S]

  ColGroup (#1, Index) IX_TIME_CUST_CHANNEL

    Col#: 2 3 4    CorStregth: 185.95

  ColGroup Usage:: PredCnt: 2  Matches Full:  Partial:

  Table: SALES  Alias: S

    Card: Original: 918843.000000  Rounded: 33  Computed: 32.54  Non Adjusted: 32.54

kkofmx: index filter:"S"."CUST_ID"=:B1

 

kkofmx: index filter:"S"."CHANNEL_ID"=2

 

  Access Path: index (Min/Max)

    Index: IX_TIME_CUST_CHANNEL

    resc_io: 3.00  resc_cpu: 21564

    ix_sel: 1.000000  ix_sel_with_filters: 0.000035

 ***** Logdef predicate Adjustment ******

 Final IO cst 0.00 , CPU cst 50.00

 ***** End Logdef Adjustment ******

 ***** Logdef predicate Adjustment ******

 Final IO cst 0.00 , CPU cst 50.01

 ***** End Logdef Adjustment ******

    Cost: 5.28  Resp: 5.28  Degree: 1

  Best:: AccessPath: IndexRange

  Index: IX_TIME_CUST_CHANNEL

         Cost: 5.28  Degree: 1  Resp: 5.28  Card: 1.00  Bytes: 0

***************************************

 

힌트를 사용했지만 Index Skip Scan은 고려조차 되지 않는다. 위의 Trace를 보면 "first row(Min/Max)가 가능하다면 Index Skip Scan을 고려하지 않는 로직이 10g 11g의 옵티마이져에 존재한다라고 추론할 수 있다. 인덱스와 where 절이 일치하지 않는 상태라 하더라도 비효율적인 index full scan (Min/Max)에 만족할 수는 없다. 바로 이럴 때 index_desc rownum 조합을 답이 틀려질 수 없도록 사용하면 된다.

 

아래처럼 max 함수를 제거하면 Index Skip Scan을 사용할 수는 있다.

 

SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */

       time_id

  FROM sales s

 WHERE cust_id = :v_cust  --30777

   AND channel_id = 2

   AND ROWNUM = 1;

 

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

| Id  | Operation                   | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT            |                      |      1 |      1 |00:00:00.01 |     264 |

|*  1 |  COUNT STOPKEY              |                      |      1 |      1 |00:00:00.01 |     264 |

|*  2 |   INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.01 |     264 |

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

 

Predicate Information (identified by operation id):

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

   1 - filter(ROWNUM=1)

   2 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

       filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

Max를 없애면 index skip scan을 사용할 수 있다. 하지만.....
블록수가 755에서 264 1/3으로 줄어들었다. 하지만 인덱스가 수정 및 삭제되면 답이 틀릴 수 있으므로 위험하긴 마찬가지 이다. 따라서 다음의 SQL처럼 사용해야 한다.

 

안정적이고 성능을 고려한 SQL  

 

SELECT MAX(time_id)

  FROM  ( SELECT /*+ INDEX_SS_DESC(S IX_TIME_CUST_CHANNEL) */ time_id

            FROM sales S

           WHERE cust_id = :v_cust                                             --30777

             AND channel_id = 2

           ORDER BY time_id DESC)

 WHERE ROWNUM = 1; 

 

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

| Id  | Operation                     | Name                 | Starts | A-Rows |   A-Time   | Buffers |

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

|   0 | SELECT STATEMENT              |                      |      1 |      1 |00:00:00.01 |     264 |

|   1 |  SORT AGGREGATE               |                      |      1 |      1 |00:00:00.01 |     264 |

|*  2 |   COUNT STOPKEY               |                      |      1 |      1 |00:00:00.01 |     264 |

|   3 |    VIEW                       |                      |      1 |      1 |00:00:00.01 |     264 |

|*  4 |     INDEX SKIP SCAN DESCENDING| IX_TIME_CUST_CHANNEL |      1 |      1 |00:00:00.01 |     264 |

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

 

Predicate Information (identified by operation id):

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

   2 - filter(ROWNUM=1)

   4 - access("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2)

       filter(("CUST_ID"=:V_CUST AND "CHANNEL_ID"=2))

 

ORDER BY를 사용했지만 인덱스의 영향으로 SORT를 하지 않으므로 성능저하도 없다. 또한 인라인뷰 내에서 ORDER BY를 사용하고 외부에서 ROWNUM을 사용했기 때문에 인덱스가 수정 및 삭제되더라도 성능이 느려질 뿐 값이 틀려질 수는 없다. 위의 SQL에서 마지막에 max 함수를 사용한 이유는 where조건에 만족하는 건수가 없더라도 null을 출력해야하기 때문이다. 앞으로 index_desc + rownum 조합을 사용할 것이라면 위의 방법을 사용하길 바란다.
 

결론

1. Index_desc + rownum을 사용하지 말고 first_row(min/max)를 사용하라

2. 1번이 비효율적인 경우에만 index_desc(혹은 index_ss_desc) + order by를 사용하고 뷰로 감싸라.
    그리고 뷰 외부에서 rownum을 사용하라.

3. first_row(min/max)를 사용할 수 있는 환경에서는 index skip scan을 사용할 수 없다. 꼭 사용하려면 집계 함수를 제거하라.

이렇게 하면 성능과 안정성을 동시에 고려할 수 있다.

 

글의 배경
이런 이야기를 하는 이유는 비판적 사고의 필요성 때문이다. 개발자에게 인라인뷰와 Order by가 없는 Index_desc + Rownum의 위험성을 설명해주었더니 나에게 책을 가져온다. 나는 본적이 없지만 아주 좋은 SQL 튜닝 책이라고 한다. 그것도 어려운 영문 책이다. 열심히 공부하는 사람임에 틀림없다. 하지만 개발자의 한마디 때문에 그사람의 인상이 바뀌어 버렸다. 그 한마디는 이 책에 Index_desc + Rownum을 사용하라고 되어있습니다.” 였다. 그것이 얼마나 위험한 것인지 여러번 증명하고 설득해 보았으나 맘을 바꾸기는 불가능 하였다. 이래서는 곤란하다. 책을 성경이나 불경처럼 여기고, 저자를 종교의 교주로 여겨서는 안 된다. 이론은 반론이 증명되면 폐기될 수 있다. 하지만 가치관이 개입된 믿음이나 신념은 좀처럼 바뀌지 않는다. 어떠한 증거를 내놓아도 그렇다. 신념은 종교생활에 사용했으면 한다.


책의 내용 중에 잘못된 것이 있으니 나쁜 책이라고 말하는게 아니다. 필자의 서적을 포함해서 모든 책의 내용은 틀릴 수 있다. 정작 나쁜 것은 책이나 저자가 종교화될 때이다. 그런 무 비판적 종교는 이공계 사람에게 치명적이다. 비판적 사고는 엔지니어와 과학자의 버팀목이자 과학기술을 발전시키는 핵심이기 때문이다. 널리 알려진 과학 논쟁인 쿤과 포퍼의 대결에서도 이러한 언급은 드러난다. 두명 모두 비판적 사고는 반드시 필요하다고 하였다. 다만 시기의 문제일 뿐이다.

답답한 마음에 글을 올려보았다. 앞으로 이런 글을 다시 쓰고 싶지 않다.

"신앙은 믿음으로 이루져야한다. 하지만 과학은 비판과 증명으로 이루어져야 한다." 


Posted by extremedb
,

  대부분의 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
,

PAGE 처리 할때나 아니면 부분범위 처리를 할때 우리는 TOP SQL 을 많이 사용한다.
오늘은 rownum 을 이용한 TOP N건 처리시 몇가지 함정이 있으므로  정확한 사용법에 대해서 알아보고자 한다.
먼저  환경을 만들기 위하여 테이블및 인덱스를 생성 한다.
환경 : ORACLE 11.1.0.6

create table scott.big_emp as                --> BIG_EMP 생성   
select rownum as empno, a.ename, a.job, a.mgr, a.hiredate, a.sal + rownum as sal, a.comm, a.deptno
  from scott.emp a,
       (select level as no from dual connect by level <= 2000) b
;

create index scott.ix_emp_01 on scott.big_emp (deptno, sal) --> deptno, sal 로 구성된 결합 인덱스 생성
;

create table scott.new_dept as            --> NEW_DEPT 생성
select a.* , a.deptno as page_count    --> 추가적으로 page_count  컬럼생성
  from scott.dept a
;

alter table scott.new_dept add                                         --> PK 및 UNIQUE 인덱스 생성
( constraint pk_new_dept primary key (deptno) using index );

begin                                                                           --> 통계정보 생성
    dbms_stats.gather_table_stats('scott', 'big_emp', cascade => true);
    dbms_stats.gather_table_stats('scott', 'new_dept', cascade => true);
end;


 EMP 와 DEPT 를 이용하여 테이블및 인덱스를 생성 하였고 new_dept 테이블에는 부서별로 PAGE COUNT 라는 컬럼이 추가적으로 생성 되었다.
아래의 SQL 을 보자.

select /*+ gather_plan_statistics */ e.*
  from  (select /*+ index_desc(e IX_EMP_01) */
                    e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname
           from big_emp e,
                  new_dept d
         where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc) e
where rownum < :v_page_count             --> :v_page_count  에 숫자 20을 대입한다.
 ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

20번 부서에 해당하는 사원중에서 급여가 큰순으로 20건만 보겠다는 의미이다.

------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |      1 |     20 |00:00:00.01 |       8 |
|   2 |   VIEW                          |             |      1 |     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |      1 |     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |     20 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------
 

위 PLAN 상의 A-Rows 에 나타나듯이 부서번호 20번에 해당하는 모든건을 엑세스및 조인한것이 아니라
 정확히 BIG_EMP 쪽을 20 건만 scan 하였다.
이것이 COUNT STOPKEY 의 위력이다.
즉 BIG_EMP 테이블에 deptno = 20 조건을 만족하는 건이 아무리 많더라도 20 건만 읽고 끝낸다는 것이다.

 하지만 rownum < :v_page_count 조건대신에 부서별로 PAGE COUNT 를 테이블에 저장 하고
그 값을 이용하여 TOP SQL 을 작성 해야 한다면 이야기가 달라진다.
아래의 SQL 을 보면 부서별로 PAGE COUNT 를 지정 하고 있다.

select deptno, page_count
  from new_dept;

<결과>
DEPTNO PAGE_COUNT
------ ----------
    10         10
    20         20
    30         30
    40         40

20번 부서의 경우 20 건만 보겠다는 의미이다.
이제 NEW_DEPT 테이블의 PAGE_COUNT 값으로 TOP SQL 을 작성해서 실행 해보자.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc) a
where rownum < a.page_count ;

select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

-------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | Starts | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   1 |  COUNT                           |             |      1 |     20 |00:00:00.01 |    6269 |
|*  2 |   FILTER                         |             |      1 |     20 |00:00:00.01 |    6269 |
|   3 |    VIEW                          |             |      1 |  10000 |00:00:00.09 |    6269 |
|   4 |     NESTED LOOPS                 |             |      1 |  10000 |00:00:00.07 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |      1 |  10000 |00:00:00.04 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |      1 |  10000 |00:00:00.01 |      29 |
-------------------------------------------------------------------------------------------------
 
 어이 없게도 20번 부서에 해당하는 전체건(10000 건)을 다 SCAN 하였다.
COUNT STOPKEY Operation 대신에 FILTER Operation 이 사용되었기 때문이다.
아래처럼 ROWNUM 조건을 인라인뷰 내로 밀어 넣어도 결과는 마찬가지 이다.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
            and rownum <= d.page_count            
          order by e.sal desc)
;
select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------
|   1 |  VIEW                            |             |  10071 |  7898   (1)|     20 |00:00:00.01 |    6269 |
|   2 |   COUNT                          |             |        |            |     20 |00:00:00.01 |    6269 |
|*  3 |    FILTER                        |             |        |            |     20 |00:00:00.01 |    6269 |
|   4 |     NESTED LOOPS                 |             |  10071 |  7898   (1)|  10000 |00:00:00.11 |    6269 |
|   5 |      TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  6 |       INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   7 |      TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|  10000 |00:00:00.08 |    6267 |
|*  8 |       INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|  10000 |00:00:00.05 |      29 |
--------------------------------------------------------------------------------------------------------------

 

그렇다면  이런 경우에 어떻게 TOP 처리를 할수 있겠는가?
이 문제는 ROWNUM 에 대한 기본을 알면 해결된다.
TOP N건 처리시에 ROWNUM 과의 비교대상은 반드시 상수(혹은 변수) 이어야 한다. 
그렇지 않다면 TOP 처리는 실패한다.
아래의 SQL은 이 문제를 해결하였다.

select /*+ gather_plan_statistics */ *
  from  (select /*+ index_desc(e IX_EMP_01) */
                e.EMPNO, e.ENAME, e.SAL, e.DEPTNO, d.dname, d.page_count
           from big_emp e,
                new_dept d
          where e.deptno = d.deptno
            and d.deptno = 20   
          order by e.sal desc)
where rownum <= (select page_count from new_dept where deptno = 20)  ;

select * from table(dbms_xplan.display_cursor(null,null,'advanced allstats last'));

-------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|*  1 |  COUNT STOPKEY                  |             |        |            |     20 |00:00:00.01 |      10 |
|   2 |   VIEW                          |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   3 |    NESTED LOOPS                 |             |  10071 |  7898   (1)|     20 |00:00:00.01 |       8 |
|   4 |     TABLE ACCESS BY INDEX ROWID | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  5 |      INDEX UNIQUE SCAN          | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
|   6 |     TABLE ACCESS BY INDEX ROWID | BIG_EMP     |  10071 |  7897   (1)|     20 |00:00:00.01 |       6 |
|*  7 |      INDEX RANGE SCAN DESCENDING| IX_EMP_01   |  10071 |    27   (0)|     20 |00:00:00.01 |       3 |
|   8 |   TABLE ACCESS BY INDEX ROWID   | NEW_DEPT    |      1 |     1   (0)|      1 |00:00:00.01 |       2 |
|*  9 |    INDEX UNIQUE SCAN            | PK_NEW_DEPT |      1 |     0   (0)|      1 |00:00:00.01 |       1 |
-------------------------------------------------------------------------------------------------------------


 PLAN 에서 보는것과 같이 서브쿼리를 사용하여 PAGE COUNT 를 상수화 하였다.
즉  rownum <= (서브쿼리) 형태가 되면 COUNT STOPKEY Operation 을 정상적으로 사용할수 있다.
"같은 테이블을 반복하여 읽지마라." 라는 튜닝의 기본적인 원칙이 깨지는 순간이다.
하지만 모든 규칙은 예외가 있음을 기억해야 한다.

결론 :
위에서 보는 바와 같이 COUNT STOPKEY 와 FILTER Operation 은 천당과 지옥의 차이이다.
몇천만건 혹은 몇억건 이상인 환경에서  FILTER  Operation 이 나온다면?
더이상 그 프로그램은 사용할수 없을것이다.
TOP SQL 처리시 주의사항은 ROWNUM 과 비교시 항상 상수(혹은변수)와 비교하거나 서브쿼리등을 이용하여 상수화 한후에 비교 하여야 한다는 것이다.

Posted by extremedb
,

얼마전에 필자는 한 지인으로 부터 페이징 처리가 소용이 없을것 같은 쿼리를 봐달라는 요청을 받았다.
SQL 을 보니 WHERE 절에 대해서는 인덱스가 적절하게 잡혀 있었으나 ORDER BY 절에 대해서는
인덱스로 해결될수 있는 성격의 쿼리가 아니었다.
다시말해 ORDER BY 절 대로 인덱스를 생성할 경우 WHERE 절이 다치는 경우가 종종 있는데 그 SQL 이
그런경우 였다..
그 지인은 웹환경에서 결과건수가  1000 건 이상이 될수도 있는 쿼리 임에도 불구하고 "ORDER BY 절 때문에 부분범위 처리가 되지 않으니 페이징 처리가 필요없다"   는 주장이 었다.
얼핏보면 전체범위가 될수 밖에 없으니 맞는말 같지만 그말은 페이징처리 (Oracle 의 Rownum) 의 특성을 모르는데서 기인한다.
페이지 처리나 TOP SQL 등은 인덱스 상황이나 ORDER BY 상황 등의 여부에 따라서 하느냐 안하는냐를 결정하는것이 아니다.
ROWNUM 처리는 무조건 하는것이 이득이다.
그이유는 3가지이다.

1.전체건을 client 로 다가져온뒤에 다버리고 첫번째 페이지만 보여주는것은 비효율적이다.
  DB 입장에서도 전체건을 fetch 하는 비효율을 범했고 client 측에서도 filtering 해서 첫화면만 보여주는 Logic이
  추가되어야 하기 때문이다.

2.전체건을 다가져오게되면 DB 에서 페이지 처리되어 첫번째 화면의 데이터만 가져오는경우와 비교해보면
  네트웍의 전송량이 많아진다.

3.인덱스가 없는 ORDER BY 에 대해서 페이지 처리(ROWNUM 처리)를 하면 전체범위에 대하여 SORT 를
  수행하지 않고 해당 페이지건만 SORT 한다.

여기서는 1, 2번에 대해서는 논하지 않고  3번문제에 대해서만 논한다.
그러면 ORDER BY 절에 관련된 인덱스도 없는데 어떻게 해당건만 SORT 를 할수 있을까?
그이유는 ORDER BY + ROWNUM  작업은 ROWNUM 이 없는 ORDER BY 작업과는 구현로직이 완전히 다르다는데 있는 것이다.
아래는 ORDER BY + ROWNUM 과 ROWNUM 이 없는 ORDER BY 와의 차이점을 잘보여준다.

테이블 건수가 100만건이고 가장큰값 MAX 10 개를 찾는걸로 가정하면

select ...
from   (select * from T ORDER BY unindexed_column)
where ROWNUM <= 10;

첫번째로 위의 ORDER BY + ROWNUM <= 10 작업은 5단계로 나뉜다.

1. 맨처음 10 건을 읽어서 SORT 한후 배열에 저장한다.
2. 11건 째부터는 테이블의 값과 배열의 값을 비교한다.-->테이블의 값과 배열에서 값이 가장 작은값과
   큰지 작은지 비교한다.
3. 비교후 작으면 버린다. --> 이경우 추가작업 없음.
4. 비교후 크면 기존의 배열에서 MIN 인건을 버리고 새로 찾은건을 10 개 내에서만 SORT 하여 배열에서
   자신의  위치를 찾아서  적재한다.  
5. 2~4 번을 100 만번 반복한다.


select ...
from T
ORDER BY unindexed_column;

두번째로 ORDER BY 만 하는작업은 위의 첫번쨰 예제에서 1~ 3번에 해당하는 작업이 없다.

1. 1~3번 작업(버리는건)이 없으므로 10 건만 SORT 하는것이 아니라 배열에 있는 전체건에 대해서
   SORT 하여 자신의 위치를 찾아서 적재한다.
2. 1번을 100 만번 반복한다.

위의 가설을 증명하기위한 예제가 아래에 있다.
먼저  from 절의 테이블 T 는 어떤 테이블이라도 상관은 없으나 대용량 일수록 차이가 크다.
또한 order by 절의 컬럼은 인덱스에 없어야 한다.(있으면  sort order by 가 되지 않는다.)
그리고 테스트를 위하여 PL/SQL 이 필요하다.

1.먼저 trace 나 10046 이벤트를 활성화 한다.

2. ORDER BY + ROWNUM 조합 테스트

select ...
bulk collect into ...
from   (select * from T ORDER BY unindexed_column)
where ROWNUM <= 10;


3. ORDER BY ONLY 테스트

select ...
bulk collect into ... limit 10
from T
ORDER BY unindexed_column;

4. 2개의 Tkprof 보고서를 비교해보면 아래처럼 실행시간은 물론이고 sort order by 시 메모리 사용량 차이가 엄청난걸 알수 있다.

1) ORDER BY + ROWNUM 보고서
Rows              Row Source Operation
-------    -------------------------------------------------------
      10      COUNT STOPKEY (cr=27065 r=26550 w=0 time=9537102 us)
... 이하생략

2) ORDER BY ONLY 보고서
Rows              Row Source Operation
-------    -------------------------------------------------------
      10      SORT ORDER BY (cr=27065 r=45303 w=31780 time=29061743 us)
... 이하생략


결론 :
첫번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 10건만 SORT 한다.(그나마 버리는건은 SORT 가 없다)
두번째 경우는 건건이 100 만번 테이블을 읽으면서 최대 백만건을 SORT 한다.
이 두가지의 차이는 어떤경우에서든 확연히 들어난다는걸 기억하자.

Reference : Effective Oracle by Design

Posted by extremedb
,

Min/Max 처리시 인덱스가 있을경우 대부분의 Plan 이 자동으로 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 처리가 된다.
위의 처리가 지원 되면서 부터는 전통적인 튜닝 방법인 INDEX_DESC 힌트 사용후에 WHERE 절에 ROWNUM = 1 을 추가하는 방식이 더이상 필요없는걸까?
모든규칙에는 예외가 있다는 말을 기억하자.
파티션이 되어있는 테이블은 PLAN 상에 INDEX RANGE SCAN (MIN/MAX) - FIRST ROW 로 처리가 되어
이상이 없는것 처럼 보이지만 비효율이 있다.
심지어 INDEX_DESC + ROWNUM = 1 조합도 비효율이 있다.
아래의 스크립트를 보자.

아래는 전형적인 MAX 일자를 구하는 SQL 이다.
계좌번호별로의 특정일자보다 작은일자중에 MAX 일자를 구하는 로직이다.
인덱스는 계좌번호 + 거래일자 로 되어있고 해당테이블은 거래일자기준으로 월별로 RANGE 파티션이 적용되어 있다.
물론 인덱스는 Global 이 아니다.
 

explain plan for
select max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
Plan hash value: 654469635
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Rows  | Bytes | Cost (%CPU)| Pstart| Pstop |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                     |     1 |    23 |    34   (0)|       |       |
|   1 |  SORT AGGREGATE               |                     |     1 |    23 |            |       |       |
|   2 |   PARTITION RANGE ITERATOR    |                     |   128 |  2944 |    34   (0)|    67 |     1 |
|   3 |    FIRST ROW                  |                     |   128 |  2944 |    34   (0)|       |       |
|   4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |   128 |  2944 |    34   (0)|    67 |     1 |
----------------------------------------------------------------------------------------------------------

해석 : 뭔가 특별히 이상할것이 없는 PLAN  이다. 즉 예측 실행계획으로는 정확한 정보를 알수가 없다.
       단지 Rows 컬럼과 Pstart/Pstop 컬럼이 좀 이상하긴 하다.

그러면 위의 쿼리를 다른방식으로 좀더 자세히 살펴보자.
SQL TRACE 를 사용해도 되지만 여기서는 dbms_xplan.display_cursor 로 실행된 SQL 의 Plan을 구한다.  

1. 원본쿼리

select /*+ gather_plan_statistics  */
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729';

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));


 
Plan hash value: 654469635
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |       |       |      1 |      91 |
|   2 |   PARTITION RANGE ITERATOR    |                     |      1 |    67 |     1 |     11 |      91 |
|   3 |    FIRST ROW                  |                     |     67 |       |       |     11 |      91 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |     67 |    67 |     1 |     11 |      91 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("B"."계좌번호"='1234567890' AND "B"."거래일자"<'20080729')
  
해석 : Plan 테이블의 Starts 컬럼을 보면 min/max 구하는 작업을 모든파티션마다 67번 반복하였다.
       Pstart, Pstop 컬럼에서도 보듯이 67 개의 파티션을 모두 엑세스 하였다.
       그결과 buffers 컬럼에 91 블럭이 나왔다  
  
  
  
2. 1번쿼리에서 index_desc 힌트와 where 절에 rownum = 1 을 추가한다.(전통적인 튜닝방법) 

select /*+ gather_plan_statistics index_desc(b) */ --> 힌트추가
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    rownum = 1;                                 --> where 절 추가

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));

Plan hash value: 4244920012
 
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
----------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE                |                     |      1 |       |       |      1 |       6 |
|*  2 |   COUNT STOPKEY                |                     |      1 |       |       |      1 |       6 |
|   3 |    PARTITION RANGE ITERATOR    |                     |      1 |    67 |     1 |      1 |       6 |
|*  4 |     INDEX RANGE SCAN DESCENDING| PK_일별계좌거래내역 |      2 |    67 |     1 |      1 |       6 |
----------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM=1)
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729')
 
해석 : Pstart, Pstop 컬럼을 보면 67 개의 파티션을 모두 엑세스 한거 같지만 rownum = 1 이 추가되어 있으므로 Starts 컬럼을 보면 min/max 구하는 작업을 단 두번만 하였으므로  buffers 컬럼에 6 블럭이 나왔고
Starts 컬럼으로 예상해 보면 단 두개의 파티션만을 엑세스 하였다.
하나의 파티션만 엑세스 해야 하지만 Index Range Scan 시에 1 row 를 더 Scan 하는것과 같은 이치라고 할수 있다.
   
 
3. 특정파티션만 access 하게 쿼리를 b.거래일자 >= '20070701' 추가함. (최적의 방법)

select /*+ gather_plan_statistics index(b) */
       max(거래일자)
from   일별계좌거래내역 b
where  b.계좌번호 = '1234567890'
and    b.거래일자 < '20080729'
and    b.거래일자 >= '20080701';           --> 파티션을 특정월로 고정함

select plan_table_output
from table(dbms_xplan.display_cursor(null,null,'typical ALLSTATS LAST'));


 
Plan hash value: 161631393
 
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name                | Starts | Pstart| Pstop | A-Rows | Buffers |
---------------------------------------------------------------------------------------------------------
|   1 |  SORT AGGREGATE               |                     |      1 |       |       |      1 |       3 |
|   2 |   PARTITION RANGE SINGLE      |                     |      1 |    67 |    67 |      1 |       3 |
|   3 |    FIRST ROW                  |                     |      1 |       |       |      1 |       3 |
|*  4 |     INDEX RANGE SCAN (MIN/MAX)| PK_일별계좌거래내역 |      1 |    67 |    67 |      1 |       3 |
---------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   4 - access("B"."NMS_LN_ACNO"='1234567890' AND "B"."CAM_CLC_BAS_DT"<'20080729' AND
              "B"."CAM_CLC_BAS_DT">='20080701')
 
해석 : Pstart, Pstop 컬럼에서 보듯이 단하나의 파티션(67번째 파티션) 에서 min/max 작업을 하였다.
       그결과 buffers 컬럼에 3 블럭이 나왔다.


4 결론
min/max - first row Plan 은 index_desc + rownum 과 성능상 같을수 있으나 파티션이 적용된 테이블이면서 Local 인덱스를 사용하는경우 전체파티션에 대하여 min/max - First Row 처리를 하므로 index_desc + rownum 처리를 하여야 비효율을 없앨수 있다. 하지만 이경우도 최적은 아니므로 특정파티션을 지정하여야 단 하나의 파티션만 엑세스 한다는 점을 기억하자.  

Posted by extremedb
,