이상 판매 점검의 「수량 상위 1%」는 인덱스를 끝까지 읽고, 「수량 상위 20줄」에는 1,399줄이 뜬다

중급SELECT유통339 / 700

상황

본사 감사팀의 「이상 판매 점검」 화면에는 위젯이 둘 있다. 하나는 판매 수량이 큰 판매 줄의 상위 1%를 내려받는 「수량 상위 1%」이고, 다른 하나는 「수량 상위 20줄」이다. 개발자는 둘 다 FETCH FIRST로 짰고, "같은 수량은 빠짐없이 보여 달라"는 말에 20줄 위젯에는 WITH TIES를 붙였다. 점검 화면은 10분마다 새로 고친다. DBA는 1% 위젯이 정렬 순서 그대로인 인덱스가 있는데도 그 인덱스를 끝까지 읽는 것을 찾았고, 감사팀은 20줄 위젯에 1,399줄이 떠서 화면이 밀린다고 한다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · SLE_QTY NUMBER NOT NULL · CF11_PK (SLE_NO, ITM_NO)
인덱스 CF11_X03 (SLE_QTY, SLE_NO, ITM_NO): 이 점검용으로 만들었다 · 위젯이 고르는 칸이 모두 들어 있다 · BLEVEL 2 · 리프 약 4,700블록(한 블록에 약 230개)
수량은 98%가 1 ~ 3이다. 단체 주문은 10, 20, 30처럼 끊어서 들어와 같은 수량이 수백 ~ 수천 줄이다. 수량 30을 넘는 줄은 19줄이고 모두 수량이 다르다. 수량 30은 1,380줄이다.
통계는 최신이다(NUM_ROWS 1,096,708).

원본 SQL

sql

-- W1 「수량 상위 1%」 (내려받기)
SELECT SLE_NO, ITM_NO, SLE_QTY
  FROM CF11
 ORDER BY SLE_QTY DESC, SLE_NO DESC, ITM_NO DESC
 FETCH FIRST 1 PERCENT ROWS ONLY;

-- W2 「수량 상위 20줄」 (같은 수량은 빠짐없이)
SELECT SLE_NO, ITM_NO, SLE_QTY
  FROM CF11
 ORDER BY SLE_QTY DESC
 FETCH FIRST 20 ROWS WITH TIES;

실행계획 학습용 예시 · PERCENT와 WITH TIES의 계획 모양은 교재 환경 23ai에서 확인

xplan

[W1]
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |  10968 |00:00:00.93 |    4703 |       |       |          |
|*  1 |  VIEW                        |          |      1 |   1096K|  10968 |00:00:00.93 |    4703 |       |       |          |
|   2 |   WINDOW BUFFER              |          |      1 |   1096K|   1096K|00:00:00.88 |    4703 |    66M|  2958K|   59M (0)|
|   3 |    INDEX FULL SCAN DESCENDING| CF11_X03 |      1 |   1096K|   1096K|00:00:00.21 |    4703 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=CEIL("from$_subquery$_002"."rowlimit_$$_total"*1/100))

[W2]
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |   1399 |00:00:00.01 |      10 |
|*  1 |  VIEW                        |          |      1 |   1096K|   1399 |00:00:00.01 |      10 |
|*  2 |   WINDOW NOSORT STOPKEY      |          |      1 |   1096K|   1400 |00:00:00.01 |      10 |
|   3 |    INDEX FULL SCAN DESCENDING| CF11_X03 |      1 |   1096K|   1400 |00:00:00.01 |      10 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rank"<=20)
   2 - filter(RANK() OVER ( ORDER BY "SLE_QTY" DESC )<=20)

문제

W1이 정렬 순서 그대로인 인덱스를 읽으면서도 끝까지 읽는 까닭을 1번 줄의 조건과 2번 줄 WINDOW BUFFER로 설명하시오.
W2가 20줄이 아니라 1,399줄을 돌려준 까닭과, 그만큼 읽고서야 멈춘 까닭을 설명하시오.
두 위젯을 고치고, PERCENT와 WITH TIES를 각각 언제 써도 되는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- W1 : 1%가 몇 줄인지는 통계(NUM_ROWS)나 야간 집계에서 미리 구해 :n 으로 넘긴다 → 정한 줄 수만 읽고 멈춘다
--      :n = CEIL(1,096,708 × 0.01) = 10,968
--      (SELECT CEIL(NUM_ROWS * 0.01) FROM USER_TABLES WHERE TABLE_NAME = 'CF11')
SELECT SLE_NO, ITM_NO, SLE_QTY
  FROM CF11
 ORDER BY SLE_QTY DESC, SLE_NO DESC, ITM_NO DESC
 FETCH FIRST :n ROWS ONLY;

-- W2 : 화면은 20줄 · 같은 수량 안에서는 판매번호 역순으로 순서를 정한다
SELECT SLE_NO, ITM_NO, SLE_QTY
  FROM CF11
 ORDER BY SLE_QTY DESC, SLE_NO DESC, ITM_NO DESC
 FETCH FIRST 20 ROWS ONLY;

-- W2 아래 한 줄 : 20번째 줄과 같은 수량이 모두 몇 줄인지 알린다 (:q20 = 20번째 줄의 수량)
SELECT COUNT(*) AS tie_cnt
  FROM CF11
 WHERE SLE_QTY = :q20;

개선 후 실행계획 학습용 예시 · PERCENT와 WITH TIES의 계획 모양은 교재 환경 23ai에서 확인

xplan

[W1 · :n = 10,968]
----------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |      1 |        |  10968 |00:00:00.02 |      51 |
|*  1 |  COUNT STOPKEY               |          |      1 |        |  10968 |00:00:00.02 |      51 |
|   2 |   VIEW                       |          |      1 |  10968 |  10968 |00:00:00.02 |      51 |
|   3 |    INDEX FULL SCAN DESCENDING| CF11_X03 |      1 |   1096K|  10968 |00:00:00.01 |      51 |
----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=:N)

[W2]  COUNT STOPKEY · INDEX FULL SCAN DESCENDING · A-Rows 20 · Buffers 3
[동점 건수]  SORT AGGREGATE · INDEX RANGE SCAN CF11_X03 (access "SLE_QTY"=:Q20) · Buffers 8

모범 답안 서술

W1의 1번 줄 조건은 rowlimit_$$_rownumber <= CEIL(rowlimit_$$_total * 1/100)이다. 1%가 몇 줄인지는 전체 줄 수(rowlimit_$$_total)를 알아야 정해지므로, 오라클은 마지막 줄까지 읽어 세기 전에는 어디서 멈출지 모른다. 그래서 STOPKEY가 붙지 않고, 2번 줄 WINDOW BUFFER가 CF11_X03을 끝까지 읽은 110만 줄을 메모리에 쌓아 둔 채(59M) 전체 수를 센 뒤에야 앞의 10,968줄을 내보낸다. 인덱스가 정렬 순서 그대로라 정렬은 없지만, 읽는 양(4,703블록)과 메모리는 줄지 않는다. W2의 WITH TIES는 20번째 줄과 값이 같은 줄을 모두 함께 돌려준다. 수량 30을 넘는 줄이 19줄이라 20번째 줄은 수량 30의 첫 줄이고, 수량 30은 1,380줄이다. 그래서 19 + 1,380 = 1,399줄이 나왔다. 2번 줄 WINDOW NOSORT STOPKEY는 순위(RANK)로 거르므로, 동점이 끝나 수량이 바뀌는 1,400번째 줄을 보고서야 멈춘다. 이 데이터에서는 읽는 양이 10블록으로 작지만, 동점이 몇 줄일지는 데이터가 정하므로 화면에 뜨는 줄 수도 정해지지 않는다. W1은 1%의 줄 수를 미리 구해 FETCH FIRST :n ROWS ONLY로 넘긴다. 줄 수는 통계(NUM_ROWS)나 야간 집계로 구하면 되고, 점검용이라 하루 사이에 늘어난 판매 몇백 줄의 차이는 받아들일 수 있다. 그러면 COUNT STOPKEY가 10,968번째 줄에서 멈춘다(Buffers 4,703 → 51, 메모리 59M → 0). W2는 정렬 키 끝에 판매번호를 붙여 동점의 순서를 정하고 ONLY로 20줄만 보여 준다. 같은 수량이 더 있다는 사실은 20번째 줄의 수량으로 센 건수(인덱스 범위 8블록)로 아래에 알린다. PERCENT는 전체 수를 세야 하므로 큰 표에 쓰면 언제나 끝까지 읽는다. 이미 묶어 줄인 작은 결과(예: 상품 88개의 판매 합계) 위에서나 쓴다. WITH TIES는 동점을 모두 보여야 하는 요구(공동 순위 시상)가 있고 동점이 작을 때 쓴다. 수량처럼 같은 값이 수천 줄인 키에는 맞지 않는다.

해설

1%를 SQL에서 세고 싶다면: COUNT(*)를 따로 세는 것은 CF11_X03을 FAST FULL SCAN으로 읽는 일이라 4,700블록이 그대로 든다. 10분마다 세지 말고 하루 한 번 세어 둔다.
기준을 수량으로 바꾸는 답: 감사팀이 보려는 것이 '단체 주문'이라면 WHERE SLE_QTY >= 10처럼 수량 기준으로 바꾸는 편이 뜻도 분명하고 인덱스 범위만 읽는다. 1%라는 기준이 어디서 왔는지 먼저 묻는다(교재 09화 §13).
WITH TIES의 STOPKEY는 순위로 거르는데, 교재 환경(23ai)에서 재 보면 서로 다른 값이 N개 나올 때까지 읽고 멈추는 모양이었다. 날짜처럼 값의 종류가 적은 키에 FETCH FIRST 10 ROWS WITH TIES를 걸면 마지막 날 하루치를 돌려주면서 열흘 치를 읽었다. 동점이 클 수 있는 키라면 A-Rows로 읽은 양을 꼭 확인한다.
OFFSET과 함께 쓰는 FETCH도 앞의 줄을 읽고 버리는 것은 ROWNUM 페이징과 같다(SORT-019).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · PERCENT와 WITH TIES의 계획 모양은 교재 환경 23ai에서 확인 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · PERCENT와 WITH TIES의 계획 모양은 교재 환경 23ai에서 확인 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황