PART-021

고객의 고액 주문 다섯 건을 보려고 그 고객 주문 500건을 다 읽고 정렬한다

중급SELECT한빛유통396 / 700

상황

한빛유통 VIP 상담 화면에는 「고액 주문 TOP 5」 칸이 있다. 전 기간에서 그 고객의 주문 금액이 큰 다섯 건을 보여 주고, 하루 약 2,000번 호출된다. 파티션 전환 때 조민우가 "인덱스에 금액까지 넣었으니 인덱스를 거꾸로 읽다가 다섯 건에서 멈추겠지" 하며 인덱스를 만들었다. 그런데 계획에는 정렬이 남아 있고 한 번에 572블록을 읽는다. 옮기기 전 T_ORD_BIG에서는 같은 모양의 인덱스로 8블록이었다.

테이블과 인덱스

T_ORD_BIG_P14 주문(파티션 복사본, 교재 14화): 1,000,000행 · PARTITION BY RANGE (ORD_DT) 월별 36칸(P202201 ~ P202412)과 빈 PMAX, 모두 37칸. 빈 PMAX는 세그먼트가 없다.
고객은 2,000명이고 고객마다 약 500건이 36칸에 고르게 흩어져 있다(칸마다 약 14건). 행마다 다른 블록에 있다.
LOCAL 인덱스 IX_P14_CUS_AMT (CUS_ID, ORD_AMT): 칸마다 높이 2. 다른 인덱스는 모두 LOCAL이다. 통계는 최신이다.
매월 1일 가장 오래된 칸을 DROP하고 새 칸을 만든다(보관 36개월).

원본 SQL

sql

SELECT *
  FROM (SELECT ORD_SEQ, ORD_DT, ORD_AMT, ORD_ST
          FROM T_ORD_BIG_P14
         WHERE CUS_ID = :v_cus
         ORDER BY ORD_AMT DESC)
 WHERE ROWNUM <= 5

실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36)

xplan

---------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                      | Name           | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                               |                |      1 |        |      5 | 00:00:00.01 |     572 |        |       |
|*  1 |  COUNT STOPKEY                                 |                |      1 |        |      5 | 00:00:00.01 |     572 |        |       |
|   2 |   VIEW                                         |                |      1 |    500 |      5 | 00:00:00.01 |     572 |        |       |
|*  3 |    SORT ORDER BY STOPKEY                       |                |      1 |    500 |      5 | 00:00:00.01 |     572 |        |       |
|   4 |     PARTITION RANGE ALL                        |                |      1 |    500 |    500 | 00:00:00.01 |     572 |      1 |    37 |
|   5 |      TABLE ACCESS BY LOCAL INDEX ROWID BATCHED | T_ORD_BIG_P14  |     36 |    500 |    500 | 00:00:00.01 |     572 |      1 |    37 |
|*  6 |       INDEX RANGE SCAN                         | IX_P14_CUS_AMT |     36 |    500 |    500 | 00:00:00.01 |      72 |      1 |    37 |
---------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
   3 - filter(ROWNUM<=5)
   6 - access("CUS_ID"=:V_CUS)

문제

인덱스에 ORD_AMT가 있는데도 정렬이 남고 500건을 다 읽는 까닭을 실행계획의 Starts와 인덱스가 정렬되어 있는 범위로 설명하시오.
SQL은 그대로 두고 다섯 건만 읽고 멈추도록 인덱스를 고치시오. 몇 블록이 될지 어림하시오.
그렇게 고쳤을 때 월 정리에서 치르는 대가와 그것을 처리하는 SQL을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- LOCAL 을 붙이지 않는다 = 파티션하지 않은 GLOBAL 인덱스 · 고객 하나의 주문이 칸과 상관없이 금액 순서로 한 줄에 선다
CREATE INDEX IX_P14_CUS_AMT_G ON T_ORD_BIG_P14 (CUS_ID, ORD_AMT);
DROP INDEX IX_P14_CUS_AMT;

-- SQL 은 그대로
SELECT *
  FROM (SELECT ORD_SEQ, ORD_DT, ORD_AMT, ORD_ST
          FROM T_ORD_BIG_P14
         WHERE CUS_ID = :v_cus
         ORDER BY ORD_AMT DESC)
 WHERE ROWNUM <= 5;

-- 월 정리 : 이제 글로벌 인덱스가 하나 있으므로 DDL 이 같이 고치게 한다
ALTER TABLE T_ORD_BIG_P14 DROP PARTITION P202201 UPDATE GLOBAL INDEXES;

개선 후 실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36)

xplan

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |      1 |        |      5 | 00:00:00.01 |       8 |        |       |
|*  1 |  COUNT STOPKEY                        |                  |      1 |        |      5 | 00:00:00.01 |       8 |        |       |
|   2 |   VIEW                                |                  |      1 |      5 |      5 | 00:00:00.01 |       8 |        |       |
|   3 |    TABLE ACCESS BY GLOBAL INDEX ROWID | T_ORD_BIG_P14    |      1 |    500 |      5 | 00:00:00.01 |       8 |  ROWID | ROWID |
|*  4 |     INDEX RANGE SCAN DESCENDING       | IX_P14_CUS_AMT_G |      1 |    500 |      5 | 00:00:00.01 |       3 |        |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=5)
   4 - access("CUS_ID"=:V_CUS)

모범 답안 서술

조민우의 생각은 파티션 없는 테이블에서는 맞다. 그런데 IX_P14_CUS_AMT는 LOCAL이라 칸마다 따로 있고, 금액 순서로 정렬되어 있는 것은 칸 안에서뿐이다. 칸은 ORD_DT로 나뉘어 있어서 어느 칸에 가장 큰 금액이 있는지 인덱스는 모른다. 그래서 옵티마이저는 36칸의 인덱스를 하나씩 시작해(PARTITION RANGE ALL, 6번 줄 Starts 36, 72블록) 그 고객의 주문 500건을 테이블에서 모두 읽고(500블록), 모은 다음 SORT ORDER BY STOPKEY로 정렬해 다섯 건을 고른다. 572블록 가운데 다섯 건 몫은 거의 없다. 칸과 상관없이 고객 하나의 주문이 금액 순서로 한 줄에 서 있으려면 인덱스가 칸으로 나뉘지 않아야 한다. LOCAL 없이 (CUS_ID, ORD_AMT) 인덱스를 만들면 파티션하지 않은 GLOBAL 인덱스가 된다. 옵티마이저는 이 인덱스를 거꾸로 읽다가(INDEX RANGE SCAN DESCENDING) COUNT STOPKEY로 다섯 건에서 멈춘다. 정렬이 사라지고 인덱스 3블록에 테이블 5블록, 모두 약 8블록이다(572 → 8). 하루 2,000번이면 약 114만 → 1만 6천 블록이다. 대가는 월 정리에서 치른다. 이 테이블에 처음으로 글로벌 인덱스가 생겼으므로, 매월 DROP PARTITION에 UPDATE GLOBAL INDEXES를 붙이지 않으면 이 인덱스가 UNUSABLE이 되고 이 화면은 에러 없이 FULL로 떨어진다. 월 정리 스크립트를 ALTER TABLE T_ORD_BIG_P14 DROP PARTITION P202201 UPDATE GLOBAL INDEXES로 바꾸고, 인덱스 요청서의 '칸 DDL 영향' 칸에 이 사실을 적는다.

해설

정렬 키가 파티션 키가 아니면 로컬 인덱스로는 칸을 넘어선 정렬 순서를 얻지 못한다. Top-N, 키 기반 페이징, MIN/MAX처럼 '순서대로 읽다 멈추는' 처리를 칸을 가로질러 하려면 GLOBAL 인덱스가 필요하다.
다른 답: 화면 요구를 '최근 1년의 고액 주문'으로 다시 정하면 로컬 인덱스로 12칸만 열게 된다(약 190블록). 정렬은 남지만 월 정리에 영향이 없다. 요구를 바꿀 수 있는지 먼저 물어본다(교재 09화).
GLOBAL 인덱스를 해시로 나눈 GLOBAL 파티션 인덱스(GLOBAL PARTITION BY HASH (CUS_ID))도 고객 하나가 한 칸에 모이므로 같은 효과가 있다. 칸 DDL 때 치르는 대가는 똑같다.
인덱스를 거꾸로 읽는 데 DESC 인덱스는 필요 없다. 오름차순 인덱스도 INDEX RANGE SCAN DESCENDING으로 읽는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36) ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 14화 실측 규모(고객 1명 500건, LOCAL 인덱스 Starts 36) ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황