고객번호 열 자리를 끝까지 쳐도 상담 화면은 고객 전체를 찾는 줄 안다

중급SELECT한빛유통39 / 700

상황

고객센터의 「고객 주문 이력」은 상담원이 고객번호를 치면 그 고객의 석 달 치 주문을 최신순으로 보여 준다. 상담원 40명이 하루 9천 번쯤 쓴다. 앞부분만 쳐도 찾게 하려고 화면은 LIKE :v_cus || '%'로 넘기지만, 상담원은 대부분 고객번호 열 자리를 다 친다. 조민우가 (CUS_ID, ORD_DT) 인덱스를 만들었는데도 FULL이라 INDEX 힌트를 붙여 봤다. 인덱스는 탔지만 거의 그대로 느리다. 아래는 'CUS_001234'의 2024-10-01 이후 주문을 찾았을 때다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · CUS_ID VARCHAR2(40) 'CUS_000001' ~ 'CUS_002000'(모두 CUS_로 시작, 고객당 500건) · ORD_DT DATE(전부 자정, 2022-01-01 ~ 2024-12-30)
고객 한 명의 2024-10-01 이후 주문은 42건이고, 테이블 곳곳의 서로 다른 블록에 있다
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): BLEVEL 2 · 리프 3,640 · 통계 최신

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.CUS_ID LIKE :v_cus || '%'
   AND o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
 ORDER BY o.ORD_DT DESC

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

xplan

[원본]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |      1 |        |     42 |00:00:00.07 |    5826 |
|   1 |  SORT ORDER BY                      |                   |      1 |  83180 |     42 |00:00:00.07 |    5826 |
|*  2 |   TABLE ACCESS FULL                 | T_ORD_BIG         |      1 |  83180 |     42 |00:00:00.07 |    5826 |
------------------------------------------------------------------------------------------------------------
   2 - filter(("O"."CUS_ID" LIKE :V_CUS||'%' AND "O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD')))

[조민우의 시도 · /*+ INDEX(o IX_ORD_BIG_CUS_DT) */]
------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                   |      1 |        |     42 |00:00:00.05 |    3685 |
|   1 |  SORT ORDER BY                      |                   |      1 |  83180 |     42 |00:00:00.05 |    3685 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |  83180 |     42 |00:00:00.05 |    3685 |
|*  3 |    INDEX RANGE SCAN                 | IX_ORD_BIG_CUS_DT |      1 |  83180 |     42 |00:00:00.05 |    3643 |
------------------------------------------------------------------------------------------------------------
   3 - access("O"."CUS_ID" LIKE :V_CUS||'%' AND "O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD'))
       filter(("O"."CUS_ID" LIKE :V_CUS||'%' AND "O"."ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD')))

[V$SQL_BIND_CAPTURE]   :V_CUS   VARCHAR2   'CUS_001234'

문제

고객번호를 정확히 넣었는데도 E-Rows가 83,180이고, 힌트로 인덱스를 태워도 리프 3,643블록을 읽는 이유를 설명하시오.
상담원이 번호 전체를 칠 때와 앞부분만 칠 때 모두 그 고객의 범위만 읽도록 SQL을 고치시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 번호를 끝까지 쳤을 때 (화면에서 열 자리를 다 받으면 이 SQL 로 보낸다)
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :v_cus
   AND o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
 ORDER BY o.ORD_DT DESC;

-- ② 앞부분만 쳤을 때 : 입력값의 \ _ % 앞에 이스케이프 문자를 넣어 글자 그대로 비교한다
SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM T_ORD_BIG o
 WHERE o.CUS_ID LIKE REPLACE(REPLACE(REPLACE(:v_cus, '\', '\\'), '_', '\_'), '%', '\%') || '%' ESCAPE '\'
   AND o.ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
 ORDER BY o.ORD_DT DESC;

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

xplan

[① · 'CUS_001234']
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |     42 |00:00:00.01 |      46 |
|   1 |  TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |     42 |     42 |00:00:00.01 |      46 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_CUS_DT |      1 |     42 |     42 |00:00:00.01 |       4 |
---------------------------------------------------------------------------------------------------------

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

모범 답안 서술

LIKE의 와일드카드는 %만이 아니다. _는 아무 글자 한 개를 뜻한다. 'CUS_001234%'에서 다섯째 글자 앞의 가 첫 와일드카드이므로, 정렬 위에서 정할 수 있는 범위는 그 앞의 'CUS'까지다. 고객번호는 모두 'CUS'로 시작하므로 'CUS'로 시작하는 범위는 인덱스 전체다. 조민우의 시도에서 3번 줄은 INDEX RANGE SCAN이라고 나오지만 실제로는 리프 3,640블록을 처음부터 끝까지 읽으며 패턴과 날짜를 맞춰 본다(Buffers 3,643). LIKE가 범위 조건이라 뒤 컬럼 ORD_DT도 범위를 줄이지 못하고 filter로만 쓰인다. 옵티마이저도 같은 규칙으로 선택도를 계산한다. 엿본 바인드 'CUS_001234'를 'CUS'로 시작하는 모든 값으로 보고, 석 달 치 전체(약 8만 3천 건)가 걸린다고 E-Rows를 83,180으로 잡았다. 그 추정이면 FULL(5,826블록)이 싸 보이므로 원본은 FULL이 되었다. 실제로는 42건이다. 상담원이 번호를 끝까지 치면 CUS_ID = :v_cus로 보낸다. 등치 조건이라 다음 컬럼 ORD_DT까지 access가 되어 그 고객의 석 달 치 42건만 찾아가고, 인덱스를 역순으로 읽으므로 SORT ORDER BY도 사라진다(Buffers 5,826 → 46). 앞부분만 칠 때는 입력값의 \, _, % 앞에 이스케이프 문자를 넣고 ESCAPE '\'를 붙인다. 그러면 'CUS_0012%'의 범위가 'CUS_0012'로 시작하는 고객 100명으로 좁혀진다.

해설

함정: LIKE :v_cus || '%' ESCAPE '\'처럼 ESCAPE 절만 붙이면 아무것도 바뀌지 않는다. 입력값 안의 _ 앞에 이스케이프 문자가 실제로 들어가야 그 _가 글자로 비교된다.
결과도 틀릴 수 있다. 'CUS_0012'로 찾으면 'CUSX0012…'나 'CUS-0012…'도 걸린다. 지금 데이터에는 그런 값이 없어서 결과가 같을 뿐이다.
_나 %가 든 코드 체계(CUS_, ITEM_)는 LIKE 검색에서 늘 이 함정이 있다. 첫 와일드카드 앞까지만 범위가 된다는 규칙은 %와 같다(IDX-003, IDX-007).
앞부분 검색은 짧게 칠수록 후보가 많아진다. 'CUS_00'이면 고객 99명, 주문 4천 건이 넘는다. 최소 자릿수를 정해 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

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

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황