매장 일별 영수증 목록의 순번이 1이 아니라 38만부터 찍히고, 화면도 느리다

기초DDL유통281 / 700

상황

본사 레거시의 「매장 일별 영수증 목록」은 매장과 날짜를 고르면 그날 영수증을 시간순으로 보여 주고, 맨 앞 칸에 순번을 찍는다. 점장 여섯 명과 영업팀이 하루 2,000번쯤 연다. 명동점 점장이 문의를 보냈다. "순번이 1부터가 아니라 384,207부터 시작하고, 번호가 중간중간 건너뛴다." 화면도 한 번에 0.1초씩 걸린다. 순번은 목록 화면들이 함께 쓰는 공통 뷰 V_SLE_LIST에서 만든다.

테이블과 인덱스

CF10 판매(영수증): 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) · 판매일자 순으로 쌓여 있어 하루치(약 700건)가 7블록 안팎에 모여 있다 · 매장 6곳
인덱스 CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT): 리프 1,393블록 · 높이 3
2023-06-01 영수증 706건, 그중 명동점(SHP_NO 1003) 121건 · 통계 최신

원본 SQL

sql

-- 공통 뷰: 목록 화면들이 순번 칸(SEQ_NO)을 여기서 가져간다
CREATE OR REPLACE VIEW V_SLE_LIST AS
SELECT ROWNUM AS SEQ_NO,
       SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10;

-- 매장 일별 영수증 목록
SELECT SEQ_NO, SLE_NO, SLE_HMS
  FROM V_SLE_LIST
 WHERE SHP_NO = :shp
   AND SLE_DT = :dt
 ORDER BY SLE_HMS;

실행계획 학습용 예시

xplan

---------------------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |    121 |00:00:00.11 |    4583 |          |
|   1 |  SORT ORDER BY       |            |      1 |    118 |    121 |00:00:00.11 |    4583 |13312  (0)|
|*  2 |   VIEW               | V_SLE_LIST |      1 |    118 |    121 |00:00:00.11 |    4583 |          |
|   3 |    COUNT             |            |      1 |        |    500K|00:00:00.06 |    4583 |          |
|   4 |     TABLE ACCESS FULL| CF10       |      1 |    500K|    500K|00:00:00.03 |    4583 |          |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("SLE_DT"=:DT AND "SHP_NO"=:SHP))

[화면에 찍힌 순번 · :shp = 1003, :dt = '20230601']
  384,207 · 384,209 · 384,214 · 384,215 · … · 384,912   (121줄)

문제

순번이 384,207부터 건너뛰며 찍힌 이유와, 인덱스가 있는데도 4번 줄에서 테이블 전체를 읽은 이유를 실행계획의 줄 번호로 설명하시오.
뷰 정의와 화면 SQL을 고쳐 순번이 1부터 시간순으로 찍히고 그날 영수증만 읽도록 하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 공통 뷰: 번호를 매기지 않는다 (조건이 뷰 안으로 들어갈 수 있게)
CREATE OR REPLACE VIEW V_SLE_LIST AS
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10;

-- 화면: 거르고 정렬한 결과에 번호를 매긴다
SELECT ROWNUM AS SEQ_NO, a.SLE_NO, a.SLE_HMS
  FROM (SELECT SLE_NO, SLE_HMS
          FROM V_SLE_LIST
         WHERE SHP_NO = :shp
           AND SLE_DT = :dt
         ORDER BY SLE_HMS) a;

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

xplan

------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                |      1 |        |    121 |00:00:00.01 |      12 |          |
|   1 |  COUNT                                |                |      1 |        |    121 |00:00:00.01 |      12 |          |
|   2 |   VIEW                                |                |      1 |    118 |    121 |00:00:00.01 |      12 |          |
|   3 |    SORT ORDER BY                      |                |      1 |    118 |    121 |00:00:00.01 |      12 |13312  (0)|
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED| CF10          |      1 |    118 |    121 |00:00:00.01 |      12 |          |
|*  5 |      INDEX RANGE SCAN                 | IX_CF10_SLE_DT |      1 |    706 |    706 |00:00:00.01 |       5 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("SHP_NO"=:SHP)
   5 - access("SLE_DT"=:DT)

[화면에 찍힌 순번]  1 · 2 · 3 · … · 121

모범 답안 서술

3번 줄 COUNT가 테이블 50만 행 전체에 ROWNUM을 매긴 뒤, 2번 줄 VIEW에서 매장과 날짜 조건으로 걸렀다. 순번은 '테이블에서 몇 번째로 읽힌 행인가'라서 384,207부터 시작하고, 사이에 낀 다른 매장의 영수증 번호만큼 건너뛰었다. 성능도 같은 줄 때문이다. ROWNUM은 나오는 순서대로 번호를 매긴다는 뜻이므로, 뷰를 바깥과 합치거나(뷰 머징) 바깥 조건을 뷰 안으로 밀어 넣으면(조건절 Pushing) 번호가 달라진다. 그래서 옵티마이저는 둘 다 하지 못하고, 뷰를 통째로 만든 다음 VIEW 줄에서 거른다. 날짜 조건이 4번 줄까지 내려가지 못하니 IX_CF10_SLE_DT를 쓸 수 없고 테이블 전체(4,583블록)를 읽는다. 뷰 정의에서 ROWNUM을 빼면 뷰가 화면 SQL에 합쳐지고 날짜 조건은 인덱스 access가 된다. 순번은 거르고 정렬한 인라인 뷰 바깥에서 ROWNUM으로 매긴다. 그날 영수증 706건을 인덱스로 찾고(5블록) 하루치 7블록에서 명동점 121건을 거른다. Buffers는 4,583 → 12로 주고, 순번은 1부터 121까지 시간순으로 찍힌다.

해설

다른 정답: 화면에서 ROW_NUMBER() OVER (ORDER BY SLE_HMS) AS SEQ_NO로 매겨도 된다. 어느 쪽이든 번호는 조건과 정렬이 끝난 결과에 매긴다.
뷰를 고치기 전에: 같은 뷰의 SEQ_NO를 쓰는 다른 화면을 먼저 찾는다(USER_DEPENDENCIES, 소스 검색). 그 화면들도 지금 엉뚱한 번호를 찍고 있었을 것이므로 함께 고친다.
ROWNUM이 막는 변환: 뷰 머징, 조건절 Pushing, 조인 조건 Pushdown(JPPD), 서브쿼리 Unnesting이 모두 막힌다(SUBQ-005, SUBQ-019). 계획에서 filter가 테이블이 아니라 VIEW 줄에 붙어 있으면 뷰 안에 ROWNUM이나 분석 함수가 있는지부터 본다.
번호가 맞아 보이는 날도 있다. 테이블에 행이 하나뿐인 테스트 DB에서는 순번이 1로 찍히므로 개발 단계에서 드러나지 않는다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황