연간 상품별 판매 맨 아래에 상품명도 분류도 빈 네 줄이 있는데, 리포트는 에러 없이 끝났다

실전SELECT유통677 / 700

상황

1월 둘째 주, 본사 회계팀이 결산용 「2023년 상품별 판매」를 내려받아 중분류로 묶어 보니 머그와 텀블러가 매장 장부보다 합계 310,472,000원 적었다. 리포트 맨 아래에는 상품명도 중분류도 비어 있는 네 줄이 있었고, 네 줄의 금액을 더하면 정확히 그 차이였다. 리포트 도구의 로그는 정상 종료였다. 알고 보니 1월 2일 상품 담당이 라이선스 계약이 끝난 굿즈 4종을 POS에서 내리면서, 판매 중지(SLE_YN = 'N')로 바꾸는 대신 상품 마스터에서 행을 지웠다. 개발자는 함수를 빼고 조인하는 B 판을 냈다. "빈칸이 깨끗이 없어져요." 오과장은 결산 리포트가 무엇을 보여 줘야 하는지 정하고, 다시는 이런 일이 조용히 지나가지 않게 해 달라고 했다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · 2023년 249,971건 · SLE_DT VARCHAR2(8)
CF11 판매상품: 1,096,708행 · 8,204블록 · 2023년분 548,401줄 · 그해 팔린 상품 88종 · 2023년 판매액 4,506,252,000원(매출 원장과 같다)
CF01 상품: 원래 109행 · 지운 4행(1073 아이언맨머그, 1074 캡틴아메리카머그, 1086 아이언맨텀블러, 1087 캡틴아메리카텀블러) 때문에 지금 105행 · 통계는 지우기 전에 모은 것
지운 4종의 2023년 판매: 12,464줄 · 수량 31,033 · 금액 310,472,000원
CF02 카테고리: 18행 · 4블록 · CF01.CTG_NO가 가리키는 중분류(머그 310 · 텀블러 312 …)
CF11에서 CF01로 가는 FK는 없다(레거시)
PKG_ITM: 상품 공통 패키지 · 매장 POS와 ERP 화면 열 곳 남짓이 같은 함수를 쓴다

원본 SQL

sql

-- 공통 패키지 PKG_ITM 안
FUNCTION ITM_NM(p_itm_no NUMBER) RETURN VARCHAR2 IS
  v_nm CF01.ITM_NM%TYPE;
BEGIN
  SELECT ITM_NM INTO v_nm FROM CF01 WHERE ITM_NO = p_itm_no;
  RETURN v_nm;
END;

FUNCTION CTG_NM(p_itm_no NUMBER) RETURN VARCHAR2 IS
  v_nm CF02.CTG_NM%TYPE;
BEGIN
  SELECT c.CTG_NM INTO v_nm
    FROM CF01 i, CF02 c
   WHERE i.ITM_NO = p_itm_no
     AND c.CTG_NO = i.CTG_NO;
  RETURN v_nm;
END;

-- [A] 회계팀 「연간 상품별 판매」 (:yyyy = '2023')
SELECT PKG_ITM.CTG_NM(g.ITM_NO) AS ctg_nm,
       PKG_ITM.ITM_NM(g.ITM_NO) AS itm_nm,
       g.qty, g.amt
  FROM (SELECT d.ITM_NO, SUM(d.SLE_QTY) AS qty, SUM(d.SLE_QTY * d.SLE_UPRC) AS amt
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN :yyyy || '0101' AND :yyyy || '1231'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO) g
 ORDER BY 1, 2;

-- [B] 개발자 안: 함수를 빼고 상품, 카테고리와 조인
SELECT c.CTG_NM AS ctg_nm, i.ITM_NM AS itm_nm, g.qty, g.amt
  FROM (SELECT d.ITM_NO, SUM(d.SLE_QTY) AS qty, SUM(d.SLE_QTY * d.SLE_UPRC) AS amt
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN :yyyy || '0101' AND :yyyy || '1231'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO) g,
       CF01 i, CF02 c
 WHERE i.ITM_NO = g.ITM_NO
   AND c.CTG_NO = i.CTG_NO
 ORDER BY 1, 2;

실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈

xplan

[A: DISPLAY_CURSOR (ALLSTATS LAST)]
-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |      |      1 |        |     88 |00:00:00.45 |   12787 |          |
|   1 |  SORT ORDER BY        |      |      1 |     88 |     88 |00:00:00.45 |   12787 |13312  (0)|
|   2 |   VIEW                |      |      1 |     88 |     88 |00:00:00.44 |   12787 |          |
|   3 |    HASH GROUP BY      |      |      1 |     88 |     88 |00:00:00.44 |   12787 | 1150K (0)|
|*  4 |     HASH JOIN         |      |      1 |    548K|    548K|00:00:00.36 |   12787 |   14M (0)|
|*  5 |      TABLE ACCESS FULL| CF10 |      1 |    250K|    249K|00:00:00.04 |    4583 |          |
|   6 |      TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.09 |    8204 |          |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."SLE_NO"="H"."SLE_NO")
   5 - filter(("H"."SLE_DT">=:YYYY||'0101' AND "H"."SLE_DT"<=:YYYY||'1231'))

[V$SQL: A를 한 번 돌린 뒤 함수 안 문장]
  SQL_TEXT                                                         EXECUTIONS  ROWS_PROCESSED  BUFFER_GETS
  SELECT ITM_NM FROM CF01 WHERE ITM_NO = :B1                               88              84          172
  SELECT C.CTG_NM FROM CF01 I, CF02 C WHERE I.ITM_NO = :B1 AND …           88              84          340

[A의 결과 맨 아래]
  중분류     상품명                 수량            금액
  텀블러     화이트텀블러          3,762      37,620,000
                                   7,772      62,176,000
                                   7,709      61,672,000
                                   7,914      94,968,000
                                   7,638      91,656,000
  88행 · 수량 1,371,547 · 금액 4,506,252,000

[B의 결과]  84행 · 빈칸 없음 · 수량 1,340,514 · 금액 4,195,780,000

[개발자의 시험: 빈 줄 하나의 상품 번호 1073]
  SQL> SELECT PKG_ITM.ITM_NM(1073) AS nm FROM DUAL;
  NM
  ----------
                              ← 빈칸 (오류 없음)
  SQL> EXEC DBMS_OUTPUT.PUT_LINE(PKG_ITM.ITM_NM(1073));
  ORA-01403: no data found
  ORA-06512: at "PUP.PKG_ITM", line 5

문제

빈 네 줄이 생겼는데도 리포트가 에러 없이 끝난 이유를 위 자료로 설명하시오. 같은 함수를 PL/SQL에서 부르면 ORA-01403이 나는데 SQL에서는 빈칸이 되는 까닭도 쓰시오.
B 판과 아우터 조인 판이 지금 리포트와 무엇이 다른지 행 수와 합계로 견주고, 결산 리포트가 무엇을 보여 줘야 하는지 정해 SQL을 다시 작성하시오.
같은 일이 다시 조용히 지나가지 않도록 데이터와 함수 쪽에서 할 일을 정하고, 그 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 결산 리포트: 판매 88줄과 합계를 지키고, 마스터가 없는 상품은 번호와 표시로 드러낸다
SELECT g.ITM_NO,
       NVL(c.CTG_NM, '(마스터 없음)')               AS ctg_nm,
       NVL(i.ITM_NM, '(마스터 없음) ' || g.ITM_NO)  AS itm_nm,
       g.qty, g.amt
  FROM (SELECT d.ITM_NO, SUM(d.SLE_QTY) AS qty, SUM(d.SLE_QTY * d.SLE_UPRC) AS amt
          FROM CF10 h, CF11 d
         WHERE h.SLE_DT BETWEEN :yyyy || '0101' AND :yyyy || '1231'
           AND d.SLE_NO = h.SLE_NO
         GROUP BY d.ITM_NO) g
  LEFT JOIN CF01 i ON i.ITM_NO = g.ITM_NO
  LEFT JOIN CF02 c ON c.CTG_NO = i.CTG_NO
 ORDER BY ctg_nm, itm_nm;

-- ② 결산 전 점검: 팔렸는데 상품 마스터에 없는 상품 (0행이어야 결산 자료를 낸다)
SELECT d.ITM_NO, COUNT(*) AS lines
  FROM CF11 d
 WHERE NOT EXISTS (SELECT 1 FROM CF01 i WHERE i.ITM_NO = d.ITM_NO)
 GROUP BY d.ITM_NO;

-- ③ 지운 4행은 1월 1일 백업에서 되살린 뒤 판매 중지로 둔다
UPDATE CF01 SET SLE_YN = 'N' WHERE ITM_NO IN (1073, 1074, 1086, 1087);
COMMIT;

-- ④ 판매 이력이 있는 상품은 지우지 못하게 한다 (켤 때 110만 행을 검사한다)
ALTER TABLE CF11 ADD CONSTRAINT CF11_FK_ITM FOREIGN KEY (ITM_NO) REFERENCES CF01 (ITM_NO);

개선 후 실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈

xplan

[① 고친 리포트 · 마스터를 되살리기 전 (DISPLAY_CURSOR · ALLSTATS LAST)]
-------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |      |      1 |        |     88 |00:00:00.45 |   12796 |          |
|   1 |  SORT ORDER BY          |      |      1 |     88 |     88 |00:00:00.45 |   12796 |13312  (0)|
|*  2 |   HASH JOIN RIGHT OUTER |      |      1 |     88 |     88 |00:00:00.45 |   12796 | 1236K (0)|
|   3 |    TABLE ACCESS FULL    | CF02 |      1 |     18 |     18 |00:00:00.01 |       4 |          |
|*  4 |    HASH JOIN RIGHT OUTER|      |      1 |     88 |     88 |00:00:00.45 |   12792 | 1269K (0)|
|   5 |     TABLE ACCESS FULL   | CF01 |      1 |    109 |    105 |00:00:00.01 |       5 |          |
|   6 |     VIEW                |      |      1 |     88 |     88 |00:00:00.44 |   12787 |          |
|   7 |      HASH GROUP BY      |      |      1 |     88 |     88 |00:00:00.44 |   12787 | 1150K (0)|
|*  8 |       HASH JOIN         |      |      1 |    548K|    548K|00:00:00.36 |   12787 |   14M (0)|
|*  9 |        TABLE ACCESS FULL| CF10 |      1 |    250K|    249K|00:00:00.04 |    4583 |          |
|  10 |        TABLE ACCESS FULL| CF11 |      1 |   1096K|   1096K|00:00:00.09 |    8204 |          |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."CTG_NO"(+)="I"."CTG_NO")
   4 - access("I"."ITM_NO"(+)="G"."ITM_NO")
   8 - access("D"."SLE_NO"="H"."SLE_NO")
   9 - filter(("H"."SLE_DT">=:YYYY||'0101' AND "H"."SLE_DT"<=:YYYY||'1231'))

[① 결과 맨 위]
  ITM_NO  중분류          상품명                    수량          금액
    1073  (마스터 없음)   (마스터 없음) 1073       7,772    62,176,000
    1074  (마스터 없음)   (마스터 없음) 1074       7,709    61,672,000
    1086  (마스터 없음)   (마스터 없음) 1086       7,914    94,968,000
    1087  (마스터 없음)   (마스터 없음) 1087       7,638    91,656,000
  88행 · 수량 1,371,547 · 금액 4,506,252,000 (매출 원장과 같다)

[② 점검]  1073 6,257줄 · 1074 6,281줄 · 1086 6,275줄 · 1087 6,202줄 (2년 치) → ③ 뒤 0행
[③ 뒤 ① 다시]  88행 · 표시 0줄 · 머그와 텀블러가 매장 장부와 같다

모범 답안 서술

함수 안 SELECT는 88번 돌았는데 돌려준 행은 84개다(V$SQL). 지운 4종에서는 CF01에 행이 없어 SELECT … INTO가 NO_DATA_FOUND를 던졌고, 함수에 예외 처리부가 없으니 예외가 함수 밖으로 나갔다. 함수를 부른 것이 PL/SQL이면 그대로 ORA-01403 오류다. 개발자가 EXEC로 시험했을 때가 그렇다. 그러나 SQL 문장 안에서 부른 함수가 NO_DATA_FOUND를 던지면, SQL 엔진은 이 예외를 '그 행에는 값이 없다'로 받아 NULL을 돌려주고 문장을 계속한다. 그래서 상품명과 중분류가 빈칸인 네 줄이 생겼고, 리포트는 정상 종료했다. 이렇게 조용히 넘어가는 것은 NO_DATA_FOUND뿐이다. 다른 예외(TOO_MANY_ROWS나 RAISE_APPLICATION_ERROR)는 SQL에서도 오류로 올라와 문장이 멈춘다. 빈칸은 ORDER BY에서 NULL이 맨 뒤로 가서 맨 아래에 모였고, 중분류로 묶던 회계팀의 표에서는 머그와 텀블러 밖으로 빠졌다. B는 내부 조인이라 CF01에 없는 4종을 통째로 버린다. 빈칸은 없어지지만 84행, 수량 1,340,514, 금액 4,195,780,000원이 되어 매출 원장보다 310,472,000원(12,464줄) 적다. 결산 자료로는 가장 나쁜 답이다. 틀렸다는 표시조차 없기 때문이다. 상품과 카테고리를 아우터 조인만 하면 지금 리포트와 똑같다. 88행에 합계는 원장과 같지만 빈칸 네 줄이 그대로 남는다. 함수 판과 아우터 조인 판은 같은 질문에 같은 답을 한 것이고, 둘 다 '없다'를 말하지 않았다. 결산 리포트는 두 가지를 지킨다. 첫째, 판매 88줄과 합계 4,506,252,000원을 원장과 맞게 지킨다. 둘째, 마스터가 없는 상품은 숨기지 않고 상품 번호와 '(마스터 없음)' 표시로 드러낸다. 그래서 ①처럼 상품과 카테고리를 아우터 조인하고 NVL로 표시를 넣으며, 상품 번호 칸을 더한다. 표시는 정렬에서 맨 위로 올라와 눈에 띈다. 읽는 블록은 두 마스터 몫 9블록만 늘어 12,787 → 12,796이다. 데이터 쪽에서는 원인을 고친다. 지운 4행을 백업에서 되살려 SLE_YN = 'N'(판매 중지)으로 두면 리포트에 상품명이 돌아온다. 다시 지우지 못하게 CF11.ITM_NO에서 CF01로 가는 FK를 건다. 판매 이력이 있는 상품을 지우려 하면 ORA-02292로 막힌다. 대가는 셋이다. FK를 켤 때 판매상품 110만 행을 한 번 검사하고, POS가 판매상품을 넣을 때마다 상품 PK를 한 번씩 확인한다. 그리고 ITM_NO로 시작하는 인덱스가 없어서 상품을 지우거나 상품 번호를 고칠 때 CF11 전체에 TM 락이 걸리므로, 상품 정리는 POS 적재가 없는 시간에 한다. FK를 걸 수 없는 사정이면 ② 점검을 결산 절차의 첫 단계로 두고, 0행이 아니면 결산 자료를 내지 않는다. 공통 함수는 함부로 바꾸지 않는다. NO_DATA_FOUND를 받아 RAISE_APPLICATION_ERROR로 바꾸면 SQL에서도 오류가 올라와 숨지 않지만, 같은 함수를 쓰는 POS와 ERP 화면도 오류를 받는다. 화면에 무엇을 보일지 함께 정할 때 바꾼다.

해설

함정: 행 수만 보면 B가 가장 깔끔하다. 빈칸이 없고 84행이다. 결산 리포트는 행보다 합계가 먼저다. 고친 뒤에는 원장 합계와 견주는 줄을 리포트 끝에 둔다.
이 동작은 교재 15화 §8.3에서 쟀다. PL/SQL에서 부른 FN_ADJ_RATE는 ORA-01403이었고, SQL에서 부른 같은 함수는 에러 없이 NULL이었다. SQL에서 부를 함수는 NO_DATA_FOUND를 안에서 처리하거나, 부르기 전에 없는 키를 따로 걸러 낸다.
함수에 표시를 돌려주는 판: WHEN NO_DATA_FOUND THEN RETURN '(마스터 없음) ' || p_itm_no로 고치면 리포트 SQL은 그대로 두고 빈칸을 없앨 수 있다. 다만 이 함수를 쓰는 화면 모두가 그 문자열을 보게 되고, 숫자를 돌려주는 함수(단가, 협력사 번호)에는 쓸 수 없다. 숫자 함수가 NULL을 돌려주면 SUM이 그 줄을 조용히 빼므로 더 위험하다.
FK를 걸기 전에는 ② 점검이 0행이어야 한다(아니면 ORA-02298). 운영 중에 110만 행 검사가 부담이면 ENABLE NOVALIDATE로 새 행부터 검사하고, 고아를 정리한 뒤 VALIDATE한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수와 금액은 원본 데이터로 셈 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황