POS 개편 전 점검에서 판매 SQL 넷 중 셋이 판매 전체나 인덱스 전체를 읽는다

실전DDL유통19 / 700

상황

유통 POS 개편을 앞두고 박팀장이 판매(CF10)를 읽는 운영 SQL 넷을 넘기며 말했다. "인덱스는 하나만 더 늘릴 수 있다. 가능하면 늘리지 말고. SQL마다 무엇을 고칠지, 인덱스는 무엇으로 할지 정리해 와." 조회 빈도는 각 SQL 위의 주석에 적혀 있다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510, 블록당 약 109행) · 모든 컬럼에 NOT NULL 제약
SLE_NO VARCHAR2(15) '20230601-0001'(앞 8자리가 SLE_DT와 전부 같다, 공백이 든 값 0건) · SLE_DT VARCHAR2(8) · SHP_NO NUMBER(5) 1001 ~ 1005
분포: 하루 596 ~ 793건(2023-06-01은 706건, 그중 매장 1003은 146건) · 2023-06-05 ~ 06-11은 4,919건 · 2023년 6월 20,953건(매장마다 4,157 ~ 4,234건) · 하루치 판매는 테이블 약 26블록 안에 모여 있다
인덱스 CF10_PK (SLE_NO): BLEVEL 2 · 리프 약 1,680 / IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CF 9,921 · 통계 최신

원본 SQL

sql

-- S1 매장 「오늘 영수증 목록」: 매장 POS 5대가 수시로 (하루 약 2,000번)
SELECT SLE_NO, SLE_HMS
  FROM CF10
 WHERE TO_CHAR(SHP_NO) = :v_shp
   AND SUBSTR(SLE_NO, 1, 8) = :v_dt
 ORDER BY SLE_NO;

-- S2 본사 「주간 일별 건수」: 앱이 날짜를 숫자(20230605)로 넘긴다 (하루 약 300번)
SELECT SLE_DT, COUNT(*)
  FROM CF10
 WHERE TO_NUMBER(SLE_DT) BETWEEN :n_from AND :n_to
 GROUP BY SLE_DT;

-- S3 고객센터 「영수증 조회」: 상담원이 붙여 넣은 번호에 공백이 섞여 온다 (하루 약 1,500번)
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10
 WHERE TRIM(SLE_NO) = :v_no;

-- S4 본사 「월 매장별 건수」 (하루 약 200번)
SELECT SHP_NO, COUNT(*)
  FROM CF10
 WHERE SLE_DT LIKE :v_ym || '%'
 GROUP BY SHP_NO;

실행계획 학습용 예시 · 건수는 적재 데이터 그대로

xplan

[S1 — 매장 1003 · 2023-06-01]
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |    146 |00:00:00.05 |    4510 |
|   1 |  SORT ORDER BY     |      |      1 |     50 |    146 |00:00:00.05 |    4510 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |     50 |    146 |00:00:00.05 |    4510 |
-----------------------------------------------------------------------------------
   2 - filter((TO_CHAR("SHP_NO")=:V_SHP AND SUBSTR("SLE_NO",1,8)=:V_DT))

[S2 — 2023-06-05 ~ 06-11]
-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |                |      1 |        |      7 |00:00:00.04 |    1406 |
|   1 |  HASH GROUP BY        |                |      1 |    730 |      7 |00:00:00.04 |    1406 |
|*  2 |   INDEX FAST FULL SCAN| IX_CF10_SLE_DT |      1 |   1250 |   4919 |00:00:00.03 |    1406 |
-----------------------------------------------------------------------------------------------
   2 - filter((TO_NUMBER("SLE_DT")>=:N_FROM AND TO_NUMBER("SLE_DT")<=:N_TO))

[S3]
-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.04 |    4510 |
|*  1 |  TABLE ACCESS FULL | CF10 |      1 |   5000 |      1 |00:00:00.04 |    4510 |
-----------------------------------------------------------------------------------
   1 - filter(TRIM("SLE_NO")=:V_NO)

[S4 — 2023-06]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.02 |     478 |
|   1 |  HASH GROUP BY                       |                |      1 |      5 |      5 |00:00:00.02 |     478 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    693 |  20953 |00:00:00.02 |     478 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_SLE_DT |      1 |    693 |  20953 |00:00:00.01 |      62 |
-------------------------------------------------------------------------------------------------------------
   3 - access("SLE_DT" LIKE :V_YM||'%')
       filter("SLE_DT" LIKE :V_YM||'%')

문제

S1 ~ S4 각각에서 컬럼이 어떻게 가공되었고 그 때문에 실행계획이 어떻게 되었는지 설명하시오. 고칠 필요가 없는 SQL이 있다면 그 이유도 설명하시오.
결과가 바뀌지 않게 SQL을 고치고, 인덱스를 늘리지 않거나 하나만 바꿔 네 SQL을 받치는 인덱스 설계를 DDL로 제안하시오.
제안한 인덱스 변경의 대가와 반영 전에 확인할 것을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- S1 : 숫자 컬럼은 숫자로 비교하고, 판매일자는 판매번호를 자르지 말고 SLE_DT로 (가공은 바인드 쪽)
SELECT SLE_NO, SLE_HMS
  FROM CF10
 WHERE SHP_NO = TO_NUMBER(:v_shp)
   AND SLE_DT = :v_dt
 ORDER BY SLE_NO;

-- S2 : 숫자 바인드를 문자로 바꿔 비교 (SLE_DT는 길이 8 고정이라 문자 순서 = 날짜 순서)
SELECT SLE_DT, COUNT(*)
  FROM CF10
 WHERE SLE_DT BETWEEN TO_CHAR(:n_from) AND TO_CHAR(:n_to)
 GROUP BY SLE_DT;

-- S3 : 공백을 지우는 일은 입력값 쪽에서
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10
 WHERE SLE_NO = TRIM(:v_no);

-- S4 : 그대로 둔다 (선두가 정해진 LIKE, 컬럼 가공 없음)

-- 인덱스 : (SLE_DT)를 (SLE_DT, SHP_NO)로 바꾼다 (개수는 그대로)
CREATE INDEX IX_CF10_DT_SHP ON CF10 (SLE_DT, SHP_NO) INVISIBLE;
ALTER SESSION SET OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE;   -- 내 세션에서 S1 · S2 · S4를 잰다
ALTER INDEX IX_CF10_DT_SHP VISIBLE;
ALTER INDEX IX_CF10_SLE_DT INVISIBLE;                       -- 다른 SQL이 견디는지 한동안 지켜본다
-- 이상이 없으면 DROP INDEX IX_CF10_SLE_DT;

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터 그대로

xplan

[S1 — 고친 뒤]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |    146 |00:00:00.01 |      27 |
|   1 |  SORT ORDER BY                       |                |      1 |    141 |    146 |00:00:00.01 |      27 |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| CF10           |      1 |    141 |    146 |00:00:00.01 |      27 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_DT_SHP |      1 |    141 |    146 |00:00:00.01 |       3 |
-------------------------------------------------------------------------------------------------------------
   3 - access("SLE_DT"=:V_DT AND "SHP_NO"=TO_NUMBER(:V_SHP))

[S2 — 고친 뒤]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      7 |00:00:00.01 |      19 |
|   1 |  SORT GROUP BY NOSORT                |                |      1 |      7 |      7 |00:00:00.01 |      19 |
|*  2 |   FILTER                             |                |      1 |        |   4919 |00:00:00.01 |      19 |
|*  3 |    INDEX RANGE SCAN                  | IX_CF10_DT_SHP |      1 |   4919 |   4919 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------------------------------
   2 - filter(TO_CHAR(:N_FROM)<=TO_CHAR(:N_TO))
   3 - access("SLE_DT">=TO_CHAR(:N_FROM) AND "SLE_DT"<=TO_CHAR(:N_TO))

[S3 — 고친 뒤]  INDEX UNIQUE SCAN CF10_PK · access("SLE_NO"=TRIM(:V_NO)) · Buffers 4

[S4 — 인덱스를 바꾼 뒤, SQL은 그대로]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                |      1 |        |      5 |00:00:00.01 |      67 |
|   1 |  HASH GROUP BY                       |                |      1 |      5 |      5 |00:00:00.01 |      67 |
|*  2 |   INDEX RANGE SCAN                   | IX_CF10_DT_SHP |      1 |    693 |  20953 |00:00:00.01 |      67 |
-------------------------------------------------------------------------------------------------------------
   2 - access("SLE_DT" LIKE :V_YM||'%')
       filter("SLE_DT" LIKE :V_YM||'%')

모범 답안 서술

S1은 숫자 컬럼 SHP_NO를 TO_CHAR로, 판매번호를 SUBSTR로 가공했다. 가공한 컬럼으로는 어느 인덱스에서도 범위를 지목하지 못해 50만 행을 다 읽는다(Buffers 4,510, E-Rows는 1%를 두 번 곱한 50). 두 가공 모두 바인드 쪽으로 옮길 수 있다. 매장 번호는 SHP_NO = TO_NUMBER(:v_shp)로, 판매일자는 판매번호를 자르지 말고 SLE_DT 컬럼으로 비교한다(판매번호 앞 8자리와 SLE_DT는 전부 같다). S2는 앱이 숫자를 넘긴다는 이유로 문자 컬럼 SLE_DT를 TO_NUMBER로 바꿨다. 인덱스에 SLE_DT가 다 있어서 테이블 대신 IX_CF10_SLE_DT를 FAST FULL SCAN으로 통째로 읽는다(1,406). SLE_DT는 길이 8로 고정이라 문자 순서와 날짜 순서가 같으므로, 바인드를 TO_CHAR로 바꿔 SLE_DT BETWEEN TO_CHAR(:n_from) AND TO_CHAR(:n_to)로 쓰면 결과는 같고 범위만 읽는다. S3는 공백이 섞인 쪽이 입력값인데 컬럼에 TRIM을 씌웠다. TRIM으로 가공한 SLE_NO로는 PK 범위를 지목하지 못해 테이블을 다 읽는다(4,510). 컬럼 값에 공백이 없다(0건)는 것을 확인했으므로 SLE_NO = TRIM(:v_no)로 바꾸면 PK로 한 건을 찾는다(4,510 → 4). S4는 고칠 필요가 없다. 선두가 정해진 LIKE라 가공 없이 IX_CF10_SLE_DT 범위를 읽고 있다. 다만 SHP_NO를 읽으러 테이블에 가는 몫이 크다(인덱스 62, 전체 478). 인덱스는 늘리지 않고 IX_CF10_SLE_DT (SLE_DT)를 (SLE_DT, SHP_NO)로 바꾼다. S1은 = 조건 둘이 다 access가 되어 그 매장 그날 행만 찾고(4,510 → 27), S2는 선두 컬럼 SLE_DT 범위를 인덱스만으로 세며(1,406 → 19), S4는 SHP_NO까지 인덱스에 있어 테이블에 가지 않는다(478 → 67). SLE_DT를 앞에 두는 이유는 S2와 S4가 판매일자 범위만 주기 때문이다. SHP_NO를 앞에 두면 S2와 S4는 선두 컬럼 조건이 없어 매장 다섯 곳을 건너뛰며 읽어야 한다. 대가는 크지 않다. 인덱스 개수는 그대로이고 키가 조금 길어질 뿐이다. 하지만 기존 (SLE_DT) 인덱스를 쓰던 다른 SQL이 있을 수 있으므로, 새 인덱스를 INVISIBLE로 만들어 세션에서 네 SQL을 재고, VISIBLE로 바꾼 뒤 기존 인덱스를 INVISIBLE로 돌려 한동안 지켜본 다음 지운다. 고친 SQL은 원래 SQL과 같은 날짜, 같은 매장으로 건수를 대조한다.

해설

함정: S1에서 SUBSTR만 떼고 SLE_NO LIKE :v_dt || '%'로 바꿔도 PK 범위로 그날 706건을 읽을 수 있다. 하지만 매장 조건을 거르러 테이블에 가야 하므로 SLE_DT와 SHP_NO를 함께 담은 인덱스보다 많이 읽는다. 같은 뜻의 컬럼이 따로 있으면 그 컬럼으로 비교한다.
S2의 TO_NUMBER는 묵시적 형변환과 결과가 같다. 문자 컬럼과 숫자를 비교하면 오라클이 컬럼 쪽에 TO_NUMBER를 씌우기 때문이다(IDX-002). 숫자를 넘기는 앱이라면 바인드 쪽에서 문자로 바꾸는 규칙을 코딩 가이드에 적는다.
S3에서 컬럼 쪽 TRIM이 정말 필요했다면(컬럼 값에 공백이 있다면) 먼저 데이터를 정리하고 입력 단계에서 막는 것이 순서다. 함수 기반 인덱스 (TRIM(SLE_NO))는 PK와 같은 내용의 인덱스를 하나 더 두는 셈이다.
인덱스를 더하는 대신 바꾸는 설계는 적재 비용을 늘리지 않는다(교재 04화 §9에서 인덱스 하나는 5만 건 적재에 redo 약 11.8MB를 더했다). 기존 인덱스가 새 인덱스의 앞부분이면 대개 바꿀 수 있다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 데이터 그대로 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터 그대로 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황