「최근 결제」 목록 맨 위에 결제도 안 된 주문 20건이 뜬다

중급SELECT한빛유통42 / 700

상황

결제팀 모니터의 「최근 결제」 칸은 방금 결제된 주문 20건을 보여 준다. 결제팀 여섯 명이 하루 종일 띄워 두고 30초마다 새로 고친다. 결제 연동을 개편한 뒤로 이 칸에는 결제 시각이 빈 주문만 20줄 뜬다. 조민우는 "PAY_DT 인덱스가 있으니 역순으로 읽으면 될 것 같은데, 힌트를 줘도 정렬을 한다"고 한다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ)
PAY_DT DATE: 결제 시각(시분초 있음) · 결제 전이면 NULL · NOT NULL 제약 없음 · 지금 미결제 41건 (문제 설정: IDX-004와 같다. 2024.12 결제 연동 개편 뒤의 모습이라고 본다)
인덱스 IX_T_ORD_PAY (PAY_DT): BLEVEL 1 · 리프 265 · 통계 최신

원본 SQL

sql

SELECT *
  FROM (SELECT ORD_SEQ, CUS_ID, PAY_DT, PAY_TP, PAY_AMT
          FROM T_ORD
         ORDER BY PAY_DT DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |      1 |        |     20 |00:00:00.03 |     896 |          |
|*  1 |  COUNT STOPKEY           |       |      1 |        |     20 |00:00:00.03 |     896 |          |
|   2 |   VIEW                   |       |      1 |    100K|     20 |00:00:00.03 |     896 |          |
|*  3 |    SORT ORDER BY STOPKEY |       |      1 |    100K|     20 |00:00:00.03 |     896 | 4096  (0)|
|   4 |     TABLE ACCESS FULL    | T_ORD |      1 |    100K|    100K|00:00:00.01 |     896 |          |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)

[화면에 나온 20줄]  PAY_DT 가 모두 (null)

문제

20줄이 모두 미결제 주문인 이유를 설명하시오.
IX_T_ORD_PAY가 정렬을 대신하지 못하고 10만 행을 정렬하는 이유를 설명하시오.
화면의 뜻에 맞게 SQL을 고쳐 인덱스를 역순으로 20건만 읽고 멈추게 하시오.

개선 SQL원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.

개선 사유무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자

모범답안

모범 답안 SQL

sql

SELECT *
  FROM (SELECT ORD_SEQ, CUS_ID, PAY_DT, PAY_TP, PAY_AMT
          FROM T_ORD
         WHERE PAY_DT IS NOT NULL          -- 화면의 뜻은 '최근 결제'. 미결제는 「결제 대기」 칸에서 따로 본다
         ORDER BY PAY_DT DESC)
 WHERE ROWNUM <= 20

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

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |              |      1 |        |     20 |00:00:00.01 |      23 |
|*  1 |  COUNT STOPKEY                |              |      1 |        |     20 |00:00:00.01 |      23 |
|   2 |   VIEW                        |              |      1 |     20 |     20 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD        |      1 |  99959 |     20 |00:00:00.01 |      23 |
|   4 |     INDEX FULL SCAN DESCENDING| IX_T_ORD_PAY |      1 |     20 |     20 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)

모범 답안 서술

오라클은 NULL을 어떤 값보다도 큰 값처럼 정렬한다. 그래서 오름차순에서는 맨 뒤(NULLS LAST), 내림차순에서는 맨 앞(NULLS FIRST)에 온다. ORDER BY PAY_DT DESC의 맨 위에는 미결제 41건이 먼저 오고, ROWNUM 20이 그중 20건을 잘라 화면에 보냈다. 결제 연동 개편으로 미결제가 NULL로 남게 되면서 생긴 일이다. IX_T_ORD_PAY는 단일 컬럼 인덱스라 PAY_DT가 NULL인 41건을 담지 않는다. 인덱스를 역순으로 읽으면 결제된 주문은 순서대로 나오지만, 맨 앞에 와야 할 미결제 41건은 나오지 않는다. 인덱스만으로는 '모든 행의 정렬된 순서'를 낼 수 없으므로 옵티마이저는 이 인덱스로 정렬을 대신하지 않는다. 힌트도 소용없다. 그래서 10만 행을 다 읽어 SORT ORDER BY STOPKEY로 정렬한 뒤 앞 20건을 자른다(Buffers 896). ORDER BY PAY_DT DESC NULLS LAST로 미결제를 뒤로 보내는 것만으로는 계획이 그대로다. NULLS LAST여도 NULL 행이 인덱스에 없다는 사실은 바뀌지 않으므로, 맨 뒤에 붙을 미결제 행까지 내려면 여전히 10만 행을 정렬해야 한다. 화면의 뜻은 '최근 결제'이므로 WHERE PAY_DT IS NOT NULL을 더한다. 그러면 답이 되는 행이 모두 인덱스에 있다는 것이 조건으로 보장된다. 옵티마이저는 INDEX FULL SCAN DESCENDING으로 가장 최근 결제부터 읽다가 20건에서 멈추고(COUNT STOPKEY), 정렬도 하지 않는다. 인덱스 3블록과 테이블 20블록, Buffers는 896 → 23이다. 30초마다 여섯 대가 부르므로 하루 약 1,550만 → 40만 블록이다.

해설

함정: ORDER BY PAY_DT DESC NULLS LAST로 바꾸면 화면은 맞아 보이지만 계획은 그대로다. NULL 행이 인덱스에 없으니 인덱스 순서만으로는 맨 뒤에 붙을 NULL 행까지 낼 수 없고, 10만 행을 정렬한 뒤 자른다.
PAY_DT에 NOT NULL을 걸 수는 없다. 미결제를 NULL로 나타내는 모델이고 실제로 41건이 있다. 이럴 때는 조건으로 보장한다(교재 09화 §7의 조건 ②를 WHERE ORD_DT IS NOT NULL로 채운 것과 같다).
결제 시각이 같은 주문이 있으면 20번째 경계에서 어느 주문이 나올지 실행마다 달라질 수 있다. 그럴 수 있다면 ORDER BY PAY_DT DESC, ORD_SEQ DESC와 (PAY_DT, ORD_SEQ) 인덱스로 정렬 키를 유일하게 한다.
미결제 주문은 따로 「결제 대기」 칸에서 보여 준다. 그 조회의 인덱스는 IDX-004에서 다뤘다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황