지역장 화면의 「담당 매장 최근 주문 20건」, 담당 매장이 많은 지역장일수록 무겁다

실전SELECT버거321 / 700

상황

지역장 12명은 담당 매장(3 ~ 40곳)의 최근 주문 20건을 한 화면에서 보고, 화면은 30초마다 새로 고친다. 40곳을 맡은 지역장 SV07의 화면은 3곳을 맡은 SV02의 화면보다 열 배 넘게 무겁다. 개발팀은 세 가지 안을 냈다. ㉠ 지금 SQL은 그대로 두고 인덱스를 더 만든다. ㉡ 시각순 인덱스 ORD_X01을 거꾸로 읽으며 담당 매장만 거른다. ㉢ 매장마다 최근 20건을 따로 뽑아 합친다. 박팀장은 "세 안을 숫자로 비교해서 하나를 골라 달라. 지역장마다 매장 수가 다르다는 것도 잊지 말고"라고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · 날짜순 적재(CF 좋음) · 매장 200곳, 매장마다 약 5,000건 · ORD_DT, ORD_HMS NOT NULL
SV_SHOP 지역장 담당 매장(이 문제에서 쓰는 파생 테이블): SV_ID · SHOP_NO · 200행 · 5블록 · PK SV_SHOP_PK (SV_ID, SHOP_NO) · 지역장 12명, 담당 매장 3 ~ 40곳
인덱스 ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 3,500블록 · ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS, ORD_NO): BLEVEL 2 · 리프 약 5,200블록(매장 하나에 약 26블록) · 통계 최신
화면 칸은 ORD_NO, SHOP_NO, ORD_DT, ORD_HMS 넷이고 모두 ORD_X02에 있다

원본 SQL

sql

SELECT o.ORD_NO, o.SHOP_NO, o.ORD_DT, o.ORD_HMS
  FROM ORD o
 WHERE o.SHOP_NO IN (SELECT v.SHOP_NO FROM SV_SHOP v WHERE v.SV_ID = :sv)
 ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC, o.ORD_NO DESC
 FETCH FIRST 20 ROWS ONLY

실행계획 학습용 예시

xplan

[SV07 · 담당 매장 40곳]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |            |      1 |        |     20 |00:00:00.11 |    1122 |          |
|*  1 |  VIEW                    |            |      1 |     20 |     20 |00:00:00.11 |    1122 |          |
|*  2 |   WINDOW SORT PUSHED RANK|            |      1 |  85000 |     20 |00:00:00.11 |    1122 | 6144  (0)|
|   3 |    NESTED LOOPS          |            |      1 |  85000 |    200K|00:00:00.07 |    1122 |          |
|*  4 |     INDEX RANGE SCAN     | SV_SHOP_PK |      1 |     17 |     40 |00:00:00.01 |       2 |          |
|*  5 |     INDEX RANGE SCAN     | ORD_X02    |     40 |   5000 |    200K|00:00:00.05 |    1120 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_003"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORD_DT") DESC ,INTERNAL_FUNCTION("O"."ORD_HMS") DESC ,INTERNAL_FUNCTION("O"."ORD_NO") DESC )<=20)
   4 - access("V"."SV_ID"=:SV)
   5 - access("O"."SHOP_NO"="V"."SHOP_NO")

[SV02 · 담당 매장 3곳]  같은 모양 · 5번 줄 Starts 3 · A-Rows 15K · Buffers 86

문제

매장마다 주문이 (일자, 시각, 주문번호) 순서로 줄 선 ORD_X02가 있는데도 2번 줄에서 정렬이 일어나는 까닭과, 지역장의 매장 수에 따라 일량이 어떻게 늘어나는지 설명하시오.
㉡안과 ㉢안이 SV07(40곳)과 SV02(3곳)에서 각각 몇 블록쯤 읽는지 어림하고, 어느 경우에 어느 안이 불리해지는지 쓰시오.
한 안을 골라 SQL로 쓰고, 그 안의 부작용과 정렬 키 끝에 주문번호를 둔 까닭을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ㉢ 매장마다 ORD_X02 를 거꾸로 읽어 20 건에서 멈추고 (40곳 × 20 = 800 줄), 모은 800 줄에서 다시 20 건
SELECT x.ORD_NO, x.SHOP_NO, x.ORD_DT, x.ORD_HMS
  FROM SV_SHOP v
       CROSS APPLY (SELECT o.ORD_NO, o.SHOP_NO, o.ORD_DT, o.ORD_HMS
                      FROM ORD o
                     WHERE o.SHOP_NO = v.SHOP_NO
                     ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC, o.ORD_NO DESC
                     FETCH FIRST 20 ROWS ONLY) x
 WHERE v.SV_ID = :sv
 ORDER BY x.ORD_DT DESC, x.ORD_HMS DESC, x.ORD_NO DESC
 FETCH FIRST 20 ROWS ONLY

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

xplan

[SV07 · 40곳]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |      1 |        |     20 |00:00:00.01 |     122 |
|*  1 |  VIEW                             |                 |      1 |     20 |     20 |00:00:00.01 |     122 |
|*  2 |   WINDOW SORT PUSHED RANK         |                 |      1 |    340 |     20 |00:00:00.01 |     122 |
|   3 |    NESTED LOOPS                   |                 |      1 |    340 |    800 |00:00:00.01 |     122 |
|*  4 |     INDEX RANGE SCAN              | SV_SHOP_PK      |      1 |     17 |     40 |00:00:00.01 |       2 |
|*  5 |     VIEW                          | VW_LAT_2D0B8FC8 |     40 |     20 |    800 |00:00:00.01 |     120 |
|*  6 |      WINDOW NOSORT STOPKEY        |                 |     40 |   5000 |    800 |00:00:00.01 |     120 |
|*  7 |       INDEX RANGE SCAN DESCENDING | ORD_X02         |     40 |   5000 |    800 |00:00:00.01 |     120 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_006"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("X"."ORD_DT") DESC ,INTERNAL_FUNCTION("X"."ORD_HMS") DESC ,INTERNAL_FUNCTION("X"."ORD_NO") DESC )<=20)
   4 - access("V"."SV_ID"=:SV)
   5 - filter("from$_subquery$_004"."rowlimit_$$_rownumber"<=20)
   6 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORD_DT") DESC ,INTERNAL_FUNCTION("O"."ORD_HMS") DESC ,INTERNAL_FUNCTION("O"."ORD_NO") DESC )<=20)
   7 - access("O"."SHOP_NO"="V"."SHOP_NO")

[SV02 · 3곳]  같은 모양 · 7번 줄 Starts 3 · A-Rows 60 · Buffers 11

모범 답안 서술

ORD_X02에서 매장 하나의 주문은 (일자, 시각, 주문번호) 순서로 줄 서 있지만, 그 줄은 매장마다 따로 있다. 5번 줄은 담당 매장 40곳을 하나씩 돌며 매장마다 범위를 처음부터 끝까지 읽으므로, 나오는 순서는 매장 순서이고 40곳을 섞은 시각 순서가 아니다. 그래서 옵티마이저는 인덱스 순서로 정렬을 대신할 수 없고, 담당 매장의 주문 20만 건(40 × 5,000)을 모두 읽은 뒤 2번 줄에서 Top-N 정렬을 한다. 일량은 매장 수 × 매장당 주문 수에 비례해서 40곳은 1,122블록, 3곳은 86블록이고, 주문이 쌓일수록 같이 늘어난다. ㉠안처럼 인덱스를 더 만들어도 여러 매장을 시각 순서로 섞은 목록은 어떤 인덱스로도 만들 수 없으므로 소용이 없다. ㉡안(ORD_X01을 거꾸로 읽으며 담당 매장만 거르기)은 STOPKEY가 담당 매장의 주문 20건이 모여야 멈춘다. SV07은 전체 주문의 약 20%가 담당 매장 몫이라 최근 약 100항목 안에 20건이 모이고, 인덱스 몇 블록과 테이블 몇 블록에 담당 여부 확인을 더해 약 90블록이면 끝난다. SV02는 몫이 1.5%라서 약 1,300항목을 지나야 하고 테이블 방문과 담당 여부 확인도 그만큼 늘어 수백 블록이 된다. 담당 매장이 리모델링으로 쉬는 주에는 쉬기 전 주문을 만날 때까지 훑으므로 더 멀리 간다. 이 안의 일량은 담당 매장 몫이 작을수록 커지고, 지역장마다, 날마다 흔들린다. ㉢안(매장마다 20건씩 뽑아 합치기)은 매장마다 ORD_X02의 범위를 거꾸로 읽다 20건에서 멈춘다(WINDOW NOSORT STOPKEY). 화면 칸이 모두 인덱스에 있어 테이블에 가지 않으므로 매장마다 약 3블록, SV07은 40곳 × 3 = 약 120블록이고 SV02는 약 11블록이다. 모은 800줄을 마지막에 한 번 정렬하는 비용은 작다. 일량이 매장 수에만 비례하고 주문 수나 매장의 영업 상태와는 상관이 없으므로 ㉢안을 고른다. SV07의 Buffers는 1,122 → 122로 준다. 부작용도 있다. 매장 200곳 전부를 보는 화면이라면 ㉢안은 약 600블록인데, 그때는 몫이 100%라 ㉡안이 몇 블록이면 끝난다. 그래서 본사 전체 화면처럼 매장이 아주 많은 화면에는 ㉡안을 쓰는 식으로 화면마다 고른다. 또 ㉢안은 CROSS APPLY가 매장마다 반복되는 NL 구조에 기댄다. FETCH FIRST가 있는 LATERAL 뷰는 병합되지 않아 이 구조가 유지되지만, 실행계획을 기준 계획으로 남겨 두고 바뀌면 알 수 있게 한다. 새 인덱스는 더 만들 필요가 없다. 정렬 키 끝에 ORD_NO DESC를 둔 것은 같은 초에 들어온 주문 둘이 20번째 자리에 걸렸을 때 어느 주문이 보일지 정하기 위해서다. 매장별 20건과 마지막 20건이 같은 규칙으로 잘려야 매장 안에서 빠지는 주문이 없다.

해설

㉢안을 UNION ALL로 쓰려면 매장 수만큼 가지를 써야 해서 화면이 SQL을 동적으로 만들게 된다. 매장 목록이 테이블에 있으면 CROSS APPLY(또는 LATERAL)가 한 문장으로 끝난다(12c 이상).
ROW_NUMBER() OVER (PARTITION BY SHOP_NO ORDER BY …) <= 20으로 매장별 20건을 고르는 것은 ㉢안이 아니다. 담당 매장 주문을 전부 읽어 매장별로 정렬한 뒤 거르므로 지금 SQL과 일량이 같다.
여러 키의 최근 N건은 키마다 STOPKEY를 걸고 합치는 것이 기본이다. 키가 아주 많거나 한 키의 몫이 크면 시각순 인덱스 하나를 거꾸로 읽는 쪽이 싸다. 어느 쪽인지는 키 수와 몫으로 어림한다(SORT-013).
30초마다 새로 고치는 화면이라 일량 차이가 하루에 쌓인다. SV07이 하루 9시간 화면을 띄워 두면 1,122 × 1,080번, 약 121만 블록이 약 13만 블록이 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황