SQLP 튜닝 연습장

테마

튜닝 연습인덱스 사용 조건
IDX-021

형변환 점검을 마친 뒤로 주문번호 한 건 조회가 주문 테이블을 다 읽는다

기초SELECT한빛유통21 / 700

상황 ​

지난주 박팀장이 "묵시적 형변환을 전부 찾아 없애라"라고 지시했다. 조민우는 고객센터 「주문번호로 찾기」 SQL에서 화면은 주문번호를 문자열로 넘기는데 ORD_SEQ는 NUMBER라는 것을 찾아냈다. 그래서 "타입을 명시적으로 맞춘다"며 조건을 고쳐 배포했다. 배포 다음 날부터 이 조회가 느려졌고, 상담원들은 "영수증에 찍힌 번호를 그대로 치면 없는 주문이라고 나온다"라고 한다. 영수증에는 주문번호가 앞을 0으로 채운 10자리(0000048213)로 찍힌다. 이 화면은 하루 2만 번쯤 호출된다.

테이블과 인덱스 ​

  • T_ORD 주문: 100,000행 · 896블록 · ORD_SEQ NUMBER(18), 1 ~ 100,000 · CUS_ID · ORD_DT · ORD_ST · PAY_TP · ORD_AMT
  • 인덱스 PK_T_ORD (ORD_SEQ): BLEVEL 1 · 통계 최신
  • 화면은 상담원이 친 값을 그대로 문자열 바인드(:v_ord_no, VARCHAR2)로 넘긴다. 영수증 번호를 치면 '0000048213', 고객이 불러 준 번호를 치면 '48213'이 들어온다

원본 SQL ​

sql
-- [배포 전] WHERE ORD_SEQ = :v_ord_no
-- [배포 후] 조민우: 문자 바인드에 맞춰 컬럼 쪽 타입을 명시적으로 바꾼다
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, PAY_TP, ORD_AMT
  FROM T_ORD
 WHERE TO_CHAR(ORD_SEQ) = :v_ord_no

실행계획 학습용 예시 ​

xplan
[배포 전 · ORD_SEQ = :v_ord_no · 입력 '0000048213']
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T_ORD    |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX UNIQUE SCAN         | PK_T_ORD |      1 |      1 |      1 |00:00:00.01 |       2 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORD_SEQ"=TO_NUMBER(:V_ORD_NO))

[배포 후 · TO_CHAR(ORD_SEQ) = :v_ord_no · 입력 '48213']
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |      1 |00:00:00.02 |     896 |
|*  1 |  TABLE ACCESS FULL          | T_ORD    |      1 |   1000 |      1 |00:00:00.02 |     896 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_CHAR("ORD_SEQ")=:V_ORD_NO)

같은 배포 후 판에 '0000048213'을 넣으면 A-Rows 0 · Buffers 896

문제 ​

  1. 배포 전 실행계획의 TO_NUMBER와 배포 후의 TO_CHAR가 각각 어느 쪽(컬럼, 바인드 변수)에 붙었는지 비교하고, 배포 후 판이 느려진 이유를 설명하시오.
  2. 영수증 번호 '0000048213'을 그대로 입력하면 두 판의 결과가 왜 다른지 설명하시오.
  3. 인덱스를 다시 쓰고 두 입력 모두 같은 주문을 찾도록 SQL을 고치고, 숫자가 아닌 값이 입력될 때의 처리도 제안하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자