누적합 계산

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

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

1 2 3 4 5 6

-- 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가 발생했다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

-- 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가 발생한다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

-- 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가 발생한 것을 확인할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- 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 | --------------------------------------------------------