판매 검색 목록은 바로 뜨는데 「총 n건」 칸만 늘 한 박자 늦게 채워진다

기초SELECT유통331 / 700

상황

본사 매장관리팀의 「판매 검색」은 매장과 기간을 넣으면 최근 판매부터 한 쪽에 20건을 보여 주고, 위에 「총 n건」을 띄운다. 기간의 기본값이 최근 1년이라 대부분 그대로 검색한다. 화면은 목록 SQL과 건수 SQL을 따로 보내는데, 목록은 바로 뜨고 건수 칸만 늦게 채워진다. 검색은 하루 약 6,000번이다. 매장관리팀장에게 물으니 건수는 "검색 조건이 너무 넓은지 가늠하는 데" 쓴다. 1,000건이 넘으면 조건을 좁히라고 안내하고 있다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_DT VARCHAR2(8) 2022-01-01 ~ 2023-12-31(하루 약 685건, 날짜순으로 쌓여 있다) · SHP_NO 매장 6곳(고르게 나뉜다)
인덱스: CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT)(리프 1,393블록, 한 블록에 약 360개)
최근 1년의 한 매장 판매는 약 41,700건이다. 통계는 최신이다.

원본 SQL

sql

-- 목록 : 매장 하나 · 최근 1년 · 최신 20건
SELECT SLE_NO, SLE_DT, SLE_HMS
  FROM (SELECT SLE_NO, SLE_DT, SLE_HMS
          FROM CF10
         WHERE SHP_NO = :shp
           AND SLE_DT BETWEEN :from_dt AND :to_dt
         ORDER BY SLE_NO DESC)
 WHERE ROWNUM <= 20;

-- 총건수
SELECT COUNT(*)
  FROM CF10
 WHERE SHP_NO = :shp
   AND SLE_DT BETWEEN :from_dt AND :to_dt;

실행계획 학습용 예시

xplan

[목록]
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |     20 |00:00:00.01 |       5 |
|*  1 |  COUNT STOPKEY                 |         |      1 |        |     20 |00:00:00.01 |       5 |
|   2 |   VIEW                         |         |      1 |     20 |     20 |00:00:00.01 |       5 |
|*  3 |    TABLE ACCESS BY INDEX ROWID | CF10    |      1 |  41667 |     20 |00:00:00.01 |       5 |
|   4 |     INDEX FULL SCAN DESCENDING | CF10_PK |      1 |    120 |    121 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------

[총건수]
---------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |        |      1 |00:00:00.06 |    4583 |
|   1 |  SORT AGGREGATE                |         |      1 |      1 |      1 |00:00:00.06 |    4583 |
|*  2 |   FILTER                       |         |      1 |        |  41682 |00:00:00.06 |    4583 |
|*  3 |    TABLE ACCESS FULL           | CF10    |      1 |  41667 |  41682 |00:00:00.06 |    4583 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  [목록]   1 - filter(ROWNUM<=20)
           3 - filter(("SHP_NO"=:SHP AND "SLE_DT">=:FROM_DT AND "SLE_DT"<=:TO_DT))
  [총건수] 2 - filter(:FROM_DT<=:TO_DT)
           3 - filter(("SHP_NO"=:SHP AND "SLE_DT">=:FROM_DT AND "SLE_DT"<=:TO_DT))

문제

목록은 5블록인데 건수 SQL은 왜 4,583블록을 읽는지 두 SQL의 차이로 설명하시오.
건수의 쓰임새에 맞게 건수 SQL을 고치고, 고친 SQL이 몇 블록을 읽을지와 그렇게 읽으려면 무엇을 확인해야 하는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 목록 SQL 은 그대로 둔다 (CF10_PK 역순으로 읽다가 20건에서 멈춘다 · 5블록)

-- 총건수 : 1,001건까지만 센다 → 1,001 이면 화면에 '1,000건 이상' 으로 보인다
--   (FULL 로 세면 테이블 앞쪽의 2022년 블록을 헛읽은 뒤에야 맞는 행이 나오므로 기간 인덱스로 시작점을 지목한다)
SELECT /*+ INDEX(c IX_CF10_SLE_DT) */ COUNT(*)
  FROM CF10 c
 WHERE c.SHP_NO = :shp
   AND c.SLE_DT BETWEEN :from_dt AND :to_dt
   AND ROWNUM <= 1001;

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

xplan

[총건수 · 상한 1,001건]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |      1 |        |      1 |00:00:00.01 |      73 |
|   1 |  SORT AGGREGATE                         |                |      1 |      1 |      1 |00:00:00.01 |      73 |
|*  2 |   COUNT STOPKEY                         |                |      1 |        |   1001 |00:00:00.01 |      73 |
|*  3 |    FILTER                               |                |      1 |        |   1001 |00:00:00.01 |      73 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED | CF10           |      1 |   1001 |   1001 |00:00:00.01 |      73 |
|*  5 |      INDEX RANGE SCAN                   | IX_CF10_SLE_DT |      1 |   6006 |   6011 |00:00:00.01 |      19 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=1001)
   3 - filter(:FROM_DT<=:TO_DT)
   4 - filter("C"."SHP_NO"=:SHP)
   5 - access("C"."SLE_DT">=:FROM_DT AND "C"."SLE_DT"<=:TO_DT)

모범 답안 서술

목록은 CF10_PK를 역순으로 읽으며 매장과 기간을 확인하다가 20건에서 멈춘다(5블록). 건수 SQL에는 멈출 곳이 없다. 조건에 맞는 판매를 빠짐없이 세야 하므로 최근 1년의 한 매장 판매 41,682건을 다 찾아야 하고, 기간이 테이블의 절반이라 옵티마이저는 CF10 전체(4,583블록)를 읽는다. 검색 한 번에 건수가 목록의 900배를 읽고, 하루 6,000번이면 건수에만 약 2,750만 블록이다. 건수의 쓰임새는 "1,000건이 넘는가"다. 그렇다면 1,001건까지만 세고 멈추면 된다. ROWNUM <= 1001을 넣으면 COUNT STOPKEY가 1,001건에서 멈추고, 1,001이 나오면 화면에 "1,000건 이상"으로 보여 준다. 기간 인덱스로 시작점을 지목하면 판매 약 6,000건을 지나 1,001건을 채우므로 4,583 → 73블록이다. 기간이 짧아 1,001건이 안 되는 검색은 정확한 건수가 그대로 나온다. 상한만 넣고 FULL에 맡기면 안 된다. 테이블이 날짜순으로 쌓여 있어 FULL은 앞쪽의 2022년 블록(약 2,300블록)을 헛읽은 뒤에야 맞는 행을 만난다. 그래서 실행계획이 IX_CF10_SLE_DT 범위 스캔으로 시작하는지 확인하고, 이 문제에서는 힌트로 정했다.

해설

정확한 건수가 꼭 필요하다면 (SHP_NO, SLE_DT) 인덱스로 인덱스만 세게 할 수 있다. 최근 1년이면 리프 약 120블록이지만 기간이 넓어질수록 그만큼 늘고, 판매가 들어올 때마다 고칠 인덱스가 하나 는다(교재 04화 §9). 요구가 "넘는가"라면 상한이 더 싸고 오래 간다.
숫자를 아예 없애는 답: 목록을 21건 가져와 21번째가 있으면 「다음」 버튼만 켠다(교재 09화 §10의 ④). 매장관리팀이 건수를 조건이 넓은지 가늠하는 데 쓰므로 이 문제에서는 상한을 택했다.
통계의 행 수(USER_TABLES.NUM_ROWS)는 테이블 전체의 근사치라서 매장과 기간이 붙은 검색에는 쓸 수 없다.
「총 n건」이 생긴 사정(누가 무엇에 쓰는지)을 화면 요구 재협상서에 남겨 두면, 나중에 기능이 바뀔 때 다시 물을 근거가 된다(교재 09화 §13).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황