하루 3,000번 여는 「미정산 주문」 화면이 20건을 보여 주려고 1,536블록을 읽는다

실전DDL한빛유통276 / 700

상황

정산팀의 「미정산 주문」 화면은 결제가 끝났는데 아직 정산 행이 없는 주문을 최근 주문부터 20건씩 보여 준다. 정산 담당 10명이 새로 고치고 다음 쪽을 넘기며 하루 3,000번쯤 연다. 한 번에 0.05초라 아무도 불평하지 않지만, DBA의 상위 SQL 목록에서 이 화면이 하루 논리 읽기 약 461만 블록으로 3위에 올라 있다. 박팀장은 인덱스를 만들어도 좋다고 했다. 다만 밤마다 정산 테이블을 통째로 다시 넣는 적재 배치에 주는 영향과, 마지막 쪽에서 무슨 일이 생기는지까지 숫자로 적어 오라고 했다.

테이블과 인덱스

T_ORD: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · COMPLETE 90,000행 · 오늘(2024-12-31) 주문 136건과 어제 주문 136건은 모두 COMPLETE · 하루치 주문은 테이블 전체에 흩어져 있다(ORD_SEQ가 주문 시각 순이 아니다)
T_ORD_STLM: 99,864행 · 640블록 · 인덱스 없음 · 01시 정산 적재 배치가 TRUNCATE 뒤 INSERT /*+ APPEND */로 전날까지의 결제분 전부를 다시 넣는다(0.1초 안팎)
미정산은 늘 오늘 결제분뿐이다. 어제까지는 밤 배치가 넣고, 배치가 빠뜨린 건은 월초 보정 배치가 메운다 · 지금 미정산 136건(7쪽)
통계 최신

원본 SQL

sql

SELECT *
  FROM (SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.PAY_DT, o.PAY_AMT
          FROM T_ORD o
         WHERE o.ORD_ST = 'COMPLETE'
           AND NOT EXISTS (SELECT 1 FROM T_ORD_STLM s WHERE s.ORD_SEQ = o.ORD_SEQ)
         ORDER BY o.ORD_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.05 |    1536 |          |
|*  1 |  COUNT STOPKEY          |            |      1 |        |     20 |00:00:00.05 |    1536 |          |
|   2 |   VIEW                  |            |      1 |    136 |     20 |00:00:00.05 |    1536 |          |
|*  3 |    SORT ORDER BY STOPKEY|            |      1 |    136 |     20 |00:00:00.05 |    1536 | 2048  (0)|
|*  4 |     HASH JOIN RIGHT ANTI|            |      1 |    136 |    136 |00:00:00.05 |    1536 | 5932K (0)|
|   5 |      TABLE ACCESS FULL  | T_ORD_STLM |      1 |  99864 |  99864 |00:00:00.01 |     640 |          |
|*  6 |      TABLE ACCESS FULL  | T_ORD      |      1 |  90000 |  90000 |00:00:00.01 |     896 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   4 - access("S"."ORD_SEQ"="O"."ORD_SEQ")
   6 - filter("O"."ORD_ST"='COMPLETE')

문제

20건을 보여 주는 화면이 1,536블록을 읽는 까닭을 실행계획 3 ~ 6번 줄로 설명하시오. ROWNUM이 있는데도 부분범위 처리가 안 되는 까닭도 쓰시오.
첫 쪽이 20건 남짓만 읽고 멈추도록 인덱스를 설계하고 SQL을 고치시오.
고친 뒤 마지막 쪽(16건)과 '다음 쪽 없음'을 확인할 때 일어나는 일, 인덱스가 야간 적재 배치에 주는 대가를 숫자로 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 최근 순으로 읽을 길 + 주문마다 정산 여부를 바로 확인할 길
CREATE INDEX IX_ORD_DT   ON T_ORD (ORD_DT);
CREATE INDEX IX_STLM_ORD ON T_ORD_STLM (ORD_SEQ);

-- 업무 규칙(미정산은 오늘 결제분뿐, 그 앞의 누락은 보정 배치)을 기간 조건으로 적어 읽는 범위의 상한을 정한다
SELECT *
  FROM (SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.PAY_DT, o.PAY_AMT
          FROM T_ORD o
         WHERE o.ORD_DT >= TRUNC(SYSDATE) - 1
           AND o.ORD_ST = 'COMPLETE'
           AND NOT EXISTS (SELECT 1 FROM T_ORD_STLM s WHERE s.ORD_SEQ = o.ORD_SEQ)
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 20;
-- 다음 쪽 : 앞 쪽 마지막 행의 ORD_DT 를 받아 o.ORD_DT < :last_dt 를 더한다 (키 기반 페이징)

개선 후 실행계획 학습용 예시 · 주문 건수는 적재 데이터 그대로

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |             |      1 |        |     20 |00:00:00.01 |      63 |
|*  1 |  COUNT STOPKEY                  |             |      1 |        |     20 |00:00:00.01 |      63 |
|   2 |   VIEW                          |             |      1 |     21 |     20 |00:00:00.01 |      63 |
|   3 |    NESTED LOOPS ANTI            |             |      1 |     21 |     20 |00:00:00.01 |      63 |
|*  4 |     TABLE ACCESS BY INDEX ROWID | T_ORD       |      1 |    272 |     20 |00:00:00.01 |      23 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IX_ORD_DT   |      1 |    272 |     20 |00:00:00.01 |       3 |
|*  6 |     INDEX RANGE SCAN            | IX_STLM_ORD |     20 |      1 |      0 |00:00:00.01 |      40 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   4 - filter("O"."ORD_ST"='COMPLETE')
   5 - access("O"."ORD_DT">=TRUNC(SYSDATE@!)-1)
   6 - access("S"."ORD_SEQ"="O"."ORD_SEQ")

[마지막 쪽 · 다음 쪽 없음]  기간 조건이 있으면 어제와 오늘 주문 272건 + 정산 인덱스 272번이 상한 (약 820블록)
                            기간 조건이 없으면 주문 9만 건을 거꾸로 끝까지 읽고 정산 인덱스를 9만 번 찾는다 (약 28만 블록)

모범 답안 서술

4번 줄 HASH JOIN RIGHT ANTI는 정산 테이블 99,864행(5번 줄, 640블록)을 해시 테이블로 올리고 주문 90,000행(6번 줄, 896블록)을 모두 대어 본 뒤, 짝이 없는 136건을 3번 줄 SORT ORDER BY STOPKEY로 넘긴다. 정렬은 136건을 모두 받아야 맨 앞 20건을 정할 수 있고, 주문을 최근 순으로 읽을 인덱스도 없다. 그래서 ROWNUM <= 20이 있어도 두 테이블을 끝까지 다 읽은 뒤에야 첫 행이 나온다. 한 번에 1,536블록, 하루 3,000번이면 약 461만 블록이다. 부분범위 처리를 하려면 주문을 최근 순으로 읽어 나가면서 주문마다 정산 행이 있는지 바로 확인해야 한다. 그래서 T_ORD (ORD_DT)와 T_ORD_STLM (ORD_SEQ) 인덱스를 함께 만든다. 그러면 IX_ORD_DT를 거꾸로 읽으며 주문 하나마다 IX_STLM_ORD를 한 번 찾아보는 NESTED LOOPS ANTI가 되고, 짝이 없는 주문 20건을 채우면 COUNT STOPKEY에서 멈춘다. 첫 쪽은 주문 20건(테이블 20블록과 인덱스 3블록)과 정산 인덱스 20번(40블록)으로 Buffers 1,536 → 63이다. 함정은 마지막 쪽이다. 남은 미정산이 16건뿐인 7쪽이나 '다음 쪽 없음'을 확인할 때는 20건을 채우지 못하므로, 기간 조건이 없으면 NL 안티 조인이 주문 9만 건을 끝까지 거꾸로 읽으며 정산 인덱스를 9만 번 찾는다. 약 28만 블록으로 고치기 전보다 180배 나쁘다. 그래서 업무 규칙(미정산은 오늘 결제분뿐이고 그보다 오래된 누락은 보정 배치가 맡는다)을 o.ORD_DT >= TRUNC(SYSDATE) - 1로 SQL에 적어 읽는 범위의 상한을 정한다. 그러면 어느 쪽이든 어제와 오늘 주문 272건과 정산 인덱스 272번, 약 820블록을 넘지 않는다. 대가는 인덱스 둘이다. T_ORD (ORD_DT)는 주문이 들어올 때마다 인덱스 항목 하나를 더 고치는 정도라 작다(하루 137건, 날짜가 늘 오른쪽 끝에 쌓인다). T_ORD_STLM (ORD_SEQ)는 밤마다 TRUNCATE 뒤 APPEND로 10만 행을 다시 넣을 때 적재 끝에 인덱스를 정렬해 다시 만드는 일이 더해진다(0.1초 → 약 0.3초, redo도 그만큼 는다). 낮 시간 약 461만 블록을 약 19만 블록(3,000 × 63)으로 줄이는 대가로는 작으므로, 이 숫자를 승인서에 적어 둔다.

해설

인덱스를 하나만 만든다면: T_ORD (ORD_DT)만 만들면 정산 테이블(640블록)을 해시로 올린 뒤 주문을 최근 순으로 대어 보다 20건에서 멈출 수 있다(HASH JOIN RIGHT ANTI는 build가 끝나면 probe 행을 바로 내보낸다). 쪽마다 약 665블록이다. 정산 쪽 인덱스와 적재 부담이 없는 대신 쪽마다 640블록을 더 읽는다.
T_ORD_STLM (ORD_SEQ)만 만들면: 주문을 최근 순으로 읽을 길이 없어 주문 전체를 읽고 정렬해야 하므로 거의 줄지 않는다. 부분범위 처리에는 정렬 순서를 대신할 인덱스가 먼저다(교재 09화).
정산 여부 컬럼: T_ORD에 정산 여부 플래그를 두고 (STLM_YN, ORD_DT) 인덱스로 읽는 설계도 있다. 읽기는 가장 싸지만, 정산 적재 배치가 주문 10만 행을 갱신해야 하고 두 테이블이 어긋날 길이 하나 더 생긴다.
NOT IN으로 쓰지 않는다: 정산 테이블의 ORD_SEQ는 NULL 허용이라 NOT IN이면 NULL을 아는 안티 조인(NA)이 되고, 주문번호 없는 정산 행이 하나만 들어와도 화면이 0건이 된다(SUBQ-042).
승인서에는 쪽당 Buffers(1,536 → 63, 마지막 쪽 상한 약 820)와 하루 합계(약 461만 → 약 19만), 적재 배치 시간의 변화를 함께 적는다(교재 14화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 주문 건수는 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 주문 건수는 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황