매장 판매 일지에서 세 쪽 넘게 넘긴 뒤 「이전」을 누르면 늘 첫 쪽이 나온다

중급SELECT유통336 / 700

상황

본사 영업지원팀의 「매장 판매 일지」는 매장 하나의 판매를 최근 것부터 한 쪽에 50건씩 보여 주고, 「다음」과 「이전」 버튼으로 넘긴다. 화면은 지금 쪽의 첫 판매번호와 마지막 판매번호를 들고 있다가 「다음」에는 마지막 번호를, 「이전」에는 첫 번호를 넘긴다. 팀원들은 "「다음」은 잘 넘어가는데 5쪽에서 「이전」을 누르면 4쪽이 아니라 1쪽이 나온다. 2쪽에서 누를 때만 맞다"라고 한다. 판매번호는 유일해서 쪽 경계에서 판매가 빠지거나 겹친 일은 없었다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · SLE_NO VARCHAR2(15) '20231231-0612'(판매일 + 그날 일련번호, 판매 순서대로 매긴다, 유일) · SHP_NO(판매가 있는 매장 5곳, 매장마다 약 10만 건) · SLE_DT · SLE_HMS
인덱스 CF10_PK (SLE_NO), CF10_X02 (SHP_NO, SLE_NO)(이 화면용) · CF10_X02는 BLEVEL 2 · 리프 약 1,900블록
판매는 판매번호 순서로 쌓여 있어 매장 하나의 이어진 50건은 테이블 블록 서너 개에 모여 있다. 통계는 최신이다.

원본 SQL

sql

-- 다음 : 지금 쪽 마지막 판매번호(:last_no)보다 앞선 50건
SELECT SLE_NO, SLE_DT, SLE_HMS
  FROM (SELECT SLE_NO, SLE_DT, SLE_HMS
          FROM CF10
         WHERE SHP_NO = :shp
           AND SLE_NO < :last_no
         ORDER BY SLE_NO DESC)
 WHERE ROWNUM <= 50;

-- 이전 : 지금 쪽 첫 판매번호(:first_no)보다 뒤인 50건
SELECT SLE_NO, SLE_DT, SLE_HMS
  FROM (SELECT SLE_NO, SLE_DT, SLE_HMS
          FROM CF10
         WHERE SHP_NO = :shp
           AND SLE_NO > :first_no
         ORDER BY SLE_NO DESC)
 WHERE ROWNUM <= 50;

실행계획 학습용 예시

xplan

[이전 · 5쪽에서 누름 · :shp = '1003', :first_no = '20231230-0349']
------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |          |      1 |        |     50 |00:00:00.01 |       7 |
|*  1 |  COUNT STOPKEY                  |          |      1 |        |     50 |00:00:00.01 |       7 |
|   2 |   VIEW                          |          |      1 |     50 |     50 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID  | CF10     |      1 |   5000 |     50 |00:00:00.01 |       7 |
|*  4 |     INDEX RANGE SCAN DESCENDING | CF10_X02 |      1 |     50 |     50 |00:00:00.01 |       3 |
------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=50)
   4 - access("SHP_NO"=:SHP AND "SLE_NO">:FIRST_NO)

[화면 · 1003 매장]
  1쪽   20231231-0684 ~ 20231231-0431
  4쪽   20231230-0604 ~ 20231230-0355
  5쪽   20231230-0349 ~ 20231230-0101   ← 여기서 「이전」
  결과  20231231-0684 ~ 20231231-0431   (1쪽과 같다)

문제

2쪽에서는 맞던 「이전」이 3쪽부터 늘 1쪽을 보여 주는 까닭을 실행계획 4번 줄의 조건과 읽는 방향으로 설명하시오.
화면 순서(최근 것부터)는 그대로 두고 「이전」이 바로 앞 쪽 50건을 보여 주도록 SQL을 고치시오.
고친 SQL에서 ROWNUM과 두 ORDER BY를 각각 어디에 두어야 하는지 그 까닭과 함께 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 다음 : 그대로
SELECT SLE_NO, SLE_DT, SLE_HMS
  FROM (SELECT SLE_NO, SLE_DT, SLE_HMS
          FROM CF10
         WHERE SHP_NO = :shp
           AND SLE_NO < :last_no
         ORDER BY SLE_NO DESC)
 WHERE ROWNUM <= 50;

-- 이전 : 지금 쪽 첫 판매번호 바로 뒤에서부터 오름차순으로 가까운 50건을 고르고(안쪽 두 단계),
--        맨 바깥에서 화면 순서(최근 것부터)로 뒤집는다
SELECT SLE_NO, SLE_DT, SLE_HMS
  FROM (SELECT SLE_NO, SLE_DT, SLE_HMS
          FROM (SELECT SLE_NO, SLE_DT, SLE_HMS
                  FROM CF10
                 WHERE SHP_NO = :shp
                   AND SLE_NO > :first_no
                 ORDER BY SLE_NO)
         WHERE ROWNUM <= 50)
 ORDER BY SLE_NO DESC;

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

xplan

[이전 · 5쪽에서 누름]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |          |      1 |        |     50 |00:00:00.01 |       7 |          |
|   1 |  SORT ORDER BY                   |          |      1 |     50 |     50 |00:00:00.01 |       7 | 6144  (0)|
|*  2 |   COUNT STOPKEY                  |          |      1 |        |     50 |00:00:00.01 |       7 |          |
|   3 |    VIEW                          |          |      1 |     50 |     50 |00:00:00.01 |       7 |          |
|   4 |     TABLE ACCESS BY INDEX ROWID  | CF10     |      1 |   5000 |     50 |00:00:00.01 |       7 |          |
|*  5 |      INDEX RANGE SCAN            | CF10_X02 |      1 |     50 |     50 |00:00:00.01 |       3 |          |
------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(ROWNUM<=50)
   5 - access("SHP_NO"=:SHP AND "SLE_NO">:FIRST_NO)

[결과]  20231230-0604 ~ 20231230-0355 (4쪽과 같다)

모범 답안 서술

「이전」의 조건 SLE_NO > :first_no에 맞는 판매는 '지금 쪽보다 뒤에 들어온 판매 전부'다. 4번 줄은 이 범위를 CF10_X02에서 큰 번호부터(INDEX RANGE SCAN DESCENDING) 읽고 1번 줄이 50건에서 멈추므로 가장 최근 50건, 곧 1쪽이 나온다. 5쪽에서 누르면 범위가 1 ~ 4쪽 200건인데 그 맨 위 50건을 읽은 셈이다. 2쪽에서 누를 때만 맞았던 것은 그때 범위가 1쪽 50건뿐이라 1쪽이 곧 바로 앞 쪽이었기 때문이다. 판매번호가 유일해서 정렬 키에는 문제가 없고, 틀린 것은 읽는 방향이다. 바로 앞 쪽은 지금 쪽 첫 줄에 가장 가까운 50건이다. 가까운 것부터 읽으려면 범위를 작은 번호부터(오름차순) 읽어야 한다. 그래서 가장 안쪽에서 ORDER BY SLE_NO로 오름차순을 정하고, 그 바깥에서 ROWNUM <= 50으로 자른 뒤, 맨 바깥에서 ORDER BY SLE_NO DESC로 화면 순서로 뒤집는다. ROWNUM은 정렬보다 먼저 매겨지므로 오름차순 ORDER BY와 같은 쿼리 블록에 두면 정렬 전에 아무 50건이 골라진다. 정렬을 담은 인라인 뷰 바깥에 두어야 한다. 고친 「이전」은 5번 줄이 인덱스를 오름차순으로 50건 읽고 멈추며(COUNT STOPKEY), 1번 줄 SORT ORDER BY는 그 50건만 뒤집으므로 작업 공간 6KB 남짓이다. Buffers는 7로 「다음」과 같고, 결과는 4쪽과 같다. 1쪽 가까이에서는 앞에 남은 판매가 50건이 안 될 수 있다(그사이 새 판매가 들어오면 1쪽 경계도 움직인다). 「이전」 결과가 50건보다 적으면 1쪽 SQL로 다시 읽어 첫 쪽을 채운다.

해설

앱이 키를 쌓아 두는 답: 지나온 쪽마다 마지막 판매번호를 앱이 쌓아 두었다가 「이전」에 꺼내 쓰면 「다음」과 같은 SQL 하나로 된다(교재 09화 §9). 새로 고침이나 링크 공유로 쌓아 둔 것이 사라지는 화면이면 위처럼 역방향 SQL을 둔다.
FETCH FIRST로 써도 같다: 안쪽을 ORDER BY SLE_NO FETCH FIRST 50 ROWS ONLY로 쓰고 바깥에서 ORDER BY SLE_NO DESC로 뒤집어도 된다. 바깥 정렬을 빼면 화면에 오래된 것부터 나온다.
「이전」에서 51건을 읽으면 51번째로 '더 앞 쪽이 있는가'를 알 수 있어 「이전」 버튼을 끌 때 쓴다.
정렬 키가 유일하지 않으면 방향을 고쳐도 경계에서 판매가 빠지거나 겹친다. 그때는 정렬 키 끝에 PK를 붙인 뒤 방향을 다룬다(SORT-021, SORT-022).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황