「없는 쿠폰」 장애를 고친 뒤로 결제 화면이 쿠폰 한 장마다 멈칫한다

중급SELECT한빛유통25 / 700

상황

한빛유통은 2023년부터 새 쿠폰에 12자리 번호를 붙인다. 쿠폰 테이블은 그 전 프로모션 시스템에서 옮겨 온 것이라 번호 컬럼이 CHAR(16)이다. 새 결제 앱을 내놓은 날, 12자리 쿠폰이 전부 「없는 쿠폰」으로 나왔다. 그런데 SQL Developer에서 WHERE CPN_NO = 'K7Q2M9X4P1R8'로 치면 쿠폰이 나온다. 조민우가 급히 조건을 TRIM(CPN_NO) = :v_cpn_no로 바꿔 장애를 닫았다. 그 뒤로 결제 한 건이 쿠폰을 확인할 때마다 0.1초 가까이 걸린다. 결제는 하루 3만 건이다.

테이블과 인덱스

T_CPN 발급 쿠폰(이 문제에서 쓰는 파생 테이블, 구 프로모션 시스템에서 이관): 800,000행 · 6,020블록 · CPN_NO CHAR(16) · CUS_ID · ISU_DT · EXP_DT · USE_YN · DC_AMT
CPN_NO는 구 번호 16자리 420,000건과 2023년 이후 12자리 380,000건이다. 12자리 번호는 CHAR라서 뒤에 공백 4칸이 채워져 저장된다
인덱스 PK_T_CPN (CPN_NO): BLEVEL 2 · 리프 약 3,330블록 · 통계 최신
결제 앱은 쿠폰 번호를 문자열 바인드(VARCHAR2)로 넘긴다

원본 SQL

sql

-- [장애 때] WHERE CPN_NO = :v_cpn_no   → 12자리 쿠폰이 0건
-- [핫픽스]  조민우
SELECT CPN_NO, CUS_ID, EXP_DT, USE_YN, DC_AMT
  FROM T_CPN
 WHERE TRIM(CPN_NO) = :v_cpn_no

실행계획 학습용 예시

xplan

[장애 때 · CPN_NO = :v_cpn_no · 'K7Q2M9X4P1R8']
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      0 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_CPN    |      1 |      1 |      0 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_CPN |      1 |      1 |      0 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CPN_NO"=:V_CPN_NO)

[핫픽스 · TRIM(CPN_NO) = :v_cpn_no · 같은 번호]
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.09 |    6024 |
|*  1 |  TABLE ACCESS FULL          | T_CPN    |      1 |   8000 |      1 |00:00:00.09 |    6024 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TRIM("CPN_NO")=:V_CPN_NO)

문제

장애 때 계획은 PK로 정확히 찾아갔는데도 0건이었다. SQL Developer의 리터럴과 앱의 바인드가 왜 다른 결과를 냈는지 CHAR 비교 규칙으로 설명하시오.
핫픽스가 느린 이유를 설명하고, 컬럼을 감싸지 않으면서 12자리와 16자리 쿠폰을 모두 찾도록 SQL을 고치시오.
근본 대책과 그 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 공백을 지우는 일을 컬럼 쪽에서 하지 않고, 바인드 쪽을 CHAR(16) 모양(뒤 공백 채움)으로 맞춘다
--   12자리 → 'K7Q2M9X4P1R8    ' · 16자리 → 그대로 · RPAD는 16자를 넘는 값을 자르므로 앱이 길이를 먼저 검사한다
SELECT CPN_NO, CUS_ID, EXP_DT, USE_YN, DC_AMT
  FROM T_CPN
 WHERE CPN_NO = RPAD(:v_cpn_no, 16)

-- (근본 대책 · 승인 사안) 컬럼을 VARCHAR2로 바꾸고 채워진 공백을 지운다
-- ALTER TABLE T_CPN MODIFY (CPN_NO VARCHAR2(16));
-- UPDATE T_CPN SET CPN_NO = RTRIM(CPN_NO) WHERE LENGTH(RTRIM(CPN_NO)) < 16;

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

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| T_CPN    |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_CPN |      1 |      1 |      1 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CPN_NO"=RPAD(:V_CPN_NO,16))

모범 답안 서술

CPN_NO는 CHAR(16)이라 12자리 번호 'K7Q2M9X4P1R8'은 뒤에 공백 4칸이 붙은 16글자로 저장되어 있다. 오라클은 비교하는 두 값이 모두 CHAR나 문자 리터럴일 때만 짧은 쪽에 공백을 채워 비교한다(blank-padded). SQL Developer에서 친 'K7Q2M9X4P1R8'은 문자 리터럴이라 공백이 채워져 같다고 판정됐다. 앱의 바인드는 VARCHAR2라서 공백을 채우지 않고 그대로 비교한다(nonpadded). 그래서 16글자와 12글자는 같지 않고, PK로 정확히 찾아가고도 0건이었다. 핫픽스는 컬럼을 TRIM으로 감쌌다. 답은 맞지만 컬럼 가공이라 PK_T_CPN에서 찾아갈 자리가 없어 80만 행을 다 읽는다(Buffers 6,024). 결제 한 건마다 이 일이 일어난다. 공백을 맞추는 일은 바인드 쪽에서 한다. CPN_NO = RPAD(:v_cpn_no, 16)이면 12자리 입력은 컬럼과 같은 16글자가 되고, 16자리 입력은 그대로다. 컬럼은 맨몸이라 PK로 한 건을 찾는다(Buffers 6,024 → 4). CAST(:v_cpn_no AS CHAR(16))로 CHAR끼리 비교하게 해도 된다. RPAD는 16자를 넘는 값을 잘라 버리므로, 앱에서 길이를 먼저 검사한다. 근본 대책은 컬럼을 VARCHAR2(16)으로 바꾸고 채워진 공백을 RTRIM으로 지우는 것이다. 그러면 바인드든 리터럴이든 같은 결과가 나온다. 대가로 38만 건을 갱신해야 하고(PK도 함께 고친다), 이 컬럼을 CHAR 비교에 기대어 쓰던 SQL과 배치를 모두 찾아 점검해야 한다. 그래서 승인 사안으로 올린다.

해설

CHAR 비교 규칙: 두 값이 모두 CHAR(또는 문자 리터럴)이면 공백을 채워 비교하고, 한쪽이라도 VARCHAR2면 채우지 않고 비교한다. 같은 SQL이 도구에서는 나오고 앱에서는 안 나오는 대표적인 경우다.
RTRIM(CPN_NO)나 TRIM(CPN_NO) 함수 기반 인덱스도 속도는 살린다. 하지만 인덱스가 하나 더 늘고, SQL 모양이 인덱스 식과 같아야만 쓰인다. 바인드 쪽 RPAD가 더 싸다.
JDBC라면 오라클 드라이버의 setFixedCHAR로 CHAR 타입 바인드를 보낼 수도 있다. 다만 드라이버 전용 API라 코드가 드라이버에 묶인다.
새로 만드는 테이블에는 길이가 바뀌는 코드에 CHAR를 쓰지 않는다. CHAR는 길이가 늘 같은 값('Y', 'N' 같은 플래그)에만 쓴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황