인덱스 이름을 표준에 맞춘 다음 날부터 「재구매 간격」이 전부 수백 일로 나온다

기초SELECT한빛유통319 / 700

상황

고객센터 주문 상세 화면 위쪽에는 「재구매 간격」(이번 주문일과 직전 주문일 사이의 날짜 수)이 있다. 지난 토요일 DBA가 인덱스 이름을 명명 표준(IX_테이블_컬럼)에 맞춰 정리했다. ALTER INDEX T_ORD_BIG_X02 RENAME TO IX_ORD_BIG_CUS_DT처럼 이름만 바꾼 작업이라 영향 검토는 하지 않았다. 월요일 아침부터 상담원들이 "재구매 간격이 전부 700일, 900일로 나온다"라고 한다. 직전 주문일을 구하는 SQL은 2019년에 강도현이 쓴 것이다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT DATE(전부 자정, 2022-01-01 ~ 2024-12-31) · 고객마다 약 500건
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): 지난 토요일까지 이름은 T_ORD_BIG_X02 · BLEVEL 2 · 리프 약 3,000블록 · 통계 최신

원본 SQL

sql

SELECT /*+ INDEX_DESC(o T_ORD_BIG_X02) */
       o.ORD_DT AS prev_dt
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :cus
   AND o.ORD_DT < :cur_dt
   AND ROWNUM = 1

실행계획 학습용 예시

xplan

[지난주 금요일 · 이름을 바꾸기 전]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY               |                   |      1 |        |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN DESCENDING| T_ORD_BIG_X02     |      1 |      1 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("O"."CUS_ID"=:CUS AND "O"."ORD_DT"<:CUR_DT)

[이번 주 월요일 · 같은 SQL]
-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |      1 |00:00:00.01 |       3 |
|*  1 |  COUNT STOPKEY               |                   |      1 |        |      1 |00:00:00.01 |       3 |
|*  2 |   INDEX RANGE SCAN           | IX_ORD_BIG_CUS_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("O"."CUS_ID"=:CUS AND "O"."ORD_DT"<:CUR_DT)

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
---------------------------------------------------------------------------
   2 -  SEL$1 / "O"@"SEL$1"
         U -  INDEX_DESC(o T_ORD_BIG_X02) / index specified in the hint doesn't exist

[화면 · CUS_000123 의 2024-12-30 주문]  직전 주문일 2022-01-04 → 재구매 간격 1,091일 (실제 직전 주문은 2024-12-27)

문제

SQL을 한 글자도 바꾸지 않았는데 결과가 바뀐 까닭을 두 실행계획과 Hint Report로 설명하시오.
인덱스의 이름이나 구성이 바뀌어도 결과가 바뀌지 않도록 SQL을 고치고, 고친 SQL이 몇 블록에서 멈추는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 직전 주문일 = 이번 주문일보다 앞선 날짜 중 가장 늦은 날 : 뜻을 SQL 에 적는다 (힌트와 인덱스 방향에 기대지 않는다)
SELECT MAX(o.ORD_DT) AS prev_dt
  FROM T_ORD_BIG o
 WHERE o.CUS_ID = :cus
   AND o.ORD_DT < :cur_dt

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

xplan

-------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |      1 |00:00:00.01 |       3 |
|   1 |  SORT AGGREGATE              |                   |      1 |      1 |      1 |00:00:00.01 |       3 |
|   2 |   FIRST ROW                  |                   |      1 |      1 |      1 |00:00:00.01 |       3 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| IX_ORD_BIG_CUS_DT |      1 |      1 |      1 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("O"."CUS_ID"=:CUS AND "O"."ORD_DT"<:CUR_DT)

모범 답안 서술

두 계획의 차이는 2번 줄 한 단어다. 금요일에는 INDEX RANGE SCAN DESCENDING, 월요일에는 INDEX RANGE SCAN이다. 힌트 INDEX_DESC(o T_ORD_BIG_X02)가 가리키는 인덱스는 이름이 바뀌어 더는 없다. 오라클은 이런 힌트에 오류를 내지 않고 조용히 버린다. Hint Report의 U - Unused와 index specified in the hint doesn't exist가 그 기록이다. 힌트가 사라지자 옵티마이저는 같은 인덱스를 앞으로 읽었고, ROWNUM = 1은 처음 읽힌 한 건, 곧 그 고객의 가장 오래된 주문일을 돌려줬다. 그래서 재구매 간격이 700 ~ 1,000일로 나왔다. 더 거슬러 올라가면 이 SQL에는 ORDER BY가 없다. 직전이라는 뜻은 SQL이 아니라 힌트와 인덱스 방향에 적혀 있었고, 결과가 실행계획에 달려 있었다. 2019년에는 흔한 기법이었지만, 인덱스 이름 변경, 재생성, 컬럼 추가처럼 결과와 상관없어 보이는 작업이 결과를 바꾼다. 고친 SQL은 뜻을 그대로 쓴다. 이번 주문일보다 앞선 날짜 중 가장 늦은 날이므로 MAX(ORD_DT)다. 인덱스 (CUS_ID, ORD_DT)에서 범위 CUS_ID = :cus AND ORD_DT < :cur_dt의 오른쪽 끝이 답이므로, FIRST ROW(INDEX RANGE SCAN (MIN/MAX))로 항목 하나만 읽고 3블록에서 멈춘다. 힌트가 없어도 같은 일을 하고, 인덱스가 바뀌어 다른 계획이 나와도 답은 같다. 행 전체가 필요하면 ORDER BY ORD_DT DESC를 인라인 뷰에 넣고 바깥에서 ROWNUM = 1로 자른다. 같은 기법을 쓴 SQL이 더 있을 수 있다. V$SQL과 소스에서 INDEX_DESC와 ROWNUM = 1이 함께 쓰인 문장을 찾아 함께 고치고, 인덱스 이름을 바꾸기 전에는 그 이름을 쓰는 힌트부터 찾는 일을 작업 절차에 넣는다.

해설

힌트는 틀려도 조용하다: 문법이 틀리거나 가리키는 대상이 없는 힌트는 오류 없이 무시된다. 19c부터 나오는 Hint Report가 그나마 흔적을 남긴다(교재 06화 §7).
INDEX_DESC와 ROWNUM = 1을 꼭 써야 한다면: 인라인 뷰 안에 ORDER BY ORD_DT DESC를 함께 적는다. 인덱스가 그대로면 정렬 없이 같은 계획(COUNT STOPKEY)이 나오고, 인덱스가 바뀌면 느려질 수는 있어도 틀리지는 않는다.
인덱스 이름을 바꾸거나 다시 만들기 전에는 V$SQL, DBA_SOURCE, SQL Plan Baseline과 SQL Profile의 힌트에서 그 이름을 검색한다. 이름만 바꾸는 일이 '영향 없음'이 아닌 이유다.
T_ORD_BIG은 전부 자정이라 같은 날 주문이 여러 건일 수 있다. 날짜만 필요한 이 화면은 상관없지만, 직전 주문 한 건을 보여 주려면 정렬 키에 ORD_SEQ를 더해야 한다(JOIN-048).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황