PART-016

파티션으로 옮긴 뒤 반품 영수증 조회가 논리 읽기 상위 목록에 올라왔다

기초SELECT유통391 / 700

상황

유통 매장 POS의 「반품·교환」 화면은 영수증 바코드(판매번호 SLE_NO)를 찍어 원래 판매를 찾는다. 다섯 매장에서 하루 약 3,000번 호출된다. 지난달 CF10을 월별 파티션 CF10_P로 옮긴 뒤 이 SQL이 AWR의 논리 읽기 상위 목록에 올라왔다. 바코드에는 판매번호만 있고, POS는 판매번호 말고는 아무것도 넘기지 않는다. POS 프로그램은 이번 분기에 고칠 수 없다.

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눠 CTAS로 지었다. 칸 하나는 약 20,800행이고 FULL로 약 191블록이다. 빈 PMAX는 세그먼트가 없다. P202311은 23번째 칸이다.
SLE_NO VARCHAR2(15)는 판매일자 8자리 + - + 그날의 일련번호 4자리다('20231107-0215'). 적재 프로그램이 SLE_DT로 번호를 만들기 때문에 앞 8자리는 지금까지 언제나 그 행의 SLE_DT와 같았다. 이 규칙을 지키는 제약은 없다.
PK CF10_P_PK (SLE_NO, SLE_DT)는 LOCAL이다(PK를 LOCAL로 만들려고 파티션 키를 뒤에 붙였다). 칸마다 높이 2다. 다른 인덱스는 없고 통계는 최신이다.
옮기기 전 CF10에서는 CF10_PK (SLE_NO)로 4블록을 읽었다.

원본 SQL

sql

SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10_P
 WHERE SLE_NO = :sle_no

실행계획 학습용 예시 · 칸 크기는 CF10을 24칸으로 나눈 값

xplan

[:sle_no = '20231107-0215']
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      1 | 00:00:00.01 |      49 |        |       |
|   1 |  PARTITION RANGE ALL                |           |      1 |      1 |      1 | 00:00:00.01 |      49 |      1 |    25 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | CF10_P    |     24 |      1 |      1 | 00:00:00.01 |      49 |      1 |    25 |
|*  3 |    INDEX RANGE SCAN                 | CF10_P_PK |     24 |      1 |      1 | 00:00:00.01 |      48 |      1 |    25 |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_NO"=:SLE_NO)

문제

한 행을 찾는 데 49블록이 드는 까닭을 실행계획의 Starts, Pstart, Pstop과 PK 정의로 설명하시오.
POS를 고치지 않고 칸 하나만 열도록 SQL을 고치고, 고쳐도 결과가 같다는 근거와 그 근거를 지키는 방법을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매번호 앞 8자리 = 판매일자 → 값(바인드) 쪽에서 파티션 키를 만든다 · 컬럼은 그대로 둔다
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10_P
 WHERE SLE_NO = :sle_no
   AND SLE_DT = SUBSTR(:sle_no, 1, 8);

-- 이 규칙이 깨지면 '없는 영수증' 이 되므로 제약으로 못 박는다 (지금 데이터로 먼저 확인)
--   SELECT COUNT(*) FROM CF10_P WHERE SUBSTR(SLE_NO, 1, 8) <> SLE_DT;   → 0
ALTER TABLE CF10_P ADD CONSTRAINT CF10_P_CK_NO
  CHECK (SUBSTR(SLE_NO, 1, 8) = SLE_DT);

개선 후 실행계획 학습용 예시 · 칸 크기는 CF10을 24칸으로 나눈 값

xplan

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |           |      1 |        |      1 | 00:00:00.01 |       3 |        |       |
|   1 |  PARTITION RANGE SINGLE             |           |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID | CF10_P    |      1 |      1 |      1 | 00:00:00.01 |       3 |    KEY |   KEY |
|*  3 |    INDEX UNIQUE SCAN                | CF10_P_PK |      1 |      1 |      1 | 00:00:00.01 |       2 |    KEY |   KEY |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_NO"=:SLE_NO AND "SLE_DT"=SUBSTR(:SLE_NO,1,8))

  (실행 때 고른 칸: 23번 P202311)

모범 답안 서술

답은 3번 줄의 Starts 24에 있다. CF10_P_PK는 LOCAL이라 칸마다 따로 있고, 선두 컬럼은 SLE_NO이며 파티션 키 SLE_DT는 뒤에 붙어 있다(nonprefixed). 조건에 SLE_DT가 없으니 옵티마이저는 이 번호가 어느 칸에 있는지 모르고(PARTITION RANGE ALL, Pstart 1, Pstop 25), 데이터가 있는 24칸마다 PK를 한 번씩 뒤진다. 칸마다 2블록씩 48블록에 테이블 1블록을 더해 49블록이다. 찾는 행은 23번 칸에 하나뿐이다. 그런데 판매번호의 앞 8자리가 곧 판매일자다. 그래서 POS를 고치지 않고도 SQL 안에서 값 쪽을 가공해 파티션 키 조건을 만들 수 있다(SLE_DT = SUBSTR(:sle_no, 1, 8)). 컬럼은 가공하지 않으므로 Pstart와 Pstop이 KEY로 찍히고, 실행할 때 칸 하나만 연다(PARTITION RANGE SINGLE). PK의 두 컬럼이 모두 등치 조건이 되어 INDEX UNIQUE SCAN으로 3블록에 끝난다(49 → 3). 하루 3,000번이면 약 147,000 → 9,000블록이다. 결과가 같은 근거는 "앞 8자리 = 판매일자"라는 적재 규칙 하나다. 지금은 제약이 없어서, 어느 날 이 규칙이 깨진 행이 들어오면 그 영수증은 에러 없이 '없는 영수증'이 된다. 그래서 지금 데이터에 어긋난 행이 0건인지 확인한 뒤 CHECK 제약 SUBSTR(SLE_NO, 1, 8) = SLE_DT로 규칙을 못 박는다. 그러면 규칙이 깨지는 순간 적재가 에러로 멈춘다.

해설

함정: WHERE SUBSTR(SLE_NO, 1, 8) = SLE_DT처럼 컬럼끼리 비교하는 조건을 더하면 칸을 하나도 고르지 못한다. 칸막이에 붙은 것은 SLE_DT 값의 구간이고, 비교할 상대가 바인드 값이어야 실행할 때 칸을 고를 수 있다.
교재 14화의 버거 영수증은 주문번호에 날짜가 없어서 POS가 월을 넘기거나 GLOBAL 인덱스를 만들어야 했다. 여기서는 키 안에 파티션 키가 이미 들어 있어서 SQL만 고치면 된다. 번호 체계를 설계할 때 날짜를 앞에 두면 이런 길이 생긴다.
CHECK 제약이 생기면 (SLE_NO, SLE_DT) PK의 유일성도 SLE_NO 하나의 유일성과 같아진다. 제약이 없으면 같은 판매번호가 다른 판매일자로 두 번 들어가도 PK가 막지 못한다.
CF10_P (SLE_NO) GLOBAL 유니크 인덱스도 답이 된다(4블록). 대신 월 정리 DDL마다 UPDATE GLOBAL INDEXES를 붙여야 하고, 빠뜨리면 이 화면이 FULL로 떨어진다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 칸 크기는 CF10을 24칸으로 나눈 값 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 칸 크기는 CF10을 24칸으로 나눈 값 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황