Range Partition 적용 테이블의 MIN/MAX 처리시의 성능저하 현상

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 처리를 하여야 비효율을 없앨수 있다. 하지만 이경우도 최적은 아니므로 특정파티션을 지정하여야 단 하나의 파티션만 엑세스 한다는 점을 기억하자.