결합 인덱스를 새로 만들었는데 2018년 패키지의 두 SQL만 계획이 그대로다

실전SELECT한빛유통20 / 700

상황

한빛유통 주문 모니터링 패키지 PKG_ORD_MON은 2018년에 강도현이 짰다. 작년에 고객센터용 (CUS_ID, ORD_DT) 인덱스와 미결제 대시보드용 (ORD_ST, ORD_DT) 인덱스가 생겼는데, 이 패키지의 두 SQL은 계획이 그대로다. 소스에는 -- 인덱스 타지 말 것 (2018.05 강도현)이라는 주석과 함께 컬럼에 || ''와 + 0이 붙어 있다. 그해 회의록에는 이렇게 적혀 있다. "고객 인덱스를 타면 B2B 대량 고객에서 느림 → 주문일 인덱스로 유도. 주간 범위를 주문일 인덱스로 읽으면 느림 → 상태 인덱스로 유도." 그때 인덱스는 단일 컬럼 (CUS_ID), (ORD_ST), (ORD_DT) 셋이었다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 하루 913건이 서로 다른 블록에 흩어져 있다
CUS_ID 2,000명 · 고객마다 주문 500건(2019년에 B2B 사업을 접어 대량 고객이 없다) · 고객 CUS_000123의 2024년 4 ~ 6월 주문 41건 · 같은 석 달의 전체 주문 83,083건
ORD_ST COMPLETE 90% · PENDING 8%(80,000건) · CANCEL 2% · 2024-06-03 ~ 06-09 주간 6,391건 중 PENDING 731건
지금 인덱스: IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT) · IX_ORD_BIG_ST_DT (ORD_ST, ORD_DT) · IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 모두 BLEVEL 2 · ORD_ST에 히스토그램 · 통계 최신(2018년의 단일 컬럼 인덱스는 지금 없다)

원본 SQL

sql

-- A. 고객 최근 주문 (고객센터 화면 · 하루 약 2만 번)
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID || '' = :v_cus                         -- 인덱스 타지 말 것 (2018.05 강도현)
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY ORD_DT DESC;

-- B. 미결제 주간 점검 (매시 정각 배치)
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT + 0 >= TO_DATE(:v_from, 'YYYYMMDD')    -- 인덱스 타지 말 것 (2018.05 강도현)
   AND ORD_DT + 0 <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[A — CUS_000123 · 2024-04-01 ~ 2024-06-30]
----------------------------------------------------------------------------------------
| Id  | Operation           | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |           |      1 |        |     41 |00:00:00.09 |    5826 |
|   1 |  SORT ORDER BY      |           |      1 |    831 |     41 |00:00:00.09 |    5826 |
|*  2 |   FILTER            |           |      1 |        |     41 |00:00:00.09 |    5826 |
|*  3 |    TABLE ACCESS FULL| T_ORD_BIG |      1 |    831 |     41 |00:00:00.09 |    5826 |
----------------------------------------------------------------------------------------
   2 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - filter(("CUS_ID"||''=:V_CUS AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND
              "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1))

[B — 2024-06-03 ~ 2024-06-09]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |    731 |00:00:00.02 |    1046 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_BIG        |      1 |    400 |    731 |00:00:00.02 |    1046 |
|*  2 |   INDEX RANGE SCAN                   | IX_ORD_BIG_ST_DT |      1 |    400 |    731 |00:00:00.01 |     315 |
-----------------------------------------------------------------------------------------------------------------
   2 - access("ORD_ST"='PENDING')
       filter(("ORD_DT"+0>=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"+0<TO_DATE(:V_TO,'YYYYMMDD')+1))

문제

A와 B에 붙은 || ''와 + 0이 지금 인덱스에서 각각 무엇을 막고 있는지 실행계획의 access와 filter로 설명하시오. 같은 종류의 가공인데 손해의 크기가 크게 다른 이유도 설명하시오.
2018년에 이 가공을 넣은 이유가 지금도 유효한지 따지고, 두 SQL을 고치시오.
패키지의 다른 SQL에도 같은 장치가 남아 있는지 찾는 방법과, 떼어 낸 뒤 확인할 것을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- A. 억제 가공을 뗀다 → (CUS_ID, ORD_DT) 두 컬럼 다 access · 역순으로 읽어 정렬도 없다
SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG
 WHERE CUS_ID = :v_cus
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1
 ORDER BY ORD_DT DESC;

-- B. 억제 가공을 뗀다 → (ORD_ST, ORD_DT) 두 컬럼 다 access
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_ST = 'PENDING'
   AND ORD_DT >= TO_DATE(:v_from, 'YYYYMMDD')
   AND ORD_DT <  TO_DATE(:v_to, 'YYYYMMDD') + 1;

-- 패키지에 같은 장치가 더 있는지 찾는다 (문자에 빈 문자열 잇기, 숫자 · 날짜에 0 더하기)
SELECT name, line, text
  FROM USER_SOURCE
 WHERE name = 'PKG_ORD_MON'
   AND REGEXP_LIKE(text, '[|]{2}\s*''''|[+-]\s*0\b');

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로

xplan

[A — 고친 뒤]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |     41 |00:00:00.01 |      44 |
|*  1 |  FILTER                       |                   |      1 |        |     41 |00:00:00.01 |      44 |
|   2 |   TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |     42 |     41 |00:00:00.01 |      44 |
|*  3 |    INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_CUS_DT |      1 |     42 |     41 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("CUS_ID"=:V_CUS AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1 AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD'))

[B — 고친 뒤]
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                  |      1 |        |    731 |00:00:00.01 |     736 |
|*  1 |  FILTER                              |                  |      1 |        |    731 |00:00:00.01 |     736 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG        |      1 |    511 |    731 |00:00:00.01 |     736 |
|*  3 |    INDEX RANGE SCAN                  | IX_ORD_BIG_ST_DT |      1 |    511 |    731 |00:00:00.01 |       5 |
-----------------------------------------------------------------------------------------------------------------
   1 - filter(TO_DATE(:V_TO,'YYYYMMDD')+1>TO_DATE(:V_FROM,'YYYYMMDD'))
   3 - access("ORD_ST"='PENDING' AND "ORD_DT">=TO_DATE(:V_FROM,'YYYYMMDD') AND "ORD_DT"<TO_DATE(:V_TO,'YYYYMMDD')+1)

모범 답안 서술

A의 CUS_ID || ''는 값으로는 CUS_ID와 같지만 옵티마이저에게는 컬럼을 가공한 식이다. IX_ORD_BIG_CUS_DT의 선두 컬럼 CUS_ID가 access를 잃어 고객 하나를 찾아가지 못하고, 3번 줄처럼 테이블을 다 읽은 뒤 41건만 남긴다(Buffers 5,826). B의 ORD_DT + 0은 IX_ORD_BIG_ST_DT의 두 번째 컬럼을 가공했다. 선두 컬럼 조건 ORD_ST = 'PENDING'은 여전히 access라 인덱스는 탄다. 다만 두 번째 컬럼 ORD_DT가 filter로 밀려나 PENDING 항목 8만 개(리프 약 310블록)를 다 훑으며 그 주의 731건을 고른다(Buffers 1,046). 같은 억제 가공이라도 선두 컬럼에 붙으면 인덱스 범위 자체가 사라지고, 뒤 컬럼에 붙으면 인덱스 안에서 거르는 양만 늘어난다. 그래서 손해의 크기가 다르다. 이 가공은 실수가 아니라 2018년에 일부러 넣은 계획 유도 장치다. 그때는 단일 컬럼 인덱스 셋뿐이라, 고객 인덱스를 막아 주문일 인덱스를 쓰게 하거나 주문일 인덱스를 막아 상태 인덱스를 쓰게 할 이유가 있었다. 지금은 두 조건을 함께 담은 결합 인덱스가 있고, 고객마다 주문이 500건으로 고르며 대량 고객도 없다. 막을 대상이던 단일 인덱스도 지금은 없다. 이유가 사라졌으므로 두 장치를 뗀다. A는 CUS_ID = :v_cus로 두 컬럼이 다 access가 되어 그 고객의 석 달 치만 역순으로 읽고 정렬도 없다(5,826 → 44). 하루 2만 번이면 약 1억 1,650만 → 88만 블록이다. B는 ORD_DT까지 access가 되어 리프 몇 블록만 읽는다(1,046 → 736). 남은 736블록은 흩어진 731행을 테이블에서 읽는 몫이라, 더 줄이려면 인덱스에 컬럼을 더하는 문제가 된다. 같은 장치가 더 있는지는 USER_SOURCE(또는 DBA_SOURCE)에서 빈 문자열 잇기와 + 0 모양을 정규식으로 찾고, V$SQL의 SQL 문장에서도 같은 모양을 찾는다. 떼어 낸 뒤에는 바꾼 SQL마다 실행계획과 Buffers를 전후로 비교하고, 다른 바인드 값(다른 고객, 다른 주간)으로도 계획이 흔들리지 않는지 확인한다. 그래도 계획을 고정해야 하는 SQL이 남는다면, 보이지 않는 가공 대신 이유와 날짜를 적은 힌트로 바꿔 힌트 유효기간 대장에 올린다.

해설

함정: 주석만 보고 '인덱스 타지 말 것'을 지키면 결합 인덱스를 만든 의미가 없다. 반대로 이유를 따지지 않고 다 떼면 그때의 문제가 돌아올 수 있다. 회의록의 이유(대량 고객, 큰 범위)가 지금 데이터에도 있는지 숫자로 확인하고 뗀다.
문자 컬럼에 빈 문자열 잇기, 숫자와 날짜 컬럼에 0 더하기는 규칙 기반 옵티마이저 시절부터 내려온 인덱스 억제 관용구다. 지금은 NO_INDEX, INDEX, FULL 같은 힌트로 같은 일을 드러내 놓고 할 수 있다. 힌트도 유효기간이 있으므로 남길 때는 이유와 규모를 적는다(교재 06화).
옵티마이저가 A에서 FULL 대신 IX_ORD_BIG_CUS_DT의 INDEX SKIP SCAN을 고를 수도 있다(고객 2,000명마다 건너뛰며 2천 블록 남짓). 어느 쪽이든 선두 컬럼 access를 잃은 것은 같다.
B의 남은 736블록을 줄이려면 (ORD_ST, ORD_DT) 뒤에 조회 컬럼을 붙인 커버링 인덱스가 필요하다. 매시 한 번 도는 배치를 위해 인덱스를 키울지는 ACC 단원에서 다루는 판단이다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 스크립트 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황