누적합 계산

9.2 버전에 분석 함수 기능이 추가되었지만 여전히 스칼라 서브 쿼리를 통해 누적 합을 계산하는 경우가 많다. 관련 사례를 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
DROP TABLE t2 PURGE;

CREATE TABLE t1 AS SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 1000');
CREATE TABLE t2 AS SELECT ROWNUM AS c1, 1 AS c2 FROM XMLTABLE ('1 to 1000');

아래가 문제의 쿼리다. 16,077개(=77+6000+10000)의 블록 I/O가 발생했다.

-- 2
SELECT a.c1
     , (SELECT      c2  FROM t2 x WHERE x.c1 = a.c1) AS c2
     , (SELECT SUM (c2) FROM t2 x WHERE x.c1 > a.c1) AS c2_ac
  FROM t1 a;

------------------------------------------------------
| Id  | Operation          | Name | Starts | Buffers |
------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |      77 |
|*  1 |  TABLE ACCESS FULL | T2   |   1000 |    6000 | -- C2
|   2 |  SORT AGGREGATE    |      |   1000 |   10000 | -- C2_AC
|*  3 |   TABLE ACCESS FULL| T2   |   1000 |   10000 |
|   4 |  TABLE ACCESS FULL | T1   |      1 |      77 |
------------------------------------------------------

인덱스를 추가하더라도 3,021개(=77+159+2785)의 블록 I/O가 발생한다.

-- 3
CREATE INDEX t2_x1 ON t2 (c1);

-------------------------------------------------------------------------
| Id  | Operation                            | Name  | Starts | Buffers |
-------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |       |      1 |      77 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | T2    |   1000 |     159 | -- C2
|*  2 |   INDEX RANGE SCAN                   | T2_X1 |   1000 |      90 |
|   3 |  SORT AGGREGATE                      |       |   1000 |    2785 | -- C2_AC
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| T2    |   1000 |    2785 |
|*  5 |    INDEX RANGE SCAN                  | T2_X1 |   1000 |    1539 |
|   6 |  TABLE ACCESS FULL                   | T1    |      1 |      77 |
-------------------------------------------------------------------------

아래와 같이 분석 함수를 사용하면 쉽게 누적합을 계산할 수 있다. c1 값이 현재 행보다 큰 행의 누적합이므로 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING으로 윈도우를 지정해야 한다. 20개의 블록 I/O가 발생한 것을 확인할 수 있다.

-- 4
SELECT a.c1
     , b.c2
     , b.c2_ac
  FROM t1 a
     , (SELECT c1, c2
             , SUM (c2) OVER (ORDER BY c1 ROWS BETWEEN 1 FOLLOWING AND UNBOUNDED FOLLOWING) AS c2_ac
          FROM t2) b
 WHERE b.c1(+) = a.c1;

--------------------------------------------------------
| Id  | Operation            | Name | Starts | Buffers |
--------------------------------------------------------
|   0 | SELECT STATEMENT     |      |      1 |      20 |
|*  1 |  HASH JOIN OUTER     |      |      1 |      20 |
|   2 |   TABLE ACCESS FULL  | T1   |      1 |      10 |
|   3 |   VIEW               |      |      1 |      10 |
|   4 |    WINDOW SORT       |      |      1 |      10 |
|   5 |     TABLE ACCESS FULL| T2   |      1 |      10 |
--------------------------------------------------------