JOIN-058

가격 조회가 느리다며 상품가격 테이블에 종료일 칸을 추가하자는 변경 요청서가 올라왔다

실전DDL유통208 / 700

상황

본사 레거시 유통의 정보기술팀이 변경 요청서를 올렸다. "상품가격(CF05)은 적용일자만 있어서 판매일의 가격을 구하는 SQL이 복잡하고 느리다. 종료일(END_DT) 칸을 추가해 선분이력으로 바꾸고, 가격 등록 화면이 새 가격을 넣을 때 앞 구간의 종료일을 닫게 하자." 근거로 든 것은 월 1회 도는 「연간 정가 매출」 배치가 6초 넘게 걸린다는 것이다. 선배는 "CF05를 쓰는 SQL 셋과 등록 화면을 다 보고, 요청서를 받을지 SQL에서 풀지 숫자와 대가로 정리해 와"라고 했다.

테이블과 인덱스

CF05 상품가격 (점이력): 157행 · 5블록 · CF05_PK (ITM_NO, UPRC_KN_CD, APL_DT) · 리프 블록 하나 · 판매가(UPRC_KN_CD = '10') 127행 = 상품 89개의 '20220101' + 38개 상품의 '20230615' · 나머지 30행은 발주단가
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_NO 앞 8자리가 판매일자 · 2023년 548,213줄(테이블의 절반)
CF05를 쓰는 곳: [Q1] POS 결제 화면의 현재가(하루 3만 번), [Q2] 연간 정가 매출 배치(월 1회), [Q3] 영수증 재출력의 당시 정가(영수증 1장에 1 ~ 4줄, 하루 500번), [Q4] 가격 등록 화면(가격 개정 때, 1년에 몇 번)
통계 최신

원본 SQL

sql

-- [Q1] POS 현재가
SELECT ITM_UPRC
  FROM CF05
 WHERE ITM_NO = :itm AND UPRC_KN_CD = '10'
   AND APL_DT = (SELECT MAX(APL_DT) FROM CF05
                  WHERE ITM_NO = :itm AND UPRC_KN_CD = '10' AND APL_DT <= :today);

-- [Q2] 연간 정가 매출 (월 · 상품별)
SELECT SUBSTR(d.SLE_NO, 1, 6) AS ym, d.ITM_NO,
       SUM(d.SLE_QTY * (SELECT p.ITM_UPRC
                          FROM CF05 p
                         WHERE p.ITM_NO = d.ITM_NO AND p.UPRC_KN_CD = '10'
                           AND p.APL_DT = (SELECT MAX(x.APL_DT) FROM CF05 x
                                            WHERE x.ITM_NO = d.ITM_NO AND x.UPRC_KN_CD = '10'
                                              AND x.APL_DT <= SUBSTR(d.SLE_NO, 1, 8)))) AS list_amt
  FROM CF11 d
 WHERE d.SLE_NO >= '20230101' AND d.SLE_NO < '20240101'
 GROUP BY SUBSTR(d.SLE_NO, 1, 6), d.ITM_NO;

-- [Q3] 영수증 재출력: Q2 와 같은 서브쿼리를 영수증 한 장(1 ~ 4줄)에 쓴다
-- [Q4] 가격 등록: INSERT INTO CF05 (ITM_NO, UPRC_KN_CD, APL_DT, ITM_UPRC) VALUES (...);

-- 변경 요청서의 DDL
ALTER TABLE CF05 ADD (END_DT VARCHAR2(8) DEFAULT '99991231');
CREATE INDEX CF05_X01 ON CF05 (ITM_NO, UPRC_KN_CD, END_DT);

실행계획 학습용 예시

xplan

[Q1] Buffers 3 (INDEX RANGE SCAN (MIN/MAX) 1 + INDEX UNIQUE SCAN 1 + 테이블 1)
[Q3] 영수증 한 장(4줄) Buffers 12 + 판매상품 4

[Q2]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |      1 |        |   1056 |00:00:06.21 |    1653K|
|   1 |  TABLE ACCESS BY INDEX ROWID    | CF05    |    548K|      1 |    548K|00:00:03.10 |    1644K|
|*  2 |   INDEX UNIQUE SCAN             | CF05_PK |    548K|      1 |    548K|00:00:02.31 |    1096K|
|   3 |    SORT AGGREGATE               |         |    548K|      1 |    548K|00:00:01.12 |     548K|
|   4 |     FIRST ROW                   |         |    548K|      1 |    548K|00:00:00.93 |     548K|
|*  5 |      INDEX RANGE SCAN (MIN/MAX) | CF05_PK |    548K|      1 |    548K|00:00:00.81 |     548K|
|   6 |  HASH GROUP BY                  |         |      1 |   1056 |   1056 |00:00:06.21 |    1653K|
|*  7 |   TABLE ACCESS FULL             | CF11    |      1 |    548K|    548K|00:00:00.09 |    8204 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"=:B1 AND "P"."UPRC_KN_CD"='10' AND "P"."APL_DT"=(SELECT MAX("X"."APL_DT") FROM "CF05" "X" ...))
   5 - access("X"."ITM_NO"=:B1 AND "X"."UPRC_KN_CD"='10' AND "X"."APL_DT"<=SUBSTR(:B2,1,8))
   7 - filter(("D"."SLE_NO">='20230101' AND "D"."SLE_NO"<'20240101'))

문제

Q2가 느린 까닭을 1번 ~ 5번 줄의 Starts로 설명하시오. 스칼라 서브쿼리 캐시가 왜 도움이 되지 않았는지, END_DT가 생기면 이 일량이 줄어드는지도 쓰시오.
요청서를 받았을 때 Q1과 Q3이 얼마나 나아지는지, Q4와 데이터 정합성에는 어떤 대가가 생기는지 숫자와 함께 쓰시오.
테이블 구조를 바꾸지 않고 같은 단순함을 얻는 방법을 DDL과 고친 Q2로 작성하고, 요청서에 대한 결론을 쓰시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

-- 요청서의 ALTER TABLE 은 받지 않는다. 같은 모양(시작일 ~ 다음 적용일 전)을 뷰로 준다
-- 종료일을 저장하지 않고 다음 적용일에서 계산하므로 겹치거나 끊길 수 없다
CREATE OR REPLACE VIEW V_CF05_SEG AS
SELECT ITM_NO, UPRC_KN_CD, ITM_UPRC,
       APL_DT AS FR_DT,
       LEAD(APL_DT, 1, '99991231') OVER (PARTITION BY ITM_NO, UPRC_KN_CD ORDER BY APL_DT) AS TO_DT
  FROM CF05;

-- [Q2] 판매 줄마다 묻지 않고 구간과 한 번에 해시 조인한다
SELECT SUBSTR(d.SLE_NO, 1, 6) AS ym, d.ITM_NO,
       SUM(d.SLE_QTY * p.ITM_UPRC) AS list_amt
  FROM CF11 d, V_CF05_SEG p
 WHERE d.SLE_NO >= '20230101' AND d.SLE_NO < '20240101'
   AND p.ITM_NO = d.ITM_NO
   AND p.UPRC_KN_CD = '10'
   AND SUBSTR(d.SLE_NO, 1, 8) >= p.FR_DT
   AND SUBSTR(d.SLE_NO, 1, 8) <  p.TO_DT
 GROUP BY SUBSTR(d.SLE_NO, 1, 6), d.ITM_NO;

-- [Q1] [Q3] 그대로 둔다 (3블록, 영수증 한 장 12블록) · [Q4] 그대로 INSERT 한 줄

개선 후 실행계획 학습용 예시

xplan

[Q2]
------------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |            |      1 |        |   1056 |00:00:00.48 |    8209 |           |
|   1 |  HASH GROUP BY         |            |      1 |   1056 |   1056 |00:00:00.48 |    8209 | 1311K (0) |
|*  2 |   HASH JOIN            |            |      1 |  27410 |    548K|00:00:00.36 |    8209 | 1236K (0) |
|   3 |    VIEW                | V_CF05_SEG |      1 |    127 |    127 |00:00:00.01 |       5 |           |
|   4 |     WINDOW SORT        |            |      1 |    127 |    127 |00:00:00.01 |       5 |           |
|*  5 |      TABLE ACCESS FULL | CF05       |      1 |    127 |    127 |00:00:00.01 |       5 |           |
|*  6 |    TABLE ACCESS FULL   | CF11       |      1 |    548K|    548K|00:00:00.09 |    8204 |           |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"="D"."ITM_NO")
       filter((SUBSTR("D"."SLE_NO",1,8)>="P"."FR_DT" AND SUBSTR("D"."SLE_NO",1,8)<"P"."TO_DT"))
   5 - filter("UPRC_KN_CD"='10')
   6 - filter(("D"."SLE_NO">='20230101' AND "D"."SLE_NO"<'20240101'))

모범 답안 서술

Q2가 느린 까닭: 1번 ~ 5번 줄의 Starts가 548K, 곧 2023년 판매상품 548,213줄과 같다. 판매 한 줄마다 스칼라 서브쿼리가 최신 적용일을 구하고(5번 줄), 그 적용일로 CF05_PK를 다시 찾고(2번 줄), 단가를 읽었다(1번 줄). 줄마다 3블록씩 1,644K블록이 전체 1,653K의 99%다. 스칼라 서브쿼리 캐시는 서브쿼리가 쓰는 바깥 칸 값을 열쇠로 결과를 기억하는데, 여기서는 d.ITM_NO와 d.SLE_NO다. SUBSTR로 잘라 쓰더라도 열쇠는 영수증마다 다른 SLE_NO 전체라서 같은 열쇠가 거의 다시 나오지 않아 캐시가 적중하지 않았다. END_DT가 생겨도 Q2를 지금처럼 판매 줄마다 BETWEEN 서브쿼리로 쓰면 부르는 횟수는 548,213번 그대로다. 느린 것은 점이력이라는 구조가 아니라 판매 줄마다 묻는 방식이다. 요청서를 받으면: Q1은 END_DT = '99991231' 등치 한 번으로 3블록 → 2블록, Q3은 영수증 한 장 12블록 → 8블록 정도다. 157행짜리 테이블이라 처음부터 일량이 작아 얻을 것이 거의 없다. 대가는 크다. Q4 가격 등록이 새 행을 넣을 때마다 같은 상품, 같은 단가종류의 앞 구간을 찾아 종료일을 닫아야 하고(INSERT 한 줄이 INSERT와 UPDATE 두 문장이 된다), 이 프로그램이나 수작업 정정이 한 번이라도 닫기를 빠뜨리면 구간이 겹치거나 끊긴다. 그러면 '구간은 하나'를 전제로 하는 조회가 두 줄을 내거나 아무것도 못 찾으므로, 겹침과 끊김을 매일 점검해야 한다. CF05를 읽는 다른 프로그램도 새 칸을 알아야 한다. 구조를 바꾸지 않는 방법: LEAD(APL_DT, 1, '99991231') OVER (PARTITION BY ITM_NO, UPRC_KN_CD ORDER BY APL_DT)로 종료일을 계산하는 뷰 V_CF05_SEG를 만든다. SQL은 요청서가 말한 선분이력처럼 FR_DT <= 판매일 < TO_DT로 단순해지고, 종료일을 저장하지 않고 다음 적용일에서 계산하므로 겹치거나 끊길 수가 없다. UPRC_KN_CD = '10'은 뷰의 파티션 키라서 뷰 안으로 들어간다(5번 줄). 뷰를 읽을 때마다 정렬하지만 157행, 5블록이다. 고친 Q2는 판매가 127행으로 구간을 만들어 build로 올리고 2023년 판매상품을 한 번 흘려 맞춘다. Buffers는 1,653K → 8,209, 시간은 6.21초 → 0.48초로 준다. 결론: 요청서는 반려하고 뷰 V_CF05_SEG와 고친 Q2를 회신한다. Q1, Q3, Q4는 그대로 둔다. 요청서가 해결하려던 '복잡함'은 뷰가, '느림'은 판매 줄마다 묻지 않는 SQL이 해결한다.

해설

종료일을 저장할 때가 있다: 이력이 수십만 행이고 화면이 특정 시점의 한 건을 인덱스로 찾아야 한다면, 저장된 종료일과 (키, 종료일) 인덱스가 값어치를 한다(가격이 날마다 바뀌는 이력처럼). 그때는 등록 프로그램의 닫기와 겹침 점검을 대가로 함께 설계한다. CF05처럼 수백 행이면 계산이 더 싸고 안전하다.
캐시 열쇠를 줄이는 다른 정답: 판매일로 먼저 묶어 (상품, 판매일) 단위로 수량을 합친 뒤(548,213줄 → 약 3만 2천 줄) 스칼라 서브쿼리에 판매일을 넘기면 부르는 횟수가 열에 하나 이하로 준다. 그래도 수만 번이라 구간 해시 조인보다 무겁다.
뷰의 한계: 구간 조건(FR_DT, TO_DT)은 뷰 안으로 들어가지 않는다. 바깥에서 거르면 다음 적용일 계산이 달라지기 때문이다. 이력이 커지면 이 점이 비용이 된다.
반려 회신에는 Q2의 전후 숫자와 함께 '등록 화면에 닫기 로직을 넣지 않아도 된다'는 점을 적는다. 운영 쪽에서는 이것이 가장 큰 이득이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황