PART-002

영수증 번호 하나를 찾는데 인덱스를 24번 연다

중급SELECT버거377 / 700

상황

매장 POS의 「영수증 재조회」는 환불이나 재출력, 포인트 확인 때 영수증 바코드(주문번호)를 찍어 조회한다. 200개 매장에서 하루 약 6만 번 호출된다. 파티션 테이블로 옮긴 뒤 DB CPU 상위 SQL 목록에 이 SQL이 올라왔다. 영수증 바코드에는 주문번호만 있다. 주문일은 영수증에 찍혀 있지만 POS가 넘기지 않는다. 넘기게 하려면 POS 배포(200개 매장, 다음 분기)가 필요하다.

테이블과 인덱스

ORD_RANGE: 1,000,000행 · PARTITION BY RANGE (ORD_YM) 월별 25칸(P201201 ~ P201312 + 빈 P_DEFAULT) · 한 달 약 41,000행
인덱스 ORD_RANGE_PK (ORD_NO, ORD_YM)는 LOCAL이다(PK를 LOCAL로 만들려고 파티션 키를 뒤에 붙였다). ORD_RANGE_X01 (ORD_DT, ORD_HMS)도 LOCAL이다. 두 인덱스 모두 칸마다 높이가 2다.
매달 1일에 보관 기간이 지난 달을 정리한다(월 1회). 이 작업은 칸째 들어내는 파티션 DDL로 바꾸기로 했다.
옮기기 전 파티션 없는 ORD에서는 ORD_PK (ORD_NO)로 4블록을 읽었다.
아래는 :ord_no = 777777(2013년 7월 주문, 19번 칸)로 실행한 결과다.

원본 SQL

sql

SELECT ORD_NO, ORD_YM, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD_RANGE
 WHERE ORD_NO = :ord_no

실행계획 실측 · 교재 14화

xplan

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |      1 | 00:00:00.01 |      50 |        |       |
|   1 |  PARTITION RANGE ALL                |              |      1 |      1 |      1 | 00:00:00.01 |      50 |      1 |    25 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | ORD_RANGE    |     24 |      1 |      1 | 00:00:00.01 |      50 |      1 |    25 |
|*  3 |    INDEX RANGE SCAN                 | ORD_RANGE_PK |     24 |      1 |      1 | 00:00:00.01 |      49 |      1 |    25 |
--------------------------------------------------------------------------------------------------------------------------------

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

문제

한 행을 찾는 데 50블록이 드는 까닭을 실행계획의 Starts, Pstart, Pstop과 인덱스 정의로 설명하시오.
고치는 방법 두 가지(조건을 더한다, 인덱스를 바꾼다)를 SQL로 작성하고, 각각의 대가와 이 화면에 어느 쪽을 권할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 안 A : 파티션 키를 같이 준다 — POS 가 영수증의 주문일에서 월을 넘긴다 (POS 배포 필요)
SELECT ORD_NO, ORD_YM, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD_RANGE
 WHERE ORD_NO = :ord_no
   AND ORD_YM = :ord_ym;

-- 안 B : POS 를 못 고치면 — 칸과 상관없는 GLOBAL 유니크 인덱스 (파티션 안 한 인덱스)
CREATE UNIQUE INDEX ORD_RANGE_GX01 ON ORD_RANGE (ORD_NO);
-- 그리고 월 정리는 반드시 글로벌 인덱스를 같이 고친다
--   ALTER TABLE ORD_RANGE DROP PARTITION P201201 UPDATE GLOBAL INDEXES;

개선 후 실행계획 실측 · 교재 14화

xplan

[안 A — ORD_NO + ORD_YM (:ord_ym = '201307')]
--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name         | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |              |      1 |        |      1 | 00:00:00.01 |       3 |        |       |
|   1 |  PARTITION RANGE SINGLE             |              |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | ORD_RANGE    |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  3 |    INDEX UNIQUE SCAN                | ORD_RANGE_PK |      1 |      1 |      1 | 00:00:00.01 |       2 |    KEY |   KEY |
--------------------------------------------------------------------------------------------------------------------------------

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

  (실행 때 19번 칸 P201307 하나 · 교재 14화 실측 3 블록)

[안 B — GLOBAL 유니크 인덱스 ORD_RANGE_GX01 (ORD_NO)]
----------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Starts | E-Rows | A-Rows |    A-Time   | Buffers | Pstart | Pstop |
----------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |      1 |        |      1 | 00:00:00.01 |       4 |        |       |
|   1 |  TABLE ACCESS BY GLOBAL INDEX ROWID | ORD_RANGE      |      1 |      1 |      1 | 00:00:00.01 |       4 |  ROWID | ROWID |
|*  2 |   INDEX UNIQUE SCAN                 | ORD_RANGE_GX01 |      1 |      1 |      1 | 00:00:00.01 |       3 |        |       |
----------------------------------------------------------------------------------------------------------------------------------

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

모범 답안 서술

답은 3번 줄의 Starts 24에 있다. ORD_RANGE_PK는 LOCAL이라 칸마다 따로 있고, 선두 컬럼이 ORD_NO이며 파티션 키 ORD_YM은 뒤에 있다(nonprefixed). 조건에 ORD_YM이 없으니 옵티마이저는 이 번호가 어느 칸에 있는지 모르고(PARTITION RANGE ALL, Pstart 1, Pstop 25) 칸마다 인덱스를 한 번씩 연다. 데이터가 있는 24칸 × 약 2블록에 테이블 1블록을 더해 약 50블록이다. 찾는 행은 19번 칸에 하나뿐이다. 안 A는 ORD_YM을 같이 주는 방법이다. 칸 하나(RANGE SINGLE)에서 INDEX UNIQUE SCAN을 한 번 해 3블록으로 끝난다(50 → 3). DB 쪽 대가는 없다. 대신 POS가 영수증의 주문일에서 월을 넘겨야 하고(200개 매장 배포), 월을 잘못 넘기면 '없는 영수증'이 된다. 안 B는 ORD_NO 하나짜리 GLOBAL 유니크 인덱스를 만드는 방법이다. 칸과 상관없이 한 번에 찾으며(TABLE ACCESS BY GLOBAL INDEX ROWID, 4블록) POS는 그대로 둔다. 대가는 파티션 DDL에서 치른다. 매달 칸을 정리할 때 UPDATE GLOBAL INDEXES 없이 하면 인덱스가 UNUSABLE이 되고, 이 조회는 에러 없이 전체 칸 스캔으로 떨어진다(교재 14화, 같은 모양에서 3 → 3,505). 유니크 인덱스라서 새 주문 INSERT는 ORA-01502로 멈춘다. 이 절을 붙이면 인덱스는 VALID로 남는다. 인덱스 하나만큼 주문 적재도 무거워진다. 권하는 안: 이번 분기는 B로 DB만 고치고 월 정리 스크립트에 UPDATE GLOBAL INDEXES를 넣는다. POS 다음 배포 때 A로 월을 넘기게 해서, 칸 수가 늘어도(보관 36개월이면 Starts 36) 한 칸만 열게 한다.

해설

LOCAL PK의 숙명: 유니크 인덱스를 LOCAL로 만들려면 파티션 키가 인덱스에 들어 있어야 한다. 그래서 PK가 (ORD_NO, ORD_YM)이고, 번호만으로 찾으면 칸 수만큼 인덱스를 연다(교재 14화 §8 실측: Starts 24, 50블록. 같은 주문을 ORD_YM과 함께 찾으면 3블록, 파티션 없는 ORD에서는 4블록).
한 번에 50블록은 싸 보여도 하루 6만 번이면 300만 블록이다. A는 18만 블록, B는 24만 블록이다. 이런 SQL은 응답 시간보다 CPU 상위 목록에서 먼저 눈에 띈다.
GLOBAL 인덱스를 해시로 나눈 GLOBAL 파티션 인덱스로 만들어도 칸 DDL 때의 문제는 같다.
주문번호가 날짜순으로 매겨진다면 서버에서 번호 구간으로 월을 구할 수도 있다. 다만 '번호는 날짜순'이 제약이 아니라 관례일 뿐이라면 위험하다(교재 09화: 정렬 키의 정답성은 실측으로 확인한다).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 실측 · 교재 14화 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 실측 · 교재 14화 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황