월 기준 이력 조회

점 이력 데이터를 지정한 월 기준으로 조회하는 쿼리의 성능 개선 사례를 살해보자.

테스트를 위해 아래와 같이 데이터를 생성하자. t1은 이력 테이블, t2는 캘린더 테이블이다.

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

-- 1 DROP TABLE t1 PURGE; DROP TABLE t2 PURGE;   CREATE TABLE t1 (cd VARCHAR2(1), dt DATE, val NUMBER);   INSERT INTO t1 VALUES ('A', DATE '2050-01-01', 1); INSERT INTO t1 VALUES ('A', DATE '2050-02-01', 2); INSERT INTO t1 VALUES ('A', DATE '2050-03-01', 3); INSERT INTO t1 VALUES ('A', DATE '2050-04-01', 1); INSERT INTO t1 VALUES ('A', DATE '2050-05-01', 2); INSERT INTO t1 VALUES ('A', DATE '2050-06-05', 3); INSERT INTO t1 VALUES ('A', DATE '2050-06-10', 1); INSERT INTO t1 VALUES ('A', DATE '2050-07-05', 2); INSERT INTO t1 VALUES ('A', DATE '2050-07-10', 3); COMMIT;   CREATE TABLE t2 AS SELECT DATE '2050-01-01' + ROWNUM - 1 AS dt FROM XMLTABLE ('1 to 365');

아래는 개선 전 쿼리다. 비효율적인 소트 머지 조인과 소트 부하로 인해 이력 건수가 많은 경우 쿼리 성능이 급격히 저하될 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69

-- 2 SELECT dt, cd, val, adt   FROM (SELECT b.dt, a.cd, a.val, a.dt AS adt              , ROW_NUMBER () OVER (PARTITION BY a.cd, b.dt ORDER BY a.dt DESC) AS rn           FROM t1 a, t2 b          WHERE b.dt >= a.dt            AND b.dt BETWEEN DATE '2050-07-01' AND DATE '2050-07-31')  WHERE rn = 1;   DT         CD VAL ADT ---------- -- --- ---------- 2050-07-01 A    1 2050-06-10 2050-07-02 A    1 2050-06-10 2050-07-03 A    1 2050-06-10 2050-07-04 A    1 2050-06-10 2050-07-05 A    2 2050-07-05 2050-07-06 A    2 2050-07-05 2050-07-07 A    2 2050-07-05 2050-07-08 A    2 2050-07-05 2050-07-09 A    2 2050-07-05 2050-07-10 A    3 2050-07-10 2050-07-11 A    3 2050-07-10 2050-07-12 A    3 2050-07-10 2050-07-13 A    3 2050-07-10 2050-07-14 A    3 2050-07-10 2050-07-15 A    3 2050-07-10 2050-07-16 A    3 2050-07-10 2050-07-17 A    3 2050-07-10 2050-07-18 A    3 2050-07-10 2050-07-19 A    3 2050-07-10 2050-07-20 A    3 2050-07-10 2050-07-21 A    3 2050-07-10 2050-07-22 A    3 2050-07-10 2050-07-23 A    3 2050-07-10 2050-07-24 A    3 2050-07-10 2050-07-25 A    3 2050-07-10 2050-07-26 A    3 2050-07-10 2050-07-27 A    3 2050-07-10 2050-07-28 A    3 2050-07-10 2050-07-29 A    3 2050-07-10 2050-07-30 A    3 2050-07-10 2050-07-31 A    3 2050-07-10   31 행이 선택되었습니다.   -------------------------------------------------------------------------------- | Id  | Operation                | Name | Starts | A-Rows | Buffers | Used-Mem | -------------------------------------------------------------------------------- |   0 | SELECT STATEMENT         |      |      1 |     31 |      16 |          | |*  1 |  VIEW                    |      |      1 |     31 |      16 |          | |*  2 |   WINDOW SORT PUSHED RANK|      |      1 |     31 |      16 |20480  (0)| -- ! |   3 |    MERGE JOIN            |      |      1 |    266 |      16 |          | |   4 |     SORT JOIN            |      |      1 |      9 |       7 | 2048  (0)| |*  5 |      TABLE ACCESS FULL   | T1   |      1 |      9 |       7 |          | |*  6 |     SORT JOIN            |      |      9 |    266 |       9 | 2048  (0)| -- ! |*  7 |      TABLE ACCESS FULL   | T2   |      1 |     31 |       9 |          | --------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      1 - filter("RN"=1)    2 - filter(ROW_NUMBER() OVER ( PARTITION BY "A"."CD","B"."DT" ORDER BY INTERNAL_FUNCTION("A"."DT") DESC               )<=1)    5 - filter("A"."DT"<=TO_DATE(' 2050-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))    6 - access("B"."DT">="A"."DT")        filter("B"."DT">="A"."DT")    7 - filter(("B"."DT">=TO_DATE(' 2050-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."DT"<=TO_DATE('               2050-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

아래 쿼리는 LAG 함수로 종료일을 생성하여 t2 테이블을 조인했다. 조인과 소트 부하가 개선되었지만 소트 영역을 b.dt >= a.dt 조건으로 스캔한 후 b.dt < a.ldt 조건으로 필터링하는 비효율이 남아 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

-- 3 SELECT b.dt, a.cd, a.val, a.dt AS adt, ldt   FROM (SELECT cd, dt, val              , LAG (dt, 1, DATE '2050-07-31' + 1) OVER (PARTITION BY cd ORDER BY dt DESC) AS ldt           FROM t1          WHERE dt <= DATE '2050-07-31') a      , t2 b  WHERE a.ldt >= DATE '2050-07-01'    AND b.dt >= a.dt    AND b.dt <  a.ldt    AND b.dt BETWEEN DATE '2050-07-01' AND DATE '2050-07-31';   DT         CD VAL ADT        LDT ---------- -- --- ---------- ---------- 2050-07-01 A    1 2050-06-10 2050-07-05 2050-07-02 A    1 2050-06-10 2050-07-05 2050-07-03 A    1 2050-06-10 2050-07-05 2050-07-04 A    1 2050-06-10 2050-07-05 2050-07-05 A    2 2050-07-05 2050-07-10 2050-07-06 A    2 2050-07-05 2050-07-10 2050-07-07 A    2 2050-07-05 2050-07-10 2050-07-08 A    2 2050-07-05 2050-07-10 2050-07-09 A    2 2050-07-05 2050-07-10 2050-07-10 A    3 2050-07-10 2050-08-01 2050-07-11 A    3 2050-07-10 2050-08-01 2050-07-12 A    3 2050-07-10 2050-08-01 2050-07-13 A    3 2050-07-10 2050-08-01 2050-07-14 A    3 2050-07-10 2050-08-01 2050-07-15 A    3 2050-07-10 2050-08-01 2050-07-16 A    3 2050-07-10 2050-08-01 2050-07-17 A    3 2050-07-10 2050-08-01 2050-07-18 A    3 2050-07-10 2050-08-01 2050-07-19 A    3 2050-07-10 2050-08-01 2050-07-20 A    3 2050-07-10 2050-08-01 2050-07-21 A    3 2050-07-10 2050-08-01 2050-07-22 A    3 2050-07-10 2050-08-01 2050-07-23 A    3 2050-07-10 2050-08-01 2050-07-24 A    3 2050-07-10 2050-08-01 2050-07-25 A    3 2050-07-10 2050-08-01 2050-07-26 A    3 2050-07-10 2050-08-01 2050-07-27 A    3 2050-07-10 2050-08-01 2050-07-28 A    3 2050-07-10 2050-08-01 2050-07-29 A    3 2050-07-10 2050-08-01 2050-07-30 A    3 2050-07-10 2050-08-01 2050-07-31 A    3 2050-07-10 2050-08-01   31 행이 선택되었습니다.   ----------------------------------------------------------------------------- | Id  | Operation             | Name | Starts | A-Rows | Buffers | Used-Mem | ----------------------------------------------------------------------------- |   0 | SELECT STATEMENT      |      |      1 |     31 |      16 |          | |   1 |  MERGE JOIN           |      |      1 |     31 |      16 |          | |   2 |   SORT JOIN           |      |      1 |      3 |       7 | 2048  (0)| -- ! |*  3 |    VIEW               |      |      1 |      3 |       7 |          | |   4 |     WINDOW SORT       |      |      1 |      9 |       7 | 2048  (0)| |*  5 |      TABLE ACCESS FULL| T1   |      1 |      9 |       7 |          | |*  6 |   FILTER              |      |      3 |     31 |       9 |          | |*  7 |    SORT JOIN          |      |      3 |     78 |       9 | 2048  (0)| -- ! |*  8 |     TABLE ACCESS FULL | T2   |      1 |     31 |       9 |          | -----------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - filter(("A"."DT"<=TO_DATE(' 2050-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "A"."LDT">TO_DATE('               2050-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))    5 - filter("DT"<=TO_DATE(' 2050-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))    6 - filter("B"."DT"<"A"."LDT")    7 - access("B"."DT">="A"."DT")        filter("B"."DT">="A"."DT")    8 - filter(("B"."DT">=TO_DATE(' 2050-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "B"."DT"<=TO_DATE('               2050-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

아래 쿼리는 이력의 일자수로 행을 복제하는 방식을 사용했다. 3번 쿼리의 비효율을 개선할 수 있다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72

-- 4 SELECT a.bdt + b.c1 - 1 AS dt, a.cd, a.val, a.dt AS adt, a.bdt, a.edt, a.edt - a.bdt AS cnt   FROM (SELECT cd, dt, val              , GREATEST (dt, DATE '2050-07-01') AS bdt              , LAG (dt, 1, DATE '2050-07-31' + 1) OVER (PARTITION BY cd ORDER BY dt DESC) AS edt           FROM t1          WHERE dt <= DATE '2050-07-31') a      , (SELECT ROWNUM AS c1           FROM XMLTABLE ('1 to 31')          WHERE ROWNUM <= DATE '2050-07-31' - DATE '2050-07-01' + 1) b  WHERE a.edt >= DATE '2050-07-01'    AND b.c1 <= a.edt - a.bdt;   DT         CD VAL ADT        BDT        EDT        CNT ---------- -- --- ---------- ---------- ---------- --- 2050-07-31 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-30 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-29 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-28 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-27 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-26 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-25 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-24 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-23 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-22 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-21 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-20 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-19 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-18 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-17 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-16 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-15 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-14 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-13 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-12 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-11 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-10 A    3 2050-07-10 2050-07-10 2050-08-01  22 2050-07-09 A    2 2050-07-05 2050-07-05 2050-07-10   5 2050-07-08 A    2 2050-07-05 2050-07-05 2050-07-10   5 2050-07-07 A    2 2050-07-05 2050-07-05 2050-07-10   5 2050-07-06 A    2 2050-07-05 2050-07-05 2050-07-10   5 2050-07-05 A    2 2050-07-05 2050-07-05 2050-07-10   5 2050-07-04 A    1 2050-06-10 2050-07-01 2050-07-05   4 2050-07-03 A    1 2050-06-10 2050-07-01 2050-07-05   4 2050-07-02 A    1 2050-06-10 2050-07-01 2050-07-05   4 2050-07-01 A    1 2050-06-10 2050-07-01 2050-07-05   4   31 행이 선택되었습니다.   -------------------------------------------------------------------------------------------------------------- | Id  | Operation                             | Name                  | Starts | A-Rows | Buffers | Used-Mem | -------------------------------------------------------------------------------------------------------------- |   0 | SELECT STATEMENT                      |                       |      1 |     31 |       7 |          | |   1 |  MERGE JOIN                           |                       |      1 |     31 |       7 |          | |   2 |   SORT JOIN                           |                       |      1 |      3 |       7 | 2048  (0)| -- ! |*  3 |    VIEW                               |                       |      1 |      3 |       7 |          | |   4 |     WINDOW SORT                       |                       |      1 |      9 |       7 | 2048  (0)| |*  5 |      TABLE ACCESS FULL                | T1                    |      1 |      9 |       7 |          | |*  6 |   SORT JOIN                           |                       |      3 |     31 |       0 | 2048  (0)| -- ! |   7 |    VIEW                               |                       |      1 |     31 |       0 |          | |*  8 |     COUNT STOPKEY                     |                       |      1 |     31 |       0 |          | |   9 |      COLLECTION ITERATOR PICKLER FETCH| XQSEQUENCEFROMXMLTYPE |      1 |     31 |       0 |          | --------------------------------------------------------------------------------------------------------------   Predicate Information (identified by operation id): ---------------------------------------------------      3 - filter("A"."EDT">=TO_DATE(' 2050-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))    5 - filter("DT"<=TO_DATE(' 2050-07-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))    6 - access(INTERNAL_FUNCTION("B"."C1")<="A"."EDT"-"A"."BDT")        filter(INTERNAL_FUNCTION("B"."C1")<="A"."EDT"-"A"."BDT")    8 - filter(ROWNUM<=31)