같은 '마지막 주문' 뷰를 쓰는데 고객 상세는 0.001초, VIP 목록 스무 줄은 0.2초가 걸린다

모범 답안 SQL ​

-- ① 인덱스: 업무 규칙의 정렬 키(주문 시각, 주문번호)를 그대로 담는다 → 역순으로 읽으면 첫 항목이 '마지막 주문'
CREATE INDEX IX_T_ORD_CUS_DT_SEQ ON T_ORD (CUS_ID, ORD_DT, ORD_SEQ) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세 곳과 상담 화면의 계획을 확인한 뒤 VISIBLE
-- 옛 IX_T_ORD_CUS_DT (CUS_ID, ORD_DT) 는 새 인덱스의 앞부분이다. 쓰던 화면이 새 인덱스로 옮겨 간 것을 보고 지운다
-- DROP INDEX IX_T_ORD_CUS_DT;

-- ② 뷰: 같은 시각이면 주문번호가 큰 주문 (S1, S3 는 이 뷰를 그대로 쓴다)
CREATE OR REPLACE VIEW V_CUS_LAST_ORD AS
SELECT CUS_ID, ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM (SELECT o.CUS_ID, o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT,
               ROW_NUMBER() OVER (PARTITION BY o.CUS_ID ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC) AS RN
          FROM T_ORD o)
 WHERE RN = 1;

-- ③ S2: 뷰 대신 고객 20명마다 따로 묻는다 (역순으로 읽다 첫 건에서 멈춘다)
SELECT c.CUS_ID, c.CUS_NM, x.ORD_SEQ, x.ORD_DT, x.ORD_ST, x.ORD_AMT
  FROM M_CUS c
 OUTER APPLY (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT
                FROM T_ORD o
               WHERE o.CUS_ID = c.CUS_ID
               ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC
               FETCH FIRST 1 ROW ONLY) x
 WHERE c.CUS_GD = 'A'
 ORDER BY x.ORD_DT DESC, x.ORD_SEQ DESC;

-- ④ S3: 그대로 (전 고객이면 테이블을 한 번 읽고 정렬하는 쪽이 싸다)

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

[S2]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                     |      1 |        |     20 |00:00:00.01 |     110 |
|   1 |  SORT ORDER BY                  |                     |      1 |     20 |     20 |00:00:00.01 |     110 |
|   2 |   NESTED LOOPS OUTER            |                     |      1 |     20 |     20 |00:00:00.01 |     110 |
|*  3 |    TABLE ACCESS FULL            | M_CUS               |      1 |     20 |     20 |00:00:00.01 |      30 |
|*  4 |    VIEW                         | VW_LAT_5D21A0B3     |     20 |      1 |     20 |00:00:00.01 |      80 |
|*  5 |     WINDOW NOSORT STOPKEY       |                     |     20 |      1 |     20 |00:00:00.01 |      80 |
|   6 |      TABLE ACCESS BY INDEX ROWID| T_ORD               |     20 |     50 |     20 |00:00:00.01 |      80 |
|*  7 |       INDEX RANGE SCAN DESCENDING| IX_T_ORD_CUS_DT_SEQ|     20 |     50 |     20 |00:00:00.01 |      60 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("C"."CUS_GD"='A')
   4 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=1)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORD_DT") DESC ,INTERNAL_FUNCTION("O"."ORD_SEQ") DESC )<=1)
   7 - access("O"."CUS_ID"="C"."CUS_ID")

[S1] Buffers 5 그대로 (새 인덱스를 역순으로 · WINDOW NOSORT STOPKEY)
[S3] Buffers 926 그대로 (WINDOW SORT PUSHED RANK · 정렬 키에 ORD_SEQ 가 더해졌을 뿐)

모범 답안 서술 ​

S1이 빠른 까닭: c.CUS_ID = :cus는 상수 조건이다. 옵티마이저는 v.CUS_ID = c.CUS_ID를 거쳐 v.CUS_ID = :cus를 만들고, 이 조건이 뷰의 파티션 키(PARTITION BY CUS_ID)에 걸리므로 뷰 안으로 밀어 넣는다. 그래서 그 고객의 50건만 IX_T_ORD_CUS_DT로 역순으로 읽다 첫 건에서 멈춘다(Buffers 5). S2가 느린 까닭: S2의 v.CUS_ID = c.CUS_ID는 상수가 아니라 M_CUS 행마다 달라지는 조인 조건이다. 분석 함수가 있는 뷰에는 이런 조인 조건이 밀려 들어가지 못한다(조인 조건 Pushdown이 되지 않는다). 그래서 뷰를 먼저 다 만든다. 6번 줄이 T_ORD 10만 행을 다 읽고 5번 줄이 10만 행을 고객과 주문 시각으로 정렬해 2,000명의 마지막 주문을 만든 뒤, 2번 줄에서 20명만 남긴다. 2,000명 몫을 만들어 1%만 쓰는 셈이다(Buffers 926, S1의 185배). 하루 3천 번이면 278만 블록에 10만 행 정렬 3천 번이다. S2 고침: 뷰 대신 OUTER APPLY로 A 등급 20명마다 따로 묻는다. 고객 한 명의 주문을 인덱스로 역순으로 읽다 첫 건에서 멈추므로(5번 줄 WINDOW NOSORT STOPKEY) 고객마다 인덱스 3블록과 테이블 1블록이다. 20 × 4 + M_CUS 30 = 110블록으로, Buffers는 926 → 110이 된다(하루 278만 → 33만). OUTER라서 주문이 없는 A 고객도 빠지지 않는다. 업무 규칙: 지금 뷰는 ORDER BY o.ORD_DT DESC만 있어서 같은 시각의 주문이 둘이면 어느 것이 1번이 될지 정해지지 않는다. 뷰와 S2 모두 ORD_DT DESC, ORD_SEQ DESC로 정렬 키를 끝까지 준다. 그런데 인덱스가 (CUS_ID, ORD_DT)뿐이면 주문번호 순서는 인덱스에 없어서 STOPKEY로 멈추지 못하고 고객의 50건을 다 읽어 정렬해야 한다. 그래서 인덱스를 (CUS_ID, ORD_DT, ORD_SEQ)로 바꾼다. 옛 인덱스는 새 인덱스의 앞부분과 같아서 상담 화면 같은 다른 SQL도 새 인덱스를 쓸 수 있다. INVISIBLE로 만들어 계획을 확인한 뒤 공개하고, 옛 인덱스를 지운다. 칸이 하나 늘 뿐이고 인덱스 수는 그대로라 주문 적재 부담은 거의 같다. S3는 그대로 둔다: 전 고객 2,000명이면 고객마다 찾는 쪽이 2,000 × 4 = 약 8,000블록으로, 테이블을 한 번 읽고 정렬하는 지금 방식(926블록, 정렬 4.6MB가 메모리 안에서 끝난다)보다 아홉 배 무겁다. 대상이 많으면 한 번에 만드는 뷰가 맞다. 뷰의 정렬 키만 고쳤으므로 S3의 결과도 업무 규칙대로 정해진다. S1은 새 인덱스로도 5블록 그대로다.

해설 ​

• 같은 뷰, 다른 계획: 뷰는 SQL을 줄여 줄 뿐 계획을 정해 주지 않는다. 바깥 조건이 상수냐 조인 조건이냐에 따라 뷰 안으로 들어가는지가 갈린다. 분석 함수 뷰에 조인으로 대상을 거르는 화면이 붙으면 이 문제를 먼저 의심한다 (교재 08화 §4 변환 도감).
• KEEP으로 쓴다면: S3을 GROUP BY CUS_ID와 KEEP (DENSE_RANK LAST ORDER BY ORD_DT, ORD_SEQ)로 써도 한 번 읽는다. 칸이 몇 개뿐이면 인라인 뷰 없이 끝난다. 다만 S2처럼 대상이 적은 화면에는 집계라서 멈추지 못하는 점이 같다.
• 기준은 대상 수: '대상 수 × 한 번에 찾는 블록'과 '테이블 한 번 + 정렬'을 견준다. 이 데이터에서는 고객 약 230명이 갈림길이다(230 × 4 ≈ 926). A와 B 등급(200명)을 함께 보는 화면이 생기면 다시 재야 한다.
• 화면마다 SQL을 나누는 대가: S2가 뷰를 쓰지 않으면 '마지막 주문'의 규칙이 두 곳에 생긴다. 규칙이 바뀔 때 함께 고칠 수 있게 뷰 주석과 S2 주석에 서로를 적어 둔다.

관련 교재 ​

• 08화 · 변환 도감 (뷰 머징과 조건 이동)
• 09화 · Top-N 정렬과 STOPKEY
• 04화 · INVISIBLE 검증 절차