전화번호 뒷자리로 최근 주문 20건을 찾는 상담 화면이 한 번에 7만 블록을 읽는다

중급SELECT한빛유통618 / 700

상황

상담팀의 「전화번호 뒷자리 조회」는 고객이 휴대전화 번호 뒤 네 자리를 불러 주면 그 고객의 최근 주문 20건을 띄운다. 상담원 40명이 하루 약 3,000번 쓴다. 0.2초씩 걸려 통화 중에 화면이 늦게 뜬다고 한다. 개발자는 번호 뒷자리를 MBL_NO LIKE '%' || :tail로 찾게 썼다. 아래 계획은 오늘 아침 첫 조회('1234')로 만들어진 커서다.

테이블과 인덱스

M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · MBL_NO '010' + 여덟 자리 · 뒤 네 자리가 같은 고객은 지금 없다
T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객당 주문 500건이 날짜마다 흩어져 있다(하루 913건 가운데 한 고객은 두세 건)
인덱스 IX_ORD_BIG_DT (ORD_DT): BLEVEL 2 · 리프 약 2,900 / IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): BLEVEL 2 · 리프 약 3,400 / 두 인덱스 모두 CF가 100만에 가깝다
통계 최신 · 히스토그램 없음

원본 SQL

sql

SELECT *
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT, c.CUS_NM
          FROM T_ORD_BIG o, M_CUS c
         WHERE c.MBL_NO LIKE '%' || :tail
           AND o.CUS_ID = c.CUS_ID
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |               |      1 |        |     20 |00:00:00.21 |   77171 |
|*  1 |  COUNT STOPKEY                  |               |      1 |        |     20 |00:00:00.21 |   77171 |
|   2 |   VIEW                          |               |      1 |     20 |     20 |00:00:00.21 |   77171 |
|   3 |    NESTED LOOPS                 |               |      1 |     20 |     20 |00:00:00.21 |   77171 |
|   4 |     NESTED LOOPS                |               |      1 |    400 |  38129 |00:00:00.12 |   39042 |
|   5 |      TABLE ACCESS BY INDEX ROWID| T_ORD_BIG     |      1 |   1000K|  38129 |00:00:00.09 |   38242 |
|   6 |       INDEX FULL SCAN DESCENDING| IX_ORD_BIG_DT |      1 |    400 |  38129 |00:00:00.01 |     113 |
|*  7 |      INDEX UNIQUE SCAN          | PK_M_CUS      |  38129 |      1 |  38129 |00:00:00.02 |     800 |
|*  8 |     TABLE ACCESS BY INDEX ROWID | M_CUS         |  38129 |      1 |     20 |00:00:00.06 |   38129 |
-----------------------------------------------------------------------------------------------------

Peeked Binds (identified by position):
--------------------------------------
   1 - :TAIL (VARCHAR2(32), CSID=873): '1234'

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   7 - access("O"."CUS_ID"="C"."CUS_ID")
   8 - filter("C"."MBL_NO" LIKE '%'||:TAIL)

문제

옵티마이저가 날짜 인덱스를 거꾸로 읽는 계획을 고른 이유를 MBL_NO 조건의 추정으로 설명하고, 실제로는 왜 38,129건을 읽고서야 멈췄는지 쓰시오.
화면은 그대로 네 자리를 받는다는 조건에서 추정이 맞도록 SQL과 통계를 고치시오. 다른 방법 하나와 그 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 뒤 네 자리를 등치 조건으로 묻는다 + 그 식에 통계를 준다 (숨은 가상 열 · 테이블 모양은 그대로)
SELECT DBMS_STATS.CREATE_EXTENDED_STATS(USER, 'M_CUS', '(SUBSTR(MBL_NO, -4))') FROM dual;
BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'M_CUS', no_invalidate => FALSE);   -- 식의 NDV 2,000
END;
/

SELECT *
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT, c.CUS_NM
          FROM T_ORD_BIG o, M_CUS c
         WHERE SUBSTR(c.MBL_NO, -4) = :tail
           AND o.CUS_ID = c.CUS_ID
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 20;

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |      1 |        |     20 |00:00:00.01 |     535 |          |
|*  1 |  COUNT STOPKEY                 |                   |      1 |        |     20 |00:00:00.01 |     535 |          |
|   2 |   VIEW                         |                   |      1 |    500 |     20 |00:00:00.01 |     535 |          |
|*  3 |    SORT ORDER BY STOPKEY       |                   |      1 |    500 |     20 |00:00:00.01 |     535 | 2048  (0)|
|   4 |     NESTED LOOPS               |                   |      1 |    500 |    500 |00:00:00.01 |     535 |          |
|   5 |      NESTED LOOPS              |                   |      1 |    500 |    500 |00:00:00.01 |      35 |          |
|*  6 |       TABLE ACCESS FULL        | M_CUS             |      1 |      1 |      1 |00:00:00.01 |      30 |          |
|*  7 |       INDEX RANGE SCAN         | IX_ORD_BIG_CUS_DT |      1 |    500 |    500 |00:00:00.01 |       5 |          |
|   8 |      TABLE ACCESS BY INDEX ROWID| T_ORD_BIG        |    500 |      1 |    500 |00:00:00.01 |     500 |          |
------------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   6 - filter(SUBSTR("C"."MBL_NO",-4)=:TAIL)
   7 - access("O"."CUS_ID"="C"."CUS_ID")

모범 답안 서술

MBL_NO LIKE '%' || :tail처럼 앞이 %인 LIKE는 통계로 계산할 방법이 없어서, 오라클은 입력값과 상관없이 고정 선택도 5%를 쓴다. 고객 2,000명 가운데 100명이 맞고, 그 고객들의 주문은 100 × 500 = 5만 건이라고 본 것이다. 주문 100만 건 가운데 5%가 맞는다면 날짜 인덱스를 최근부터 거꾸로 읽으면서 고객을 대어 보다가 400건쯤 읽으면 20건이 찬다. 고객 100명의 주문 5만 건을 다 모아 정렬하는 것보다 싸므로 6번 줄의 INDEX FULL SCAN DESCENDING을 골랐다. 실제로 뒤 네 자리가 1234인 고객은 한 명뿐이다. 그 고객의 주문은 하루 913건 가운데 두세 건, 그것도 닷새에 하루꼴이라 날짜를 42일 거슬러 38,129건을 읽고서야 20건이 찼다. 읽은 주문마다 T_ORD_BIG 테이블과 M_CUS를 한 번씩 찾아가서(7번과 8번 줄 Starts 38,129) 77,171블록을 읽었다. 추정이 실제보다 100배 컸고, 그 숫자로 '금방 멈출 것'에 걸었다가 진 셈이다. 화면은 그대로 네 자리를 받되, 조건을 뒤 네 자리 등치(SUBSTR(c.MBL_NO, -4) = :tail)로 바꾸고 그 식에 표현식 통계를 만든다. 식의 NDV가 2,000이라 E-Rows가 1이 되고, 옵티마이저는 고객 한 명을 먼저 찾은 뒤 IX_ORD_BIG_CUS_DT로 그 고객의 주문 500건만 읽어 정렬한다. Buffers는 77,171 → 535다. 테이블에 보이는 열은 늘지 않는다. 다른 방법도 있다. ① 가상 열 MBL_TAIL4를 두고 등치로 물으면 같은 효과에 인덱스도 걸 수 있지만, 테이블에 열이 보이게 늘어 컬럼 목록 없이 값만 쓰는 INSERT가 깨진다. ② /*+ DYNAMIC_SAMPLING(c 3) */는 SQL을 거의 그대로 두고 30블록뿐인 고객 테이블을 통째로 세게 하지만, 하드 파싱마다 표본을 뜨고 엿본 첫 값으로 계획이 정해진다. ③ LEADING(c o) USE_NL(o) 힌트는 지금은 맞지만 판단을 굳힌다. 표현식 통계도 식이 글자까지 같은 SQL에만 쓰이므로, 같은 조회를 하는 다른 화면에는 같은 모양을 쓰도록 코딩 표준에 적는다.

해설

추정이 틀렸을 때 가장 크게 다치는 곳이 STOPKEY다. '조건에 맞는 행이 흔하다'고 잘못 보면 정렬된 인덱스를 따라 읽다 금방 멈출 것으로 계산하는데, 실제로 드물면 끝없이 읽는다(교재 09화 부분범위 처리).
이 화면의 가장 정직한 고침은 요구다. 상담원이 고객 번호나 전체 전화번호로 찾을 수 있다면 PK_M_CUS나 MBL_NO 등치로 끝난다. 네 자리 검색이 꼭 필요한지 먼저 묻는다.
함정: LIKE :tail(앞의 % 없이)로 바꾸고 화면이 '%1234'를 넘기게 해도 추정은 같다. 엿본 값이 %로 시작하면 다시 5%다.
뒤 네 자리가 같은 고객이 많아지면(고객 수만 명) 식의 NDV가 1만에 묶여 한 번에 여러 명이 나온다. 그때는 이름이나 생년월일을 함께 받는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황