쿠폰 시스템 오픈을 앞두고 결제 화면의 「쓸 수 있는 쿠폰」이 열릴 때마다 쿠폰 80만 장을 다 읽는다

실전DDL한빛유통150 / 700

상황

한빛유통이 쿠폰 시스템을 새로 연다. 결제 화면의 「쓸 수 있는 쿠폰」(S1)이 쿠폰 테이블을 통째로 읽는다는 점검 결과가 나오자, 개발팀은 인덱스 셋을 요청했다. "(CUS_ID, USE_YN, EXP_DT), (USE_YN, EXP_DT), (CUS_ID, EXP_DT)" 조민우는 첫째 대신 '안 쓴 쿠폰만 담는' 함수 기반 인덱스를 쓰자고 덧붙였다. 이 테이블은 읽기보다 쓰기가 많다. 결제 화면에서 쿠폰을 고르면 다른 기기에서 같은 쿠폰을 쓰지 못하게 바로 USE_YN을 'Y'로 잡고, 결제를 끝내지 않거나 다른 쿠폰으로 바꾸거나 주문을 취소하면 'N'으로 되돌린다. 박팀장의 주문은 이렇다. "하루 숫자로 따져서 최소한으로. USE_YN을 인덱스에 넣을지부터 정해 와라."

테이블과 인덱스

T_CPN 발급 쿠폰(이 문제에서 쓰는 파생 테이블): 800,000행 · 6,020블록 · CPN_NO CHAR(16) · CUS_ID · ISU_DT · EXP_DT · USE_YN · USE_DT · DC_AMT · 최근 1년 발급분만 두고 발급 순서로 쌓인다(지난 쿠폰은 매달 이력으로 옮긴다)
발급 하루 약 2,200장(캠페인 배치와 실시간 발급) · 유효기간 14일 · 고객 2,000명이라 고객당 약 400장
USE_YN: 'Y' 약 96,000장(12%) · 'N' 약 704,000장(88%) · 'N'의 대부분은 쓰지 않은 채 기한이 지난 쿠폰이다(만료 처리는 따로 하지 않는다)
고객 한 명이 가진 기한 안 지난 쿠폰은 평균 15장이고, 그중 약 3장은 이미 썼거나 지금 결제 중에 잡혀 있다
인덱스 PK_T_CPN (CPN_NO): BLEVEL 2 · 리프 약 3,330블록 · 다른 인덱스는 없다 · 통계 최신

원본 SQL

sql

-- S1 결제 화면 「쓸 수 있는 쿠폰」 · 하루 약 9,000번
SELECT CPN_NO, DC_AMT, EXP_DT
  FROM T_CPN
 WHERE CUS_ID = :v_cus
   AND USE_YN = 'N'
   AND EXP_DT >= SYSDATE
 ORDER BY DC_AMT DESC;

-- S2 쿠폰 잡기 · 놓기 · 하루 약 2만 6천 번 · 약 2만 5천 7백 번
UPDATE T_CPN SET USE_YN = 'Y', USE_DT = SYSDATE WHERE CPN_NO = :v_cpn AND USE_YN = 'N';
UPDATE T_CPN SET USE_YN = 'N', USE_DT = NULL    WHERE CPN_NO = :v_cpn AND USE_YN = 'Y';

-- S3 앱 「내 쿠폰함」(기한 안 지난 쿠폰과 지난 30일 안에 끝난 쿠폰) · 하루 약 4,000번
SELECT CPN_NO, DC_AMT, EXP_DT, USE_YN
  FROM T_CPN
 WHERE CUS_ID = :v_cus
   AND EXP_DT >= TRUNC(SYSDATE) - 30
 ORDER BY EXP_DT;

-- S4 「만료 임박 알림」 배치 · 매일 07:00 한 번
SELECT CUS_ID, CPN_NO, EXP_DT
  FROM T_CPN
 WHERE USE_YN = 'N'
   AND EXP_DT >= TRUNC(SYSDATE) + 3
   AND EXP_DT <  TRUNC(SYSDATE) + 4;

-- S5 발급 · 하루 약 2,200건
INSERT INTO T_CPN (CPN_NO, CUS_ID, ISU_DT, EXP_DT, USE_YN, DC_AMT)
VALUES (:v_cpn, :v_cus, SYSDATE, TRUNC(SYSDATE) + 14, 'N', :v_amt);

-- 요청서와 조민우 안
CREATE INDEX IX_CPN_CUS_YN_EXP ON T_CPN (CUS_ID, USE_YN, EXP_DT);
CREATE INDEX IX_CPN_YN_EXP     ON T_CPN (USE_YN, EXP_DT);
CREATE INDEX IX_CPN_CUS_EXP    ON T_CPN (CUS_ID, EXP_DT);
CREATE INDEX IX_CPN_UNUSED     ON T_CPN (CASE WHEN USE_YN = 'N' THEN CUS_ID END,
                                         CASE WHEN USE_YN = 'N' THEN EXP_DT END);

실행계획 학습용 예시 · T_CPN은 이 문제에서 정의한 파생 테이블 · UPDATE 한 번의 블록 변경과 redo는 교재 04화 §9 비례

xplan

[S1 · CUS_000123 · 지금 (PK만)]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |       |      1 |        |     12 |00:00:00.04 |    6020 |          |
|   1 |  SORT ORDER BY     |       |      1 |      5 |     12 |00:00:00.04 |    6020 | 2048  (0)|
|*  2 |   TABLE ACCESS FULL| T_CPN |      1 |      5 |     12 |00:00:00.04 |    6020 |          |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("CUS_ID"=:V_CUS AND "USE_YN"='N' AND "EXP_DT">=SYSDATE@!))

[시험 DB에 요청서 인덱스를 만들어 잰 값 · INVISIBLE 인덱스도 DML 때는 고쳐지므로 S2와 S5는 그대로 잴 수 있다]
  인덱스 구성                               S1 Buffers   S2 한 번 (블록 변경 · redo)   S5 한 번 (블록 변경)
  PK만                                         6,020       3회 · 약 380바이트             2회
  PK + (CUS_ID, EXP_DT)                           18       3회 · 약 380바이트             3회
  PK + (CUS_ID, USE_YN, EXP_DT)                   15       7회 · 약 900바이트             3회
  PK + 요청서 셋                                  15      11회 · 약 1,420바이트           5회
  PK + (CUS_ID, EXP_DT) + 조민우 안               15       5회 · 약 640바이트             4회
  조민우 안의 크기 : 항목 약 704,000개 · 리프 약 3,130블록 (PK 3,330블록과 비슷하다)

문제

S1 하나만 보면 요청서의 (CUS_ID, USE_YN, EXP_DT)가 가장 적게 읽는다. 그런데도 USE_YN을 인덱스에 넣을지는 S2를 함께 봐야 하는 이유를, USE_YN이 바뀔 때 인덱스에서 일어나는 일과 하루 숫자로 설명하시오.
조민우의 함수 기반 인덱스가 이 테이블에서 생각만큼 작지 않은 이유를 쓰고, USE_YN을 테이블에서 거르는 것과 비교해 어떤 대가가 있는지 설명하시오.
S1 ~ S5를 함께 보고 최소 인덱스 세트를 설계해 DDL을 작성하고, SQL마다 어떤 길로 몇 블록을 읽을지 어림하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객은 = , 기한은 범위 : S1 과 S3 가 같이 쓴다. USE_YN 은 넣지 않는다 (잡기 · 놓기가 이 인덱스를 건드리지 않게)
CREATE INDEX IX_CPN_CUS_EXP ON T_CPN (CUS_ID, EXP_DT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- S1 · S3 를 세션에서 재고 → T2 → VISIBLE
ALTER INDEX IX_CPN_CUS_EXP VISIBLE;

-- 만들지 않는다 : (CUS_ID, USE_YN, EXP_DT) · (USE_YN, EXP_DT) · 안 쓴 쿠폰만 담는 함수 기반 인덱스
-- S1 ~ S5 의 SQL 은 그대로 (S1 의 USE_YN = 'N' 은 테이블에서 거른다 · S4 는 하루 한 번 FULL)

개선 후 실행계획 학습용 예시 · T_CPN은 이 문제에서 정의한 파생 테이블 · UPDATE 한 번의 블록 변경과 redo는 교재 04화 §9 비례

xplan

[세트 · 시험 DB에서 잰 값]
        길                                                                  Buffers   하루
  S1    IX_CPN_CUS_EXP 범위 스캔 + 테이블 15행 (USE_YN은 테이블에서 거른다)         18   약 16만
  S2    PK_T_CPN 유니크 스캔 (USE_YN을 담은 인덱스가 없어 인덱스는 고치지 않는다)   4   약 21만
  S3    IX_CPN_CUS_EXP 범위 스캔 + 테이블 48행                                    51   약 20만
  S4    TABLE ACCESS FULL                                                      6,020   6,020
  S5    INSERT · PK_T_CPN과 IX_CPN_CUS_EXP를 고친다 (블록 변경 3회)                -       -

[S1 · CUS_000123]
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |     12 |00:00:00.01 |      18 |          |
|   1 |  SORT ORDER BY                       |                |      1 |      5 |     12 |00:00:00.01 |      18 | 2048  (0)|
|*  2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_CPN          |      1 |      5 |     12 |00:00:00.01 |      18 |          |
|*  3 |    INDEX RANGE SCAN                  | IX_CPN_CUS_EXP |      1 |     15 |     15 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------------------
   2 - filter("USE_YN"='N')
   3 - access("CUS_ID"=:V_CUS AND "EXP_DT">=SYSDATE@!)

모범 답안 서술

① S1만 보면 (CUS_ID, USE_YN, EXP_DT)가 가장 적게 읽는다. 고객의 기한 안 지난 쿠폰 15장 가운데 'N'인 12장만 인덱스에서 골라 테이블 12블록을 읽으므로 15블록이다. (CUS_ID, EXP_DT)로 찾으면 15장을 다 테이블에서 읽고 USE_YN으로 3장을 버려 18블록이다. 차이는 한 번에 3블록, 하루 9,000번이면 약 2만 7천 블록이다. 하지만 이 테이블에서 USE_YN은 가장 자주 바뀌는 컬럼이다. 인덱스는 값의 순서로 정렬된 목록이라, USE_YN이 'N'에서 'Y'로 바뀌면 그 쿠폰의 항목을 'N' 자리에서 지우고 'Y' 자리에 다시 넣어야 한다. 잡기가 하루 약 2만 6천 번, 놓기가 약 2만 5천 7백 번이니 이 일이 하루 약 5만 1천 7백 번 생긴다. 시험 DB에서 재 보면 UPDATE 한 번의 블록 변경이 3 → 7회, redo가 약 380 → 900바이트로 늘어난다. 하루로 곱하면 블록 변경 약 20만 회와 redo 약 27MB가 더 생긴다. 블록을 고치는 일은 버퍼를 읽기만 하는 일보다 훨씬 비싸고, 고친 만큼 언두와 redo, 아카이브 로그가 남는다. S1이 아끼는 하루 2만 7천 블록 읽기로는 갚을 수 없다. (USE_YN, EXP_DT)까지 두면 같은 일이 두 배가 되고, 그 인덱스는 'N' 구역과 'Y' 구역이 멀리 떨어져 있어 항목이 서로 다른 리프 블록을 오간다. 그래서 USE_YN은 인덱스에 넣지 않는다. ② 조민우 안은 'N'일 때만 키가 생기는 함수 기반 인덱스다. 처리된 행이 빠져나가 작게 유지되는 큐 테이블(IDX-047)과 달리, 이 테이블에서는 쓰지 않은 채 기한이 지난 쿠폰이 'N'으로 그대로 남는다. 'N'이 행의 88%(약 70만 4천 개)라 리프가 약 3,130블록으로 PK와 비슷하다. 기한을 식에 넣어 걸러 낼 수도 없다. SYSDATE처럼 값이 바뀌는 함수는 함수 기반 인덱스의 식에 쓸 수 없기 때문이다. 게다가 잡기마다 항목이 빠지고 놓기마다 다시 들어오므로 UPDATE 한 번의 블록 변경이 3 → 5회로 는다. S3이 쓸 (CUS_ID, EXP_DT)는 따로 있어야 하므로 인덱스가 하나 더 늘고, 발급할 때마다 고칠 인덱스도 는다. S1도 인덱스와 똑같은 식으로 고쳐야 쓰인다. USE_YN을 테이블에서 거르는 쪽은 S1에서 한 번에 테이블 3블록을 더 읽는 것이 대가의 전부다. ③ 세트는 PK와 (CUS_ID, EXP_DT) 하나다. S1은 고객은 =, 기한은 범위로 15항목(리프 한 블록)을 찾고 테이블 15블록에서 USE_YN을 거른다(6,020 → 18, 하루 약 5,418만 → 16만 블록). S3은 같은 인덱스로 기한 안 지난 쿠폰과 지난 30일에 끝난 쿠폰 약 48장을 읽어 약 51블록이다. S2는 PK로 4블록이고, USE_YN을 담은 인덱스가 없으니 잡기와 놓기는 테이블 블록만 고친다. S4는 하루 한 번이라 FULL 6,020블록(약 0.05초)으로 둔다. (USE_YN, EXP_DT)를 만들면 S4는 줄지만 하루 5만 번 넘는 갱신이 그 인덱스를 고치게 된다. S5 발급은 PK와 이 인덱스 둘만 고친다(블록 변경 3회). 요청서대로라면 발급 한 번에 5회, 잡기와 놓기 한 번에 11회다.

해설

ACC-066과 견주어 본다: 그쪽은 정산 여부를 인덱스에 넣어 화면이 한 번에 5,776블록을 아꼈고, 값이 바뀌는 일은 한 달에 한 번 마감 배치뿐이었다. 그래서 인덱스를 두었다. 이쪽은 아끼는 것이 한 번에 3블록이고 값은 하루 5만 번 넘게 바뀐다. 같은 '상태 컬럼을 인덱스에 넣을까'라도 답은 두 숫자의 크기가 정한다.
만료를 상태로 두는 설계: 매일 밤 기한이 지난 쿠폰을 'E'로 바꾸는 배치를 두면 'N'인 쿠폰은 기한 안 지난 약 2만 5천 장뿐이라 조민우 안이 작아진다. 하지만 잡기와 놓기가 그 인덱스를 고치는 일은 그대로이고, 밤마다 2,000장 남짓을 고치는 배치가 하나 는다. 상태 값의 뜻이 바뀌므로 다른 프로그램도 함께 고쳐야 한다.
S1의 테이블 15블록: (CUS_ID, EXP_DT, DC_AMT)처럼 반환 컬럼을 붙여도 USE_YN과 CPN_NO를 보려면 테이블에 가야 한다. USE_YN을 빼고는 커버링으로 만들 수 없으므로 테이블 15블록은 이 세트가 치르는 값이다.
잡기를 다른 테이블로: 결제 중 잡아 둔 쿠폰을 따로 '잡힌 쿠폰' 테이블에 넣는 설계도 있다. 그러면 T_CPN의 USE_YN은 결제가 끝날 때 한 번만 바뀐다. 갱신 일을 줄이는 근본 대책이지만 업무 흐름이 바뀌므로 개발팀과 함께 정한다(LOCK 단원의 동시성 제어).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · T_CPN은 이 문제에서 정의한 파생 테이블 · UPDATE 한 번의 블록 변경과 redo는 교재 04화 §9 비례 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · T_CPN은 이 문제에서 정의한 파생 테이블 · UPDATE 한 번의 블록 변경과 redo는 교재 04화 §9 비례 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황