PART-020

고객 상담 화면의 6개월 주문 조회가 칸 여섯 개만 여는데도 600블록이 넘는다

중급SELECT한빛유통395 / 700

상황

한빛유통 고객센터의 「최근 주문」 탭은 고객 한 명의 최근 6개월 주문을 최신순으로 보여 준다. 상담원이 전화를 받을 때마다 열어서 하루 약 3,000번 호출된다. T_ORD_BIG을 월별 파티션으로 옮기면서 DBA 표준 "로컬 인덱스는 파티션 키를 맨 앞에 둔다(Prefixed)"대로 인덱스를 만들었다. 박팀장: "칸도 여섯 개만 열고 인덱스도 타는데, 왜 한 번에 600블록이 넘나."

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX, 모두 37칸. P202401은 25번째, P202406은 30번째 칸이다. 칸 하나는 약 27,400 ~ 27,800행이다.
ORD_DT는 DATE이고 전부 자정이다. 고객은 2,000명이고 고객마다 약 500건(한 달에 약 14건)이다. 고객 CUS_000123의 2024년 상반기 주문은 83건이다.
LOCAL 인덱스 IX_P14_DT_CUS (ORD_DT, CUS_ID): 칸마다 리프 약 95블록, 높이 2. 다른 인덱스는 없다. 통계는 최신이다.
매월 1일 가장 오래된 칸을 DROP한다(보관 36개월).

원본 SQL

sql

SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG_P14
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD')
 ORDER BY ORD_DT DESC

실행계획 학습용 예시 · 교재 14화 규모

xplan

[:v_cus = 'CUS_000123', :v_from = '20240101', :v_to = '20240701']
-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |               |      1 |        |     83 | 00:00:00.01 |     661 |        |       |
|   1 |  SORT ORDER BY                                |               |      1 |     83 |     83 | 00:00:00.01 |     661 |        |       |
|*  2 |   FILTER                                      |               |      1 |        |     83 | 00:00:00.01 |     661 |        |       |
|   3 |    PARTITION RANGE ITERATOR                   |               |      1 |     83 |     83 | 00:00:00.01 |     661 |    KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |      6 |     83 |     83 | 00:00:00.01 |     661 |    KEY |   KEY |
|*  5 |      INDEX RANGE SCAN                         | IX_P14_DT_CUS |      6 |     83 |     83 | 00:00:00.01 |     578 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')>TO_DATE(:V_FROM,'YYYYMMDD'))
   5 - access("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "CUS_ID"=:V_CUS AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD'))
       filter("CUS_ID"=:V_CUS)

  (실행 때 고른 칸: 25 ~ 30번, P202401 ~ P202406)

문제

칸을 여섯 개만 열고 인덱스도 쓰는데 661블록을 읽는 까닭을 실행계획의 Starts, Buffers, Predicate Information으로 설명하시오.
SQL은 그대로 두고 인덱스를 다시 설계하시오. "로컬 인덱스는 Prefixed로 만든다"는 표준이 이 화면에서 왜 맞지 않는지 쓰시오.
조회 기간을 3년으로 넓혀 달라는 요청이 오면 어떤 인덱스가 나은지, 그 대가와 함께 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 칸은 테이블의 ORD_DT 조건이 고른다 · 칸 안에서는 등치 조건(CUS_ID)을 앞에, 범위 조건(ORD_DT)을 뒤에
CREATE INDEX IX_P14_CUS_DT ON T_ORD_BIG_P14 (CUS_ID, ORD_DT) LOCAL;

-- 다른 SQL 이 IX_P14_DT_CUS 를 쓰지 않는지 V$SQL_PLAN 으로 확인한 뒤 걷는다
DROP INDEX IX_P14_DT_CUS;

-- SQL 은 그대로
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG_P14
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD')
 ORDER BY ORD_DT DESC;

개선 후 실행계획 학습용 예시 · 교재 14화 규모

xplan

-------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                     | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                              |               |      1 |        |     83 | 00:00:00.01 |      95 |        |       |
|   1 |  SORT ORDER BY                                |               |      1 |     83 |     83 | 00:00:00.01 |      95 |        |       |
|*  2 |   FILTER                                      |               |      1 |        |     83 | 00:00:00.01 |      95 |        |       |
|   3 |    PARTITION RANGE ITERATOR                   |               |      1 |     83 |     83 | 00:00:00.01 |      95 |    KEY |   KEY |
|   4 |     TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14 |      6 |     83 |     83 | 00:00:00.01 |      95 |    KEY |   KEY |
|*  5 |      INDEX RANGE SCAN                         | IX_P14_CUS_DT |      6 |     83 |     83 | 00:00:00.01 |      12 |    KEY |   KEY |
-------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')>TO_DATE(:V_FROM,'YYYYMMDD'))
   5 - access("CUS_ID"=:V_CUS AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD'))

모범 답안 서술

칸은 제대로 골랐다(PARTITION RANGE ITERATOR, 25 ~ 30번 여섯 칸). 문제는 칸 안이다. 5번 줄의 access 조건은 ORD_DT 범위로 시작하고 CUS_ID는 filter에 다시 나온다. 선두 컬럼 ORD_DT가 범위 조건이라서, 그 뒤의 CUS_ID는 읽는 범위를 줄이지 못하고 읽은 키를 거르기만 한다. 그래서 칸마다 한 달 치 리프 약 95블록을 다 읽었다. 6칸 × 약 96블록 = 578블록이고, 찾은 83건의 테이블 액세스 83블록을 더해 661블록이다. 칸을 고르는 일은 인덱스 선두가 아니라 테이블의 파티션 키 조건(ORD_DT >= … AND ORD_DT < …)이 한다. 로컬 인덱스는 테이블과 같은 칸으로 나뉘므로 Nonprefixed여도 테이블이 고른 여섯 칸의 인덱스만 본다. 그러니 칸 안에서는 결합 인덱스 원리대로 등치 조건 CUS_ID를 앞에, 범위 조건 ORD_DT를 뒤에 둔다. (CUS_ID, ORD_DT) LOCAL이면 칸마다 그 고객의 키 14개 정도만 읽어 2블록이면 되고, 인덱스는 578 → 12, 전체는 661 → 95블록으로 준다. SQL은 바꾸지 않는다. "로컬 인덱스는 Prefixed로"라는 표준은 파티션 키가 등치 조건으로 오는 조회에서나 맞다. 이 화면처럼 파티션 키가 범위 조건이고 다른 컬럼이 등치 조건이면, Prefixed는 칸 안에서 범위를 먼저 타는 나쁜 순서가 된다. 표준은 "칸은 테이블 조건이 고르고, 칸 안의 컬럼 순서는 일반 인덱스 원리로 정한다"로 고친다. 조회 기간이 3년으로 넓어지면 Nonprefixed 로컬 인덱스는 36칸마다 한 번씩 뒤진다(36 × 2 = 72블록에 테이블 약 500블록). 이때는 칸과 상관없이 한 번에 찾는 GLOBAL (CUS_ID, ORD_DT)가 인덱스 쪽을 72 → 3블록으로 줄인다. 대가는 월 정리다. 매월 DROP PARTITION에 UPDATE GLOBAL INDEXES를 붙여야 하고, 빠뜨리면 인덱스가 UNUSABLE이 되어 이 화면이 FULL로 떨어진다. 테이블 액세스 500블록이 대부분이라 인덱스에서 아끼는 69블록이 그 대가만큼 큰지는 따져 봐야 한다.

해설

함정: Prefixed로 두고 INDEX_SS 같은 힌트로 해결하려는 것. 날짜가 전부 자정이라 칸 안에 날짜가 30개뿐이어서 SKIP SCAN도 될 수는 있지만, 인덱스 컬럼 순서를 바로잡는 쪽이 단순하고 확실하다.
같은 표를 놓고 SQLP에서 자주 묻는 것: 파티션 키가 범위로 오고 다른 컬럼이 등치로 오면 Nonprefixed 로컬 인덱스가 낫다. 범위가 많은 칸에 걸치면 칸마다 수직 탐색을 하므로 GLOBAL Prefixed 인덱스가 나을 수 있다.
교재 14화 실측: 고객 1명 500건을 날짜 없이 찾으면 LOCAL (CUS_ID)는 인덱스 Starts 36에 572블록(인덱스 72), GLOBAL (CUS_ID)는 504블록(인덱스 4)이었다. 한 달로 좁히면 LOCAL은 16블록이다.
인덱스를 바꾸기 전에 IX_P14_DT_CUS를 쓰는 다른 SQL(예: 날짜만 주는 일 집계)이 있는지 V$SQL_PLAN으로 확인한다. 있다면 그 SQL에는 (ORD_DT, …) Prefixed가 맞을 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황