고객센터 주문번호 찾기용 인덱스 하나를 더 지었을 뿐인데 야간 재적재가 5초에서 22초가 됐다

중급DDL한빛유통474 / 700

상황

한빛유통 고객센터는 고객이 불러 주는 주문번호(HB-20240615-0012345 모양)로 주문을 찾는다. 주문번호는 저장된 칸이 아니라 주문일자와 주문 순번을 이어 붙인 표기다. 지난달 개발팀이 표기 함수 FN_ORD_NO를 만들고 그 함수로 함수 기반 인덱스를 만들었다. 체인 ①(T_ORD_BIG 재적재)은 14화 이후 인덱스를 끄고 직접 경로로 넣은 뒤 다시 짓는데, 새 인덱스도 그 순서에 넣었다. 그 뒤로 체인 ①이 5.0초에서 21.9초가 됐다. 오과장: "인덱스 하나를 더 지었을 뿐인데 다른 두 인덱스를 합친 것의 세 배가 넘게 걸려요."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_SEQ 1 ~ 1,000,000(유일) · ORD_DT 전부 자정 · 체인 ①이 끝난 뒤에만 화면이 읽고, 낮에는 고치는 일이 없다.
인덱스: IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT) · IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ) · 지난달 만든 IX_ORD_BIG_ORDNO_FX (FN_ORD_NO(ORD_DT, ORD_SEQ))
FN_ORD_NO: PL/SQL 함수 · DETERMINISTIC · 테이블을 읽지 않고 'HB-' || 주문일자(YYYYMMDD) || '-' || 순번 7자리를 돌려준다.
고객센터 「주문번호로 찾기」는 하루 약 2,400번이다. 주문번호 형식은 바뀐 적이 없고, 화면이 형식을 먼저 검사한다.
체인 ①은 일요일 22:00에 시작해 04:00까지 끝나야 한다.

원본 SQL

sql

-- 지난달 개발팀
CREATE OR REPLACE FUNCTION FN_ORD_NO(p_dt DATE, p_seq NUMBER)
RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
  RETURN 'HB-' || TO_CHAR(p_dt, 'YYYYMMDD') || '-' || LPAD(p_seq, 7, '0');
END;

CREATE INDEX IX_ORD_BIG_ORDNO_FX ON T_ORD_BIG (FN_ORD_NO(ORD_DT, ORD_SEQ));

-- 고객센터 「주문번호로 찾기」
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE FN_ORD_NO(ORD_DT, ORD_SEQ) = :v_ord_no;

-- 체인 ① 끝부분 (14화 이후 순서에 한 줄을 더했다)
ALTER INDEX IX_ORD_BIG_DT_AMT REBUILD;
ALTER INDEX IX_ORD_BIG_DT_SEQ REBUILD;
ALTER INDEX IX_ORD_BIG_ORDNO_FX REBUILD;

실행계획 학습용 예시 · 함수 호출 수는 시험용 패키지 카운터로 센 값

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_ORD_BIG           |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IX_ORD_BIG_ORDNO_FX |      1 |      1 |      1 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SQLP"."FN_ORD_NO"("ORD_DT","ORD_SEQ")=:V_ORD_NO)

[체인 ① 단계별 — 연습 DB 복사본]
  단계                                    경과       CPU      PL/SQL 함수 호출
  TRUNCATE · UNUSABLE · APPEND 적재       0.3 초     0.2 초                0
  REBUILD IX_ORD_BIG_DT_AMT               2.4 초     2.1 초                0
  REBUILD IX_ORD_BIG_DT_SEQ               2.3 초     2.0 초                0
  REBUILD IX_ORD_BIG_ORDNO_FX            16.9 초    16.2 초        1,000,000
  합계                                   21.9 초                 (지난달 5.0 초)

[REBUILD IX_ORD_BIG_ORDNO_FX — 세션 통계]
  PL/SQL execution elapsed time   11.4 초
  sorts (rows)                    1,000,000
  리프 블록                        약 4,370 (키 19바이트 + ROWID)

문제

인덱스 하나를 다시 짓는 데 16.9초가 걸리는 이유를 설명하시오.
「주문번호로 찾기」를 지금처럼 4블록 안팎으로 처리하면서 체인 ①을 원래 시간으로 돌려놓으시오.
같은 식을 SQL 내장 함수로 쓴 가상 컬럼에 인덱스를 만드는 방법과 비교해 대가를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① PL/SQL 함수로 만든 인덱스를 지운다 (체인 ①의 REBUILD 한 줄도 뺀다)
DROP INDEX IX_ORD_BIG_ORDNO_FX;

-- ② 주문번호 'HB-YYYYMMDD-nnnnnnn' 은 (ORD_DT, ORD_SEQ) 를 펼친 표기다
--    → 가공은 바인드 쪽에서 하고, 이미 있는 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ) 로 찾는다
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT  = TO_DATE(SUBSTR(:v_ord_no, 4, 8), 'YYYYMMDD')
   AND ORD_SEQ = TO_NUMBER(SUBSTR(:v_ord_no, 13));

개선 후 실행계획 학습용 예시 · 함수 호출 수는 시험용 패키지 카운터로 센 값

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_ORD_BIG         |      1 |      1 |      1 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | IX_ORD_BIG_DT_SEQ |      1 |      1 |      1 |00:00:00.01 |       3 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_DT"=TO_DATE(SUBSTR(:V_ORD_NO,4,8),'YYYYMMDD') AND
              "ORD_SEQ"=TO_NUMBER(SUBSTR(:V_ORD_NO,13)))

[체인 ①] 21.9 → 5.0 초 (REBUILD 16.9 초 · PL/SQL 함수 100만 번 · 인덱스 약 4,370블록이 사라졌다)
[비교 — SQL 식 가상 컬럼 + 인덱스] REBUILD 2.6 초 · 리프 약 4,370 · 체인 ① 약 7.6 초

모범 답안 서술

함수 기반 인덱스는 함수의 결과를 키로 저장한다. 그래서 인덱스를 다시 지을 때는 100만 행마다 FN_ORD_NO를 불러 키를 계산해야 한다. 이 함수는 PL/SQL이라 행마다 SQL 엔진에서 PL/SQL 엔진으로 건너갔다 돌아오고(컨텍스트 전환), 호출 1,000,000번에 PL/SQL 실행 시간만 11.4초, REBUILD 16.9초가 됐다. DETERMINISTIC은 같은 인자가 되풀이될 때 호출을 줄여 줄 수 있을 뿐인데, 인자 (ORD_DT, ORD_SEQ)는 행마다 다르므로 한 번도 줄지 않는다. 같은 크기의 다른 두 인덱스는 함수 호출이 없어 2.3 ~ 2.4초다. 낮에는 이 테이블을 고치지 않으니 비용은 재적재 때만 들지만, 화면이 행을 넣는 테이블이었다면 INSERT마다 함수를 불렀을 것이다. 주문번호는 새 정보가 아니라 (ORD_DT, ORD_SEQ)를 이어 붙인 표기다. 그러니 키를 따로 만들지 말고, 입력한 주문번호를 바인드 쪽에서 풀어 주문일자(TO_DATE(SUBSTR(:v_ord_no, 4, 8), 'YYYYMMDD'))와 순번(TO_NUMBER(SUBSTR(:v_ord_no, 13)))으로 비교한다. 컬럼은 가공하지 않았으므로 이미 있는 IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)로 두 칸 모두 access 조건이 되어 지금처럼 4블록에 찾는다. 함수 기반 인덱스를 지우고 체인 ①의 REBUILD 한 줄을 빼면 체인 ①은 21.9 → 5.0초로 돌아가고, 인덱스 약 4,370블록도 사라진다. 다른 방법은 같은 식을 SQL 내장 함수로 쓴 가상 컬럼('HB-' || TO_CHAR(ORD_DT, 'YYYYMMDD') || '-' || LPAD(ORD_SEQ, 7, '0'))을 만들고 그 칸에 인덱스를 만드는 것이다. PL/SQL 호출은 없어져 REBUILD가 2.6초로 줄지만, 인덱스 하나(리프 약 4,370블록)와 그 재생성 비용은 남아 체인 ①이 약 7.6초다. 이미 있는 인덱스로 같은 일을 할 수 있으므로 인덱스를 늘리지 않는 쪽을 고른다.

해설

함정: 함수 기반 인덱스가 기대는 PL/SQL 함수를 CREATE OR REPLACE로 다시 컴파일하면 그 인덱스가 DISABLED가 되고, 그 테이블의 DML이 ORA-30554로 멈춘다(PLSQL-003). PL/SQL 함수로 만든 인덱스는 비용뿐 아니라 배포 순서까지 묶는다.
바인드 쪽 가공은 입력 형식이 틀리면 TO_DATE가 에러를 낸다. 이 화면처럼 형식을 먼저 검사하거나, 검사에 걸린 입력은 SQL을 보내지 않는다.
교재 02화(IDX-001)와 같은 원리다. 컬럼을 가공하지 말고 바인드 쪽을 가공하면 이미 있는 인덱스가 범위를 지목한다. 이 문제에서는 그 원리가 인덱스 하나와 재적재 17초를 아꼈다.
주문번호가 정말 따로 매기는 값(예: 제휴몰 번호)이라면 칸으로 저장하고 B-tree 인덱스를 만드는 것이 맞다. 그때도 함수가 아니라 저장된 칸에 만든다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 함수 호출 수는 시험용 패키지 카운터로 센 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 함수 호출 수는 시험용 패키지 카운터로 센 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황