JOIN-071

판매 뷰로 만든 「상품별 누적 판매」가 쓰지도 않는 판매 헤더 50만 건을 함께 읽는다

실전DDL유통221 / 700

상황

본사 영업기획팀의 BI 도구는 시맨틱 계층의 뷰 V_SLE_DTL 하나로 판매 데이터를 본다. 사용자가 칸을 고르면 BI 도구가 그 칸만 SELECT 목록에 넣어 뷰에 SQL을 보낸다. 자주 쓰는 리포트는 아래 넷이고, Q1을 열면 1초 넘게 걸린다. 선배는 "쓰지 않는 테이블은 옵티마이저가 지우게 할 수 있어. 다만 이 DB에 무엇이 없는지부터 봐"라고 했다. 오과장은 제약을 거는 것은 좋지만 POS 판매 저장이 느려지거나 멈추면 안 된다고 했다.

테이블과 인덱스

뷰 V_SLE_DTL: CF11 b JOIN CF10 a ON a.SLE_NO = b.SLE_NO JOIN CF07 s ON s.SHP_NO = a.SHP_NO JOIN CF01 p ON p.ITM_NO = b.ITM_NO LEFT JOIN CF02 g ON g.CTG_NO = p.CTG_NO · 내보내는 칸: b의 칸 전부와 SLE_AMT(= SLE_QTY * SLE_UPRC), a.SLE_DT, a.SHP_NO, s.SHP_NM, p.ITM_NM, p.PTNR_NO, g.CTG_NM
CF11 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) / CF10 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · SHP_NO는 NULL을 허용하지만 NULL인 행은 없다
CF07 6행 · CF07_PK (SHP_NO) / CF01 109행 · CF01_PK (ITM_NO) / CF02 18행 · CF02_PK (CTG_NO)
본사 레거시라서 PK 말고는 제약이 없다(FK 0개) · CF11.ITM_NO나 CF10.SHP_NO로 시작하는 인덱스는 없다
어제 점검: 판매 헤더가 없는 판매상품 0줄, 매장 마스터에 없는 판매 0건, 상품 마스터에 없는 판매상품 412줄(2023년 3월 시즌 메뉴 2개를 번호를 바꿔 다시 등록하면서 옛 번호를 CF01에서 지웠다) · 통계 최신

원본 SQL

sql

-- [Q1] 상품별 누적 판매 (상품팀, 하루 40번)
SELECT ITM_NO, ITM_NM, SUM(SLE_QTY) AS qty, SUM(SLE_AMT) AS amt
  FROM V_SLE_DTL
 GROUP BY ITM_NO, ITM_NM;

-- [Q2] 매장별 일 매출 (영업기획, 하루 20번)
SELECT SHP_NM, SLE_DT, SUM(SLE_AMT) AS amt
  FROM V_SLE_DTL
 WHERE SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SHP_NM, SLE_DT;

-- [Q3] 영수증 상세 (매장 POS 재출력, 하루 3,000번)
SELECT ITM_NO, ITM_NM, SLE_QTY, SLE_UPRC, SLE_AMT
  FROM V_SLE_DTL
 WHERE SLE_NO = :v_sle_no;

-- [Q4] 카테고리별 월 매출 (MD, 하루 60번)
SELECT CTG_NM, SUM(SLE_AMT) AS amt
  FROM V_SLE_DTL
 WHERE SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY CTG_NM;

실행계획 학습용 예시

xplan

[Q1] 상품별 누적 판매
------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |     88 |00:00:01.12 |   12796 |           |
|   1 |  HASH GROUP BY          |      |      1 |    109 |     88 |00:00:01.12 |   12796 | 1123K (0) |
|*  2 |   HASH JOIN             |      |      1 |  1096K |  1096K |00:00:00.98 |   12796 | 1290K (0) |
|   3 |    TABLE ACCESS FULL    | CF01 |      1 |    109 |    109 |00:00:00.01 |       5 |           |
|*  4 |    HASH JOIN            |      |      1 |  1096K |  1096K |00:00:00.71 |   12791 |   33M (0) |
|*  5 |     HASH JOIN           |      |      1 |   500K |   500K |00:00:00.18 |    4587 | 1156K (0) |
|   6 |      TABLE ACCESS FULL  | CF07 |      1 |      6 |      6 |00:00:00.01 |       4 |           |
|   7 |      TABLE ACCESS FULL  | CF10 |      1 |   500K |   500K |00:00:00.05 |    4583 |           |
|   8 |     TABLE ACCESS FULL   | CF11 |      1 |  1096K |  1096K |00:00:00.09 |    8204 |           |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"="B"."ITM_NO")
   4 - access("A"."SLE_NO"="B"."SLE_NO")
   5 - access("S"."SHP_NO"="A"."SHP_NO")

[Q3] 영수증 상세 · 판매 한 건
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |         |      1 |        |      3 |00:00:00.01 |      16 |
|   1 |  NESTED LOOPS                           |         |      1 |      2 |      3 |00:00:00.01 |      16 |
|   2 |   NESTED LOOPS                          |         |      1 |      2 |      3 |00:00:00.01 |      10 |
|   3 |    NESTED LOOPS                         |         |      1 |      1 |      1 |00:00:00.01 |       6 |
|   4 |     TABLE ACCESS BY INDEX ROWID         | CF10    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  5 |      INDEX UNIQUE SCAN                  | CF10_PK |      1 |      1 |      1 |00:00:00.01 |       3 |
|   6 |     TABLE ACCESS BY INDEX ROWID         | CF07    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  7 |      INDEX UNIQUE SCAN                  | CF07_PK |      1 |      1 |      1 |00:00:00.01 |       1 |
|   8 |    TABLE ACCESS BY INDEX ROWID BATCHED  | CF11    |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  9 |     INDEX RANGE SCAN                    | CF11_PK |      1 |      2 |      3 |00:00:00.01 |       3 |
|  10 |   TABLE ACCESS BY INDEX ROWID           | CF01    |      3 |      1 |      3 |00:00:00.01 |       6 |
|* 11 |    INDEX UNIQUE SCAN                    | CF01_PK |      3 |      1 |      3 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("A"."SLE_NO"=:V_SLE_NO)
   7 - access("S"."SHP_NO"="A"."SHP_NO")
   9 - access("B"."SLE_NO"=:V_SLE_NO)
  11 - access("P"."ITM_NO"="B"."ITM_NO")

문제

네 SQL마다 결과에 쓰는 테이블과, 쓰지 않는데 계획에 남는 테이블을 가리고 남는 이유를 쓰시오. CF02가 Q1과 Q3의 계획에 이미 없는 이유도 설명하시오.
쓰지 않는 조인이 계획에서 사라지도록 제약을 설계해 DDL로 작성하시오. 어제 점검의 412줄을 어떻게 처리할지 정하고, 그 결정이 리포트 숫자에 주는 영향도 쓰시오.
제약이 POS 판매 저장과 마스터 관리에 주는 영향을 쓰고, 검증 없이 RELY DISABLE NOVALIDATE로 거는 안과 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 412줄의 부모 : 2023년 3월에 지운 옛 상품 번호 2개를 판매 중지 상태로 되살린다
INSERT INTO CF01 (ITM_NO, ITM_NM, CTG_NO, PTNR_NO, SLE_YN)
VALUES (:v_old_itm1, :v_old_nm1, :v_ctg1, :v_ptnr1, 'N');
INSERT INTO CF01 (ITM_NO, ITM_NM, CTG_NO, PTNR_NO, SLE_YN)
VALUES (:v_old_itm2, :v_old_nm2, :v_ctg2, :v_ptnr2, 'N');
COMMIT;

-- ② 고아 재확인 : 셋 다 0 이어야 VALIDATE 가 통과한다
SELECT COUNT(*) FROM CF11 b WHERE NOT EXISTS (SELECT 1 FROM CF10 a WHERE a.SLE_NO = b.SLE_NO);
SELECT COUNT(*) FROM CF11 b WHERE NOT EXISTS (SELECT 1 FROM CF01 p WHERE p.ITM_NO = b.ITM_NO);
SELECT COUNT(*) FROM CF10 a WHERE NOT EXISTS (SELECT 1 FROM CF07 s WHERE s.SHP_NO = a.SHP_NO);

-- ③ FK 셋 : NOVALIDATE 로 먼저 켜고(새 행부터 검사, 금방 끝난다) 이어서 검증한다(검증하는 동안 DML 을 막지 않는다)
ALTER TABLE CF11 ADD CONSTRAINT FK_CF11_CF10 FOREIGN KEY (SLE_NO) REFERENCES CF10 (SLE_NO) ENABLE NOVALIDATE;
ALTER TABLE CF11 ADD CONSTRAINT FK_CF11_CF01 FOREIGN KEY (ITM_NO) REFERENCES CF01 (ITM_NO) ENABLE NOVALIDATE;
ALTER TABLE CF10 ADD CONSTRAINT FK_CF10_CF07 FOREIGN KEY (SHP_NO) REFERENCES CF07 (SHP_NO) ENABLE NOVALIDATE;
ALTER TABLE CF11 MODIFY CONSTRAINT FK_CF11_CF10 VALIDATE;
ALTER TABLE CF11 MODIFY CONSTRAINT FK_CF11_CF01 VALIDATE;
ALTER TABLE CF10 MODIFY CONSTRAINT FK_CF10_CF07 VALIDATE;

-- ④ 뷰와 BI 도구가 보내는 SQL 은 그대로 둔다 : 쓰지 않는 조인은 옵티마이저가 지운다
--    운영 규칙 : 상품과 매장은 지우지 않고 SLE_YN 으로 판매 중지를 표시한다

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

xplan

[Q1] 상품별 누적 판매 · 옛 상품 번호 2개를 되살리고 FK 셋을 검증한 뒤
----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |     90 |00:00:00.41 |    8209 |           |
|   1 |  HASH GROUP BY        |      |      1 |    111 |     90 |00:00:00.41 |    8209 | 1123K (0) |
|*  2 |   HASH JOIN           |      |      1 |  1096K |  1096K |00:00:00.30 |    8209 | 1290K (0) |
|   3 |    TABLE ACCESS FULL  | CF01 |      1 |    111 |    111 |00:00:00.01 |       5 |           |
|   4 |    TABLE ACCESS FULL  | CF11 |      1 |  1096K |  1096K |00:00:00.09 |    8204 |           |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("P"."ITM_NO"="B"."ITM_NO")

[Q3] 영수증 상세 · 같은 조건
------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |      3 |00:00:00.01 |      10 |
|   1 |  NESTED LOOPS                          |         |      1 |      2 |      3 |00:00:00.01 |      10 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED  | CF11    |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                    | CF11_PK |      1 |      2 |      3 |00:00:00.01 |       3 |
|   4 |   TABLE ACCESS BY INDEX ROWID          | CF01    |      3 |      1 |      3 |00:00:00.01 |       6 |
|*  5 |    INDEX UNIQUE SCAN                   | CF01_PK |      3 |      1 |      3 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("B"."SLE_NO"=:V_SLE_NO)
   5 - access("P"."ITM_NO"="B"."ITM_NO")

모범 답안 서술

남는 조인과 이유: Q1은 CF11과 CF01(상품명)만 쓰는데 CF10과 CF07이 남는다(4번과 5번 줄, CF10 FULL 4,583블록과 판매 50만 건의 해시 영역 33M). Q2는 CF10, CF07, CF11(금액)을 쓰고 CF01이 남는다. Q3는 CF11과 CF01을 쓰고 CF10과 CF07이 남는다(16블록 중 6블록). Q4는 CF10(기간), CF11, CF01, CF02를 쓰고 CF07이 남는다. 남는 조인은 모두 내부 조인이다. 내부 조인은 짝이 없는 행을 버리므로, 옵티마이저는 짝이 반드시 하나 있다는 보장이 없으면 결과에 쓰지 않는 테이블도 지우지 못한다. '하나'는 부모 쪽 PK가 보장하지만 '반드시 있다'는 FK가 보장한다. 이 DB에는 FK가 없다. CF02가 이미 없는 이유: CF02는 아우터 조인이다. 아우터 조인은 짝이 없어도 행을 버리지 않으므로 '반드시 있다'가 필요 없고, CF02_PK로 '많아야 하나'만 보장되면 행 수가 바뀌지 않는다. 그래서 FK 없이도 CTG_NM을 쓰지 않는 SQL에서는 지금도 지워진다. 제약 설계: FK 셋을 건다. CF11.SLE_NO → CF10, CF11.ITM_NO → CF01, CF10.SHP_NO → CF07이다. 순서는 고아 확인 → ENABLE NOVALIDATE(새 행부터 검사, 금방 끝난다) → VALIDATE(기존 행 검사)다. 이미 켜진 제약을 검증하는 동안에는 DML을 막지 않으므로 POS 저장을 멈추지 않는다. SHP_NO는 NULL을 허용하지만 NULL인 행이 없으므로, 옵티마이저는 SHP_NO IS NOT NULL 조건을 덧붙이고 조인을 지운다. 412줄: 이 줄은 지금 뷰를 쓰는 모든 리포트에서 빠지고 있다. 상품명이 필요 없는 Q2의 매장 매출에서도 내부 조인 때문에 빠진다. 상품 FK를 걸려면 부모가 있어야 하므로, 옛 번호 2개를 CF01에 판매 중지(SLE_YN = 'N')로 되살린다. 그러면 412줄이 모든 리포트에 다시 들어가 Q1은 88 → 90줄이 되고, Q1과 Q2의 2023년 3월 이전 금액이 늘어난다. 원장(판매상품 합계)과는 오히려 맞게 되지만 숫자가 바뀐다는 것을 리포트 사용자에게 미리 알린다. 효과: Q1은 CF10과 CF07이 사라져 CF11 FULL과 상품 해시 조인만 남는다. Buffers는 12,796 → 8,209, 시간은 1.12 → 0.41초다. Q3는 16 → 10블록이고 하루 3,000번이면 약 1만 8천 블록이 준다. Q2는 CF01 조인, Q4는 CF07 조인이 사라져 해시 조인 하나씩의 CPU가 준다. 대가: POS가 판매상품을 넣을 때마다 CF10과 CF01의 PK로 부모를 확인하고, 판매를 넣을 때 CF07을 확인한다. 한 줄에 인덱스 몇 블록이라 저장 시간에는 거의 드러나지 않는다. 마스터 쪽이 더 문제다. CF11.ITM_NO로 시작하는 인덱스가 없어서, 상품을 지우거나 상품 번호를 바꾸면 판매상품 110만 줄을 다 확인하고 그동안 판매상품 테이블에 잠금이 걸려 POS 저장이 기다릴 수 있다. 상품과 매장은 지우지 않고 SLE_YN으로 판매 중지를 표시하는 규칙을 함께 정한다. RELY DISABLE NOVALIDATE 안: 제약을 검사하지 않고 '믿어도 된다'고만 표시하는 방법이다. 저장할 때의 검사 비용도, 마스터 삭제 때의 잠금 문제도 없고, 12.2 이후에는 QUERY_REWRITE_INTEGRITY = TRUSTED일 때 조인 제거에도 쓸 수 있다. 대신 데이터가 그 약속을 어기면 옵티마이저는 틀린 결과를 낸다. 412줄을 그대로 두고 RELY로 걸면 상품명이 필요 없는 Q2에서는 조인이 지워져 412줄이 들어가고, 상품명이 필요한 Q1에서는 조인이 남아 빠진다. 같은 날 두 리포트의 합계가 서로 달라진다. 고아가 실제로 있었던 이 DB에서는 검증하는 FK를 권한다.

해설

뷰를 아우터 조인으로 바꾸면: CF10, CF07, CF01 조인을 모두 LEFT JOIN으로 바꾸면 FK 없이 PK만으로도 지워진다. 하지만 뜻이 바뀐다. 412줄은 상품명 NULL로 들어오고, 헤더가 없는 판매상품이 생기면 날짜와 매장이 NULL인 줄이 리포트에 섞인다. 데이터의 규칙은 제약으로 지키는 쪽이 낫다.
지울 수 없는 경우: 결과에 그 테이블의 칸을 하나라도 쓰거나(Q2의 SHP_NM), WHERE에 그 테이블의 조건이 있으면(Q4의 SLE_DT) 지우지 않는다. 조인 제거는 쓰지 않는 테이블에만 일어난다.
확인 방법: 제약을 건 뒤 DBMS_XPLAN.DISPLAY_CURSOR(FORMAT => '+OUTLINE')에서 ELIMINATE_JOIN 줄로 지워진 조인을 확인한다(교재 08화 §5).
FK 컬럼 인덱스: 마스터를 지울 일이 생긴다면 CF11 (ITM_NO, SLE_NO) 인덱스를 두는 방법도 있다. 다만 110만 행 테이블의 적재 비용이 늘어나므로, 지우지 않는 규칙이 지켜진다면 만들지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황