결제 대기 주문을 오래된 것부터 정리하려고 마지막 쪽으로 가면 화면이 3초씩 멈춘다

실전SELECT한빛유통334 / 700

상황

정산팀의 「결제 대기 정리」는 결제 대기(PENDING) 주문을 최신순(ORD_DT DESC, ORD_SEQ DESC)으로 한 쪽에 20건씩 보여 준다. 정산팀은 오래된 대기 건부터 처리하려고 쪽 번호 칸에 마지막 쪽 근처(3,990쪽 안팎)를 넣고 넘어가는데, 그때마다 3초 넘게 멈춘다. 정산팀은 한 쪽을 처리하면(결제 확인 뒤 COMPLETE로 바꾼다) 다음 쪽으로 넘어간다. 오과장은 ROWID로 테이블 액세스를 늦추는 안, 커버링 인덱스 안, 키 기반 페이징 안을 숫자와 부작용으로 비교한 리포트를 원한다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST PENDING 8%(80,000건) · COMPLETE 90% · CANCEL 2%
인덱스 IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT, ORD_SEQ): 높이 3 · 리프 약 3,800블록(한 블록에 약 263개). 같은 상태의 주문은 테이블 곳곳에 흩어져 있다(클러스터링 팩터가 행 수에 가깝다).
화면 칸은 ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT 넷이다. 정산팀은 하루 수백 건을 PENDING에서 COMPLETE로 바꾼다.
오래된 블록은 대부분 버퍼 캐시에 없다. 통계는 최신이다.

원본 SQL

sql

SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
  FROM (SELECT ROWNUM rn, a.*
          FROM (SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
                  FROM T_ORD_BIG
                 WHERE ORD_ST = 'PENDING'
                 ORDER BY ORD_DT DESC, ORD_SEQ DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20

실행계획 학습용 예시

xplan

[3,990쪽 · 79,781 ~ 79,800번째]   (1쪽은 같은 모양 · A-Rows 20 · Buffers 23)
----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads |
----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                  |      1 |        |     20 |00:00:03.21 |   80107 |  5512 |
|*  1 |  VIEW                           |                  |      1 |     20 |     20 |00:00:03.21 |   80107 |  5512 |
|*  2 |   COUNT STOPKEY                 |                  |      1 |        |  79800 |00:00:03.20 |   80107 |  5512 |
|   3 |    VIEW                         |                  |      1 |  80000 |  79800 |00:00:03.17 |   80107 |  5512 |
|   4 |     TABLE ACCESS BY INDEX ROWID | T_ORD_BIG        |      1 |  80000 |  79800 |00:00:03.14 |   80107 |  5512 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_DT |      1 |  80000 |  79800 |00:00:00.05 |     307 |     0 |
----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">(:PAGE-1)*20)
   2 - filter(ROWNUM<=:PAGE*20)
   5 - access("ORD_ST"='PENDING')

문제

3,990쪽의 Buffers 80,107이 어디서 나오는지 실행계획으로 설명하시오.
ROWID로 테이블 액세스를 늦추는 안, 커버링 인덱스 안, 키 기반 페이징 안을 각각 SQL(또는 DDL)로 쓰고, 1쪽과 3,990쪽의 예상 일량과 부작용을 비교하시오.
정산팀이 마지막 쪽으로 가는 까닭을 생각해 하나를 권하시오. 정산팀이 처리한 건이 목록에서 빠질 때 쪽 번호 페이징과 키 기반 페이징에 각각 무슨 일이 생기는지도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 안 1 : ROWID 로 테이블 액세스를 늦춘다 (쪽 번호 · 인덱스 그대로)
SELECT /*+ LEADING(x) USE_NL(t) */ t.ORD_SEQ, t.ORD_DT, t.CUS_ID, t.ORD_AMT
  FROM (SELECT rid, rn
          FROM (SELECT ROWNUM rn, rid
                  FROM (SELECT ROWID rid
                          FROM T_ORD_BIG
                         WHERE ORD_ST = 'PENDING'
                         ORDER BY ORD_DT DESC, ORD_SEQ DESC)
                 WHERE ROWNUM <= :page * 20)
         WHERE rn > (:page - 1) * 20) x,
       T_ORD_BIG t
 WHERE t.ROWID = x.rid
 ORDER BY x.rn;

-- 안 2 : 커버링 인덱스로 지금 인덱스를 대신한다 (SQL 은 그대로)
CREATE INDEX IX_ORD_BIG_ST_DT_CV ON T_ORD_BIG (ORD_ST, ORD_DT, ORD_SEQ, CUS_ID, ORD_AMT);
-- 계획 확인 뒤 DROP INDEX IX_ORD_BIG_ST_DT;

-- 안 3 (권하는 안) : '오래된 순' 보기 + 키 기반 페이징 (21건 = 20건 + 다음 쪽 확인)
--   첫 쪽
SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
  FROM (SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_ST = 'PENDING'
         ORDER BY ORD_DT, ORD_SEQ)
 WHERE ROWNUM <= 21;
--   다음 쪽 : 앞 쪽 마지막 줄 (:p_dt, :p_seq) 다음부터
SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
  FROM (SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_ST = 'PENDING'
           AND ORD_DT >= :p_dt
           AND (ORD_DT > :p_dt OR ORD_SEQ > :p_seq)
         ORDER BY ORD_DT, ORD_SEQ)
 WHERE ROWNUM <= 21;

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

xplan

[안 3 · 다음 쪽 · 몇 쪽째든 같은 모양]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                  |      1 |        |     21 |00:00:00.01 |      24 |
|*  1 |  COUNT STOPKEY                 |                  |      1 |        |     21 |00:00:00.01 |      24 |
|   2 |   VIEW                         |                  |      1 |     21 |     21 |00:00:00.01 |      24 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD_BIG        |      1 |   4000 |     21 |00:00:00.01 |      24 |
|*  4 |     INDEX RANGE SCAN           | IX_ORD_BIG_ST_DT |      1 |     21 |     21 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=21)
   4 - access("ORD_ST"='PENDING' AND "ORD_DT">=:P_DT)
       filter(("ORD_DT">:P_DT OR "ORD_SEQ">:P_SEQ))

[세 안의 일량 (Buffers · 학습용 어림)]
                            1쪽      3,990쪽     인덱스 리프      바뀌는 것
  지금 (쪽 번호)             23      80,107      약 3,800         -
  안 1  ROWID로 늦게         23         327      약 3,800         SQL 과 힌트
  안 2  커버링 인덱스          3         473      약 5,900         인덱스 교체
  안 3  키 기반 (오래된 순)    24          24      약 3,800         화면 (바로 가기 없음)

모범 답안 서술

3,990쪽은 79,781 ~ 79,800번째 줄이다. 4번 줄이 인덱스를 거꾸로 79,800개 읽으며(5번 줄 307블록) 줄마다 테이블에 가서 화면 칸을 채우고, 2번 줄 ROWNUM이 그다음에 세어 1번 줄에서 79,780줄을 버린다. 같은 상태의 주문이 테이블 곳곳에 흩어져 있어 79,800번이 거의 모두 다른 블록을 찾아가고(Buffers 80,107), 5,826블록짜리 테이블의 대부분을 한 블록씩 디스크에서 읽는다(Reads 5,512). 화면에 쓰이는 것은 그중 20번이다. 안 1(ROWID로 늦게 찾아가기)은 버릴 줄을 인덱스의 ROWID만으로 세고 20줄만 테이블에 간다. 1쪽 23블록, 3,990쪽 327블록이다. 인덱스와 화면은 그대로지만 SQL이 길어지고, ROWID 조인이 해시 조인과 FULL로 바뀌지 않도록 LEADING(x) USE_NL(t)로 고정해야 한다. 쪽이 깊을수록 인덱스 리프를 세는 몫은 남는다. 안 2(커버링 인덱스)는 CUS_ID와 ORD_AMT까지 담은 인덱스로 지금 인덱스를 대신한다. SQL은 그대로이고 테이블을 읽지 않아 1쪽 3블록, 3,990쪽 473블록이다. 대신 100만 행 인덱스의 리프가 약 3,800 → 5,900블록으로 커지고, 결제가 끝나 상태가 바뀔 때마다 더 큰 항목을 지우고 다시 넣는다. 화면 칸이 하나 늘면 다시 테이블에 간다. 안 3(키 기반 페이징)은 몇 쪽째든 약 24블록이지만 쪽 번호로 바로 가지 못한다. 정산팀이 마지막 쪽으로 가는 까닭은 "오래된 대기 건부터" 보려는 것이다. 그렇다면 '오래된 순' 보기를 두고 첫 쪽부터 「다음」으로 넘기면 되므로 바로 가기가 필요 없다. 같은 인덱스를 정방향으로 읽을 뿐이라 새 인덱스도 필요 없다. 그래서 안 3을 권한다(80,107 → 24). 안 3이 맞는 까닭이 하나 더 있다. 정산팀은 한 쪽을 처리하고(PENDING에서 COMPLETE로) 다음 쪽으로 넘어간다. 처리한 20건이 목록에서 빠지면 그 뒤 줄이 20칸씩 앞으로 당겨진다. 쪽 번호 페이징은 그 상태에서 다음 쪽(21 ~ 40번째)을 읽으므로 처리하지 않은 20건을 건너뛴다. 키 기반은 마지막으로 본 (ORD_DT, ORD_SEQ) 다음부터 읽으므로 빠진 행과 상관없이 건너뛰는 건이 없다.

해설

처리하며 넘기는 목록에서 쪽 번호 페이징은 틀린다. 성능보다 이쪽이 더 큰 문제다. 정합성은 "처리, 다음 쪽"을 끝까지 되풀이한 뒤 처음 대기 건수와 처리 건수가 같은지, 남은 PENDING이 0건인지로 확인한다.
화면 요구를 바꿀 수 없을 때는 안 1이 차선이다(교재 09화 재협상서의 B안). 안 2는 이 화면이 자주 쓰이고 칸이 바뀌지 않는다는 확신이 있을 때 고른다. 자주 바뀌는 상태 컬럼이 선두인 인덱스를 키우면 적재와 갱신 비용도 함께 커진다(교재 14화).
PENDING만 담는 작은 함수 기반 인덱스(CASE WHEN ORD_ST = 'PENDING' THEN ORD_DT END 같은 식)도 후보다. 항목이 8만 개뿐이라 작지만, SQL의 조건을 같은 식으로 써야만 쓰인다.
최신순 보기도 남겨 둔다면 같은 인덱스를 역방향으로 읽는 키 기반 SQL을 하나 더 둔다. 정렬 기준이 늘 때마다 SQL과 포스트잇이 따로 필요하다는 것을 재협상서의 다음 재협상 조건에 적는다(교재 09화 §13).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황