SQLP 튜닝 연습장

테마

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

판매 일 마감 연계가 한 달에 두세 번 중복 키 오류로 멈춘다

중급INSERT유통53 / 700

상황 ​

유통 본사의 야간 연계는 매일 01시에 전날 판매와 전날 취소된 판매를 정보계 적재 테이블 IF_SLE_DAY에 담는다. 원래는 두 조건을 OR로 이은 한 문장이었는데, CNCL_DTT에 인덱스가 없어 판매 50만 건을 다 읽었다. 지난달 DBA가 IX_CF10_CNCL을 만들었다. 같은 날 조민우는 "OR는 인덱스를 못 탄다"며 SQL을 UNION ALL 두 갈래로 나눴다. 연계는 빨라졌지만 그 뒤로 한 달에 두세 번 ORA-00001(유일 제약 위반)로 멈춘다.

테이블과 인덱스 ​

  • CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15) · SLE_DT VARCHAR2(8) NOT NULL · SHP_NO · CNCL_DTT DATE(취소 일시, 취소되지 않은 판매는 NULL) · 하루 판매 약 700건 · 하루 취소 3 ~ 8건(대부분 며칠 전 판매의 반품이고, 그날 판매를 그날 취소하는 일은 한 달에 두세 번) (문제 설정: 교재의 CF10에는 취소 컬럼이 없다. 반품 처리를 위해 운영 중에 더한 컬럼이라고 본다)
  • 인덱스 CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT) · IX_CF10_CNCL (CNCL_DTT): 지난달 생성, 취소된 판매 약 4,000건만 담긴다 · 통계 최신
  • IF_SLE_DAY 정보계 적재 테이블: PK (SLE_NO) · 연계 전에 비운다
  • 멈춘 날 2023-06-14: 판매 702건, 취소 5건(그중 20230614-0388은 그날 14시에 팔고 16시에 취소한 판매)

원본 SQL ​

sql
INSERT INTO IF_SLE_DAY (SLE_NO, SLE_DT, SHP_NO, CNCL_DTT)
SELECT SLE_NO, SLE_DT, SHP_NO, CNCL_DTT
  FROM CF10
 WHERE SLE_DT = :v_ymd                                     -- 전날 판매
UNION ALL
SELECT SLE_NO, SLE_DT, SHP_NO, CNCL_DTT
  FROM CF10
 WHERE CNCL_DTT >= TO_DATE(:v_ymd, 'YYYYMMDD')             -- 전날 취소
   AND CNCL_DTT <  TO_DATE(:v_ymd, 'YYYYMMDD') + 1

실행계획 학습용 예시 ​

xplan
[멈춘 날 2023-06-14 · INSERT 안의 SELECT만 떼어 돌린 계획]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows | A-Time      | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    707 | 00:00:00.01 |      20 |
|   1 |  UNION-ALL                            |                |      1 |        |    707 | 00:00:00.01 |      20 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |    685 |    702 | 00:00:00.01 |      13 |
|*  3 |    INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |    685 |    702 | 00:00:00.01 |       5 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |      6 |      5 | 00:00:00.01 |       7 |
|*  5 |    INDEX RANGE SCAN                   | IX_CF10_CNCL   |      1 |      6 |      5 | 00:00:00.01 |       2 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("SLE_DT"=:V_YMD)
   5 - access("CNCL_DTT">=TO_DATE(:V_YMD,'YYYYMMDD') AND "CNCL_DTT"<TO_DATE(:V_YMD,'YYYYMMDD')+1)

문제 ​

  1. 이 연계가 어떤 날에만 ORA-00001로 멈추는지 실행계획의 숫자로 설명하시오.
  2. 두 인덱스를 그대로 쓰면서 한 판매를 한 번만 담도록 SQL을 고치시오. 둘째 갈래에서 첫 갈래를 빼는 조건을 NOT으로 쓸 때와 LNNVL로 쓸 때의 차이를 갈래의 순서와 NULL의 관점에서 설명하시오.
  3. 손으로 나누지 않고 OR 한 문장으로 되돌리는 방법과 UNION으로 바꾸는 방법도 평가하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자