TunA :: TunA

Oracle/Tuning2020. 3. 16. 11:03

월 기준 이력 조회

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

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

-- 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');

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

-- 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 조건으로 필터링하는 비효율이 남아 있다.

-- 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번 쿼리의 비효율을 개선할 수 있다.

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

저작자표시 비영리 변경금지

'Oracle > Tuning' 카테고리의 다른 글

뷰 병합이 동작하지 않는 사례 (0)	2020.04.07
조인에 따른 사용자 함수의 동작 (0)	2020.03.21
INDEX 힌트의 세 가지 방식 (0)	2020.02.23
NOT EXISTS 서브 쿼리가 UNNEST되지 않는 사례 (0)	2020.02.21
BATCH NL 조인과 TABLE ACCESS BY INDEX ROWID BATCHED 오퍼레이션 (0)	2020.02.18

Posted by 정희락_

«이전 1 ··· 4 5 6 7 8 9 10 ··· 256 다음»

TunA

월 기준 이력 조회

'Oracle > Tuning' 카테고리의 다른 글

카테고리

최근에 올라온 글

글 보관함

달력

링크

티스토리툴바

« » 2024.5
일	월	화	수	목	금	토
			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