택배 기사가 수령인 번호 뒷자리 네 개를 불러 줄 때마다 주문 테이블을 다 읽는다

실전DDL한빛유통45 / 700

상황

한빛유통 고객센터에는 택배 기사와 수령인의 배송 문의가 하루 2천 통쯤 온다. 송장에는 수령인 휴대폰 번호가 가운데 네 자리를 가린 채(010-****-5678) 찍혀 있어서, 상담원은 뒷자리 네 개와 대략의 주문일로 주문을 찾는다. 화면 SQL은 앞에 %를 붙인 LIKE다. 박팀장은 대안을 넷 놓고 비교해 오라고 했다. DBA는 역방향 키 인덱스(REVERSE 옵션)를 만들면 뒤에서부터 찾을 수 있다고 했고, 개발팀은 검색 엔진을 들이자고 한다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 1,040블록 · PK_T_ORD (ORD_SEQ) · ORD_DT DATE(시분초 있음) · 한 달 약 4,110건(테이블 곳곳에 흩어져 있다)
RCV_MBL_NO VARCHAR2(11): 수령인 휴대폰 '01012345678'(하이픈 없음, 모든 주문에 값이 있다) · 뒷자리 네 개는 0000 ~ 9999에 고르게 퍼져 있다 (문제 설정: 2024.12 배송 개편 때 더한 컬럼이다. 컬럼이 늘어 테이블이 896 → 1,040블록이 되었다)
인덱스 IX_T_ORD_DT (ORD_DT): BLEVEL 1 · 리프 265 · 통계 최신
주문을 넣거나 배송지를 고치는 일이 하루 약 3천 건

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, RCV_MBL_NO
  FROM T_ORD
 WHERE RCV_MBL_NO LIKE '%' || :v_last4
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')

실행계획 학습용 예시

xplan

[뒷자리 '5678' · 최근 30일]
--------------------------------------------------------------------------------------
| Id  | Operation         | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |      1 |        |      1 |00:00:00.02 |    1040 |
|*  1 |  TABLE ACCESS FULL| T_ORD |      1 |    206 |      1 |00:00:00.02 |    1040 |
--------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(("ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "RCV_MBL_NO" LIKE '%'||:V_LAST4))

문제

원본이 IX_T_ORD_DT도 쓰지 않고 테이블 전체를 읽는 이유를 설명하시오.
뒷자리 검색을 받치는 방법 넷, ① 뒷자리 네 개를 담은 가상 컬럼과 인덱스 ② REVERSE 함수로 만든 함수 기반 인덱스 ③ DBA가 말한 역방향 키 인덱스 ④ 전문 검색(Oracle Text나 외부 검색 엔진)을 비교하고 하나를 고르시오.
고른 방법의 DDL과 SQL을 작성하고, 대가와 반영 전에 확인할 것을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 채택 : 뒷자리 네 개를 가상 컬럼으로 두고, 주문일까지 담은 인덱스로 찾아간다
ALTER TABLE T_ORD ADD (
  RCV_MBL_L4 VARCHAR2(4) GENERATED ALWAYS AS (SUBSTR(RCV_MBL_NO, -4)) VIRTUAL
);
CREATE INDEX IX_T_ORD_RCV_L4 ON T_ORD (RCV_MBL_L4, ORD_DT);
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD', METHOD_OPT => 'FOR COLUMNS RCV_MBL_L4 SIZE 1');

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, RCV_MBL_NO
  FROM T_ORD
 WHERE RCV_MBL_L4 = :v_last4
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD');

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

xplan

------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD           |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN                  | IX_T_ORD_RCV_L4 |      1 |      1 |      1 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("RCV_MBL_L4"=:V_LAST4 AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD'))

모범 답안 서술

1번 줄의 조건은 둘 다 filter다. RCV_MBL_NO는 앞이 %라서 정렬 위에서 시작점을 정할 수 없고, 인덱스도 없다. ORD_DT에는 인덱스가 있지만 30일 치 4,110건은 테이블 곳곳에 흩어져 있어, 날짜 인덱스로 찾아가면 테이블 블록을 4천 번 넘게 한 블록씩 읽어야 한다. FULL(1,040블록)이 싸므로 옵티마이저는 FULL을 골랐다. 그 4,110건 가운데 뒷자리가 맞는 주문은 1건이다. 네 방법을 비교한다. ① 가상 컬럼: SUBSTR(RCV_MBL_NO, -4)를 가상 컬럼 RCV_MBL_L4로 두고 (RCV_MBL_L4, ORD_DT) 인덱스를 만들면, 뒷자리가 앞에서부터 정렬되므로 뒷자리 검색이 등치 조건이 되고 ORD_DT 범위까지 access가 된다(Buffers 1,040 → 4). 문서에 있는 기능이고, SQL에는 컬럼 이름만 쓰면 된다. ② REVERSE 함수 기반 인덱스: 번호를 뒤집어 정렬하면 REVERSE(RCV_MBL_NO) LIKE REVERSE(:v_last4) || '%'가 전방 일치가 되어 뒷자리 길이가 바뀌어도 쓸 수 있다. 하지만 REVERSE는 문서에 없는 함수라 지원을 기대하기 어렵고, SQL이 인덱스와 똑같은 식을 써야 하며, LIKE가 범위 조건이라 뒤 컬럼 ORD_DT는 access가 되지 못한다. ③ 역방향 키 인덱스: 키의 바이트를 뒤집어 저장해, 번호가 하나씩 늘어나는 키의 삽입이 맨 오른쪽 블록에 몰리는 경합을 흩는 용도다. 이웃한 값이 흩어지므로 = 검색만 찾아가고 범위나 LIKE는 찾아가지 못한다. 뒷자리 검색에는 쓸 수 없다. ④ 전문 검색: 글 속의 단어를 찾는 도구라 숫자 네 자리에는 과하다. Oracle Text는 동기화가 커밋보다 늦을 수 있고 텍스트 인덱스 테이블과 정기 최적화가 따로 들며, 외부 검색 엔진은 연계와 운영 부담이 더 크다. ①을 고른다. 대가는 10만 행짜리 인덱스 하나다. 하루 약 3천 건씩 주문을 넣거나 배송지를 고칠 때마다 이 인덱스도 고친다. 가상 컬럼은 테이블에 보이는 컬럼이라 컬럼 목록 없이 INSERT INTO T_ORD VALUES (…)를 쓰는 프로그램은 깨진다(교재 03화 §4). 반영 전에 그런 INSERT가 없는지 소스를 찾고, 가상 컬럼의 통계를 모은 뒤, 인덱스를 INVISIBLE로 만들어 세션에서 재 보고 바꾼다.

해설

함정: 역방향 키 인덱스는 이름 때문에 뒷자리 검색용으로 오해받는다. 교재 14화 §2.4의 실측에서 역순 키 인덱스는 BETWEEN조차 찾아가지 못하고 인덱스 전체(2,230블록)를 훑었다.
수령인 번호를 암호화해 저장해야 한다면(개인정보 안전성 확보 조치) 원문 LIKE도 가상 컬럼도 쓸 수 없다. 그럴 때는 뒷자리 네 개만 따로 평문 컬럼으로 적재해 두는 설계가 흔하다. 뒷자리 네 개만 평문으로 둘 수 있는지는 보안 담당과 정한다.
뒷자리가 같은 주문은 30일에 0 ~ 2건이다. 기간을 1년으로 넓혀도 (RCV_MBL_L4, ORD_DT)가 범위를 지키므로 약 15건만 읽는다.
요구도 함께 본다. 송장 번호를 받을 수 있는 문의라면 송장 번호의 등치 검색으로 끝난다. 화면은 송장 번호 칸을 먼저 두고, 뒷자리 검색은 그다음 수단으로 둔다(IDX-003의 요구 재협상).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황