같은 집계에 FULL 힌트를 주자 다른 숫자가 나왔다

중급DDL유통653 / 700

상황

본사 유통지원팀의 「대분류별 판매 수량」 리포트는 함수 FN_TOP_CTG로 상품의 대분류를 구한다. 2년 전 개발자가 이렇게 짰고, 느리다는 말이 나오자 그 함수로 인덱스도 만들었다. 소스 주석에는 이렇게 적혀 있다. "ORA-30553이 나서 DETERMINISTIC을 붙였더니 됐다." 어제 상품 분류 개편으로 아이스크림(중분류 114)을 음료(100)에서 푸드(200) 아래로 옮겼다(CF02 한 줄을 고치고 커밋). 오늘 회계팀에서 연락이 왔다. "푸드 판매 수량이 판매 원장과 안 맞는다." 확인하려고 같은 문장에 FULL 힌트를 주자 다른 숫자가 나왔다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 함수 기반 인덱스 CF11_FX01 (FN_TOP_CTG(ITM_NO), SLE_QTY)(리프 약 2,440블록)
CF01 상품: 109행 · 5블록 · PK(ITM_NO) · CTG_NO(중분류)
CF02 카테고리: 18행 · 4블록 · PK(CTG_NO) · UP_CTG_NO(대분류 100 음료 · 200 푸드 · 300 굿즈 · 900 기타)
판매된 상품 88종 · 푸드(브레드, 케이크, 샌드위치) 21종 105,736행 · 아이스크림 3종 9,350행(수량 23,434)

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_TOP_CTG(p_itm_no NUMBER) RETURN NUMBER DETERMINISTIC IS
  v_up NUMBER;
BEGIN
  SELECT c.UP_CTG_NO INTO v_up
    FROM CF01 i, CF02 c
   WHERE i.ITM_NO = p_itm_no
     AND c.CTG_NO = i.CTG_NO;
  RETURN v_up;
END;
/

CREATE INDEX CF11_FX01 ON CF11 (FN_TOP_CTG(ITM_NO), SLE_QTY);

-- 대분류별 판매 수량 (:top = 200 푸드)
SELECT SUM(SLE_QTY) AS qty, COUNT(*) AS lines
  FROM CF11
 WHERE FN_TOP_CTG(ITM_NO) = :top;

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

xplan

[어제 — 분류 개편]
 UPDATE CF02 SET UP_CTG_NO = 200 WHERE CTG_NO = 114;   COMMIT;

[오늘 — 그대로 돌림 (:top = 200)]
-----------------------------------------------------------------------------------------
| Id  | Operation         | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |      1 |        |      1 |00:00:00.02 |     237 |
|   1 |  SORT AGGREGATE   |           |      1 |      1 |      1 |00:00:00.02 |     237 |
|*  2 |   INDEX RANGE SCAN| CF11_FX01 |      1 |    366K|    105K|00:00:00.01 |     237 |
-----------------------------------------------------------------------------------------
   2 - access("PUP"."FN_TOP_CTG"("ITM_NO")=:TOP)

      QTY      LINES
  264,496    105,736

[확인 — 같은 문장에 /*+ FULL(CF11) */]
-----------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.48 |    8204 |
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.48 |    8204 |
|*  2 |   TABLE ACCESS FULL| CF11 |      1 |    366K|    115K|00:00:00.47 |    8204 |
-----------------------------------------------------------------------------------------
   2 - filter("PUP"."FN_TOP_CTG"("ITM_NO")=:TOP)
   (+ 함수 안 SQL — DETERMINISTIC 이라 한 번의 페치 안에서 같은 상품의 답을 기억한다)

      QTY      LINES
  287,930    115,086

문제

같은 문장이 계획에 따라 다른 답을 내는 이유를 설명하고, 어느 쪽이 맞는지 쓰시오.
정합성과 속도를 둘 다 지키도록 DDL과 SQL을 고치시오.
DETERMINISTIC을 붙여도 되는 함수의 조건을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 거짓 약속 위에 선 인덱스를 먼저 지운다 (함수를 먼저 고치면 인덱스가 DISABLED 되어 CF11 의 DML 이 막힌다)
DROP INDEX CF11_FX01;

-- ② 함수에서 DETERMINISTIC 을 뗀다 — 테이블을 읽는 함수다 (다시 함수 인덱스 · 캐시에 쓰이지 않게)
CREATE OR REPLACE FUNCTION FN_TOP_CTG(p_itm_no NUMBER) RETURN NUMBER IS
  v_up NUMBER;
BEGIN
  SELECT c.UP_CTG_NO INTO v_up
    FROM CF01 i, CF02 c
   WHERE i.ITM_NO = p_itm_no
     AND c.CTG_NO = i.CTG_NO;
  RETURN v_up;
END;
/

-- ③ 속도는 진짜 컬럼에 — 상품별 수량을 인덱스만으로 읽는다
CREATE INDEX CF11_X01 ON CF11 (ITM_NO, SLE_QTY);

-- ④ 리포트 — 분류는 '지금의' CF02 · CF01 에서 (한 문장 · 한 시점 · 두 마스터는 PK 라 판매 행이 늘지 않는다)
SELECT SUM(s.SLE_QTY) AS qty, COUNT(*) AS lines
  FROM CF02 c, CF01 i, CF11 s
 WHERE c.UP_CTG_NO = :top
   AND i.CTG_NO    = c.CTG_NO
   AND s.ITM_NO    = i.ITM_NO;

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

xplan

--------------------------------------------------------------------------------------------------
| Id  | Operation           | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |          |      1 |        |      1 |00:00:00.03 |     339 |          |
|   1 |  SORT AGGREGATE     |          |      1 |      1 |      1 |00:00:00.03 |     339 |          |
|   2 |   NESTED LOOPS      |          |      1 |    299K|    115K|00:00:00.03 |     339 |          |
|*  3 |    HASH JOIN        |          |      1 |     24 |     24 |00:00:00.01 |       9 | 1250K (0)|
|*  4 |     TABLE ACCESS FULL| CF02    |      1 |      4 |      4 |00:00:00.01 |       4 |          |
|   5 |     TABLE ACCESS FULL| CF01    |      1 |    109 |    109 |00:00:00.01 |       5 |          |
|*  6 |    INDEX RANGE SCAN | CF11_X01 |     24 |  12463 |    115K|00:00:00.02 |     330 |          |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("I"."CTG_NO"="C"."CTG_NO")
   4 - filter("C"."UP_CTG_NO"=:TOP)
   6 - access("S"."ITM_NO"="I"."ITM_NO")

    QTY      LINES
287,930    115,086      (FULL + 함수로 다시 계산한 답과 같다)

모범 답안 서술

함수 기반 인덱스 CF11_FX01은 행을 넣을 때(그리고 인덱스를 만들 때) 계산한 FN_TOP_CTG(ITM_NO)의 값을 저장해 두고, 조회할 때는 함수를 다시 호출하지 않고 그 값을 읽는다. 오라클은 이 함수가 DETERMINISTIC(같은 인자면 언제나 같은 답)이라는 선언을 믿고 인덱스를 만들어 주었을 뿐, 함수가 CF01과 CF02를 읽는다는 것은 검사하지 않는다. 어제 CF02 한 줄(아이스크림 → 푸드)이 바뀌었지만 CF11의 인덱스 항목은 아무도 다시 계산하지 않았다. 인덱스 안의 아이스크림 9,350행은 아직 음료(100)다. 그래서 인덱스로 읽으면 옛 분류(264,496, 105,736)가 나오고, FULL로 함수를 다시 호출하면 새 분류(287,930, 115,086)가 나온다. 같은 문장이 계획에 따라 다른 답을 내며, 맞는 쪽은 지금의 분류를 읽은 FULL이다. 고치는 순서는 이렇다. ① 거짓 약속 위에 선 CF11_FX01을 지운다. ② 함수에서 DETERMINISTIC을 뗀다. ③ 리포트는 함수 대신 CF02 → CF01 → CF11 조인으로 바꾼다. 분류는 늘 지금의 CF01과 CF02에서 한 문장, 한 시점으로 읽는다(상품과 카테고리는 PK로 조인하므로 판매 행이 늘지 않는다). ④ 속도는 진짜 컬럼의 인덱스로 얻는다. CF11 (ITM_NO, SLE_QTY) 인덱스로 푸드 24종의 수량만 인덱스에서 읽으므로, Buffers가 8,204(FULL, 맞는 답) → 339로 줄고 테이블은 읽지 않는다. 함수 인덱스의 237과 비슷한 일량으로 맞는 답을 얻는다. DETERMINISTIC을 붙여도 되는 함수는 답이 인자만으로 정해지는 함수다. 테이블을 읽거나 시계(SYSDATE), 세션 설정, 패키지 변수, 시퀀스에 기대는 함수에는 붙이지 않는다. 오라클은 그 약속을 검사하지 않고, 함수 기반 인덱스나 결과 캐시에서는 그 약속이 '영속'이 된다.

해설

순서가 중요하다. 함수 기반 인덱스가 기대는 함수를 CREATE OR REPLACE하면 그 인덱스는 DISABLED가 되고, CF11에 대한 DML이 ORA-30554로 막힌다. 판매가 쌓이는 테이블이라면 인덱스를 먼저 지운다.
개편 뒤 인덱스를 다시 만들면 어떨까? 그날은 답이 맞는다. 그러나 '분류가 바뀔 때마다 판매 인덱스 110만 항목을 다시 만든다'는 절차를 잊는 날 같은 사고가 난다. 함수가 기대는 테이블을 오라클이 모르는 구조이기 때문이다.
한 문장 안에서도 문제가 생긴다. 함수 안 SQL은 호출마다 그 순간의 데이터를 보고, DETERMINISTIC은 한 번의 페치 안에서 같은 인자의 답을 기억한다. 긴 리포트 도중에 분류가 바뀌면 옛 분류와 새 분류가 섞이고, 섞이는 양도 페치 크기에 따라 달라진다. 조인은 문장이 시작된 시점 하나로 읽는다(15b화).
반정규화(CF11에 대분류 컬럼)도 방법이지만, 분류가 바뀌는 날 판매 110만 행을 고치는 배치가 필요하다. 판매 원장이 '판매 당시의 분류'를 원하는지 '지금의 분류'를 원하는지는 회계팀과 먼저 정한다. 함수 인덱스의 값은 둘 중 어느 것도 아니었다(인덱스를 만든 날이나 그 행을 넣은 날의 분류).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황