JOIN-048

고객별 마지막 주문 요약에 PK를 붙인 다음 날부터 야간 적재가 ORA-00001로 멈춘다

기초INSERT한빛유통198 / 700

상황

CRM 화면의 「마지막 주문」 칸은 야간 배치가 채우는 요약 테이블을 읽는다. 배치는 요약 테이블을 비우고(TRUNCATE) 고객마다 마지막 주문 한 건을 다시 넣는다. 지난달 CRM팀이 "고객이 두세 줄씩 보인다"라고 해서 DBA가 요약 테이블에 PK를 붙였다. 그다음 날부터 배치가 ORA-00001로 멈추고, 칸은 비어 있다. 업무 규칙은 "마지막 주문은 주문일이 가장 늦은 주문, 같은 날이면 주문번호가 큰 주문"이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · ORD_DT는 전부 자정 (시분초 없음) · 고객 2,000명이 500건씩 · ORD_SEQ는 유일하다
고객마다 주문이 있는 날은 219일이고 하루에 2 ~ 3건씩이다. 마지막 주문일에 주문이 2건인 고객은 1,435명, 3건인 고객은 565명이다
T_CUS_LAST_ORD 고객별 마지막 주문 요약 (이 문제에서 쓰는 파생 테이블): CUS_ID · LAST_ORD_SEQ · LAST_ORD_DT · LAST_ORD_AMT · BAS_DT · 지난달 붙인 PK_T_CUS_LAST_ORD (CUS_ID) · 통계 최신

원본 SQL

sql

INSERT INTO T_CUS_LAST_ORD (CUS_ID, LAST_ORD_SEQ, LAST_ORD_DT, LAST_ORD_AMT, BAS_DT)
SELECT o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_AMT, TRUNC(SYSDATE)
  FROM T_ORD_BIG o,
       (SELECT CUS_ID, MAX(ORD_DT) AS MAX_DT
          FROM T_ORD_BIG
         GROUP BY CUS_ID) m
 WHERE o.CUS_ID = m.CUS_ID
   AND o.ORD_DT = m.MAX_DT

실행계획 학습용 예시 · 행 수는 실데이터로 계산

xplan

[SELECT 부분만 떼어 돌린 실행계획]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |   4565 |00:00:00.43 |   11652 |          |
|*  1 |  HASH JOIN            |           |      1 |   2000 |   4565 |00:00:00.43 |   11652 | 1321K (0)|
|   2 |   VIEW                |           |      1 |   2000 |   2000 |00:00:00.24 |    5826 |          |
|   3 |    HASH GROUP BY      |           |      1 |   2000 |   2000 |00:00:00.24 |    5826 | 1497K (0)|
|   4 |     TABLE ACCESS FULL | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
|   5 |   TABLE ACCESS FULL   | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("O"."CUS_ID"="M"."CUS_ID" AND "O"."ORD_DT"="M"."MAX_DT")

[배치 로그]  ORA-00001: unique constraint (SQLP.PK_T_CUS_LAST_ORD) violated

문제

고객 2,000명인데 4,565행이 나오는 까닭을 데이터와 조인 조건으로 설명하시오. 실행계획에서 같은 테이블을 몇 번 읽는지도 쓰시오.
업무 규칙대로 고객마다 한 행을 넣고, T_ORD_BIG은 한 번만 읽도록 적재 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객별로 한 번 묶으면서 (주문일, 주문번호)가 가장 늦은 한 건의 값을 꺼낸다
INSERT INTO T_CUS_LAST_ORD (CUS_ID, LAST_ORD_SEQ, LAST_ORD_DT, LAST_ORD_AMT, BAS_DT)
SELECT CUS_ID,
       MAX(ORD_SEQ) KEEP (DENSE_RANK LAST ORDER BY ORD_DT, ORD_SEQ),
       MAX(ORD_DT),
       MAX(ORD_AMT) KEEP (DENSE_RANK LAST ORDER BY ORD_DT, ORD_SEQ),
       TRUNC(SYSDATE)
  FROM T_ORD_BIG
 GROUP BY CUS_ID

개선 후 실행계획 학습용 예시 · 행 수는 실데이터로 계산

xplan

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |                |      1 |        |      0 |00:00:00.33 |    9918 |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_CUS_LAST_ORD |      1 |        |      0 |00:00:00.33 |    9918 |          |
|   2 |   SORT GROUP BY          |                |      1 |   2000 |   2000 |00:00:00.29 |    5826 |  178K (0)|
|   3 |    TABLE ACCESS FULL     | T_ORD_BIG      |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
-------------------------------------------------------------------------------------------------------------------

[적재 결과]  2,000행 · ORA-00001 없음 (테이블과 PK 인덱스에 넣는 몫이 Buffers 약 4,100)

모범 답안 서술

T_ORD_BIG의 ORD_DT는 전부 자정이고, 고객마다 주문이 있는 날에는 하루 2 ~ 3건씩 있다. 인라인 뷰의 MAX(ORD_DT)는 마지막 주문 '날짜'일 뿐 주문 한 건을 가리키지 않는다. 그 날짜로 다시 조인하면(o.ORD_DT = m.MAX_DT) 그날 주문이 모두 붙어서, 마지막 날 주문이 2건인 고객 1,435명은 두 줄, 3건인 고객 565명은 세 줄이 된다. 그래서 2,000명인데 4,565행이 나왔고, PK가 없던 때는 CRM 화면에 고객이 두세 줄씩 보였으며, PK를 붙이자 첫 중복에서 ORA-00001로 멈췄다. 실행계획의 4번과 5번 줄이 모두 T_ORD_BIG을 FULL로 읽는다. 마지막 날짜를 구할 때 한 번, 그 날짜로 다시 조인할 때 한 번이라 같은 테이블을 두 번 읽었다(5,826 × 2 = 11,652블록). 고친 SQL은 고객별로 한 번만 묶는다. MAX(ORD_AMT) KEEP (DENSE_RANK LAST ORDER BY ORD_DT, ORD_SEQ)는 고객 안에서 (주문일, 주문번호)가 가장 늦은 한 건의 금액을 꺼낸다. 주문번호는 유일하므로 같은 날 주문이 여럿이어도 업무 규칙대로 주문번호가 큰 한 건으로 정해진다. 결과는 4,565행 → 2,000행이 되고, SELECT 부분의 Buffers는 11,652 → 5,826으로 준다. 해시 조인도 사라진다.

해설

정렬 키에 ORD_SEQ를 빼면: KEEP (DENSE_RANK LAST ORDER BY ORD_DT)만 쓰면 동점 2 ~ 3건 가운데 MAX(ORD_AMT), 곧 금액이 가장 큰 값이 나온다. 주문번호와 금액이 서로 다른 주문에서 나온 줄이 생긴다. MAX(ORD_SEQ)와 MAX(ORD_AMT)를 따로 구해도 마찬가지다.
다른 정답: ROW_NUMBER() OVER (PARTITION BY CUS_ID ORDER BY ORD_DT DESC, ORD_SEQ DESC)로 번호를 매기고 1번만 넣어도 한 번 읽는다. 행 전체가 필요하면 이쪽이 편하고, 칸이 몇 개뿐이면 KEEP이 인라인 뷰 없이 끝난다.
PK는 옳은 조치였다: 중복이 화면에 나오던 문제를 적재 시점의 에러로 바꿔 드러냈다. 에러를 피하려고 PK를 떼거나 DISTINCT를 붙이면 동점인 주문 가운데 어느 것이 남을지 알 수 없다.
TRUNCATE 뒤에 통째로 채우는 적재라 /*+ APPEND */를 붙여 직접 경로로 넣을 수도 있다. 2,000행이라 얻는 것은 적다 (교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 행 수는 실데이터로 계산 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 행 수는 실데이터로 계산 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황