개발팀이 WHERE절에 나오는 컬럼마다 인덱스를 하나씩 달라고 한다

실전DDL이커머스80 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

신규 이커머스 플랫폼을 오픈하기 전에 점검하고 있다. 개발팀이 화면 SQL 셋과 함께 인덱스 요청서를 보냈는데, 내용은 이렇다. "WHERE에 나오는 컬럼마다 하나씩: (customer_id), (order_date), (status), (order_id)" 같은 주에 정합성 회복 계획도 확정됐다. 꺼져 있는 FK FK_ORDERS_CUSTOMER_ID를 다시 켜고, 매달 휴면 고객 정리 배치가 주문이 없는 고객을 customers에서 지운다. 주문 화면은 배치가 도는 시간에도 주문을 넣는다. 박팀장은 이렇게 당부했다. "최소한으로. 무엇을 만들고 무엇을 안 만드는지, 각각 왜인지."

테이블과 인덱스

orders: 34,908행 · 193블록 · order_date DATE(시분초 있음, 2020 ~ 2024, 하루 약 20건) · status 6종(히스토그램 없음) · total_amount
고객 5,230명 · 고객당 주문 평균 6.7건(고객 390은 16건) · 2024년 6월 주문 563건
인덱스: PK (order_id) 하나뿐(실행계획에는 PK_ORDERS로 적는다) · FK 39개 전부 DISABLED(FK_ORDERS_CUSTOMER_ID 포함)
참고 실측(교재 18화): (customer_id, order_date) 인덱스는 리프 112 · CF 34,730 · 1MB이고, 만드는 데 0.1초 안팎 걸린다. orders 복사본에 34,908행을 일반 INSERT하면 redo 1.56MB, 블록 변경 1,816이다. 여기에 그 인덱스 하나를 더하면 redo 약 12.1MB, 블록 변경 약 72,600으로 늘어난다

원본 SQL

sql

-- S1 「고객 주문 내역」 — 고객센터 · 하루 종일
SELECT order_id, order_date, status, total_amount
  FROM orders
 WHERE customer_id = :cust
 ORDER BY order_date DESC
 FETCH FIRST 20 ROWS ONLY;

-- S2 「상태별 일 매출」 — 운영 대시보드 · 상담원 화면 30 대가 1분마다 · 기간은 보통 한 달
SELECT TRUNC(order_date) AS dt, COUNT(*) AS cnt, SUM(total_amount) AS amt
  FROM orders
 WHERE status = :st
   AND order_date >= :d_from AND order_date < :d_to
 GROUP BY TRUNC(order_date);

-- S3 「주문 상세 헤더」 — 주문번호 단건
SELECT order_id, customer_id, order_date, status, total_amount
  FROM orders
 WHERE order_id = :id;

실행계획 학습용 예시 · 인덱스 규모와 적재 비용은 교재 18화 실측

xplan

[S1 — 고객 390]
---------------------------------------------------------------------------------------------
| Id  | Operation                | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |        |      1 |        |     16 |00:00:00.01 |     193 |
|*  1 |  VIEW                    |        |      1 |      7 |     16 |00:00:00.01 |     193 |
|*  2 |   WINDOW SORT PUSHED RANK|        |      1 |      7 |     16 |00:00:00.01 |     193 |
|*  3 |    TABLE ACCESS FULL     | ORDERS |      1 |      7 |     16 |00:00:00.01 |     193 |
---------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC )<=20)
   3 - filter("CUSTOMER_ID"=:CUST)

[S2 — 2024년 6월 · 상태 하나]
----------------------------------------------------------------------------------------
| Id  | Operation           | Name   | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |        |      1 |        |     29 |00:00:00.01 |     193 |
|   1 |  HASH GROUP BY      |        |      1 |     94 |     29 |00:00:00.01 |     193 |
|*  2 |   FILTER            |        |      1 |        |     97 |00:00:00.01 |     193 |
|*  3 |    TABLE ACCESS FULL| ORDERS |      1 |     94 |     97 |00:00:00.01 |     193 |
----------------------------------------------------------------------------------------
   2 - filter(:D_FROM<:D_TO)
   3 - filter(("STATUS"=:ST AND "ORDER_DATE">=:D_FROM AND "ORDER_DATE"<:D_TO))

[S3]
-------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORDERS    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_ORDERS |      1 |      1 |      1 |00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------
   2 - access("ORDER_ID"=:ID)

문제

요청서의 인덱스 넷을 하나씩 만든다, 만들지 않는다, 다른 모양으로 만든다 중 하나로 판정하시오. 제안한 인덱스 세트로 S1, S2, S3의 실행계획이 어떻게 바뀌는지 줄 단위로 설명하시오.
FK를 다시 켤 때 orders의 FK 컬럼에 인덱스가 없으면 휴면 고객 정리 배치에 무슨 일이 생기는지 설명하시오. 제안한 인덱스 세트가 그 문제를 어떻게 막는지도 설명하시오.
이 인덱스 세트의 적재 비용을 숫자로 적고, 그래도 만드는(또는 만들지 않는) 이유를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① S1 + FK : 고객으로 찾고 날짜 역순 20 건에서 멈춘다 · 선두가 FK 컬럼이라 부모 DELETE 가 자식을 훑지 않는다
CREATE INDEX IX_ORD_CUST_DT ON orders (customer_id, order_date) INVISIBLE;

-- ② S2 : = 인 status 앞 · 범위인 order_date 뒤 · 합계 total_amount 까지 담아 테이블에 안 간다
CREATE INDEX IX_ORD_ST_DT ON orders (status, order_date, total_amount) INVISIBLE;

-- ③ S3 : 만들지 않는다 — PK_ORDERS (order_id) 가 이미 있다
-- 요청서의 단독 인덱스 넷은 만들지 않는다 (위 두 개가 대신한다)

ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 세션에서 S1 · S2 를 재고 → T2 승인 → VISIBLE

-- FK 는 인덱스 ① 을 VISIBLE 로 한 뒤, 고아 주문을 세고(0 건이어야) 켠다
--   SELECT COUNT(*) FROM orders o WHERE NOT EXISTS (SELECT 1 FROM customers c WHERE c.customer_id = o.customer_id);
ALTER TABLE orders ENABLE VALIDATE CONSTRAINT FK_ORDERS_CUSTOMER_ID;

개선 후 실행계획 학습용 예시 · 인덱스 규모와 적재 비용은 교재 18화 실측

xplan

[S1 — IX_ORD_CUST_DT]
----------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                |      1 |        |     16 |00:00:00.01 |      18 |
|*  1 |  VIEW                          |                |      1 |      7 |     16 |00:00:00.01 |      18 |
|*  2 |   WINDOW NOSORT STOPKEY        |                |      1 |      7 |     16 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ORDERS         |      1 |      7 |     16 |00:00:00.01 |      18 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_CUST_DT |      1 |      7 |     16 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=20)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("ORDER_DATE") DESC )<=20)
   4 - access("CUSTOMER_ID"=:CUST)

[S2 — IX_ORD_ST_DT]
---------------------------------------------------------------------------------------------
| Id  | Operation           | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |      1 |        |     29 |00:00:00.01 |       2 |
|   1 |  HASH GROUP BY      |              |      1 |     94 |     29 |00:00:00.01 |       2 |
|*  2 |   FILTER            |              |      1 |        |     97 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN | IX_ORD_ST_DT |      1 |     94 |     97 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------
   2 - filter(:D_FROM<:D_TO)
   3 - access("STATUS"=:ST AND "ORDER_DATE">=:D_FROM AND "ORDER_DATE"<:D_TO)

[S3] 그대로 — PK_ORDERS · 3 블록

모범 답안 서술

요청서의 인덱스 넷은 이렇게 판정한다. (order_id)는 만들지 않는다. PK_ORDERS가 이미 order_id 선두라서 S3은 지금도 INDEX UNIQUE SCAN 3블록이고, 같은 컬럼 목록으로는 만들 수도 없다(ORA-01408). (status) 단독은 값이 6종뿐이라 혼자서는 옵티마이저가 고를 만큼 범위를 좁히지 못한다. (customer_id)와 (order_date) 단독 인덱스 대신, 조건과 정렬의 모양에 맞춘 결합 인덱스 둘을 만든다. ① (customer_id, order_date): S1은 지금 193블록을 FULL로 읽고 WINDOW SORT로 정렬한다(1 ~ 3번 줄). 이 인덱스가 있으면 고객 390의 16항목을 날짜 역순으로 찾아가(RANGE SCAN DESCENDING) 정렬 없이 20건에서 멈춘다(WINDOW NOSORT STOPKEY). Buffers는 193 → 18로 준다. 또 선두가 FK 컬럼 customer_id라서 FK 인덱스 역할도 한다. ② (status, order_date, total_amount): S2는 '상태 =' 조건에 '기간 범위' 조건이 붙는다. =로 들어오는 status를 앞에, 범위 조건인 order_date를 뒤에 두고 합계를 낼 total_amount까지 담으면 테이블에 가지 않는다(193 → 2). 상담원 화면 30대가 1분마다 호출하는 SQL이라, 한 번에 아끼는 191블록이 하루에는 수백만 블록이 된다. FK를 인덱스 없이 켜면 문제가 생긴다. 휴면 고객을 한 명 지울 때마다 오라클은 자식 테이블 orders에 그 고객의 주문이 정말 없는지 확인하려고 193블록을 통째로 훑는다. 공식 문서에는 이때 문장이 실행되는 동안 자식 테이블에 테이블 락(TM, S 모드)을 건다고 되어 있다. 교재 12화에서 이 버전으로 잰 결과는 자식 쪽의 진행 중인 트랜잭션을 TX 모드 4로 하나씩 기다리는 모습이었다. 어느 쪽이든 부모 쪽 배치가 주문 트랜잭션에 묶이고, 그동안 이미 지운 고객들의 행 락을 계속 쥐고 있게 된다. FK 컬럼이 선두인 인덱스 ①이 있으면 인덱스에서 '없음'을 확인하고 끝나므로 아무도 기다리지 않는다. 그래서 인덱스 ①을 먼저 VISIBLE로 바꾸고, 고아 주문을 센 뒤 FK를 켠다. 적재 비용은 교재 18화 실측으로 가늠한다. orders 34,908행을 일반 INSERT할 때 redo가 인덱스 없이는 1.56MB이고, 인덱스 하나를 더하면 약 12.1MB(블록 변경 1,816 → 약 72,600)가 된다. 인덱스가 둘이면 인덱스 몫이 그 두 배쯤이다. 그래도 만든다. 주문은 하루 약 20건이라 온라인 적재 비용은 작고, 두 화면은 하루 종일 호출되기 때문이다. 대량 이관 때는 인덱스를 UNUSABLE → 적재 → REBUILD 순서로 처리해 비용을 따로 관리한다.

해설

다른 정답: S2를 인덱스 없이 두는 것도 답이 될 수 있다. 193블록 FULL은 작은 편이다. 판단 근거는 호출 빈도다. 1분마다 30대가 아니라 하루 몇 번만 호출된다면 인덱스 ②의 적재 비용이 더 비싸다. 그 판단을 숫자로 적었다면 정답이다.
(customer_id) 단독 인덱스로도 FK 쪽 문제는 막을 수 있고 S1도 빨라진다. 하지만 정렬(WINDOW SORT)이 남아서, 고객의 주문이 많아질수록 20건에서 멈추지 못한다. 두 번째 컬럼이 order_date이기 때문에 정렬까지 인덱스로 처리할 수 있다(교재 09화, 18화 졸업 과제 E1).
FK 인덱스는 orders 하나로 끝나지 않는다. 부모 orders를 지우거나 키를 바꿀 일이 있다면 order_items, payments, shipping의 order_id에서도 같은 검사가 일어난다(교재 18화 IX_C18_PAY_ORD, IX_C18_SHP_ORD).
인덱스 없는 FK의 락 모양은 문서와 측정이 어긋났다. 버전마다 다를 수 있으니 직접 다시 잰다. 결론(FK에는 인덱스를 둔다)은 같다(교재 12화 §8).
S1의 정렬 키 order_date는 유일하지 않다. 같은 시각의 주문 둘이 20건 경계에 걸리면 순서가 흔들리므로, 정렬 끝에 order_id를 붙인다(교재 09화, 18화 E1 rev01).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 인덱스 규모와 적재 비용은 교재 18화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 인덱스 규모와 적재 비용은 교재 18화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황