「주문 조회」 개편에 단일 컬럼 인덱스 셋을 달아 달라는 요청서와 한 달 치 조회 조건 통계가 함께 왔다

실전DDL한빛유통75 / 700

상황

한빛유통 「주문 조회」 화면을 개편한다. 주문일자 범위는 필수이고(최대 31일), 고객번호, 주문상태, 결제수단은 선택이다. 결과는 최근 주문부터 50건씩 보여 준다. 개발팀은 고른 조건 조합마다 SQL을 따로 만들어 보내도록 짰고(동적 SQL), 인덱스 요청서에는 "조건으로 쓰는 컬럼마다 하나씩: CUS_ID, ORD_ST, PAY_TP"라고 적었다. 박팀장은 운영 화면의 한 달 치 조회 로그를 뽑아 건네며 이렇게 말했다. "조합마다 어떻게 찾아갈지부터 정하고, 인덱스는 최소로."

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_DT DATE(시분초 있음, 2023-01-01 ~ 2024-12-31, 하루 약 137건, 같은 날짜의 주문은 테이블 곳곳에 흩어져 있다) · CUS_ID 2,000명(고객당 50건, 한 달에 약 2건) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2% · PAY_TP CARD · CASH · POINT가 1/3씩
인덱스 PK_T_ORD (ORD_SEQ) · IX_T_ORD_DT (ORD_DT): BLEVEL 1 · 리프 265블록 · CF 약 99,986 · 통계 최신
주문 INSERT 하루 약 140건 · 결제와 취소로 ORD_ST를 고치는 UPDATE 하루 약 300건
한 달 치 조회 로그(약 9만 번)

조합	조건	비율	보통 고르는 기간
C1	기간 + 고객	48%	한 달
C2	기간 + 상태	22%	하루
C3	기간만	17%	하루 ~ 일주일
C4	기간 + 결제수단	9%	일주일
C5	기간 + 상태 + 결제수단	4%	하루

원본 SQL

sql

-- C1 (다른 조합도 모양이 같고 조건 한 줄만 다르다)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, PAY_TP, ORD_AMT
  FROM T_ORD
 WHERE ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
   AND CUS_ID = :v_cus
 ORDER BY ORD_DT DESC
 FETCH FIRST 50 ROWS ONLY;

-- C2 : … AND ORD_ST = :v_st          C3 : 조건 줄 없음
-- C4 : … AND PAY_TP = :v_tp          C5 : … AND ORD_ST = :v_st AND PAY_TP = :v_tp

-- 개발팀 요청서
CREATE INDEX IX_T_ORD_CUS ON T_ORD (CUS_ID);
CREATE INDEX IX_T_ORD_ST  ON T_ORD (ORD_ST);
CREATE INDEX IX_T_ORD_TP  ON T_ORD (PAY_TP);

실행계획 학습용 예시

xplan

[C1 · 지금 · CUS_000777 · 2024년 6월]
-----------------------------------------------------------------------------------------------
| Id  | Operation                | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |       |      1 |        |      2 |00:00:00.01 |     896 |
|*  1 |  VIEW                    |       |      1 |      2 |      2 |00:00:00.01 |     896 |
|*  2 |   WINDOW SORT PUSHED RANK|       |      1 |      2 |      2 |00:00:00.01 |     896 |
|*  3 |    TABLE ACCESS FULL     | T_ORD |      1 |      2 |      2 |00:00:00.01 |     896 |
-----------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=50)
   3 - filter(("CUS_ID"=:V_CUS AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[C2 · 지금 · PENDING · 2024-06-14]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |             |      1 |        |     11 |00:00:00.01 |     139 |
|*  1 |  VIEW                          |             |      1 |     11 |     11 |00:00:00.01 |     139 |
|*  2 |   WINDOW NOSORT STOPKEY        |             |      1 |     11 |     11 |00:00:00.01 |     139 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | T_ORD       |      1 |     11 |     11 |00:00:00.01 |     139 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_T_ORD_DT |      1 |    137 |    137 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=50)
   3 - filter("ORD_ST"=:V_ST)
   4 - access("ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1 AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD'))

문제

개발팀이 요청한 단일 컬럼 인덱스 셋이 각 조합에서 어떻게 쓰일지, 또는 쓰이지 않을지 설명하시오.
다섯 조합을 받치는 최소 인덱스 세트를 설계하고, 조합마다 어느 인덱스를 어떻게 쓰는지(access, filter, 정렬과 50건 멈춤) 설명하시오.
기존 IX_T_ORD_DT를 어떻게 할지와, 이 설계가 INSERT와 UPDATE에 주는 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① C1 : 고객 = , 기간 범위 · 역순으로 읽어 50 건에서 멈춘다
CREATE INDEX IX_T_ORD_CUS_DT ON T_ORD (CUS_ID, ORD_DT) INVISIBLE;
-- ② C2 ~ C5 : 늘 들어오는 기간을 선두로, 상태와 결제수단은 인덱스 안에서 거른다 (기존 (ORD_DT) 를 대신한다)
CREATE INDEX IX_T_ORD_DT_ST_TP ON T_ORD (ORD_DT, ORD_ST, PAY_TP) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- C1 ~ C5 를 세션에서 잰다
ALTER INDEX IX_T_ORD_CUS_DT VISIBLE;
ALTER INDEX IX_T_ORD_DT_ST_TP VISIBLE;
ALTER INDEX IX_T_ORD_DT INVISIBLE;      -- 다른 SQL 이 견디는지 지켜본 뒤
DROP INDEX IX_T_ORD_DT;
-- 단일 컬럼 (CUS_ID) · (ORD_ST) · (PAY_TP) 는 만들지 않는다 · 화면 SQL 은 그대로

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

xplan

[C1 · 고친 뒤]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                 |      1 |        |      2 |00:00:00.01 |       4 |
|*  1 |  VIEW                          |                 |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY        |                 |      1 |      2 |      2 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD           |      1 |      2 |      2 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_T_ORD_CUS_DT |      1 |      2 |      2 |00:00:00.01 |       2 |
-----------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=50)
   4 - access("CUS_ID"=:V_CUS AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1 AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD'))

[C2 · 고친 뒤]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     11 |00:00:00.01 |      13 |
|*  1 |  VIEW                          |                   |      1 |     11 |     11 |00:00:00.01 |      13 |
|*  2 |   WINDOW NOSORT STOPKEY        |                   |      1 |     11 |     11 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID | T_ORD             |      1 |     11 |     11 |00:00:00.01 |      13 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_T_ORD_DT_ST_TP |      1 |     11 |     11 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=50)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC )<=50)
   4 - access("ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1 AND "ORD_ST"=:V_ST AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD'))
       filter("ORD_ST"=:V_ST)

[C3 · 일주일]  IX_T_ORD_DT_ST_TP 역순 · 50건에서 멈춤 · Buffers 52
[C4 · 일주일 · CASH]  같은 인덱스 · 항목 약 150개를 읽어 맞는 50건 · Buffers 52
[C5 · 하루 · PENDING · CASH]  같은 인덱스 · 4건 · Buffers 6

모범 답안 서술

요청서의 단일 컬럼 인덱스부터 본다. (ORD_ST)와 (PAY_TP)는 값이 셋뿐이다. PENDING만 8,000건, CASH만 33,000건이고 그 행들은 테이블 전체에 흩어져 있어, 이 인덱스로 찾아가면 FULL(896블록)보다 비싸다. C2 ~ C5는 기간이 이미 137 ~ 959건으로 좁혀 주는 조합이라, 옵티마이저가 이 두 인덱스를 고를 일이 없다. 결제와 취소로 ORD_ST가 바뀔 때마다 고칠 거리만 는다. (CUS_ID)는 C1에서 쓰이지만 고객의 주문 50건을 테이블에서 다 읽고 기간을 걸러야 하고(약 52블록), 최근순으로 고르려면 정렬도 해야 한다. 조합을 묶어 보면 찾아가는 길은 둘이다. C1은 고객이 등치로 들어오므로 (CUS_ID, ORD_DT)를 만든다. 고객 등치 조건과 기간 범위가 둘 다 access이고, 인덱스를 거꾸로 읽으면 결과가 이미 최근순이라 정렬 없이 50건에서 멈춘다. 이 고객의 6월 주문 2건이면 4블록이다(Buffers 896 → 4). C2 ~ C5는 모두 기간을 주고, 상태와 결제수단은 보통 하루 ~ 일주일 범위 안에서 쓰인다. 기간을 선두로 상태와 결제수단을 뒤에 담은 (ORD_DT, ORD_ST, PAY_TP)를 만들면, 기간 범위를 거꾸로 읽으면서 상태와 결제수단은 인덱스 안에서 거르고 맞는 행만 테이블에 간다. 하루치가 리프 1블록, 일주일이 리프 4블록이라 거르는 비용은 작다. C2는 그날 PENDING 11건만 테이블에서 읽고(139 → 13), C3과 C4는 맞는 행 50건을 채우면 멈추며(약 52블록), C5는 4건이다(약 6블록). 기존 IX_T_ORD_DT (ORD_DT)는 새 인덱스의 접두라서 하던 일을 새 인덱스가 대신한다. INVISIBLE로 돌려 다른 SQL이 견디는지 지켜본 뒤 지운다. 그러면 보조 인덱스는 하나에서 둘로 하나만 는다(요청서대로면 넷이다). 대가는 주문 INSERT마다 고칠 인덱스가 하나 느는 것과, 결제나 취소로 ORD_ST가 바뀔 때마다 새 인덱스의 항목을 지우고 다시 넣는 것(하루 약 300건)이다.

해설

상태를 선두로 둔 (ORD_ST, ORD_DT)는 「석 달 취소 추이」처럼 긴 기간에 상태를 고정해 보는 SQL에 필요하다(ACC-001). 이 화면의 상태 조건은 하루 ~ 일주일 안에서만 쓰이므로 기간 선두 인덱스 안에서 거르는 것으로 충분하다.
기간이 시분초가 있는 DATE 범위라 ORD_ST와 PAY_TP는 순서와 상관없이 인덱스 안의 filter다. 둘의 순서는 거의 상관없고, 화면에서 더 자주 쓰는 상태를 앞에 두었다.
조합마다 SQL을 따로 보내기 때문에 이 설계가 들어맞는다. 한 SQL에 (:v_st IS NULL OR ORD_ST = :v_st) 같은 옵션 조건을 다 묶으면 옵티마이저가 조합마다 다른 인덱스를 고를 수 없다(IDX-051의 옵션 조건).
2페이지부터는 키 기반 페이징(마지막으로 본 ORD_DT, ORD_SEQ 다음부터)을 쓰면 같은 인덱스로 페이지마다 50건씩만 읽는다(교재 09화 §9).
C4는 일주일 범위라 괜찮지만, 결제수단으로 한 달을 고르는 일이 잦아지면 50건을 채우기 전에 읽는 항목이 늘어난다. 로그를 분기마다 다시 보고 판단한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황