취소 주문은 549건뿐인데 6월 주문 내려받기가 취소 테이블을 2만 7천 번 찾아간다

모범 답안 SQL ​

-- 취소 사유는 취소 주문에만 있다 : 서브쿼리를 CASE 안에 넣어 CANCEL 행에서만 부른다
SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.ORD_AMT,
       CASE WHEN o.ORD_ST = 'CANCEL'
            THEN (SELECT c.CNCL_RSN
                    FROM T_ORD_CNCL c
                   WHERE c.ORD_SEQ = o.ORD_SEQ)
       END AS cncl_rsn
  FROM T_ORD_BIG o
 WHERE o.ORD_DT >= DATE '2024-06-01'
   AND o.ORD_DT <  DATE '2024-07-01'
 ORDER BY o.ORD_SEQ;

-- 고치기 전에 규칙을 데이터로 확인한다 (0 건이어야 결과가 같다)
-- SELECT COUNT(*) FROM T_ORD_CNCL c, T_ORD_BIG o
--  WHERE o.ORD_SEQ = c.ORD_SEQ AND o.ORD_ST <> 'CANCEL';

-- 외부 조인 안 : 취소 테이블 20,000 건(2,860 블록)을 통째로 읽어 해시 조인한다 (8,686 블록)
-- SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.ORD_AMT, c.CNCL_RSN
--   FROM T_ORD_BIG o, T_ORD_CNCL c
--  WHERE o.ORD_DT >= DATE '2024-06-01' AND o.ORD_DT < DATE '2024-07-01'
--    AND c.ORD_SEQ(+) = o.ORD_SEQ
--  ORDER BY o.ORD_SEQ;

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

-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |               |      1 |        |  27390 |00:00:00.06 |    7473 |          |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD_CNCL    |    549 |      1 |    549 |00:00:00.01 |    1647 |          |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD_CNCL |    549 |      1 |    549 |00:00:00.01 |    1098 |          |
|   3 |  SORT ORDER BY              |               |      1 |  27390 |  27390 |00:00:00.06 |    7473 | 1726K (0)|
|*  4 |   TABLE ACCESS FULL         | T_ORD_BIG     |      1 |  27390 |  27390 |00:00:00.04 |    5826 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("C"."ORD_SEQ"=:B1)
   4 - filter(("O"."ORD_DT">=TO_DATE(' 2024-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "O"."ORD_DT"<TO_DATE(' 2024-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

모범 답안 서술 ​

1번과 2번 줄은 취소 사유를 가져오는 스칼라 서브쿼리다. 6월 주문 27,390건마다 한 번씩 돌아(Starts 27,390) PK_T_ORD_CNCL을 찾았지만, 사유를 찾은 행은 취소 주문 549건뿐이다(A-Rows 549). 나머지 26,841번은 인덱스 두 블록을 읽고 빈손으로 돌아왔다. 이 서브쿼리에서만 55,329블록이고, 주문을 읽는 데는 5,826블록이 들었다. 캐시도 줄여 주지 못했다. 캐시의 열쇠는 :B1, 곧 바깥의 주문번호인데 주문번호는 행마다 달라 같은 열쇠가 다시 들어오지 않는다. 기억해 둔 값을 다시 쓸 일이 없으니 캐시는 행 수만큼 들여다보는 일만 더한다. 사유는 취소 주문에만 있으므로 서브쿼리를 CASE WHEN o.ORD_ST = 'CANCEL' THEN (SELECT …) END 안에 넣는다. CASE는 맞는 WHEN의 THEN만 계산하므로 서브쿼리는 CANCEL 549행에서만 돈다(Starts 549). Buffers는 61,155 → 7,473, A-Time은 0.31 → 0.06초다. 결과가 같으려면 'CANCEL이 아닌 주문에는 취소 행이 없다'는 규칙이 데이터에서도 지켜져야 한다. 철회한 취소의 행이 남아 있으면 원래 SQL은 그 사유를 보여 주고 고친 SQL은 NULL을 보여 준다. FK도 제약도 없으니 고치기 전에 o.ORD_ST <> 'CANCEL'인데 취소 행이 있는 주문을 세어 0건인지 확인한다. 외부 조인(c.ORD_SEQ(+) = o.ORD_SEQ)으로 바꿔도 결과는 같다. 해시 조인이면 취소 테이블 20,000건(2,860블록)을 통째로 읽어 8,686블록이 된다. 지금은 CASE 쪽(7,473블록)이 조금 싸고, 취소가 쌓일수록 외부 조인은 6월에 쓰지 않을 지난 취소까지 더 읽는다. NL 외부 조인이면 주문마다 PK를 찾으므로 지금과 같은 일을 한다.

해설 ​

• CASE는 필요한 갈래만 계산한다: CASE와 DECODE는 맞는 조건의 결과만 계산하므로, 스칼라 서브쿼리를 그 안에 넣으면 필요한 행에서만 돈다. 서브쿼리 안에 AND o.ORD_ST = 'CANCEL'을 더하는 방법도 비슷하게 듣는다. 그때 서브쿼리의 Starts는 27,390 그대로이고, 바깥 값만 보고 판정하는 FILTER가 인덱스 탐색을 549번으로 막는다.
• 캐시가 듣는 경우와 다르다: 입력 종류가 적으면 캐시가 반복을 받아 준다(교재 07화의 지역 8곳). 입력이 행마다 다르고(주문번호) 결과가 드문 행에만 있으면 캐시가 아니라 부르는 횟수를 줄여야 한다.
• 외부 조인이 나은 경우: 분기나 1년 치처럼 내려받는 범위가 커서 취소 행 대부분을 쓰게 되면 해시 외부 조인이 낫다. 다만 T_ORD_CNCL처럼 행이 긴 테이블은 사유 칸 하나만 써도 블록은 통째로 읽는다.
• 조건의 순서: CASE의 조건에 서브쿼리를 쓰면(WHEN (SELECT …) IS NOT NULL) 다시 모든 행에서 돈다. 싼 조건(컬럼 비교)을 WHEN에 두고 비싼 계산은 THEN 안에 둔다.

관련 교재 ​

• 07화 · 스칼라 서브쿼리 캐시 — 칸이 정해진 메모판
• 06화 · Starts — 왕복 횟수