판매 목록 첫 쪽 20줄을 여는 데 2.4초가 걸린다

중급SELECT유통329 / 700

상황

본사 영업관리팀의 「판매 목록」은 최근 판매부터 한 쪽에 20건을 보여 주고, 판매마다 품목 수와 판매 금액(수량 × 단가의 합)을 붙인다. 영업관리팀 30명이 하루 약 4,000번 여는데 첫 쪽부터 2.4초가 걸린다. 판매번호는 판매일자와 그날의 순번으로 매겨서 판매번호 역순이 곧 최신순이다(교재 09화에서 재서 확인한 전제다).

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록 · CF10_PK (SLE_NO) · 판매번호 순서와 쌓인 순서가 거의 같다.
CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · 판매당 평균 2.2품목 · 한 판매의 품목 줄은 같은 블록에 모여 있다.
판매상품이 하나도 없는 판매는 지금 0건이다(판매 저장 프로시저가 두 테이블을 한 트랜잭션으로 넣는다). 둘 사이에 FK는 없다.
통계는 최신이다. PGA가 넉넉해 지금은 디스크 정렬이 없다.

원본 SQL

sql

SELECT *
  FROM (SELECT h.SLE_NO, h.SLE_DT, h.SHP_NO,
               COUNT(*)                    AS itm_cnt,
               SUM(d.SLE_QTY * d.SLE_UPRC) AS sle_amt
          FROM CF10 h, CF11 d
         WHERE d.SLE_NO = h.SLE_NO
         GROUP BY h.SLE_NO, h.SLE_DT, h.SHP_NO
         ORDER BY h.SLE_NO DESC)
 WHERE ROWNUM <= 20

실행계획 학습용 예시

xplan

--------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |      |      1 |        |     20 |00:00:02.41 |   12787 |          |
|*  1 |  COUNT STOPKEY           |      |      1 |        |     20 |00:00:02.41 |   12787 |          |
|   2 |   VIEW                   |      |      1 |    500K|     20 |00:00:02.41 |   12787 |          |
|*  3 |    SORT ORDER BY STOPKEY |      |      1 |    500K|     20 |00:00:02.41 |   12787 | 4096 (0) |
|   4 |     HASH GROUP BY        |      |      1 |    500K|    500K|00:00:02.17 |   12787 |   47M (0)|
|*  5 |      HASH JOIN           |      |      1 |   1096K|   1096K|00:00:01.18 |   12787 |   34M (0)|
|   6 |       TABLE ACCESS FULL  | CF10 |      1 |    500K|    500K|00:00:00.06 |    4583 |          |
|   7 |       TABLE ACCESS FULL  | CF11 |      1 |   1096K|   1096K|00:00:00.12 |    8204 |          |
--------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM<=20)
   3 - filter(ROWNUM<=20)
   5 - access("D"."SLE_NO"="H"."SLE_NO")

문제

화면은 20줄인데 실행계획의 어느 줄에서 몇 행을 조인하고 묶는지 쓰고, 첫 쪽이 2.4초 걸리는 까닭을 설명하시오.
결과가 바뀌지 않게 SQL을 고치시오. 고친 SQL이 원래 SQL과 같은 결과를 내려면 데이터가 어떤 조건을 만족해야 하는지, 그 조건이 깨지면 화면이 어떻게 달라지는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매에서 먼저 최근 20건을 고르고(CF10_PK 역순), 그 20건의 판매상품만 붙여 묶는다
SELECT h.SLE_NO, h.SLE_DT, h.SHP_NO,
       COUNT(*)                    AS itm_cnt,
       SUM(d.SLE_QTY * d.SLE_UPRC) AS sle_amt
  FROM (SELECT SLE_NO, SLE_DT, SHP_NO
          FROM (SELECT SLE_NO, SLE_DT, SHP_NO
                  FROM CF10
                 ORDER BY SLE_NO DESC)
         WHERE ROWNUM <= 20) h,
       CF11 d
 WHERE d.SLE_NO = h.SLE_NO
 GROUP BY h.SLE_NO, h.SLE_DT, h.SHP_NO
 ORDER BY h.SLE_NO DESC

-- 정합성 점검에 넣는 확인 쿼리 : 판매상품이 없는 판매 (0 이어야 고친 SQL 과 원래 SQL 의 결과가 같다)
-- SELECT COUNT(*) FROM CF10 h WHERE NOT EXISTS (SELECT 1 FROM CF11 d WHERE d.SLE_NO = h.SLE_NO);

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

xplan

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |         |      1 |        |     20 |00:00:00.01 |      46 |          |
|   1 |  SORT GROUP BY                         |         |      1 |     20 |     20 |00:00:00.01 |      46 | 2048 (0) |
|   2 |   NESTED LOOPS                         |         |      1 |     44 |     44 |00:00:00.01 |      46 |          |
|   3 |    VIEW                                |         |      1 |     20 |     20 |00:00:00.01 |       4 |          |
|*  4 |     COUNT STOPKEY                      |         |      1 |        |     20 |00:00:00.01 |       4 |          |
|   5 |      VIEW                              |         |      1 |    500K|     20 |00:00:00.01 |       4 |          |
|   6 |       TABLE ACCESS BY INDEX ROWID      | CF10    |      1 |    500K|     20 |00:00:00.01 |       4 |          |
|   7 |        INDEX FULL SCAN DESCENDING      | CF10_PK |      1 |     20 |     20 |00:00:00.01 |       3 |          |
|   8 |    TABLE ACCESS BY INDEX ROWID BATCHED | CF11    |     20 |      2 |     44 |00:00:00.01 |      42 |          |
|*  9 |     INDEX RANGE SCAN                   | CF11_PK |     20 |      2 |     44 |00:00:00.01 |      22 |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(ROWNUM<=20)
   9 - access("D"."SLE_NO"="H"."SLE_NO")

모범 답안 서술

5번 줄 HASH JOIN이 CF10 50만 행과 CF11 1,096,708행을 모두 조인하고(A-Rows 1096K), 4번 줄 HASH GROUP BY가 그 결과를 판매 50만 건으로 묶은 뒤에야(A-Rows 500K) 3번 줄이 판매번호 역순 20건을 고른다. 조인과 집계가 ROWNUM보다 안쪽에 있으므로 20건을 고르기 전에 전부 해야 한다. 두 테이블을 다 읽는 12,787블록에 해시 조인(34M)과 해시 집계(47M)의 CPU가 더해져 첫 쪽부터 2.4초가 걸리고, 열 때마다 이 일을 처음부터 되풀이한다. 고친 SQL은 판매에서 먼저 최근 20건을 고르고(페이징), 그 20건의 판매상품만 붙여 묶는다(조인과 집계). 판매번호 역순 20건은 CF10_PK를 거꾸로 읽다가 20건에서 멈추고(4블록), 판매마다 CF11_PK의 선두 컬럼 SLE_NO로 그 판매의 품목 줄만 찾아간다(20번, 44줄). Buffers는 12,787 → 46이고 해시 영역도 쓰지 않는다. 두 SQL의 결과가 같으려면 판매마다 판매상품이 한 줄 이상 있어야 한다. 원래 SQL은 내부 조인이라서 판매상품이 없는 판매를 아예 건너뛰고 다음 판매로 20건을 채운다. 고친 SQL은 먼저 20건을 자르므로 그런 판매가 끼면 그 쪽이 19건으로 줄어든다. 지금은 0건이고 저장 프로시저가 두 테이블을 한 트랜잭션으로 넣지만 FK로 막혀 있지는 않다. 그래서 NOT EXISTS로 판매상품 없는 판매를 세는 확인 쿼리를 정합성 점검에 넣어 둔다. 그런 판매를 '품목 0'으로 보여 줘야 한다면 바깥 조인으로 바꾸는데, 그때는 원래 화면과 결과가 달라지므로 요구를 먼저 물어 정한다.

해설

스칼라 서브쿼리를 붙이는 답: 바깥에서 판매 20건마다 (SELECT COUNT(*) …)와 (SELECT SUM(…) …)를 붙여도 페이징 뒤라서 싸다. 다만 같은 품목 줄을 두 번 찾는다(40번). 판매마다 한 번에 묶으려면 OUTER APPLY(또는 LATERAL)를 쓴다. 이때는 바깥 조인이 되어 품목이 없는 판매도 '품목 0'으로 남는다.
쪽 번호가 있다면 같은 모양으로 ROWNUM <= :page * 20과 rn > (:page - 1) * 20을 판매 쪽 인라인 뷰 안에 둔다. 버리는 판매는 CF10_PK와 CF10만 읽고 판매상품은 읽지 않는다.
먼저 묶고 붙이는 답과 헷갈리지 않는다: 판매상품을 판매번호별로 먼저 묶은 뒤 붙이는 방식은 전체를 집계하는 리포트에 맞는 답이다. 20건만 보는 목록에서는 묶기 전에 잘라야 한다.
2.4초 가운데 블록을 읽는 몫은 작다. 해시 조인과 해시 집계의 work area(34M, 47M)는 PGA가 줄어드는 날 디스크로 넘쳐 몇 배 느려질 수 있다(교재 09화 §6).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황