JOIN-050

가격 구간이 겹치는지 보는 아침 점검이 820블록을 읽는 데 7초 가까이 걸린다

기초SELECT한빛유통200 / 700

상황

한빛유통은 최저가 연동 배치가 매일 04시에 상품마다 그날 판매가를 정하고 가격 이력에 새 구간을 연다. 지난달 배치가 재처리되면서 같은 날 구간이 두 번 들어간 상품이 생겼고, 그 상품의 주문은 반품 화면에서 두 줄로 보였다. 그래서 데이터팀이 매일 06시에 겹친 구간을 찾는 점검을 넣었다. 결과는 몇 줄뿐인데 매번 7초 가까이 걸리고, 같은 시각에 도는 정산 배치를 밀어낸다. 조민우는 "읽는 블록이 820개뿐이라 더 줄일 데가 없다"라고 한다.

테이블과 인덱스

T_ITM_PRC_LOG 상품 판매가 이력 (이 문제에서 쓰는 파생 테이블, 선분이력): ITM_ID · ST_DTM (구간 시작, DATE) · ED_DTM (다음 구간의 시작 시각, 이 시각 미만까지) · SLE_PRC
2023-01-01부터 날마다 상품 100개에 한 구간씩: 73,106행 · 410블록 · 상품마다 약 731행 · 지금 구간의 ED_DTM은 DATE '9999-12-31'
인덱스 IX_ITM_PRC_LOG (ITM_ID, ST_DTM): PK나 유니크가 아닌 일반 인덱스라서 같은 시작 시각이 두 번 들어가도 막지 못한다 · 통계 최신
지난달 재처리로 상품 6개의 2024-10-21 구간이 한 번 더 들어가 있다

원본 SQL

sql

SELECT a.ITM_ID, a.ST_DTM, a.ED_DTM, b.ST_DTM AS ST_DTM2, b.ED_DTM AS ED_DTM2
  FROM T_ITM_PRC_LOG a, T_ITM_PRC_LOG b
 WHERE b.ITM_ID = a.ITM_ID
   AND b.ROWID <> a.ROWID
   AND b.ST_DTM < a.ED_DTM
   AND a.ST_DTM < b.ED_DTM

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |      1 |        |     12 |00:00:06.84 |     820 |           |
|*  1 |  HASH JOIN         |               |      1 |    133K|     12 |00:00:06.84 |     820 | 4318K (0) |
|   2 |   TABLE ACCESS FULL| T_ITM_PRC_LOG |      1 |  73106 |  73106 |00:00:00.01 |     410 |           |
|   3 |   TABLE ACCESS FULL| T_ITM_PRC_LOG |      1 |  73106 |  73106 |00:00:00.01 |     410 |           |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("B"."ITM_ID"="A"."ITM_ID")
       filter(("B"."ST_DTM"<"A"."ED_DTM" AND "A"."ST_DTM"<"B"."ED_DTM" AND "B".ROWID<>"A".ROWID))

[결과]  12행 (상품 6개의 겹친 구간 6쌍이 앞뒤를 바꿔 두 번씩)  ·  CPU used by this session 6.71초

문제

테이블을 두 번 한 번씩만 읽었는데(820블록) 7초 가까이 걸리는 까닭을 해시 조인의 build와 probe로 설명하시오.
같은 상품의 구간을 시작 시각 순으로 늘어놓았을 때 무엇만 비교하면 되는지 생각해, 테이블을 한 번 읽는 점검 SQL로 고치시오. 구간이 끊긴 곳(빈 시간)도 함께 찾도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 상품마다 시작 시각 순으로 늘어놓고, 바로 다음 구간의 시작과 내 끝을 비교한다 (테이블 한 번)
SELECT ITM_ID, ST_DTM, ED_DTM, NEXT_ST,
       CASE WHEN NEXT_ST IS NULL THEN '마지막 구간이 닫혀 있음'
            WHEN ED_DTM > NEXT_ST THEN '겹침'
            ELSE '끊김' END AS CHK
  FROM (SELECT ITM_ID, ST_DTM, ED_DTM,
               LEAD(ST_DTM) OVER (PARTITION BY ITM_ID ORDER BY ST_DTM, ED_DTM) AS NEXT_ST
          FROM T_ITM_PRC_LOG)
 WHERE ED_DTM <> NVL(NEXT_ST, DATE '9999-12-31')

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

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |      6 |00:00:00.09 |     410 |           |
|*  1 |  VIEW               |               |      1 |  73106 |      6 |00:00:00.09 |     410 |           |
|   2 |   WINDOW SORT       |               |      1 |  73106 |  73106 |00:00:00.07 |     410 | 2694K (0) |
|   3 |    TABLE ACCESS FULL| T_ITM_PRC_LOG |      1 |  73106 |  73106 |00:00:00.01 |     410 |           |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ED_DTM"<>NVL("NEXT_ST",TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[결과]  6행 (상품 6개의 2024-10-21 구간이 '겹침') · 끊김 0건 · 닫힌 마지막 구간 0건

모범 답안 서술

읽은 블록은 820(410 × 2)이지만 시간은 1번 줄 HASH JOIN에서 쓰였다. 조인 키가 ITM_ID 하나뿐이라 상품 하나의 약 731행이 해시 테이블의 같은 자리에 모인다. probe 쪽 한 행은 그 731행을 하나씩 꺼내 filter의 부등호 세 개를 따져 본다. 상품 100개 × 731 × 731, 곧 5,343만 번 넘게 비교하고 그중 12쌍만 남겼다. 블록이 아니라 CPU가 일량이고(CPU 6.71초), 이력이 하루에 100행씩 늘면 비교 횟수는 제곱으로 는다. 구간이 제대로 이어져 있다면, 같은 상품의 구간을 시작 시각 순으로 늘어놓았을 때 각 구간의 끝은 바로 다음 구간의 시작과 같아야 한다. 그러므로 모든 쌍을 맞대 볼 필요 없이 바로 다음 구간 하나와만 비교하면 된다. 내 끝이 다음 시작보다 늦으면 겹침이고, 이르면 끊김(빈 시간)이다. 정렬했을 때 멀리 떨어진 구간과 겹친다면 그 사이의 다음 구간과도 반드시 겹치므로, 겹친 곳이 빠지지 않는다. LEAD(ST_DTM) OVER (PARTITION BY ITM_ID ORDER BY ST_DTM, ED_DTM)으로 다음 구간의 시작을 옆 칸에 가져오면 테이블을 한 번 읽고 한 번 정렬하는 것으로 끝난다. 마지막 구간은 다음 시작이 없으니 9999-12-31과 비교해 닫혀 있지 않은지도 함께 본다. 시간은 6.84초 → 0.09초, Buffers는 820 → 410으로 준다. 결과는 겹친 쌍을 두 번씩 내던 12행 대신, 겹침이 시작된 구간 6행이다.

해설

끊김은 자기 조인으로 찾기 어렵다: 겹침은 두 행이 만나야 보이지만, 끊김은 '다음 행이 없다'는 사실이라 자기 조인으로 찾으려면 NOT EXISTS를 또 붙여야 한다. 다음 행을 옆에 가져오면 두 가지가 한 비교로 끝난다.
점검보다 막는 쪽이 싸다: 재처리가 같은 시작 시각을 다시 넣지 못하게 (ITM_ID, ST_DTM)에 유니크 제약을 두면 이번 같은 겹침은 넣는 순간 막힌다. 다만 시작 시각이 다른 겹침(끝을 잘못 닫은 경우)은 제약으로 못 막으므로 점검은 남긴다.
이 점검이 중요한 까닭: 반품 화면처럼 '시작 시각이 주문 시각 이전인 구간 중 가장 늦은 한 건'을 고르는 조회는 구간이 겹치지 않는다는 전제에서만 맞다. 겹치면 줄이 불거나 어느 쪽 가격이 나올지 정해지지 않는다.
해시 조인이 느린 까닭이 build 크기나 TEMP가 아니라 '키 하나에 행이 몰려서'일 때는 Used-Mem도 작고 Buffers도 작다. A-Time과 CPU를 같이 봐야 드러난다 (교재 06화 §4).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황