멤버십 카드 번호로 주문을 찾는 데 한 번에 1.4초, 카드 번호 칸에는 인덱스가 있다

중급DDL버거683 / 700

상황

버거 본사 고객센터와 가맹점주 앱의 「멤버십 적립 내역」은 고객이 불러 주는 카드 번호로 그 카드의 주문을 찾는다. POS 기종마다 카드 번호를 저장하는 모양이 달라서, 작년에 개발팀이 하이픈과 공백을 지우는 함수 FN_NORM_CARD를 만들어 조건에 넣었다. 앱은 입력한 번호에서 숫자만 남겨 :card_no로 넘긴다. 오후 피크에는 이 조회가 한 시간에 1,200번쯤 들어오고, 한 번에 1.4초가 걸리며, AWR의 CPU 1위다. 카드 번호 칸에는 인덱스 ORD_X02가 있는데 쓰이지 않는다.

테이블과 인덱스

ORD(이 문제용으로 멤버십 카드 번호 칸 MBR_CARD_NO VARCHAR2(19)를 더한 판): 1,000,000행 · 22,940블록 · ORD_PK (ORD_NO) · ORD_X01 (ORD_DT, ORD_HMS) · ORD_X02 (MBR_CARD_NO) · 통계 최신
카드를 낸 주문은 230,412건(23%)이고 나머지는 MBR_CARD_NO가 NULL이다 · 카드 31,560장 · 카드 한 장의 주문은 평균 7.3건
POS 기종 셋이 카드 번호를 '1234-5678-9012-3456', '1234 5678 9012 3456', '1234567890123456' 모양으로 저장한다
POS는 영업시간 내내 주문을 넣는다(하루 약 1,370건)

원본 SQL

sql

CREATE OR REPLACE FUNCTION FN_NORM_CARD(p_card VARCHAR2) RETURN VARCHAR2 IS
BEGIN
  RETURN REPLACE(REPLACE(p_card, '-'), ' ');         -- 하이픈과 공백을 지운다
END;
/

-- 멤버십 적립 내역 (:card_no = '1234567890123456')
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO
  FROM ORD
 WHERE FN_NORM_CARD(MBR_CARD_NO) = :card_no
 ORDER BY ORD_DT DESC, ORD_HMS DESC;

실행계획 학습용 예시

xplan

[DISPLAY_CURSOR (ALLSTATS LAST)]
---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      9 |00:00:01.42 |   22940 |          |
|   1 |  SORT ORDER BY     |      |      1 |  10000 |      9 |00:00:01.42 |   22940 | 2048  (0)|
|*  2 |   TABLE ACCESS FULL| ORD  |      1 |  10000 |      9 |00:00:01.42 |   22940 |          |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("DEV"."FN_NORM_CARD"("MBR_CARD_NO")=:CARD_NO)

[V$SQL: 오후 피크 1시간]  EXECUTIONS 1,200 · BUFFER_GETS 27,528,000 · CPU_TIME 1,620 초 · PLSQL_EXEC_TIME 1,320 초
[한 번 평균]              함수 호출 1,000,000번 · CPU 1.35 초 · 경과 1.42 초

문제

카드 번호 칸에 인덱스가 있는데 테이블 전체를 읽는 이유와, 한 번에 1.35초의 CPU가 드는 이유를 설명하시오.
하이픈과 공백을 무시하는 규칙은 그대로 두고 인덱스로 그 카드의 주문만 찾도록 함수와 DDL을 고치시오. 인덱스를 만든 뒤 챙길 것과 그 대가도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 함수: 답이 인자(카드 번호 문자열)만으로 정해진다 → DETERMINISTIC은 참인 약속이다
CREATE OR REPLACE FUNCTION FN_NORM_CARD(p_card VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN REPLACE(REPLACE(p_card, '-'), ' ');
END;
/

-- ② 구분자를 지운 값의 인덱스 (조회 SQL은 그대로 · 조건의 식과 인덱스의 식이 같다)
--    카드가 없는 주문은 함수가 NULL을 돌려주므로 인덱스에 들어가지 않는다 (230,412항목)
--    POS가 주문을 계속 넣으므로 ONLINE으로 만든다
CREATE INDEX ORD_FX01 ON ORD (FN_NORM_CARD(MBR_CARD_NO)) ONLINE;

-- ③ 숨은 컬럼의 통계 (없으면 E-Rows가 기본 선택도 1%인 10,000으로 남는다)
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'ORD', method_opt => 'FOR ALL HIDDEN COLUMNS SIZE 1');

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |          |      1 |        |      9 |00:00:00.01 |      12 |          |
|   1 |  SORT ORDER BY                       |          |      1 |      7 |      9 |00:00:00.01 |      12 | 2048  (0)|
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| ORD      |      1 |      7 |      9 |00:00:00.01 |      12 |          |
|*  3 |    INDEX RANGE SCAN                  | ORD_FX01 |      1 |      7 |      9 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("DEV"."FN_NORM_CARD"("MBR_CARD_NO")=:CARD_NO)

[인덱스]  ORD_FX01 · 230,412항목 · BLEVEL 2 · 리프 약 920블록 (카드가 없는 주문 769,588건은 들어 있지 않다)
[V$SQL: 오후 피크 1시간]  EXECUTIONS 1,200 · BUFFER_GETS 14,400 · CPU_TIME 2 초 · PLSQL_EXEC_TIME 0

모범 답안 서술

ORD_X02에는 POS가 저장한 그대로의 카드 번호, 곧 하이픈이나 공백이 섞인 값이 들어 있다. 그런데 조건은 FN_NORM_CARD(MBR_CARD_NO), 곧 컬럼을 함수로 감싼 값과 비교한다. 구분자를 지운 값은 인덱스 어디에도 없으므로 ORD_X02로 찾지 못하고, Predicate Information 2번 줄처럼 filter가 되어 테이블 전체(22,940블록)를 읽는다. 그러면서 주문 1,000,000건마다 함수를 부른다. 카드가 없는 77%의 주문에도 함수는 불린다. 한 번은 1µs 남짓이라도 100만 번이면 실행 한 번에 PL/SQL 몫이 1.1초이고, 블록을 읽는 몫까지 더해 CPU 1.35초다. 1시간 CPU_TIME 1,620초 중 PLSQL_EXEC_TIME이 1,320초다. E-Rows도 함수 조건의 기본 선택도(1%)로 10,000이 나왔다. 규칙은 그대로 두고 구분자를 지운 값으로 인덱스를 만든다. 함수 기반 인덱스는 함수에 DETERMINISTIC이 있어야 만들 수 있다(없으면 ORA-30553). 이 함수는 답이 인자만으로 정해진다. 테이블이나 시계(SYSDATE), 세션 설정, 패키지 변수를 읽지 않으므로 '같은 입력이면 같은 답'은 참인 약속이고, 붙여도 된다. 인덱스가 계산한 값을 저장해 두므로 조회 SQL은 고치지 않아도 조건의 식과 인덱스의 식이 같아 INDEX RANGE SCAN으로 그 카드의 주문 9건만 찾는다. 카드가 없는 주문은 함수가 NULL을 돌려주어 인덱스에 들어가지 않으므로 인덱스는 230,412항목뿐이다. 함수 호출은 실행마다 1,000,000 → 0번, Buffers는 22,940 → 12, 1시간 CPU는 1,620 → 2초가 된다. 만든 뒤에는 숨은 컬럼의 통계를 모은다(FOR ALL HIDDEN COLUMNS). 함수 기반 인덱스는 숨은 가상 컬럼으로 만들어지는데, 그 컬럼에 통계가 없으면 E-Rows가 기본 선택도 1%인 10,000으로 남아 옵티마이저가 인덱스를 고르지 않을 수 있다. 통계를 모으면 E-Rows는 카드 한 장의 평균인 7이 된다. POS가 영업시간 내내 주문을 넣으므로 인덱스는 ONLINE으로 만든다. 대가도 있다. 인덱스가 계산해 둔 값을 저장하므로, 정규화 규칙(함수)을 고치면 인덱스를 다시 만들어야 한다. 함수를 CREATE OR REPLACE하면 인덱스가 DISABLED가 되고, 그동안 ORD에 주문을 넣는 POS의 INSERT가 ORA-30554로 막힌다. 함수를 고치는 배포는 인덱스 재생성과 묶어 영업시간 밖에 한다. 그리고 주문을 넣을 때마다 함수가 한 번씩 불린다(하루 1,370건 남짓이라 작다). 인덱스를 만들 때는 100만 행마다 함수를 한 번씩 부른다.

해설

PLSQL-003과 무엇이 다른가: 그 함수는 테이블을 읽어서 DETERMINISTIC이 거짓 약속이었고, 함수 기반 인덱스가 옛 분류를 돌려주었다. 이 함수는 인자만 보므로 같은 장치가 맞는다. 붙이기 전에 함수가 무엇을 읽는지부터 본다(교재 15b화).
가상 컬럼도 답이다: MBR_CARD_KEY AS (FN_NORM_CARD(MBR_CARD_NO)) 가상 컬럼에 인덱스를 두면 SQL을 MBR_CARD_KEY = :card_no로 쓸 수 있어 식을 맞출 일이 없다. 함수에 DETERMINISTIC이 필요한 것은 같다.
내장 함수만 쓰는 판: REPLACE(REPLACE(MBR_CARD_NO, '-'), ' ')로 인덱스를 만들고 SQL도 그렇게 쓰면 PL/SQL 호출이 아예 없다. 다만 규칙이 함수와 SQL 두 곳에 생긴다.
바인드 쪽에서 맞추는 길: 저장 모양이 세 가지뿐이므로 앱이 세 모양을 만들어 MBR_CARD_NO IN (:c1, :c2, :c3)로 찾으면 원래 ORD_X02로도 된다. 인덱스를 늘리지 않는 대신, POS가 네 번째 모양을 쓰기 시작하면 그 주문들이 조용히 빠진다. 새 인덱스를 만들었다면 ORD_X02를 다른 화면이 쓰는지 확인한 뒤 정리해 주문 INSERT 비용을 줄인다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황