월요일 아침에 등록한 정산이 전부 지난 금요일 날짜다

모범 답안 SQL ​

-- 정산일 함수 — 숨은 입력(시각)을 인자로 꺼낸다 · 결과 캐시도 약속(DETERMINISTIC)도 붙이지 않는다
CREATE OR REPLACE FUNCTION FN_STLM_DT(p_at DATE DEFAULT SYSDATE) RETURN DATE IS
BEGIN
  RETURN TRUNC(p_at + 6/24);         -- 18:00 + 6시간 = 다음 날 0시 → 18시 이후 등록은 다음 날 정산
END;
/

-- 등록 — 그대로 (한 건에 한 번 · 그 INSERT 의 '지금')
INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
VALUES (:seq, :ord_seq, :cus_id, FN_STLM_DT, :amt);

-- 목록 — 문장마다 '지금' 을 한 번 (스칼라 서브쿼리)
SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_DT <= (SELECT FN_STLM_DT FROM dual);

-- 경계 시험 — 원장은 규칙 그대로 ('18시 이후는 다음 날')
SELECT FN_STLM_DT(TO_DATE('20240614 175959', 'YYYYMMDD HH24MISS')) AS fri_1759,   -- 06-14
       FN_STLM_DT(TO_DATE('20240614 180000', 'YYYYMMDD HH24MISS')) AS fri_1800,   -- 06-15
       FN_STLM_DT(TO_DATE('20240614 235959', 'YYYYMMDD HH24MISS')) AS fri_2359,   -- 06-15
       FN_STLM_DT(TO_DATE('20240617 090000', 'YYYYMMDD HH24MISS')) AS mon_0900    -- 06-17
  FROM dual;

개선 후 실행계획 교재 15b화 실측 · Buffers는 원본 규모 ​

[목록 문장 — rev01 + 스칼라 서브쿼리]
 함수 본문 실행 1 · 결과 캐시 찾기 0 · Buffers 640 · 계획 해시는 바뀐다 (스칼라 서브쿼리 줄이 붙는다)

[경계 시험]
 FRI_1759     FRI_1800     FRI_2359     MON_0900
 2024-06-14   2024-06-15   2024-06-15   2024-06-17

모범 답안 서술 ​

결과 캐시는 '같은 질문'인지를 인자와 의존 객체로만 판단한다. FN_STLM_DT는 인자가 없으니 언제 호출해도 같은 질문이 된다. 그래서 금요일 17:31에 처음 호출한 순간의 답 6/14가 인스턴스의 결과 캐시에 들어갔고, 그 뒤 모든 세션이 그 답을 받았다. 진짜 입력인 시계(SYSDATE)는 인자도 의존 객체도 아니다. 무효화는 의존 객체가 바뀌어 커밋될 때만 일어나는데, 이 결과가 기대는 객체는 함수 자신 하나뿐이라 정산이 계속 등록돼도 지워지지 않았다. 무효화가 고장 난 것이 아니다. 금요일 오후의 대조는 정산일이 전부 과거인 데이터로 같은 날 돌렸으니 '이 데이터에서는 같았다'는 뜻일 뿐이었다. 고치는 방법은 네 가지다. ① RESULT_CACHE를 뗀다. ② DETERMINISTIC도 뗀다. 시계를 읽는 함수에 '같은 입력이면 같은 답'은 거짓 약속이고, 아무도 이를 검사하지 않는다. ③ 숨은 입력(시각)을 인자로 꺼낸다(p_at DATE DEFAULT SYSDATE). 기존 호출부에는 그대로 '지금'이 들어가고, 시험할 때는 금요일 17:59:59, 18:00:00, 23:59:59, 월요일 09:00 같은 경계 시각을 넣어 규칙과 대조할 수 있다. ④ 목록은 (SELECT FN_STLM_DT FROM dual) 스칼라 서브쿼리로 문장마다 '지금'을 한 번만 구한다. 그 한 단어로 얻은 것은 없다. 2019 원본의 DETERMINISTIC이 이미 목록의 호출을 10만 → 1로 줄여 놓았고, 세 판의 계획과 Buffers(640), 계획 해시가 모두 같았다. 빨라지지도 않았는데 답만 틀렸다. 매일 자정에 INVALIDATE하는 잡은 처방이 아니다. 마감은 18시이고, 잡을 잊는 날 같은 사고가 난다. 6/14 17:30부터 6/17 09:40 사이에 등록된 정산의 정산일은 등록 화면 로그로 찾아 정정한다.

해설 ​

• 결과 캐시가 맞는 자리도 있다. 보정률 함수(고객 등급 → 비율)는 답이 인자와 테이블(M_CUS)로만 정해진다. 같은 한 단어가 그 함수에는 맞고 이 함수에는 틀렸다. 다만 1,984칸에 고객 2,000명이면 두 번째 판도 0이 되지는 않는다(15b화).
• 숨은 입력은 넷이다. 시계, 세션 시간대(CURRENT_DATE), 세션 NLS, 세션 상태(SYS_CONTEXT)는 모두 '처음 호출한 세션의 그 순간'이 인스턴스 전체에 남는다. 다른 세션, 다른 직원에게도 같은 답이 간다.
• 오라클이 막아 주지 않는다. SYSDATE를 문장에 직접 쓰면 /*+ RESULT_CACHE */를 거절하지만, 함수 안에 숨기면 받아 준다. result_cache_integrity = ENFORCED여도 DETERMINISTIC이라고 적힌 함수는 믿는다. RESULT_CACHE_MODE = FORCE면 힌트 한 줄 없이도 같은 사고가 난다.
• 등록 한 건의 정산일은 그 INSERT가 실행된 순간으로 정해진다. 17:59:59에 화면을 열고 18:00:01에 저장하면 다음 날 정산이다(2019년 규칙과 같다). 이 경계도 시험 목록에 넣는다.

관련 교재 ​

• 15b화 · 같은 답을 두 번 계산하지 마라
• 15b화 · 숨은 입력이 있는 함수에 캐시를 붙이면