점 이력 데이터를 지정한 월 기준으로 조회하는 쿼리의 성능 개선 사례를 살해보자.
테스트를 위해 아래와 같이 데이터를 생성하자. t1은 이력 테이블, t2는 캘린더 테이블이다.
아래는 개선 전 쿼리다. 비효율적인 소트 머지 조인과 소트 부하로 인해 이력 건수가 많은 경우 쿼리 성능이 급격히 저하될 수 있다.
아래 쿼리는 LAG 함수로 종료일을 생성하여 t2 테이블을 조인했다. 조인과 소트 부하가 개선되었지만 소트 영역을 b.dt >= a.dt 조건으로 스캔한 후 b.dt < a.ldt 조건으로 필터링하는 비효율이 남아 있다.
아래 쿼리는 이력의 일자수로 행을 복제하는 방식을 사용했다. 3번 쿼리의 비효율을 개선할 수 있다.
-- 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')))
-- 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')))
-- 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 |
