주문상품 모니터에서 쪽 끝에 걸린 주문은 버거만 보이고 감자와 콜라가 사라진다

모범 답안 SQL ​

-- 정렬 키 순서 그대로의 인덱스 · 기존 X01 (ORD_DT, ORD_HMS) 을 앞부분으로 품는다
CREATE INDEX ORD_ITEM_X02 ON ORD_ITEM (ORD_DT, ORD_HMS, ORD_NO, ITEM_ID) INVISIBLE;
-- X01 을 쓰던 SQL(일자별 집계 배치 등)이 X02 로도 같은 범위를 읽는지 확인한 뒤
-- ALTER INDEX ORD_ITEM_X02 VISIBLE;  DROP INDEX ORD_ITEM_X01;

-- 다음 쪽 : 포스트잇 = 앞 쪽 마지막 줄의 (ORD_DT, ORD_HMS, ORD_NO, ITEM_ID)
SELECT ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
  FROM (SELECT ORD_NO, ITEM_ID, ORD_ITEM_QTY, ORD_DT, ORD_HMS
          FROM ORD_ITEM
         WHERE ORD_DT <= :p_dt
           AND (ORD_DT < :p_dt
                OR (ORD_DT = :p_dt
                    AND (ORD_HMS < :p_hms
                         OR (ORD_HMS = :p_hms
                             AND (ORD_NO < :p_no
                                  OR (ORD_NO = :p_no AND ITEM_ID < :p_item))))))
         ORDER BY ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC, ITEM_ID DESC)
 WHERE ROWNUM <= 30;

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

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |              |      1 |        |     30 |00:00:00.01 |       7 |
|*  1 |  COUNT STOPKEY                 |              |      1 |        |     30 |00:00:00.01 |       7 |
|   2 |   VIEW                         |              |      1 |     30 |     30 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORD_ITEM     |      1 |   2472K|     30 |00:00:00.01 |       7 |
|*  4 |     INDEX RANGE SCAN DESCENDING| ORD_ITEM_X02 |      1 |     30 |     30 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=30)
   4 - access("ORD_DT"<=:P_DT)
       filter(("ORD_DT"<:P_DT OR ("ORD_DT"=:P_DT AND ("ORD_HMS"<:P_HMS OR ("ORD_HMS"=:P_HMS AND
              ("ORD_NO"<:P_NO OR ("ORD_NO"=:P_NO AND "ITEM_ID"<:P_ITEM))))))))

모범 답안 서술 ​

정렬 키 (ORD_DT, ORD_HMS)는 유일하지 않다. 같은 주문의 상품 줄은 두 값이 모두 같고, 같은 초에 들어온 다른 매장의 주문도 두 값이 같다. 4번 줄은 ORD_DT <= :p_dt로 범위를 잡고 ORD_DT < :p_dt OR ORD_HMS < :p_hms로 거르므로, 다음 쪽에는 포스트잇('20131230', '184512')보다 "작은" 줄만 나온다. 포스트잇과 값이 똑같은 줄, 곧 998771의 감자와 콜라, 같은 초에 들어온 998772의 두 줄은 앞 쪽 30줄 안에 들지 못했고 다음 쪽 조건에도 걸리지 않아 건너뛰어진다. 일량은 6블록으로 문제가 없고, 틀린 것은 결과다. 정렬 키를 PK까지 붙여 ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC, ITEM_ID DESC로 유일하게 끝내고, 포스트잇도 네 값으로 바꿔 "그 줄 바로 다음"부터 읽는다. 이때 인덱스가 (ORD_DT, ORD_HMS)뿐이면 옵티마이저는 같은 초 안의 순서를 인덱스에서 얻지 못하므로, 포스트잇 이전의 줄을 전부(최악이면 247만 줄) 읽어 정렬한 뒤 30줄을 고른다. 정렬 키 순서 그대로의 (ORD_DT, ORD_HMS, ORD_NO, ITEM_ID) 인덱스가 있어야 역순으로 30줄만 읽고 멈춘다(블록은 6 → 7로 거의 그대로다). 인덱스는 ORD_ITEM_X01을 넓히는 쪽으로 정한다. 새 인덱스는 X01을 앞부분으로 품으므로 X01을 쓰던 일자별 집계 배치도 같은 범위를 읽을 수 있다. 둘 다 두면 하루 3,390줄을 넣을 때마다 인덱스 두 개를 고쳐야 한다. INVISIBLE로 만들어 X01을 쓰던 SQL의 계획과 일량을 확인한 뒤 X01을 지운다. 대가로 리프 블록이 ORD_NO와 ITEM_ID만큼 커진다. 정합성은 한 쪽이 아니라 하루치를 끝까지 넘겨 모은 결과로 확인한다. 화면으로 모은 줄 수가 엑셀 내려받기와 같은지(3,390 = 3,390), 두 결과의 MINUS 양방향이 0 / 0인지, 정렬 키 네 값의 중복이 0인지 본다.

해설 ​

• ROWID를 동점 깨기로 쓰는 답: 비유일 인덱스는 같은 키 안에서 ROWID 순으로 저장되므로 ORDER BY ORD_DT DESC, ORD_HMS DESC, ROWID DESC로 인덱스를 바꾸지 않고 버티는 방법도 떠올릴 수 있다. 하지만 ROWID는 테이블을 옮기거나(MOVE, SHRINK) 파티션을 바꾸면 달라지고, 옵티마이저가 그 정렬을 인덱스로 대신한다는 보장도 없다. 업무 키(PK)로 끝내는 편이 안전하다.
• 주문 단위로 쪽을 나누는 답: 메뉴팀이 주문을 통째로 보고 싶어 한다면 주문(ORD)을 30건씩 키 기반으로 자르고 그 주문들의 상품 줄을 붙이는 방법도 있다(페이징 후 조인). 쪽마다 줄 수가 달라지므로 화면 요구를 먼저 묻는다.
• ORD_ITEM_QTY까지 인덱스에 넣으면 테이블을 읽지 않는다. 화면 칸이 하나 늘면 다시 테이블에 가는 설계라서 이 문제에서는 넣지 않았다.
• INVISIBLE 인덱스는 세션에서 켜도 옵티마이저가 정렬 대체 계획을 고르지 않은 경우가 있었다(교재 09화 §7). 정렬 대체 계획은 힌트로 따로 확인하고, INVISIBLE 검증은 "안 바뀌어야 할 SQL이 바뀌는가"를 잡는 데 쓴다.

관련 교재 ​

• 09화 · 키 기반 페이징 — 포스트잇
• 09화 · 인덱스가 정렬을 대신한다 (INVISIBLE 검증)
• 04화 · 인덱스의 DML 비용