「매장 생존 점검」이 POS가 멈춘 매장을 못 잡고, 1분마다 5천 블록을 읽는다

중급SELECT버거320 / 700

상황

본사 운영실의 「매장 생존 점검」 화면은 매장마다 마지막 주문 일시를 보여 주고, 30분 넘게 주문이 없는 매장을 붉게 칠한다(POS 장애 감지용). 운영실 PC 6대가 1분마다 새로 고친다. 지난주 SH0117 매장의 POS가 두 시간 멈췄는데 화면은 붉어지지 않았다. 그 시간에도 SH0117의 마지막 주문 시각은 '23:58:41'로 떠 있었다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · SHOP_NO 200곳('SH0001' ~ 'SH0200', 매장마다 약 5,000건) · ORD_DT VARCHAR2(8), ORD_HMS VARCHAR2(6) · 둘 다 NOT NULL이고 0 채움 정상
SHOP 매장: 200행 · 5블록 · PK (SHOP_NO) · SHOP_NM
인덱스 ORD_PK (ORD_NO), ORD_X01 (ORD_DT, ORD_HMS), ORD_X02 (SHOP_NO, ORD_DT, ORD_HMS, ORD_NO): BLEVEL 2 · 리프 약 5,200블록(매장 하나에 약 26블록) · 통계 최신

원본 SQL

sql

SELECT s.SHOP_NO, s.SHOP_NM,
       MAX(o.ORD_DT)  AS last_dt,
       MAX(o.ORD_HMS) AS last_hms
  FROM SHOP s, ORD o
 WHERE o.SHOP_NO = s.SHOP_NO
 GROUP BY s.SHOP_NO, s.SHOP_NM
 ORDER BY last_dt, last_hms

실행계획 학습용 예시

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |         |      1 |        |    200 |00:00:00.38 |    5233 |          |
|   1 |  SORT ORDER BY          |         |      1 |    200 |    200 |00:00:00.38 |    5233 |   18K (0)|
|   2 |   HASH GROUP BY         |         |      1 |    200 |    200 |00:00:00.38 |    5233 | 1394K (0)|
|*  3 |    HASH JOIN            |         |      1 |   1000K|   1000K|00:00:00.27 |    5233 | 1572K (0)|
|   4 |     TABLE ACCESS FULL   | SHOP    |      1 |    200 |    200 |00:00:00.01 |       5 |          |
|   5 |     INDEX FAST FULL SCAN| ORD_X02 |      1 |   1000K|   1000K|00:00:00.09 |    5228 |          |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("O"."SHOP_NO"="S"."SHOP_NO")

[화면 · SH0117 · POS 가 멈춘 날 14:20]  last_dt 20131216 · last_hms 235841  (실제 그날 마지막 주문: 20131216 120733)

문제

SH0117의 마지막 주문 시각이 '23:58:41'로 나온 까닭을 SQL로 설명하시오.
매장마다 마지막 주문 한 건의 일자와 시각을 정확히 돌려주면서, 매장마다 인덱스의 끝 항목 하나만 읽도록 SQL을 고치시오. GROUP BY의 MAX가 왜 인덱스 끝에서 멈추지 못하는지도 설명하시오.
고친 SQL의 일량이 매장 수와 주문 수에 따라 어떻게 늘어나는지 쓰고, 지금 SQL과 견주시오.

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

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

모범답안

모범 답안 SQL

sql

-- 매장마다 따로 묻는다 : ORD_X02 에서 그 매장 범위를 거꾸로 읽어 첫 항목 하나 (일자와 시각이 같은 주문에서 나온다)
SELECT s.SHOP_NO, s.SHOP_NM, x.ORD_DT AS last_dt, x.ORD_HMS AS last_hms
  FROM SHOP s
       OUTER APPLY (SELECT o.ORD_DT, o.ORD_HMS
                      FROM ORD o
                     WHERE o.SHOP_NO = s.SHOP_NO
                     ORDER BY o.ORD_DT DESC, o.ORD_HMS DESC
                     FETCH FIRST 1 ROW ONLY) x
 ORDER BY last_dt, last_hms

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

xplan

---------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                 |      1 |        |    200 |00:00:00.01 |     605 |
|   1 |  SORT ORDER BY                   |                 |      1 |    200 |    200 |00:00:00.01 |     605 |
|   2 |   NESTED LOOPS OUTER             |                 |      1 |    200 |    200 |00:00:00.01 |     605 |
|   3 |    TABLE ACCESS FULL             | SHOP            |      1 |    200 |    200 |00:00:00.01 |       5 |
|*  4 |    VIEW                          | VW_LAT_A18161FF |    200 |      1 |    200 |00:00:00.01 |     600 |
|*  5 |     WINDOW NOSORT STOPKEY        |                 |    200 |      1 |    200 |00:00:00.01 |     600 |
|*  6 |      INDEX RANGE SCAN DESCENDING | ORD_X02         |    200 |   5000 |    200 |00:00:00.01 |     600 |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("from$_subquery$_005"."rowlimit_$$_rownumber"<=1)
   5 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("O"."ORD_DT") DESC ,INTERNAL_FUNCTION("O"."ORD_HMS") DESC )<=1)
   6 - access("O"."SHOP_NO"="S"."SHOP_NO")

모범 답안 서술

MAX(o.ORD_DT)와 MAX(o.ORD_HMS)는 같은 그룹 안에서 각각 따로 구한 최댓값이다. 앞의 것은 그 매장의 마지막 주문일이지만, 뒤의 것은 2년 치 주문 전체에서 가장 늦은 시각이다. 두 값이 같은 주문에서 나왔다는 보장이 없다. 밤늦게까지 영업한 날이 한 번이라도 있었던 매장은 늘 23시대가 찍히므로, SH0117의 POS가 12시 7분에 멈춘 날에도 마지막 주문이 '23:58:41'로 보였다. 장애 감지 화면이 장애를 못 잡은 까닭이다. 일량은 5번 줄에서 나온다. GROUP BY의 MAX는 그룹마다 인덱스의 끝 하나로 답할 수 없다. FIRST ROW(MIN/MAX)는 조건 하나에 끝 하나일 때만 쓰이고, 그룹 200개의 끝을 한 번에 구하라는 GROUP BY에는 쓰이지 않는다. 그래서 ORD_X02 전체 100만 항목을 INDEX FAST FULL SCAN으로 읽고(5,228블록) 해시 조인과 해시 집계를 한다. 1분마다 6대이므로 하루 약 4,500만 블록이다. 고친 SQL은 매장마다 따로 묻는다. OUTER APPLY 안에서 그 매장의 범위를 ORD_X02로 거꾸로(RANGE SCAN DESCENDING) 읽고 첫 항목 하나에서 멈춘다(WINDOW NOSORT STOPKEY). 일자와 시각을 한 행에서 함께 가져오므로 같은 주문의 값이고, 주문이 아직 없는 새 매장도 OUTER라서 빈칸으로 남는다. 매장마다 약 3블록, 200 × 3 = 600에 SHOP 5블록을 더해 Buffers는 5,233 → 605로 준다. 고친 SQL의 일량은 매장 수에 비례하고 주문 수와는 상관이 없다. 주문이 열 배로 늘어도 605 근처지만, 매장이 2,000곳이 되면 약 6,000블록이 된다. 지금 SQL은 반대로 주문 수에 비례한다. 매장 200곳, 주문 100만 건에서는 매장마다 묻는 쪽이 9배쯤 싸다.

해설

스칼라 서브쿼리로 쓴다면: (SELECT MAX(o.ORD_DT) FROM ORD o WHERE o.SHOP_NO = s.SHOP_NO)는 매장마다 FIRST ROW 한 번이다. 시각까지 맞게 구하려면 ORD_DT = 그 최댓값 조건으로 MAX(ORD_HMS)를 한 번 더 물어야 해서 매장마다 약 6블록이 든다. MAX(o.ORD_DT || o.ORD_HMS)로 한 번에 구하면 인자를 가공한 것이라 FIRST ROW가 되지 않고 매장의 5,000항목을 다 읽는다.
인덱스가 순서를 주지 않으면 이 방법은 오히려 비싸다. ORD_X02가 없다면 매장마다 ORD를 훑게 되므로 지금처럼 한 번에 집계하는 편이 낫다. 그룹 수가 적고, 그룹마다 끝을 찾는 인덱스가 있을 때 쓰는 방법이다.
그룹마다 최근 N건이나 이력의 최신 한 건처럼 행 전체가 필요한 경우는 이 단원의 그룹별 상위 N건 문제에서 ROW_NUMBER와 견줘 다룬다.
매장 테이블에 '마지막 주문 일시' 컬럼을 두고 주문 때마다 갱신하는 방법도 있다. 읽기는 5블록이 되지만 주문마다 매장 행을 갱신하므로 같은 매장 주문끼리 행 락을 기다리게 된다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황