판매번호 규칙에서 벗어난 판매 37건을 찾는 점검이 10분마다 판매 50만 건을 다 읽는다

중급SELECT유통41 / 700

상황

유통 본사 정보보안팀의 「판매번호 이상 점검」은 판매번호가 판매일자로 시작하지 않는 판매를 찾는다. 관제 모니터에서 10분마다 돈다. 지난주에는 매장 POS 한 대의 시계가 초기화되어 '1970년' 판매가 찍혔고, 개점 리허설을 하던 교육용 단말의 판매도 섞여 들어왔다. 조민우는 부정형을 없애려고 조건을 SLE_NO > '21'로 바꿔 보았다. 빨라졌지만 지난주의 1970년 판매 23건이 결과에서 사라졌다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510) · SLE_NO VARCHAR2(15) NOT NULL · 정상 판매번호는 '20230601-0001'처럼 판매일자 8자리로 시작한다
규칙에서 벗어난 판매 37건: POS 시계 초기화로 찍힌 '19700101-0001' ~ '19700101-0023' 23건, 교육용 단말의 'TR01-0001' ~ 'TR01-0014' 14건 (문제 설정: 교재의 CF10에는 이런 행이 없다)
인덱스 CF10_PK (SLE_NO): BLEVEL 2 · 리프 약 1,680 · 통계 최신 · 세션의 문자 비교는 BINARY(NLS_COMP, NLS_SORT 기본값)

원본 SQL

sql

SELECT SHP_NO, SLE_NO, SLE_DT, SLE_HMS, SLE_DOW
  FROM CF10
 WHERE SLE_NO NOT LIKE '20%'
 ORDER BY SHP_NO, SLE_NO

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |     37 |00:00:00.05 |    4510 |
|   1 |  SORT ORDER BY     |      |      1 |   5000 |     37 |00:00:00.05 |    4510 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |   5000 |     37 |00:00:00.05 |    4510 |
-------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("SLE_NO" NOT LIKE '20%')

문제

부정형 NOT LIKE가 인덱스 범위를 정하지 못하는 이유를 설명하고, 조민우의 SLE_NO > '21'이 23건을 놓친 이유를 문자 정렬 순서로 설명하시오.
결과가 바뀌지 않게 SQL을 고쳐 CF10_PK의 범위만 읽도록 하시오. 고친 조건이 원래 조건과 같은 행을 고른다는 근거도 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

SELECT SHP_NO, SLE_NO, SLE_DT, SLE_HMS, SLE_DOW
  FROM CF10
 WHERE SLE_NO <  '20'       -- '20' 으로 시작하는 덩어리의 앞쪽 (19700101-… 같은 값)
    OR SLE_NO >= '21'       -- 덩어리의 뒤쪽 (TR01-… 같은 값)
 ORDER BY SHP_NO, SLE_NO

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

xplan

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                 |      1 |        |     37 |00:00:00.01 |      45 |
|   1 |  SORT ORDER BY                         |                 |      1 |     38 |     37 |00:00:00.01 |      45 |
|   2 |   VIEW                                 | VW_ORE_1B35BA0F |      1 |     38 |     37 |00:00:00.01 |      45 |
|   3 |    UNION-ALL                           |                 |      1 |        |     37 |00:00:00.01 |      45 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10            |      1 |     19 |     23 |00:00:00.01 |      27 |
|*  5 |      INDEX RANGE SCAN                  | CF10_PK         |      1 |     19 |     23 |00:00:00.01 |       4 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10            |      1 |     19 |     14 |00:00:00.01 |      18 |
|*  7 |      INDEX RANGE SCAN                  | CF10_PK         |      1 |     19 |     14 |00:00:00.01 |       4 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("SLE_NO"<'20')
   7 - access("SLE_NO">='21')
       filter(LNNVL("SLE_NO"<'20'))

모범 답안 서술

2번 줄의 조건은 filter("SLE_NO" NOT LIKE '20%')이다. CF10_PK는 판매번호를 정렬해 두었는데, 'NOT LIKE'는 '이 덩어리가 아닌 것'을 묻는 부정형이라 정렬 위에서 '여기부터 여기까지'라는 한 범위로 지목할 수 없다. 그래서 50만 행을 다 읽고 37건만 남긴다(Buffers 4,510). '20'으로 시작하는 값은 정렬 위에서 한 덩어리다. 바이트 순으로 비교하면 '20'으로 시작하는 값은 모두 '20' 이상 '21' 미만에 있고, 그 구간에 든 값은 모두 '20'으로 시작한다. 그러므로 그 밖은 '20'보다 작은 앞쪽과 '21' 이상인 뒤쪽, 두 구간이다. 조민우의 SLE_NO > '21'은 뒤쪽만 남겼다. '1970…'은 첫 글자 '1'이 '2'보다 작아 덩어리 앞쪽에 정렬되므로 잡히지 않았다. 'TR01…'은 'T'가 '2'보다 커서 뒤쪽에 있다. 두 구간을 다 남겨 SLE_NO < '20' OR SLE_NO >= '21'로 쓴다. 판매번호는 NOT NULL이라 NULL을 따질 일도 없으므로, 이 조건은 NOT LIKE '20%'와 정확히 같은 행을 고른다. 옵티마이저는 OR-Expansion으로 두 갈래를 UNION ALL로 나누고, 갈래마다 CF10_PK의 범위를 찾아간다. 뒤 갈래에는 LNNVL("SLE_NO"<'20')이 붙어 두 갈래가 겹치지 않게 한다. Buffers는 4,510 → 45로 준다. 10분마다 돌므로 하루로 치면 약 65만 → 6,500블록이다.

해설

함정 ①: SLE_NO > '21'처럼 한쪽만 남기면 지금 보이는 이상값의 모양에 맞춘 조건이 된다. 점검 SQL은 아직 없는 모양까지 잡아야 하므로 원래 조건과 같은 집합이어야 한다.
함정 ②: LIKE '19%' OR LIKE 'TR%'처럼 지금 보이는 접두어를 늘어놓으면 다음 이상값('00000000-…', 'POS…')을 놓친다.
두 범위가 NOT LIKE와 같은 것은 바이트 순(BINARY) 비교이기 때문이다. NLS_COMP=LINGUISTIC인 세션에서는 문자 순서가 달라질 수 있으므로 점검 배치의 세션 설정을 확인한다.
부정형의 처방은 남는 쪽의 모양에 따라 다르다. 남는 값이 몇 개로 정해져 있으면 IN 목록(IDX-005), 남는 쪽이 정렬 위의 구간이면 이 문제처럼 두 범위다. 근본 대책은 이상값이 들어오지 못하게 하는 것이다(CHECK (SUBSTR(SLE_NO, 1, 8) = SLE_DT), POS 시계 동기화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황