고객 상세의 「최근 주문 10건」에 어제 주문이 없다, 날짜순으로 줄은 서 있는데

기초SELECT한빛유통311 / 700

상황

고객센터 상담 화면의 고객 상세 아래쪽에는 「최근 주문 10건」 칸이 있고, 하루에 약 2만 번 열린다. 상담원 조민우가 캡처를 보내왔다. "어제 주문했다는 고객인데 이 칸에 어제 주문이 없어요. 날짜는 최신순으로 잘 줄 서 있는데 중간중간 비는 것 같아요." 화면 개발자의 답은 이랬다. "정렬은 ORDER BY ORD_DT DESC로 했고, 10건만 가져오려고 ROWNUM도 걸었다." 아래는 고객 CUS_000777을 열었을 때의 실행계획과 화면이다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · ORD_DT DATE(시분초 있음, 2023-01-01 ~ 2024-12-31) · 고객마다 약 50건 · 한 고객의 주문은 테이블 곳곳의 블록에 흩어져 있다
고객 CUS_000777의 주문은 52건이고, 마지막 주문은 2024-12-30 19:42(어제)다
인덱스 IX_T_ORD_CUS (CUS_ID): BLEVEL 1 · 리프 약 330블록 · 같은 CUS_ID 안에서는 ROWID 순서로 저장된다 · 통계 최신

원본 SQL

sql

SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD
 WHERE CUS_ID = :cus
   AND ROWNUM <= 10
 ORDER BY ORD_DT DESC

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |              |      1 |        |     10 |00:00:00.01 |      12 |          |
|   1 |  SORT ORDER BY                        |              |      1 |     10 |     10 |00:00:00.01 |      12 | 2048  (0)|
|*  2 |   COUNT STOPKEY                       |              |      1 |        |     10 |00:00:00.01 |      12 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD        |      1 |     50 |     10 |00:00:00.01 |      12 |          |
|*  4 |     INDEX RANGE SCAN                  | IX_T_ORD_CUS |      1 |     50 |     10 |00:00:00.01 |       2 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=10)
   4 - access("CUS_ID"=:CUS)

[화면 · CUS_000777 · 위에서부터]
  2024-11-02  2024-08-19  2024-07-30  2024-05-11  2024-03-08
  2023-12-27  2023-09-14  2023-06-02  2023-04-20  2023-01-15
  (실제 최근 주문 2024-12-30 · 2024-12-21 · 2024-12-09 … 는 화면에 없다)

문제

화면의 10건이 '가장 최근 10건'이 아닌 까닭을 실행계획 1번 줄과 2번 줄의 위아래 관계로 설명하시오.
결과가 '가장 최근 주문 10건'이 되도록 SQL을 고치고, 10건만 읽고 멈추도록 인덱스도 제안하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 한 명의 주문이 주문일 순서로 줄 선 인덱스 (앞부분이 (CUS_ID) 와 같다)
CREATE INDEX IX_T_ORD_CUS_DT ON T_ORD (CUS_ID, ORD_DT);
-- 사용처를 확인한 뒤 DROP INDEX IX_T_ORD_CUS;

-- 정렬은 안쪽, 10 건 자르기는 바깥
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM (SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
          FROM T_ORD
         WHERE CUS_ID = :cus
         ORDER BY ORD_DT DESC)
 WHERE ROWNUM <= 10;

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

xplan

----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |     10 |00:00:00.01 |      12 |
|*  1 |  COUNT STOPKEY                 |                 |      1 |        |     10 |00:00:00.01 |      12 |
|   2 |   VIEW                         |                 |      1 |     10 |     10 |00:00:00.01 |      12 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD           |      1 |     50 |     10 |00:00:00.01 |      12 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_T_ORD_CUS_DT |      1 |     10 |     10 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("CUS_ID"=:CUS)

[인덱스는 (CUS_ID) 그대로 두고 SQL만 고쳤을 때]
  INDEX RANGE SCAN 52건 → TABLE ACCESS 52번 → SORT ORDER BY STOPKEY → 10건 · Buffers 54

모범 답안 서술

1번 줄 SORT ORDER BY가 2번 줄 COUNT STOPKEY보다 위에 있다. 실행계획은 아래(안쪽)부터 일하므로, 4번 줄이 인덱스에서 고객의 주문을 찾는 대로 2번 줄이 ROWNUM을 매기다가 10건에서 멈추고, 1번 줄은 그 10건만 정렬한다. ROWNUM은 WHERE 절을 통과한 행에 정렬보다 먼저 매겨지기 때문이다. 같은 고객 안에서 인덱스 항목은 ROWID 순서로 놓여 있고, 이 테이블에서 ROWID 순서는 주문일 순서가 아니다. 그래서 52건 중 아무 10건이 뽑혔고, 화면에서는 그 10건이 최신순으로 줄 서 있었을 뿐이다. 어제 주문은 빠질 수밖에 없다. 정렬을 인라인 뷰 안에 넣고 바깥에서 ROWNUM <= 10으로 자르면 '정렬한 뒤 위에서 10건'이 된다. 다만 지금 인덱스 (CUS_ID)로는 고객의 52건을 다 읽고 정렬해야 하므로(SORT ORDER BY STOPKEY, Buffers 54) 결과는 맞아도 읽는 양이 늘어난다. (CUS_ID, ORD_DT) 인덱스를 만들면 고객 한 명의 주문이 주문일 순서로 줄 서 있다. 인덱스를 거꾸로(RANGE SCAN DESCENDING) 읽다가 10건에서 멈추므로 정렬 줄이 사라진다. Buffers는 12로 원본과 같지만(인덱스 2, 테이블 10), 이번에는 정말 '가장 최근 10건'이다.

해설

함정: 이 SQL은 테스트에서 맞게 보이기 쉽다. 주문을 날짜순으로 넣은 테스트 테이블이라면 ROWID 순서가 날짜순이라 늘 '가장 오래된 10건'이 나오고, 주문이 10건 이하인 고객은 결과가 언제나 맞다. 결과가 저장 순서와 실행계획에 기대고 있으면 SQL이 맞는 것이 아니다.
FETCH FIRST 10 ROWS ONLY(12c 이상)는 언제나 정렬한 뒤에 자르므로 이 실수가 생기지 않는다. 실행계획에는 WINDOW NOSORT STOPKEY(인덱스가 순서를 줄 때)나 WINDOW SORT PUSHED RANK로 나온다.
새 인덱스 (CUS_ID, ORD_DT)는 앞부분이 (CUS_ID)와 같아서 기존 인덱스를 쓰던 SQL도 그대로 처리된다. 사용처를 확인하고 기존 인덱스를 지우면 인덱스 개수가 늘지 않는다(교재 04화 인덱스의 DML 비용).
같은 시각의 주문 둘이 10번째 자리에 걸리면 순서가 흔들린다. 페이지를 넘기는 화면이라면 정렬 키 끝에 ORD_SEQ를 붙이고 인덱스에도 넣는다(SORT-002, SORT-005).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황