080 수신거부 전화 한 통마다 연락처 60만 건을 읽고, 어떤 고객은 거부해도 문자가 계속 온다

실전UPDATE한빛유통31 / 700

상황

한빛유통의 080 수신거부 ARS는 고객이 누른 휴대폰 번호로 마케팅 문자 수신 동의를 끈다. ARS 연동 모듈은 누른 숫자를 정수(long)로 바꿔 넘기고, 처리 SQL은 UPDATE 한 문장이다. 하루 3천 통쯤 들어오고 통마다 0.4초가 걸린다. 고객센터에는 "수신거부를 했는데 문자가 또 온다"는 민원이 매주 들어온다. 오과장이 어제 처리 결과를 뽑아 보니 갱신 0건으로 끝난 전화가 81통이었다.

테이블과 인덱스

T_CUS_CTC 고객 연락처(이 문제에서 쓰는 파생 테이블, 2023년 회원 통합 때 신 회원과 구 회원의 연락처를 합쳤다): 600,000행 · 3,760블록 · CTC_SEQ(PK) · CUS_ID · MBL_NO VARCHAR2(20) · SMS_AGR_YN · UPD_DT
MBL_NO는 신 회원 570,000건이 '01012345678' 모양(11자리)이고, 구 회원 이관분 30,000건은 '+821012345678' 모양(국가 번호)이다. 하이픈이 든 값은 지금은 없다
인덱스 PK_T_CUS_CTC (CTC_SEQ) · IX_CUS_CTC_MBL (MBL_NO): BLEVEL 2 · 리프 약 1,880블록 · 통계 최신
회원 가입과 정보 수정 화면이 하루 약 4천 건의 연락처를 넣거나 고친다

원본 SQL

sql

-- ARS 연동 : ps.setLong(1, 1012345678L)   ← 고객이 누른 01012345678
UPDATE T_CUS_CTC
   SET SMS_AGR_YN = 'N', UPD_DT = SYSDATE
 WHERE MBL_NO = :1

실행계획 학습용 예시

xplan

[고객이 01012345678을 눌렀을 때]
--------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                |      1 |        |      0 |00:00:00.38 |    3768 |
|   1 |  UPDATE            | T_CUS_CTC      |      1 |        |      0 |00:00:00.38 |    3768 |
|*  2 |   TABLE ACCESS FULL| T_CUS_CTC      |      1 |   6000 |      2 |00:00:00.38 |    3762 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_NUMBER("MBL_NO")=:1)

[V$SQL_BIND_CAPTURE]   :1   NUMBER   1012345678

[어제 처리 결과 · 3,012통]
  갱신 1건 이상   2,931통
  갱신 0건           81통   ← 79통은 연락처가 '+8210…' 모양인 구 회원, 2통은 등록되지 않은 번호

문제

2번 줄의 TO_NUMBER가 어디서 왔는지, 그리고 구 회원의 수신거부가 갱신 0건으로 끝난 이유를 설명하시오.
① SQL에서 바인드 쪽을 TO_CHAR(:1)로 바꾸는 방법, ② TO_NUMBER(MBL_NO) 함수 기반 인덱스를 만드는 방법, ③ ARS가 누른 번호를 그대로 문자열로 넘기고 저장 형식을 하나로 통일하는 방법을 비교하시오.
채택한 방법을 SQL과 DDL로 작성하고, 반영 순서와 부작용, 재발 방지책을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ③ 채택. 전화번호는 처음부터 끝까지 문자열이다
--   ARS 연동 : ps.setLong(1, 1012345678L) → ps.setString(1, "01012345678")  (누른 그대로)
--   SQL은 그대로 두어도 바인드가 VARCHAR2가 되므로 access("MBL_NO"=:1)로 찾아간다
UPDATE T_CUS_CTC
   SET SMS_AGR_YN = 'N', UPD_DT = SYSDATE
 WHERE MBL_NO = :1;

-- [반영 1] 저장 형식 통일 : 구 회원 이관분 30,000건 '+8210…' → '010…' (업무 외 시간 · 건수를 먼저 센다)
UPDATE T_CUS_CTC
   SET MBL_NO = '0' || SUBSTR(MBL_NO, 4)
 WHERE MBL_NO LIKE '+82%';

-- [반영 2] 다시 섞이지 않게 : 가입 · 수정 화면이 '+82'나 하이픈을 넣으면 여기서 막힌다
ALTER TABLE T_CUS_CTC ADD CONSTRAINT CK_CUS_CTC_MBL
  CHECK (REGEXP_LIKE(MBL_NO, '^0[0-9]{9,10}$'));

-- [반영 3] ARS 연동 모듈 배포 (setString)

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

xplan

[고친 뒤 · 바인드 VARCHAR2 '01012345678']
--------------------------------------------------------------------------------------------
| Id  | Operation          | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | UPDATE STATEMENT   |                |      1 |        |      0 |00:00:00.01 |       9 |
|   1 |  UPDATE            | T_CUS_CTC      |      1 |        |      0 |00:00:00.01 |       9 |
|*  2 |   INDEX RANGE SCAN | IX_CUS_CTC_MBL |      1 |      1 |      2 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("MBL_NO"=:1)

모범 답안 서술

2번 줄의 TO_NUMBER("MBL_NO")는 ARS가 번호를 정수(setLong)로 넘겨서 생겼다. 문자 컬럼과 숫자 바인드를 비교하면 오라클은 문자 쪽, 즉 컬럼 MBL_NO를 숫자로 바꾼다. 컬럼이 가공되어 IX_CUS_CTC_MBL을 쓰지 못하고, 전화 한 통마다 연락처 60만 건을 읽으며 행마다 숫자로 바꾼다(Buffers 3,768). 신 회원 번호 '01012345678'은 숫자로 바꾸면 1012345678이라 바인드와 같아서 우연히 맞는다. 구 회원 번호 '+821012345678'은 숫자로 바꾸면 821012345678이다. 1012345678과 같지 않으니 갱신 0건으로 끝나고, 고객은 수신거부를 했는데도 문자를 받는다. 81통 중 79통이 이 경우다. ① 바인드 쪽 TO_CHAR(:1)은 인덱스를 살리지만, 정수 1012345678을 문자로 바꾸면 '1012345678'이다. 맨 앞 0이 이미 사라졌으므로 모든 번호가 0건이 된다. LPAD(TO_CHAR(:1), 11, '0')로 메워도 11자리 휴대폰만 맞고, 10자리 번호와 '+82' 모양은 여전히 못 찾는다. ② TO_NUMBER(MBL_NO) 함수 기반 인덱스는 SQL을 그대로 둔 채 인덱스를 쓰게 한다. 하지만 INSERT와 UPDATE 때마다 그 식을 계산하므로, 가입 화면이 하이픈 든 번호('010-1234-5678')를 한 건이라도 넣으면 그 INSERT가 ORA-01722로 실패한다. 하루 4천 건의 가입과 정보 수정이 장애 위험을 안게 되고, '+82' 모양을 못 찾는 문제도 그대로다. ③을 채택한다. 전화번호는 계산하는 값이 아니라 글자이므로 처음부터 끝까지 문자열로 다룬다. ARS가 누른 그대로 setString으로 넘기면 SQL은 그대로여도 access("MBL_NO"=:1)로 찾아간다(Buffers 3,768 → 9). 반영 순서는 이렇다. 먼저 구 회원 3만 건을 '010…' 모양으로 통일하고(건수를 먼저 세고 업무 외 시간에), 다음에 CHECK 제약을 걸어 다른 모양이 다시 들어오지 않게 막고, 그 뒤에 ARS 모듈을 배포한다. 부작용은 3만 건을 갱신하면서 인덱스도 3만 항목을 고친다는 것과, 가입과 수정 화면이 다른 모양을 넣으면 이제 제약 위반으로 실패한다는 것이다. 그래서 화면 쪽 입력 검사를 함께 고친다. 연락처를 받아 가는 문자 발송 시스템이 '+82' 모양을 기대하는지도 확인한다.

해설

숫자처럼 생긴 문자: 전화번호, 우편번호, 사업자번호, 카드번호는 숫자로 이루어졌어도 계산하지 않는 값이라 문자로 둔다. 숫자 타입으로 다루는 순간 맨 앞의 0이 사라지고, 비교할 때마다 컬럼 쪽 형변환이 생긴다.
급할 때의 임시 처방: ARS 배포까지 며칠이 걸린다면 WHERE MBL_NO IN (LPAD(TO_CHAR(:1), 11, '0'), '+82' || TO_CHAR(:1))처럼 바인드 쪽에서 두 모양을 만들어 찾을 수 있다. 인덱스는 살지만 번호 모양 규칙을 SQL에 박아 두는 셈이라 형식 통일이 끝나면 지운다.
함수 기반 인덱스의 ORA-01722 위험은 조회가 아니라 쓰기 쪽에서 터진다. 조회 성능을 고치려다 가입 장애를 만들 수 있으므로, 함수 기반 인덱스를 만들 때는 그 식이 앞으로 들어올 모든 값에서 성공하는지 먼저 따진다.
CHECK 제약을 거는 ALTER TABLE은 기존 60만 행을 모두 검사한다. 형식 통일이 덜 끝났으면 제약 생성이 실패하므로, 반영 1의 결과를 세어 본 뒤에 건다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황