부분 취소 재정산을 연 뒤로 월초 정산 보정 배치가 0.1초에서 12초가 됐다

모범 답안 SQL ​

-- 두 컬럼 NOT IN → 두 등치 조건의 NOT EXISTS (해시 안티 조인으로 한 번씩 읽는다)
INSERT INTO T_ORD_STLM (STLM_SEQ, ORD_SEQ, CUS_ID, STLM_DT, STLM_AMT)
SELECT SQ_STLM.NEXTVAL, o.ORD_SEQ, o.CUS_ID, o.PAY_DT, o.PAY_AMT
  FROM T_ORD o
 WHERE o.PAY_DT >= :v_from
   AND o.PAY_DT <  :v_to
   AND o.ORD_ST = 'COMPLETE'
   AND NOT EXISTS (SELECT 1
                     FROM T_ORD_STLM s
                    WHERE s.ORD_SEQ = o.ORD_SEQ
                      AND s.STLM_DT = o.PAY_DT);
-- 재발 방지(재무팀과 정한 뒤) : ALTER TABLE T_ORD_STLM MODIFY (ORD_SEQ NOT NULL, STLM_DT NOT NULL);

개선 후 실행계획 학습용 예시 · 주문 건수는 적재 데이터 그대로 ​

------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT         |            |      1 |        |      0 |00:00:00.06 |    1562 |          |
|   1 |  LOAD TABLE CONVENTIONAL | T_ORD_STLM |      1 |        |      0 |00:00:00.06 |    1562 |          |
|   2 |   SEQUENCE               | SQ_STLM    |      1 |        |     23 |00:00:00.06 |    1536 |          |
|*  3 |    HASH JOIN ANTI        |            |      1 |     37 |     23 |00:00:00.06 |    1536 | 1305K (0)|
|*  4 |     TABLE ACCESS FULL    | T_ORD      |      1 |   3699 |   3699 |00:00:00.01 |     896 |          |
|   5 |     TABLE ACCESS FULL    | T_ORD_STLM |      1 |    100K|    100K|00:00:00.02 |     640 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("S"."ORD_SEQ"="O"."ORD_SEQ" AND "S"."STLM_DT"="O"."PAY_DT")
   4 - filter(("O"."ORD_ST"='COMPLETE' AND "O"."PAY_DT">=:V_FROM AND "O"."PAY_DT"<:V_TO))

[대조]  넣은 행 23 = 23 · STLM_AMT 합계 원래 SQL 과 같음

모범 답안 서술 ​

5번 줄 TABLE ACCESS FULL T_ORD_STLM의 Starts가 3,699다. 6월 COMPLETE 주문 3,699건(4번 줄)마다 3번 줄의 FILTER가 서브쿼리를 한 번씩 열어 정산 테이블을 처음부터 훑었다. 짝이 있는 주문은 그 행을 만날 때까지(평균 320블록쯤), 짝이 없는 23건은 끝까지(640블록) 읽어 모두 1,190K 블록이 됐다. 주문번호가 모두 달라 FILTER의 메모판도 소용이 없다. FILTER가 된 까닭은 두 컬럼 NOT IN이다. 컬럼이 NULL을 허용하면 NOT IN은 NOT EXISTS와 뜻이 다르므로 옵티마이저가 그냥 안티 조인으로 바꿀 수 없다. 한 컬럼일 때는 NULL을 아는 안티 조인(HASH JOIN ANTI NA)으로 펼쳤지만, NA 안티 조인은 컬럼 하나짜리 NOT IN에만 쓰인다. 두 컬럼이 되자 펼치기를 포기하고, LNNVL(…<>:B1) AND LNNVL(…<>:B2)로 NULL의 뜻을 지키는 FILTER가 남았다. NOT EXISTS에 두 등치 조건(s.ORD_SEQ = o.ORD_SEQ AND s.STLM_DT = o.PAY_DT)을 적으면 HASH JOIN ANTI로 두 테이블을 한 번씩 읽는다. Buffers는 1,192K → 1,562, 시간은 12.43초 → 0.06초다. 분기 보정(석 달, 약 11,200건)도 같은 1,562블록 안팎이다. 두 SQL이 같은 답을 내는 조건은 비교하는 네 컬럼에 NULL이 없는 것이다. T_ORD.ORD_SEQ는 PK이고 PAY_DT는 범위 조건이 있어 NULL일 수 없으며, T_ORD_STLM의 두 컬럼은 NUM_NULLS가 0이다. 그래서 지금은 23건으로 답이 같다. 정산 쪽에 주문번호는 있고 정산일이 빈 행이 생기면 NOT IN은 그 주문을 빼고 NOT EXISTS는 넣는다. 재발을 막으려면 두 컬럼에 NOT NULL 제약을 건다(ALTER TABLE T_ORD_STLM MODIFY (ORD_SEQ NOT NULL, STLM_DT NOT NULL)). 그러면 NOT IN을 그대로 써도 옵티마이저가 일반 안티 조인으로 펼친다. 대가는 제약을 거는 순간 10만 행을 검사하는 동안 테이블에 락이 걸리고, 주문 없이 넣는 정산(수기 조정 같은 것)을 받을 수 없게 된다는 점이다. 이것은 재무팀과 정해야 한다.

해설 ​

• 시간이 데이터와 함께 는다: FILTER의 일량은 바깥 행 수 × 정산 테이블 크기로 는다. 석 달을 보정하면 바깥이 3배가 되고, 정산 테이블이 쌓이면 안쪽도 커진다. 안티 조인은 두 테이블을 한 번씩 읽는 몫에서 끝난다.
• 다른 정답: NOT IN을 남기고 서브쿼리에 WHERE s.ORD_SEQ IS NOT NULL AND s.STLM_DT IS NOT NULL을 적어도 일반 안티 조인으로 펼쳐진다. 결국 NOT EXISTS와 같은 뜻을 만드는 것이므로 처음부터 NOT EXISTS로 쓰는 편이 읽기 쉽다.
• 대조: 바꾸기 전과 뒤에 넣은 건수(23건)와 금액 합계를 맞춰 보고, 정산 테이블의 두 컬럼에 NULL이 정말 없는지(COUNT(*) - COUNT(ORD_SEQ)) 그날 다시 센다.
• 인덱스는 필요 없다: 한 달에 한 번 도는 배치이고 두 테이블을 한 번씩 읽어 1,562블록이면 된다. 정산 테이블에 인덱스를 더하면 밤마다 10만 행을 다시 넣는 적재가 느려진다(교재 14화).

관련 교재 ​

• 08화 · NOT IN 과 NULL — Null-Aware 안티 조인
• 14화 · 야간 배치가 아침을 넘긴다