주문번호 앞자리로 후보를 띄우는 상담 화면이 한 번 찾을 때마다 대용량 주문을 다 읽는다

실전SELECT한빛유통33 / 700

상황

고객센터의 「주문번호 앞자리 검색」 화면은 고객이 주문번호를 끝까지 불러 주지 못할 때 쓴다. 상담원이 앞 네다섯 자리를 치면 그 숫자로 시작하는 주문이 후보로 뜬다. 상담원 40명이 하루 6천 번쯤 쓴다. 박팀장은 "LIKE를 지우라는 말만으로는 안 된다. 화면 요구를 지키는 방법을 두세 개 놓고 대가까지 비교해 와라"라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_SEQ NUMBER(18), 1 ~ 1,000,000(유일, 적재 순서라 번호가 가까우면 같은 블록에 있다) · CUS_ID · ORD_DT · ORD_ST · ORD_AMT
인덱스 IX_ORD_BIG_SEQ (ORD_SEQ): 이 문제에서 만들어 둔 유니크 인덱스 · BLEVEL 2 · 리프 약 2,090블록 · 통계 최신
화면은 상담원이 친 숫자를 문자열(:v_prefix)로 넘긴다

원본 SQL

sql

SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_SEQ LIKE :v_prefix || '%'
 ORDER BY ORD_SEQ

실행계획 학습용 예시

xplan

[상담원이 4821을 쳤을 때]
------------------------------------------------------------------------------------------
| Id  | Operation          | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |           |      1 |        |    111 |00:00:00.14 |    5826 |
|   1 |  SORT ORDER BY     |           |      1 |  50000 |    111 |00:00:00.14 |    5826 |
|*  2 |   TABLE ACCESS FULL| T_ORD_BIG |      1 |  50000 |    111 |00:00:00.14 |    5826 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TO_CHAR("ORD_SEQ") LIKE :V_PREFIX||'%')

문제

2번 줄의 TO_CHAR가 왜 생겼는지 설명하고, '4821'로 시작하는 주문이 왜 111건인지 숫자로 설명하시오.
화면 요구(앞자리로 후보 찾기)를 지키는 방법으로 ① 숫자 범위로 바꾸기 ② 문자 사본 컬럼(가상 컬럼)과 인덱스 ③ 요구 재협상(다른 단서를 함께 받기)을 비교하시오.
채택한 방법을 SQL로 작성하고, 입력값에 둘 제한을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 채택 : 접두어 p(자릿수 L)로 시작하는 번호는 자릿수마다 [p × 10^k, (p + 1) × 10^k) 범위다 (k = 0 … 7 - L)
--   범위 목록을 행으로 만들어 조인한다 → 범위마다 IX_ORD_BIG_SEQ를 찾아간다 · 컬럼은 그대로
--   입력 제한 : 숫자만, 0으로 시작하지 않게, 4자리 이상 (화면에서 검사)
SELECT o.ORD_SEQ, o.CUS_ID, o.ORD_DT, o.ORD_ST, o.ORD_AMT
  FROM (SELECT TO_NUMBER(:v_prefix) * POWER(10, LEVEL - 1)       AS lo,
               (TO_NUMBER(:v_prefix) + 1) * POWER(10, LEVEL - 1) AS hi
          FROM dual
       CONNECT BY LEVEL <= 8 - LENGTH(:v_prefix)) r,
       T_ORD_BIG o
 WHERE o.ORD_SEQ >= r.lo
   AND o.ORD_SEQ <  r.hi
 ORDER BY o.ORD_SEQ

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |    111 |00:00:00.01 |      17 |
|   1 |  SORT ORDER BY                         |                |      1 |      1 |    111 |00:00:00.01 |      17 |
|   2 |   NESTED LOOPS                         |                |      1 |      1 |    111 |00:00:00.01 |      17 |
|   3 |    VIEW                                |                |      1 |      1 |      4 |00:00:00.01 |       0 |
|*  4 |     CONNECT BY WITHOUT FILTERING       |                |      1 |        |      4 |00:00:00.01 |       0 |
|   5 |      FAST DUAL                         |                |      1 |      1 |      1 |00:00:00.01 |       0 |
|   6 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG      |      4 |      1 |    111 |00:00:00.01 |      17 |
|*  7 |     INDEX RANGE SCAN                   | IX_ORD_BIG_SEQ |      4 |      1 |    111 |00:00:00.01 |      12 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(LEVEL<=8-LENGTH(:V_PREFIX))
   7 - access("O"."ORD_SEQ">="R"."LO" AND "O"."ORD_SEQ"<"R"."HI")

모범 답안 서술

2번 줄의 TO_CHAR("ORD_SEQ")는 오라클이 붙였다. LIKE는 문자열 연산이라 숫자 컬럼에 LIKE를 쓰면 묵시적 형변환으로 컬럼 쪽이 문자로 바뀐다. 인덱스에는 숫자가 숫자 순서로 정렬되어 있으므로 '4821로 시작하는 문자열'의 범위를 지목할 수 없고, 100만 행을 다 읽어 행마다 문자로 바꿔 본다(Buffers 5,826). 번호는 1부터 1,000,000까지라 '4821'로 시작하는 번호는 자릿수마다 따로 있다. 네 자리 4821이 한 개, 다섯 자리 48210 ~ 48219가 열 개, 여섯 자리 482100 ~ 482199가 백 개이고, 일곱 자리 4821000 이상은 1,000,000을 넘으니 없다. 그래서 1 + 10 + 100 = 111건이다. 숫자 순서로 보면 한 덩어리가 아니라 자릿수마다 떨어진 범위 셋이다. ① 숫자 범위로 바꾸기: 접두어 p로 시작하는 번호는 자릿수마다 p × 10^k 이상, (p + 1) × 10^k 미만이다. 이 범위 목록을 CONNECT BY로 행으로 만들어 조인하면 범위마다 IX_ORD_BIG_SEQ를 찾아간다(7번 줄 Starts 4). 스키마를 바꾸지 않고 인덱스도 더하지 않는다. 대가는 SQL이 낯설다는 것과 입력 검사가 꼭 필요하다는 것이다. ② 문자 사본 컬럼: ORD_NO_TXT VARCHAR2(18) GENERATED ALWAYS AS (TO_CHAR(ORD_SEQ, 'FM999999999999999999')) 가상 컬럼과 인덱스를 만들고 ORD_NO_TXT LIKE :v_prefix || '%'로 찾는다. SQL은 단순하지만 100만 행짜리 인덱스가 하나 늘어 주문을 넣을 때마다 고쳐야 하고(적재 비용), 테이블에 보이는 컬럼이 늘어 컬럼 목록 없는 INSERT는 ORA-00947로 깨진다(교재 03화 §4.3). ③ 요구 재협상: 앞자리 대신 고객 휴대폰 뒷자리나 주문일을 함께 받으면 후보가 훨씬 적어진다. 가장 좋지만 상담 절차를 바꿔야 한다. ①을 채택한다. 이 화면 하나를 위해 테이블과 적재를 바꿀 이유가 없다. 고친 SQL은 범위 넷을 찾아가며 111건만 읽는다(Buffers 5,826 → 17). 입력에는 제한을 둔다. 0으로 시작하는 값은 원본 LIKE에서는 0건이지만 TO_NUMBER를 거치면 0이 사라져 다른 번호를 찾으므로 화면에서 막는다. 세 자리 이하는 후보가 1,000건을 넘으므로 네 자리 이상만 받고, 후보가 200건을 넘으면 더 치도록 안내하는 상한을 둔다.

해설

같은 규칙, 반대 방향: 문자 컬럼에 숫자를 비교하면 컬럼이 TO_NUMBER로 바뀌고, 숫자 컬럼에 LIKE를 쓰면 컬럼이 TO_CHAR로 바뀐다. 어느 쪽이든 컬럼 쪽이 바뀌면 인덱스를 못 쓴다.
BETWEEN :p * 1000 AND :p * 1000 + 999 하나로는 틀린다: 일곱 자리 번호만 찾고 네 자리, 다섯 자리, 여섯 자리 번호를 놓친다. 숫자 접두어가 범위 하나로 바뀌는 것은 자릿수가 정해진 값(여덟 자리 숫자 날짜 같은)뿐이다.
가상 컬럼 대신 TO_CHAR(ORD_SEQ) 함수 기반 인덱스를 만들어도 같은 효과를 낸다. 다만 SQL이 인덱스 식과 글자까지 같아야 쓰이고, 묵시적 형변환이 만드는 식에 기대는 셈이라 문서로 남기기 어렵다.
주문번호가 1,000만을 넘어 여덟 자리가 되면 8 - LENGTH(:v_prefix)의 8도 바뀌어야 한다. 최대 자릿수는 상수로 박지 말고 설정값으로 둔다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황