고객 요약 팝업에 '총 주문 금액' 칸 하나를 더했더니 4블록이던 조회가 500블록을 넘는다

기초SELECT한빛유통96 / 700

상황

고객센터의 「고객 요약」 팝업은 고객 번호를 넣으면 그 고객의 주문 건수를 보여 준다. 상담원 서른 명이 하루 6천 번쯤 연다. 이번 달 요청으로 팝업에 '총 주문 금액' 칸을 더해 SUM(ORD_AMT)가 SQL에 들어갔다. 배포 다음 날부터 팝업이 굼뜨다는 말이 나왔다. 개발자는 "조건은 그대로 CUS_ID 하나라서 같은 인덱스를 타는데요"라고 한다. 아래는 고객 CUS_000123이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록(블록당 약 172행) · ORD_SEQ 순으로 쌓였다 · CUS_ID 2,000명, 고객당 500건 · 한 고객의 주문은 ORD_SEQ 2,000건 간격으로 나온다
인덱스 IX_ORD_BIG_CUS (CUS_ID): BLEVEL 2 · 리프 약 3,060블록(리프 한 블록에 약 327항목) · CLUSTERING_FACTOR 약 1,000,000 · 이 팝업만 쓴다 · 통계 최신
다른 인덱스: IX_ORD_BIG_DT (ORD_DT) · 주문은 운영에서 계속 INSERT된다

원본 SQL

sql

SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[지난달: 건수만 셀 때]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE                      |                |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                   | IX_ORD_BIG_CUS |      1 |    500 |    500 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------
   2 - access("CUS_ID"=:V_CUS)

[지금: 합계 칸을 더한 뒤]
----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      1 |00:00:00.01 |     504 |
|   1 |  SORT AGGREGATE                      |                |      1 |      1 |      1 |00:00:00.01 |     504 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG      |      1 |    500 |    500 |00:00:00.01 |     504 |
|*  3 |    INDEX RANGE SCAN                  | IX_ORD_BIG_CUS |      1 |    500 |    500 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------

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

문제

같은 인덱스를 타는데 Buffers가 4에서 504로 늘어난 이유를 두 실행계획의 차이와 CLUSTERING_FACTOR로 설명하시오.
팝업이 다시 테이블을 읽지 않도록 인덱스를 고치고 DDL을 작성하시오. 기존 인덱스를 어떻게 할지와 그 대가도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객 번호로 찾고 · 합계 컬럼까지 담는다 → 테이블에 가지 않는다
CREATE INDEX IX_ORD_BIG_CUS_AMT ON T_ORD_BIG (CUS_ID, ORD_AMT) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서 재고 → T2 → VISIBLE

SELECT COUNT(*) AS ord_cnt, SUM(ORD_AMT) AS ord_amt     -- SQL 은 그대로
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus;

-- 새 인덱스를 VISIBLE 로 바꾼 뒤 · 기존 (CUS_ID) 는 접두부라 INVISIBLE → 관찰 → DROP
ALTER INDEX IX_ORD_BIG_CUS INVISIBLE;

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

------------------------------------------------------------------------------------------------
| Id  | Operation          | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                    |      1 |        |      1 |00:00:00.01 |       5 |
|   1 |  SORT AGGREGATE    |                    |      1 |      1 |      1 |00:00:00.01 |       5 |
|*  2 |   INDEX RANGE SCAN | IX_ORD_BIG_CUS_AMT |      1 |    500 |    500 |00:00:00.01 |       5 |
------------------------------------------------------------------------------------------------
   2 - access("CUS_ID"=:V_CUS)

모범 답안 서술

지난달 계획은 2번 줄 INDEX RANGE SCAN 하나로 끝났다. 건수만 세면 인덱스 항목 500개를 세기만 하면 되기 때문이다(Buffers 4). 합계 칸을 더하자 2번 줄에 TABLE ACCESS BY INDEX ROWID BATCHED가 생겼다. ORD_AMT가 인덱스에 없어서 500건마다 ROWID로 테이블을 찾아가야 한다. 그 500번이 500블록이 된 까닭은 CLUSTERING_FACTOR에 있다. T_ORD_BIG은 ORD_SEQ 순으로 쌓였고 한 고객의 주문은 2,000건 간격으로 나온다. 블록 하나에 약 172행이 들어가므로 한 고객의 주문은 행마다 다른 블록에 있다. CF가 행 수와 같은 약 1,000,000인 것이 그 뜻이다. 그래서 Buffers가 4 → 504로 늘었다. 고객 번호 뒤에 ORD_AMT를 붙인 (CUS_ID, ORD_AMT) 인덱스를 만들면 SQL이 쓰는 컬럼이 인덱스에 다 있으므로 테이블에 가지 않는다(Buffers 504 → 5). 하루 6천 번이면 약 302만 → 3만 블록이다. 테이블에 가지 않으니 CF가 나빠도 상관이 없다. 기존 (CUS_ID) 인덱스는 새 인덱스의 접두부라서 하던 일을 새 인덱스가 대신한다. 다른 SQL이 쓰는지 확인한 뒤 INVISIBLE로 바꿔 관찰하고 지운다. 그러면 인덱스 수가 늘지 않으므로 주문 INSERT의 적재 비용도 거의 그대로다. 대가는 이 인덱스가 지금의 컬럼 목록을 전제로 한다는 점이다. 팝업에 칸이 하나 더 늘면(예를 들어 최근 주문일) 테이블 액세스가 다시 돌아온다. T2 승인서에 전제하는 컬럼 목록을 적어 둔다.

해설

함정: 이 팝업에서 문제를 만든 것은 조건이 아니라 SELECT 목록이다. 조건이 같아도 SELECT에 컬럼 하나가 늘면 계획에 테이블 줄이 생긴다(교재 04화 §7의 160 → 41,819와 같은 모양).
COUNT(*)만 셀 때 테이블에 가지 않은 것도 같은 원리다. 인덱스에 행마다 항목이 하나씩 있으니 항목 수가 곧 건수다. CUS_ID가 NOT NULL이라서 가능하다(ACC-006).
다른 정답: 고객별 합계를 미리 계산한 요약 테이블을 두는 방법도 있다. 다만 주문이 들어올 때마다 요약을 고쳐야 하므로, 인덱스 한 컬럼으로 끝나는 이 경우에는 과하다.
(CUS_ID, ORD_DT, ORD_AMT)처럼 날짜를 가운데 넣어도 커버링은 된다. 최근 주문 목록 같은 다른 화면까지 쓸 계획이 있다면 그쪽이 낫다. 다만 리프가 더 커진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황