SQLP 튜닝 연습장

테마

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

인덱스 없는 정산 테이블에 인덱스를 달아 달라는 요청서에 SQL 네 개가 붙어 왔다

실전DDL한빛유통32 / 700

상황 ​

정산 테이블 T_ORD_STLM에는 인덱스가 하나도 없다. 정산팀 오과장이 화면과 배치에서 쓰는 SQL 네 개를 모아 인덱스를 요청했다. 요청서 맨 아래에는 "바인드는 개발팀에 물어서 적었다"라는 메모가 있다. 이 테이블은 매일 밤 비운 뒤 주문 10만 건을 한 번에 다시 적재한다(교재 14화의 P1). 박팀장은 이렇게 당부했다. "인덱스부터 만들지 말고, 만든 인덱스를 이 SQL들이 정말 쓰는지부터 따져 와라."

테이블과 인덱스 ​

  • T_ORD_STLM 정산: 100,000행 · 640블록 · 인덱스 없음 · 통계 최신
  • STLM_SEQ NUMBER(18) · ORD_SEQ NUMBER(18) · CUS_ID VARCHAR2(40)(고객 2,000명, 고객당 약 50건) · STLM_DT DATE(주문 시각 그대로, 시분초 있음, 2023-01-01 ~ 2024-12-31 · 한 시간에 약 6건 · 한 달 약 4,110건) · STLM_AMT
  • 같은 날짜의 정산은 테이블 곳곳에 흩어져 있다
  • 정산 화면의 WAS는 접속할 때 NLS_DATE_FORMAT = 'YYYY/MM/DD HH24:MI:SS'를 건다(개발팀 확인). 야간 배치 서버의 세션은 기본값 DD-MON-RR이다

원본 SQL ​

sql
-- S1 「PG 대사」 야간 배치 · 하루 10만 번 · PG 파일의 주문번호를 문자열로 바인드
SELECT STLM_SEQ, STLM_AMT
  FROM T_ORD_STLM
 WHERE ORD_SEQ = :ord_no;

-- S2 「시간대별 정산」 화면 · 정산 담당 8명이 수시로 · '2024/06/14 09:00:00' 모양 문자열 두 개
SELECT STLM_SEQ, ORD_SEQ, CUS_ID, STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_DT >= :d_from
   AND STLM_DT <  :d_to;

-- S3 「고객 정산 문의」 상담 화면 · 하루 약 3천 번 · 'YYYYMMDD' 문자열
SELECT STLM_SEQ, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE CUS_ID  = :cus_id
   AND STLM_DT >= TO_DATE(:from_ymd, 'YYYYMMDD')
 ORDER BY STLM_DT DESC;

-- S4 「월 마감 점검」 · 월 1회 · '2024/06' 모양 문자열
SELECT COUNT(*), SUM(STLM_AMT)
  FROM T_ORD_STLM
 WHERE STLM_DT LIKE :ym || '%';

실행계획 학습용 예시 ​

xplan
[S2 · 2024/06/14 09:00:00 ~ 10:00:00]
-----------------------------------------------------------------------------------------
| Id  | Operation           | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |            |      1 |        |      6 |00:00:00.01 |     644 |
|*  1 |  FILTER             |            |      1 |        |      6 |00:00:00.01 |     644 |
|*  2 |   TABLE ACCESS FULL | T_ORD_STLM |      1 |      6 |      6 |00:00:00.01 |     644 |
-----------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(TO_DATE(:D_TO)>TO_DATE(:D_FROM))
   2 - filter(("STLM_DT">=TO_DATE(:D_FROM) AND "STLM_DT"<TO_DATE(:D_TO)))

[나머지 셋도 TABLE ACCESS FULL(640블록 안팎) · Predicate Information만 옮긴다]
S1   filter("ORD_SEQ"=TO_NUMBER(:ORD_NO))
S3   filter(("CUS_ID"=:CUS_ID AND "STLM_DT">=TO_DATE(:FROM_YMD,'YYYYMMDD')))
S4   filter(TO_CHAR(INTERNAL_FUNCTION("STLM_DT")) LIKE :YM||'%')

문제 ​

  1. S1 ~ S4에서 일어나는 형변환을 찾아, 인덱스를 만들었을 때 그 SQL이 고치지 않은 채로 인덱스 범위 스캔을 쓸 수 있는지 각각 판정하시오. 판정의 근거(변환이 붙은 쪽)와 결과가 틀릴 위험이 있는지도 함께 설명하시오.
  2. 꼭 필요한 인덱스만 DDL로 작성하고, 고쳐야 하는 SQL을 고치시오.
  3. 이 인덱스들이 야간 적재에 주는 부담과 그 대책을 설명하시오.
원본 SQL을 고쳐 쓰세요. 인덱스 DDL과 힌트도 쓸 수 있습니다.
무엇이 문제였고 왜 이렇게 고쳤는지 쓰세요.0자