2페이지에 1페이지 끝 주문이 또 나오고, 어떤 주문은 어디에도 없다

실전SELECT버거310 / 700

상황

매장 점장용 「우리 매장 주문 목록」은 최신순으로 한 페이지에 20건씩 보여 준다. SH0017 점장: "2페이지 맨 위가 1페이지 끝에서 본 주문이다. 엑셀로 내려받은 목록에 있는 주문이 화면을 끝까지 넘겨도 안 나온다. 그리고 넘길 때마다 0.3초씩 걸린다." 화면 요구서에는 "주문일 최신순" 한 줄뿐이어서 SQL도 ORDER BY ORD_DT DESC 하나다. 200개 매장 점장이 마감 무렵에 몰아서 연다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · ORD_DT VARCHAR2(8) 날짜만('20131229') · ORD_HMS VARCHAR2(6) · SHOP_NO 200개 매장 · ORD_NO NUMBER(PK)
매장 SH0017의 주문은 5,012건이다. 하루 약 7건이라 같은 ORD_DT가 7줄씩 있다.
인덱스는 ORD_PK (ORD_NO)와 ORD_X01 (ORD_DT, ORD_HMS)이고, SHOP_NO로 시작하는 인덱스는 없다.
화면에 보이는 칸은 ORD_NO, ORD_DT, ORD_HMS 셋이다. 통계는 최신이다.

원본 SQL

sql

SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM (SELECT ROWNUM AS rn, a.*
          FROM (SELECT ORD_NO, ORD_DT, ORD_HMS
                  FROM ORD
                 WHERE SHOP_NO = :shop
                 ORDER BY ORD_DT DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20

실행계획 학습용 예시

xplan

[2페이지 — :shop = 'SH0017' · :page = 2]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name | Starts | E-Rows | A-Rows |    A-Time   | Buffers |  Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |      |      1 |        |     20 | 00:00:00.31 |   22422 |           |
|*  1 |  VIEW                     |      |      1 |     40 |     20 | 00:00:00.31 |   22422 |           |
|*  2 |   COUNT STOPKEY           |      |      1 |        |     40 | 00:00:00.31 |   22422 |           |
|   3 |    VIEW                   |      |      1 |   5000 |     40 | 00:00:00.31 |   22422 |           |
|*  4 |     SORT ORDER BY STOPKEY |      |      1 |   5000 |     40 | 00:00:00.31 |   22422 | 4096  (0) |
|*  5 |      TABLE ACCESS FULL    | ORD  |      1 |   5000 |   5012 | 00:00:00.30 |   22422 |           |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">(:PAGE-1)*20)
   2 - filter(ROWNUM<=:PAGE*20)
   4 - filter(ROWNUM<=:PAGE*20)
   5 - filter("SHOP_NO"=:SHOP)

[화면 — 경계에 걸린 날 20131229 · SH0017 의 그날 주문 7 건]
  1페이지 15 ~ 20번   995702 · 995431 · 995288 · 994612 · 995103 · 994780
  2페이지 21번        995103                       ← 1페이지 19번과 같은 주문
  어느 페이지에도 없음 994955

문제

같은 주문이 두 페이지에 나오고 어떤 주문은 어디에도 없는 까닭을 정렬 키와 실행계획 4번 줄로 설명하시오.
정합성(빠지거나 겹치는 주문이 없음)과 성능(어느 페이지든 비슷한 일량)을 함께 고친 SQL과 인덱스를 작성하시오.
'n 페이지 바로 가기'를 지키는 안과 '다음 페이지'만 두는 안을 비교하고, 각 안의 대가(부작용)와 정합성 확인 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장 + 정렬 키(유일) 순서의 인덱스 · 화면 칸 셋을 다 담아 테이블에 안 간다
CREATE INDEX ORD_X02 ON ORD (SHOP_NO, ORD_DT, ORD_HMS, ORD_NO);

-- 안 A : 페이지 번호 유지 — 정렬 키를 유일하게 (PK 인 ORD_NO 로 끝낸다)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM (SELECT ROWNUM AS rn, a.*
          FROM (SELECT ORD_NO, ORD_DT, ORD_HMS
                  FROM ORD
                 WHERE SHOP_NO = :shop
                 ORDER BY ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20;

-- 안 B : '다음' 만 — 앞 페이지 마지막 줄 (:p_dt, :p_hms, :p_no) 뒤에서 21 건 (21번째는 '다음' 버튼을 켤지 보는 용도)
SELECT ORD_NO, ORD_DT, ORD_HMS
  FROM (SELECT ORD_NO, ORD_DT, ORD_HMS
          FROM ORD
         WHERE SHOP_NO = :shop
           AND ORD_DT <= :p_dt
           AND (ORD_DT < :p_dt
                OR (ORD_DT = :p_dt AND (ORD_HMS < :p_hms
                                        OR (ORD_HMS = :p_hms AND ORD_NO < :p_no))))
         ORDER BY ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC)
 WHERE ROWNUM <= 21;

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

xplan

[안 A · 2페이지]
------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name    | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |         |      1 |        |     20 | 00:00:00.01 |       4 |
|*  1 |  VIEW                           |         |      1 |     40 |     20 | 00:00:00.01 |       4 |
|*  2 |   COUNT STOPKEY                 |         |      1 |        |     40 | 00:00:00.01 |       4 |
|   3 |    VIEW                         |         |      1 |   5000 |     40 | 00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN DESCENDING | ORD_X02 |      1 |   5000 |     40 | 00:00:00.01 |       4 |
------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">(:PAGE-1)*20)
   2 - filter(ROWNUM<=:PAGE*20)
   4 - access("SHOP_NO"=:SHOP)

[안 A · 100페이지 (1,981 ~ 2,000번째)]  같은 모양 · 인덱스 A-Rows 2000 · Buffers 약 13 (리프를 앞에서부터 세며 지나간다)

[안 B · 어느 페이지든]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |     21 | 00:00:00.01 |       4 |
|*  1 |  COUNT STOPKEY                 |         |      1 |        |     21 | 00:00:00.01 |       4 |
|   2 |   VIEW                         |         |      1 |     21 |     21 | 00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN DESCENDING | ORD_X02 |      1 |     21 |     21 | 00:00:00.01 |       4 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=21)
   3 - access("SHOP_NO"=:SHOP AND "ORD_DT"<=:P_DT)
       filter(("ORD_DT"<:P_DT OR ("ORD_DT"=:P_DT AND ("ORD_HMS"<:P_HMS OR
              ("ORD_HMS"=:P_HMS AND "ORD_NO"<:P_NO)))))

모범 답안 서술

원인은 정렬 키가 유일하지 않다는 데 있다. ORDER BY ORD_DT DESC는 날짜끼리의 순서만 정할 뿐, 같은 날 주문 약 7건 사이의 순서는 SQL 어디에도 없다. 4번 줄 SORT ORDER BY STOPKEY는 1페이지에서는 위 20건, 2페이지에서는 위 40건을 남기는 서로 다른 정렬이고, 두 정렬이 동점끼리의 순서를 똑같이 정한다는 보장이 없다. 그래서 20131229의 7건이 경계에 걸리자 995103은 두 페이지에 나오고 994955는 어느 페이지에도 없다. 버그가 아니라 SQL이 '같은 날은 아무 순서나 좋다'고 한 결과다. 성능 쪽을 보면 페이지마다 같은 일을 처음부터 한다. 5번 줄이 매번 ORD 22,422블록을 다 읽어 SH0017의 5,012건을 골라 정렬한다. 1페이지든 100페이지든 22,422블록이다. 고친 안은 정렬 키를 ORD_DT DESC, ORD_HMS DESC, ORD_NO DESC로 끝까지 유일하게 하고(ORD_NO는 PK), 그 순서 그대로 SHOP_NO로 시작하는 인덱스 (SHOP_NO, ORD_DT, ORD_HMS, ORD_NO)를 만든다. 매장 조건으로 범위를 잡고 인덱스를 거꾸로 읽다가 40건에서 멈추므로 정렬 줄이 사라진다. 화면 칸 셋이 모두 인덱스에 있어 테이블도 읽지 않는다. 2페이지의 Buffers는 22,422 → 4로 준다. 순서가 매번 같으니 빠지거나 겹치는 주문이 없다. 안 A(페이지 번호 유지, ROWNUM)는 'n 페이지 바로 가기'를 지킨다. 대신 뒤 페이지일수록 앞의 행을 리프에서 세며 지나가야 해서 일량이 늘어난다(100페이지 약 13블록). 이 매장 규모(5,012건)라면 문제없다. 안 B(앞 페이지 마지막 줄 뒤에서 21건, 키 기반)는 어느 페이지든 약 4블록이고 그 사이에 새 주문이 들어와도 목록이 밀리지 않지만, 바로 가기를 못 한다. 따라서 화면 요구를 다시 정해야 한다. 두 안에 공통으로 드는 대가는 인덱스 하나만큼의 적재 비용이다(하루 약 1,370건, 주문마다 키 하나 더). 정합성은 한 페이지만 볼 것이 아니라 매장 5,012건 전체를 페이지로 끝까지 넘겨 모은 결과를 원본과 대조해 확인한다(건수 5,012 = 5,012, MINUS 양방향 0 / 0, 중복 0).

해설

OFFSET으로 바꿔도 같다. OFFSET 20 ROWS FETCH NEXT 20 ROWS ONLY도 정렬 키가 유일하지 않으면 똑같이 빠지고 겹친다. 정합성은 문법이 아니라 정렬 키의 문제다.
ORD_HMS까지만 붙이면 부족하다. 같은 매장에서 같은 초에 들어온 주문은 드물지만 0건이라는 제약이 없다(교재 09화에서 유통의 (SLE_DT, SLE_HMS)는 같은 초에 겹치는 쌍이 13,585개였다). 유일성은 PK로 끝내야 보장된다.
인덱스 없이 정렬 키만 고치면 정합성은 고쳐지지만 성능은 그대로 22,422블록이다. 반대로 인덱스만 만들고 ORDER BY ORD_DT DESC를 그대로 두면 지금은 인덱스 순서대로 나와 맞아 보여도, 실행계획이 바뀌는 날 다시 틀린다. SQL이 순서를 약속하지 않았기 때문이다.
화면 요구서의 "주문일 최신순"은 동점의 순서를 말하지 않았다. 요구서에 '같은 날은 주문 시각, 주문번호 역순'이라는 한 줄을 더 적게 한다(교재 09화 화면 요구 재협상서).
화면 아래에 '총 n건'이 있다면 그 COUNT(*)도 클릭할 때마다 다시 센다. SORT-001과 같은 재협상거리다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황