점장 앱 달력에 「주문 있는 날」 점을 찍는 데 한 번에 1천 블록을 읽는다

실전SELECT버거349 / 700

상황

점장 앱 첫 화면의 달력은 그 달에 주문이 있었던 날짜에 점을 찍는다. 200개 매장 점장이 앱을 열 때마다 호출되어 하루 약 6만 번 돌고, DBA가 뽑은 논리 읽기 순위에서 2위다. 개발팀이 세 가지 안을 냈다. ㉠ (SHOP_NO, ORD_DT) 인덱스를 새로 만든다. ㉡ 인덱스는 그대로 두고 그 달의 날짜(최대 31개)를 만들어 날마다 '그 매장 주문이 있는가'만 묻는다. ㉢ 매장별 일자별 주문 집계 테이블을 만들어 야간 배치로 채우고 달력은 그 테이블을 읽는다. 박팀장은 "숫자로 비교하고, 안마다 대가와 정합성 문제를 적어서 하나를 골라 달라. 주문 테이블 인덱스를 늘리려면 적재팀과 협의해야 한다는 것도 잊지 말고"라고 했다.

테이블과 인덱스

ORD 주문: 1,000,000행 · 22,422블록 · SHOP_NO 200곳 · ORD_DT VARCHAR2(8) · 날짜순으로 쌓여 있다 · 하루 약 1,370건, 매장 하나는 하루 약 7건(하루 1 ~ 2건인 작은 매장도 있다) · 2013년 12월 42,408건
인덱스 ORD_PK (ORD_NO), ORD_X01 (ORD_DT, ORD_HMS): BLEVEL 2 · 리프 약 3,500블록(한 블록에 약 286개) · CF 22,223
아래 실행계획은 매장 SH0017(2013년 12월 주문 212건, 31일 모두 주문 있음)이다. 통계는 최신이다.

원본 SQL

sql

SELECT DISTINCT ORD_DT
  FROM ORD
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :ym || '01' AND :ym || '31'
 ORDER BY ORD_DT

실행계획 학습용 예시

xplan

[SH0017 · :ym = '201312']
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |         |      1 |        |     31 |00:00:00.03 |    1092 |       |       |          |
|   1 |  SORT UNIQUE                         |         |      1 |     31 |     31 |00:00:00.03 |    1092 |  2048 |  2048 | 2048  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD     |      1 |    211 |    212 |00:00:00.03 |    1092 |       |       |          |
|*  3 |    INDEX RANGE SCAN                  | ORD_X01 |      1 |  42466 |  42408 |00:00:00.01 |     150 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SHOP_NO"=:SHOP)
   3 - access("ORD_DT">=:YM||'01' AND "ORD_DT"<=:YM||'31')

문제

점 31개를 찍는 데 1,092블록을 읽는 까닭을 실행계획 2번, 3번 줄의 A-Rows와 Predicate로 설명하시오.
㉠㉡㉢의 일량(평균 매장과 작은 매장), 대가, 정합성 문제를 비교하시오.
하나를 골라 SQL(필요하면 DDL)로 쓰고, 고른 까닭과 그 안을 바로 쓸 수 없을 때의 차선을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ㉠ (권하는 안) : 매장, 주문일 순서의 인덱스 → 그 매장 그 달 약 212항목만 · 이미 날짜순이라 중복 제거에 정렬이 없다
CREATE INDEX IX_ORD_SHOP_DT ON ORD (SHOP_NO, ORD_DT);

-- SQL 은 그대로
SELECT DISTINCT ORD_DT
  FROM ORD
 WHERE SHOP_NO = :shop
   AND ORD_DT BETWEEN :ym || '01' AND :ym || '31'
 ORDER BY ORD_DT;

-- ㉡ (인덱스 협의가 끝날 때까지의 차선) : 그 달의 날짜마다 '그 매장 주문이 있는가' 만 묻는다
SELECT d.DT AS ORD_DT
  FROM (SELECT TO_CHAR(TO_DATE(:ym || '01', 'YYYYMMDD') + LEVEL - 1, 'YYYYMMDD') AS DT
          FROM DUAL
       CONNECT BY LEVEL <= TO_NUMBER(TO_CHAR(LAST_DAY(TO_DATE(:ym || '01', 'YYYYMMDD')), 'DD'))) d
 WHERE EXISTS (SELECT 1
                 FROM ORD o
                WHERE o.ORD_DT = d.DT
                  AND o.SHOP_NO = :shop)
 ORDER BY d.DT;

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

xplan

[㉠ · SH0017 · 2013년 12월]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |     31 |00:00:00.01 |       3 |       |       |          |
|   1 |  SORT UNIQUE NOSORT            |                |      1 |     31 |     31 |00:00:00.01 |       3 |       |       |          |
|*  2 |   INDEX RANGE SCAN             | IX_ORD_SHOP_DT |      1 |    211 |    212 |00:00:00.01 |       3 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------
   2 - access("SHOP_NO"=:SHOP AND "ORD_DT">=:YM||'01' AND "ORD_DT"<=:YM||'31')

[㉡ · 같은 매장 · 같은 달]
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |     31 |00:00:00.01 |     251 |       |       |          |
|   1 |  SORT ORDER BY                 |                |      1 |      1 |     31 |00:00:00.01 |     251 |  2048 |  2048 | 2048  (0)|
|   2 |   NESTED LOOPS SEMI            |                |      1 |      1 |     31 |00:00:00.01 |     251 |       |       |          |
|   3 |    VIEW                        |                |      1 |      1 |     31 |00:00:00.01 |       0 |       |       |          |
|   4 |     CONNECT BY WITHOUT FILTERING|               |      1 |        |     31 |00:00:00.01 |       0 |       |       |          |
|   5 |      FAST DUAL                 |                |      1 |      1 |      1 |00:00:00.01 |       0 |       |       |          |
|*  6 |    TABLE ACCESS BY INDEX ROWID | ORD            |     31 |      7 |     31 |00:00:00.01 |     251 |       |       |          |
|*  7 |     INDEX RANGE SCAN           | ORD_X01        |     31 |   1368 |   6262 |00:00:00.01 |      96 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------
   6 - filter("O"."SHOP_NO"=:SHOP)
   7 - access("O"."ORD_DT"="D"."DT")

[㉡ · 하루 1 ~ 2건인 매장 SH0143]  같은 모양 · 7번 줄 A-Rows 약 2만 · Buffers 약 690
[㉢ · 집계 테이블 SHOP_DAY_ORD (SHOP_NO, ORD_DT) PK 범위]  31줄 · Buffers 3

모범 답안 서술

3번 줄은 ORD_X01로 그 달의 날짜 범위를 잡을 뿐 매장은 가리지 못해, 전 매장의 12월 주문 42,408항목(리프 약 150블록)을 읽는다. 매장 조건은 2번 줄에서 테이블에 가서야 filter로 걸린다. 그래서 42,408건을 모두 테이블에서 확인해(약 940블록) 212건만 남기고, 1번 줄 SORT UNIQUE가 그것을 날짜 31개로 줄인다. 점 31개를 찍으려고 매장 200곳의 한 달 주문을 다 읽는 셈이라 한 번에 1,092블록, 하루 6만 번이면 약 6,500만 블록이다. ㉠ (SHOP_NO, ORD_DT) 인덱스를 만들면 매장 조건과 날짜 범위가 모두 access가 되어 그 매장 그 달의 212항목(리프 1블록)만 읽는다. 항목이 이미 날짜순이라 SORT UNIQUE NOSORT가 날짜가 바뀔 때마다 한 줄씩 내보내므로 정렬 작업도 없다. Buffers는 1,092 → 3이고 매장 규모와 상관없이 거의 같으며 SQL은 그대로다. 대가는 100만 행 테이블에 인덱스가 하나 느는 것이다(리프 약 3,030블록). 주문을 넣을 때마다 고칠 인덱스가 하나 더 생기지만 하루 약 1,370건이라 부담은 작다. ㉡은 인덱스 없이 날짜 31개를 만들고 날마다 그 매장 주문이 있는가만 묻는다. 세미 조인이라 그날의 ORD_X01 범위를 읽다가 그 매장의 첫 주문을 만나면 멈춘다. SH0017처럼 하루 약 7건인 매장은 날마다 약 200항목을 지나 첫 주문을 만나므로 Buffers가 약 251이다. 하지만 일량이 매장에 따라 달라진다. 하루 1 ~ 2건인 작은 매장은 첫 주문이 멀어 약 690블록을 읽고, 주문이 없는 날은 그날 주문을 끝까지 읽는다. SQL도 길어져 유지하기 어렵다. ㉢은 집계 테이블을 PK 범위로 31줄 읽어 3블록이면 끝난다. 하지만 야간 배치로 채우므로 오늘 들어온 주문의 점은 밤이 되어야 찍히고, 취소로 그날 주문이 모두 없어져도 다음 배치까지 점이 남는다. 점장이 가장 자주 보는 것이 이번 달의 오늘이라 정합성 대가가 크고, 테이블과 배치를 하나씩 더 지켜야 한다. ㉠을 고른다. 일량이 가장 작고(3블록) 매장 규모와 상관없이 일정하며, SQL을 바꾸지 않고, 정합성 문제가 없다. 대가인 인덱스 하나의 적재 비용은 하루 1,370건이라 작다. 적재팀 협의가 끝날 때까지는 ㉡을 차선으로 쓴다. ㉡만으로도 평균 매장에서 1,092 → 약 251로 준다.

해설

DISTINCT를 EXISTS로 바꾸는 두 방향: SORT-034는 작은 마스터(상품)에서 출발해 '판 적이 있는가'를 물었다. ㉡은 마스터가 없어 날짜 목록을 만들어 출발점으로 삼았다. 출발점이 작고 '있는가'에서 멈출 수 있을 때 EXISTS가 이긴다.
평균 매장만 재면 ㉡을 고르게 된다: SH0017의 251블록만 보면 ㉡으로 충분해 보인다. 작은 매장, 주문이 없는 날처럼 끝단의 값을 바꿔 가며 재야 안의 일량 분포가 보인다(교재 09화 §3의 '무엇을 세나').
매장별 화면이 더 있다면 (SHOP_NO, ORD_DT, ORD_HMS, ORD_NO)로 넓혀 다른 화면(매장 주문 목록, 마지막 주문 시각)까지 함께 받게 할 수 있다. 리프가 약 5,200블록으로 커지는 대신 인덱스 하나로 여러 화면을 처리한다.
㉢처럼 미리 집계해 두는 방법은 집계가 크고 실시간성이 덜 중요한 화면에 맞는다(SORT s5의 구체화 뷰). 오늘 데이터를 함께 보여 주려면 '어제까지는 집계 테이블, 오늘은 원본'으로 나눠야 하고, 그만큼 SQL과 검증이 는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황