주문상품 인덱스 넷 가운데 「영수증 재출력용」 하나를 지워도 되는지 묻는다

기초DDL버거69 / 700

상황

버거원 POS의 「영수증 재출력」은 주문번호로 주문상품을 읽는다. 2년 전 개발자는 이 화면을 위해 ORD_ITEM_X03 (ORD_NO)를 만들며 이렇게 적었다. "PK가 (ORD_NO, ITEM_ID) 두 컬럼이라 주문번호 하나만으로는 PK를 못 탈 것 같다." 이번 점검에서 주문상품 적재가 무겁다는 말이 나왔고, 오과장은 X03을 지워도 되는지 물었다.

테이블과 인덱스

ORD_ITEM 주문상품: 2,472,717행 · 55,265블록 · (ORD_NO, ITEM_ID) 순으로 쌓였다 · 주문 하나에 평균 2.5줄 · POS가 하루 약 3,400줄을 INSERT한다
ORD_ITEM_PK (ORD_NO, ITEM_ID): BLEVEL 2 · 리프 약 6,200블록 · CF 약 55,100
ORD_ITEM_X01 (ORD_DT, ORD_HMS): 리프 약 9,320블록 · ORD_ITEM_X02 (ITEM_ID, ORD_DT, ORD_ITEM_QTY): 「상품 월간 추이」용
ORD_ITEM_X03 (ORD_NO): BLEVEL 2 · 리프 약 5,400블록 · CF 약 55,100 · 통계 최신
V$SQL_PLAN에서 X03을 쓰는 SQL은 「영수증 재출력」 하나만 보인다

원본 SQL

sql

-- 「영수증 재출력」 (매장 POS · 하루 약 6천 번)
SELECT ITEM_ID, ORD_ITEM_QTY
  FROM ORD_ITEM
 WHERE ORD_NO = :v_ord_no
 ORDER BY ITEM_ID;

-- 2년 전 만든 인덱스
CREATE INDEX ORD_ITEM_X03 ON ORD_ITEM (ORD_NO);

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |              |      1 |        |      3 |00:00:00.01 |       4 |
|   1 |  SORT ORDER BY                       |              |      1 |      3 |      3 |00:00:00.01 |       4 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD_ITEM     |      1 |      3 |      3 |00:00:00.01 |       4 |
|*  3 |    INDEX RANGE SCAN                  | ORD_ITEM_X03 |      1 |      3 |      3 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("ORD_NO"=:V_ORD_NO)

문제

개발자의 걱정(PK가 두 컬럼이라 주문번호 하나로는 못 탄다)이 맞는지 결합 인덱스의 선두 컬럼으로 설명하시오.
ORD_ITEM_X03을 정리하는 절차를 작성하고, 지웠을 때 얻는 것과 지우기 전에 확인할 것을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- PK (ORD_NO, ITEM_ID) 의 선두가 ORD_NO 다 → X03 이 하는 일을 PK 가 다 한다
ALTER INDEX ORD_ITEM_X03 INVISIBLE;     -- 영수증 SQL 이 PK 로 가는지 보고, 2주 동안 다른 SQL 을 지켜본다
DROP INDEX ORD_ITEM_X03;

-- 「영수증 재출력」 SQL 은 그대로
SELECT ITEM_ID, ORD_ITEM_QTY
  FROM ORD_ITEM
 WHERE ORD_NO = :v_ord_no
 ORDER BY ITEM_ID;

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

xplan

-----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |      1 |        |      3 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| ORD_ITEM    |      1 |      3 |      3 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | ORD_ITEM_PK |      1 |      3 |      3 |00:00:00.01 |       3 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_NO"=:V_ORD_NO)

모범 답안 서술

걱정은 맞지 않다. 결합 인덱스는 선두 컬럼부터 조건이 이어지는 만큼 범위를 지목한다. ORD_ITEM_PK는 주문번호 순으로, 같은 주문 안에서는 상품번호 순으로 정렬되어 있으므로 ORD_NO = :v_ord_no 하나로 그 주문의 2 ~ 3항목을 찾아간다. 두 컬럼을 다 줘야 하는 것은 UNIQUE SCAN으로 한 건을 찾을 때뿐이다. 범위를 못 정하는 것은 선두 ORD_NO 없이 ITEM_ID만 줄 때다(IDX-006의 모양). X03은 PK의 접두라서 X03이 하는 일을 PK가 다 한다. PK로 읽어도 인덱스 3블록과 테이블 1블록, 모두 4블록으로 같다. 게다가 PK는 같은 주문 안에서 ITEM_ID 순이라 결과가 이미 ORDER BY 순서이므로 SORT ORDER BY가 없어진다. X03에서는 같은 주문 안의 항목이 ROWID 순으로 놓여 있어, 옵티마이저가 ITEM_ID 순서를 장담할 수 없으니 정렬이 남아 있었다. 얻는 것은 POS가 주문상품을 넣을 때마다 고치는 인덱스가 하나 주는 것(하루 약 3,400줄, 장애 뒤 재적재 때는 247만 줄)과 리프 약 5,400블록(약 42MB)이다. 지우기 전에는 세 가지를 확인한다. X03이 유니크 인덱스가 아닌지, X03을 쓰는 다른 SQL이 없는지(V$SQL_PLAN과 DBA_INDEX_USAGE), ORD_ITEM이 ORD를 가리키는 FK의 자식 쪽 확인을 PK가 대신하는지(FK 컬럼 ORD_NO가 PK의 선두다). 그리고 INVISIBLE로 바꿔 영수증 SQL이 PK로 가는 것을 보고 2주쯤 지켜본 뒤 지운다.

해설

함정: 반대로 PK를 지우고 X03을 남기자는 생각은 안 된다. PK 제약을 받치는 인덱스는 지울 수 없고, 유일성 검사도 PK가 한다(IDX-068).
인덱스 하나가 늘리는 적재 비용은 교재 14화 §2.1의 실측(5만 건에 인덱스 하나당 redo 약 12MB, 블록 변경 약 10만)을 참고한다. POS의 하루 3,400줄은 작지만, 장애 뒤 재적재나 이관처럼 247만 줄을 다시 넣을 때 크게 드러난다.
접두가 같은 짧은 인덱스는 리프가 작아서 FAST FULL SCAN으로 세는 SQL에 유리할 수 있다(교재 04화 §6). 주문 수를 세는 SQL이 따로 있다면 그 SQL도 PK로 재 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황