같은 날은 주문 순서대로 보여 달라는 요청 하나에 장부 첫 화면이 14블록에서 5,826블록이 됐다

중급SELECT한빛유통317 / 700

상황

정산팀의 「주문 장부」 첫 화면은 최신 주문 10건을 보여 준다. 09화 rev01 뒤로 인덱스를 거꾸로 읽어 14블록이면 끝났다. 지난주 정산팀이 "같은 날 주문은 주문번호 순서(먼저 들어온 것부터)로 보여 달라. 종이 장부와 대조하기 쉽게"라고 요청했고, 개발자는 ORD_SEQ DESC를 ORD_SEQ로 바꿔 배포했다. 배포 다음 날부터 이 화면이 0.3초씩 걸린다. 정산팀 30명이 하루 종일 연다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정, 하루 913 ~ 914건이 같은 ORD_DT) · ORD_SEQ NOT NULL, 유일 · ORD_DT는 NULL 허용(실제 0건)
인덱스 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ): BLEVEL 2 · 리프 3,344블록 · CF 1,000,000(교재 09화 rev01) · ORD_SEQ가 NOT NULL이라 모든 행이 인덱스에 있다
이 인덱스는 기간 조회와 다른 화면의 키 기반 페이징(ORD_DT DESC, ORD_SEQ DESC)에도 쓰인다 · 통계 최신

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 ORDER BY ORD_DT DESC, ORD_SEQ
 FETCH FIRST 10 ROWS ONLY

실행계획 학습용 예시 · 교재 09화 rev01 규모

xplan

[지난주까지 : ORDER BY ORD_DT DESC, ORD_SEQ DESC]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |     10 |00:00:00.01 |      14 |          |
|*  1 |  VIEW                         |                   |      1 |     10 |     10 |00:00:00.01 |      14 |          |
|*  2 |   WINDOW NOSORT STOPKEY       |                   |      1 |     10 |     10 |00:00:00.01 |      14 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |      1 |   1000K|     10 |00:00:00.01 |      14 |          |
|   4 |     INDEX FULL SCAN DESCENDING| IX_ORD_BIG_DT_SEQ |      1 |     10 |     10 |00:00:00.01 |       4 |          |
-------------------------------------------------------------------------------------------------------------------

[지금 : ORDER BY ORD_DT DESC, ORD_SEQ]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |     10 |00:00:00.29 |    5826 |          |
|*  1 |  VIEW                         |                   |      1 |     10 |     10 |00:00:00.29 |    5826 |          |
|*  2 |   WINDOW SORT PUSHED RANK     |                   |      1 |   1000K|     10 |00:00:00.29 |    5826 | 4096  (0)|
|   3 |    TABLE ACCESS FULL          | T_ORD_BIG         |      1 |   1000K|   1000K|00:00:00.05 |    5826 |          |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC ,"ORD_SEQ")<=10)

문제

정렬 방향 하나를 바꿨을 뿐인데 실행계획이 인덱스 역순 읽기에서 테이블 전체 읽기와 정렬로 바뀐 까닭을 설명하시오.
정산팀의 요구(같은 날은 주문번호 오름차순)를 지키면서 다시 10건만 읽고 멈추도록 인덱스를 설계하고, 그 대가와 기존 인덱스를 어떻게 할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- (내림, 오름) 순서를 그대로 담은 인덱스 : 앞으로 읽으면 ORD_DT DESC, ORD_SEQ ASC
CREATE INDEX IX_ORD_BIG_DTD_SEQ ON T_ORD_BIG (ORD_DT DESC, ORD_SEQ) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 재고 (계획은 힌트로도 확인) → T2 승인 → VISIBLE
-- 기존 IX_ORD_BIG_DT_SEQ 는 그대로 둔다 (기간 조회와 (DESC, DESC) 키 기반 페이징이 쓴다)

-- SQL 은 그대로 (요구대로 같은 날은 주문번호 오름차순)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 ORDER BY ORD_DT DESC, ORD_SEQ
 FETCH FIRST 10 ROWS ONLY;

개선 후 실행계획 학습용 예시 · 교재 09화 rev01 규모

xplan

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                    |      1 |        |     10 |00:00:00.01 |      14 |          |
|*  1 |  VIEW                         |                    |      1 |     10 |     10 |00:00:00.01 |      14 |          |
|*  2 |   WINDOW NOSORT STOPKEY       |                    |      1 |     10 |     10 |00:00:00.01 |      14 |          |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG          |      1 |   1000K|     10 |00:00:00.01 |      14 |          |
|   4 |     INDEX FULL SCAN           | IX_ORD_BIG_DTD_SEQ |      1 |     10 |     10 |00:00:00.01 |       4 |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=10)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC ,"ORD_SEQ")<=10)

모범 답안 서술

인덱스 (ORD_DT, ORD_SEQ)는 앞으로 읽으면 (오름, 오름), 거꾸로 읽으면 (내림, 내림) 순서만 준다. 지난주까지의 ORD_DT DESC, ORD_SEQ DESC는 거꾸로 읽은 순서와 같아서 INDEX FULL SCAN DESCENDING으로 10건만 읽고 멈췄다(14블록). 바뀐 ORD_DT DESC, ORD_SEQ는 날짜는 내림차순이고 같은 날 안에서는 오름차순이라 방향이 섞였다. 어느 쪽으로 읽어도 이 순서가 나오지 않으므로 인덱스로 정렬을 대신할 수 없고, 옵티마이저는 3번 줄에서 100만 행(5,826블록)을 다 읽어 2번 줄 WINDOW SORT PUSHED RANK로 Top-N 정렬을 한다. 요구를 지키려면 인덱스가 그 방향을 담아야 한다. (ORD_DT DESC, ORD_SEQ) 인덱스는 날짜를 내림차순으로, 같은 날 안에서는 주문번호를 오름차순으로 저장한다. 이 인덱스를 앞으로(INDEX FULL SCAN) 읽으면 화면 순서 그대로 나오므로 WINDOW NOSORT STOPKEY로 10건에서 멈춘다. Buffers는 5,826 → 14로 돌아간다(인덱스 4, 테이블 10). ORD_SEQ가 NOT NULL이라 모든 행이 인덱스에 있다는 조건도 그대로 성립한다. 대가가 있다. 컬럼에 DESC를 붙인 인덱스는 내부적으로 SYS_OP_DESCEND를 쓰는 함수 기반 인덱스다. 기존 IX_ORD_BIG_DT_SEQ는 기간 조회와 (내림, 내림) 키 기반 페이징이 쓰고 있어 지울 수 없으므로 인덱스가 하나 더 는다. 주문 한 건을 넣을 때마다 고칠 인덱스가 하나 더 늘어 적재 비용이 들고, 리프 약 3,300블록만큼 공간이 든다. 30명이 하루 종일 여는 화면이라 이 비용을 치를 만하다고 판단하고, 적재 비용은 T2 승인서에 적는다.

해설

요구를 다시 묻는 안: 정산팀이 원하는 것이 종이 장부와의 대조라면, 화면 전체를 (내림, 오름)으로 바꾸는 대신 날짜 하나를 고르면 그날 주문을 주문번호 순으로 보여 주는 화면이 더 맞을 수 있다. WHERE ORD_DT = :d ORDER BY ORD_SEQ는 기존 인덱스를 앞으로 읽어 끝난다. 인덱스를 더하기 전에 화면 요구 재협상서로 확인할 거리다(교재 09화 §13).
첫 화면만이라면: WHERE ORD_DT = (SELECT MAX(ORD_DT) FROM T_ORD_BIG) ORDER BY ORD_SEQ FETCH FIRST 10 ROWS ONLY도 기존 인덱스로 약 16블록에 끝난다. 하지만 다음 페이지가 날짜 경계를 넘으면 쓸 수 없다.
이 순서로 키 기반 페이징을 하려면 조건도 방향을 따라야 한다. ORD_DT < :p_dt OR (ORD_DT = :p_dt AND ORD_SEQ > :p_seq)처럼 날짜는 작은 쪽, 주문번호는 큰 쪽으로 이어 간다.
교재 09화에서 본 대로 이 버전은 INVISIBLE 인덱스를 세션에서 켜도 정렬을 대신하는 계획을 스스로 고르지 않았다. 정렬 대체 계획은 힌트로 따로 확인한다(교재 09화 §7).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 09화 rev01 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 09화 rev01 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황