「VIP 주문만」을 켜면 목록이 느려지고, 빠르게 고쳤다는 SQL은 빈 쪽을 보여 준다

실전SELECT한빛유통330 / 700

상황

정산팀의 「주문 관리」는 전체 주문을 최신순(ORD_DT DESC, ORD_SEQ DESC)으로 한 쪽에 20건씩 보여 준다. 「VIP(A 등급) 주문만」을 켜면 1쪽이 0.2초, 10쪽이 2초쯤 걸린다. 정산팀은 쪽 번호를 그대로 쓰고 싶어 한다. 조민우가 "고객 조인을 쪽을 자른 다음으로 옮겼다"며 SQL 2를 올렸는데, 빨라진 대신 1쪽에 주문이 한두 건만 나오거나 아예 비었다. 오과장은 두 SQL을 비교하고 제대로 된 안을 내라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 고객당 약 500건 · 같은 날짜의 주문도, 한 고객의 주문도 테이블 곳곳에 흩어져 있다.
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 1%(20명) · B 9% · C 90%. A 등급 고객의 주문은 약 10,000건(1%)이다.
인덱스: IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)(교재 09화) · IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)(CS 화면에서 고객 한 명의 기간 조회에 쓴다).
FK T_ORD_BIG.CUS_ID → M_CUS가 켜져 있다. 통계는 최신이고 CUS_GD에 히스토그램은 없다.

원본 SQL

sql

-- SQL 1 : 지금 운영 중 (VIP 켜짐)
SELECT *
  FROM (SELECT ROWNUM rn, a.*
          FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.CUS_ID, c.CUS_NM, o.ORD_AMT
                  FROM T_ORD_BIG o, M_CUS c
                 WHERE c.CUS_ID = o.CUS_ID
                   AND c.CUS_GD = 'A'
                 ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20;

-- SQL 2 : 조민우가 올린 안
SELECT x.ORD_SEQ, x.ORD_DT, x.CUS_ID, c.CUS_NM, x.ORD_AMT
  FROM (SELECT *
          FROM (SELECT ROWNUM rn, a.*
                  FROM (SELECT ORD_SEQ, ORD_DT, CUS_ID, ORD_AMT
                          FROM T_ORD_BIG
                         ORDER BY ORD_DT DESC, ORD_SEQ DESC) a
                 WHERE ROWNUM <= :page * 20)
         WHERE rn > (:page - 1) * 20) x,
       M_CUS c
 WHERE c.CUS_ID = x.CUS_ID
   AND c.CUS_GD = 'A'
 ORDER BY x.rn;

실행계획 학습용 예시

xplan

[SQL 1 · 1쪽]   (10쪽은 같은 모양 · 6번 줄 A-Rows 20,130 · Buffers 약 40,400 · 2.05초)
-----------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |      1 |        |     20 |00:00:00.21 |    4049 |
|*  1 |  VIEW                            |                   |      1 |     20 |     20 |00:00:00.21 |    4049 |
|*  2 |   COUNT STOPKEY                  |                   |      1 |        |     20 |00:00:00.21 |    4049 |
|   3 |    VIEW                          |                   |      1 |  333K  |     20 |00:00:00.21 |    4049 |
|   4 |     NESTED LOOPS                 |                   |      1 |  333K  |     20 |00:00:00.21 |    4049 |
|   5 |      NESTED LOOPS                |                   |      1 |   1000K|   2013 |00:00:00.19 |    2049 |
|   6 |       TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |      1 |   1000K|   2013 |00:00:00.18 |    2020 |
|   7 |        INDEX FULL SCAN DESCENDING| IX_ORD_BIG_DT_SEQ |      1 |   1000K|   2013 |00:00:00.01 |       9 |
|*  8 |       INDEX UNIQUE SCAN          | PK_M_CUS          |   2013 |      1 |   2013 |00:00:00.01 |      29 |
|*  9 |      TABLE ACCESS BY INDEX ROWID | M_CUS             |   2013 |      1 |     20 |00:00:00.02 |    2000 |
-----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">(:PAGE-1)*20)
   2 - filter(ROWNUM<=:PAGE*20)
   8 - access("C"."CUS_ID"="O"."CUS_ID")
   9 - filter("C"."CUS_GD"='A')

[SQL 2 · 1쪽]
-------------------------------------------------------------------------------------------------------------------
| Id  | Operation                          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                   |      1 |        |      0 |00:00:00.01 |      45 |
|   1 |  SORT ORDER BY                     |                   |      1 |      7 |      0 |00:00:00.01 |      45 |
|   2 |   NESTED LOOPS                     |                   |      1 |      7 |      0 |00:00:00.01 |      45 |
|   3 |    NESTED LOOPS                    |                   |      1 |     20 |     20 |00:00:00.01 |      25 |
|*  4 |     VIEW                           |                   |      1 |     20 |     20 |00:00:00.01 |      23 |
|*  5 |      COUNT STOPKEY                 |                   |      1 |        |     20 |00:00:00.01 |      23 |
|   6 |       VIEW                         |                   |      1 |   1000K|     20 |00:00:00.01 |      23 |
|   7 |        TABLE ACCESS BY INDEX ROWID | T_ORD_BIG         |      1 |   1000K|     20 |00:00:00.01 |      23 |
|   8 |         INDEX FULL SCAN DESCENDING | IX_ORD_BIG_DT_SEQ |      1 |     20 |     20 |00:00:00.01 |       3 |
|*  9 |     INDEX UNIQUE SCAN              | PK_M_CUS          |     20 |      1 |     20 |00:00:00.01 |       2 |
|* 10 |    TABLE ACCESS BY INDEX ROWID     | M_CUS             |     20 |      1 |      0 |00:00:00.01 |      20 |
-------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("RN">(:PAGE-1)*20)
   5 - filter(ROWNUM<=:PAGE*20)
   9 - access("C"."CUS_ID"="X"."CUS_ID")
  10 - filter("C"."CUS_GD"='A')

문제

SQL 2가 빈 쪽이나 한두 건짜리 쪽을 보여 주는 까닭을 설명하고, 조인 상대에 조건이 있을 때 "페이징 후 조인"을 쓸 수 없는 이유를 쓰시오.
SQL 1의 1쪽 Buffers 4,049가 어디서 나오는지 쓰고, 쪽이 뒤로 갈수록, 그리고 등급 조건이 B나 C일 때 일량이 어떻게 달라지는지 설명하시오.
쪽 번호를 유지하면서 A 등급 화면이 몇 쪽째든 비슷한 일량이 되도록 SQL과 인덱스를 고치고, 그 안의 대가와 그 안이 맞지 않는 경우를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 고객별 인덱스를 정렬 키와 화면 칸까지 넓힌다 (기존 (CUS_ID, ORD_DT) 를 앞부분으로 품는다)
CREATE INDEX IX_ORD_BIG_CUS_DT_SEQ ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_SEQ, ORD_AMT);
-- CS 화면 SQL 의 계획과 일량을 확인한 뒤 DROP INDEX IX_ORD_BIG_CUS_DT;

-- A 등급 화면 : 고객 20명을 먼저 고르고, 그 고객들의 주문만 인덱스에서 읽어 정렬한 뒤 자른다
SELECT *
  FROM (SELECT ROWNUM rn, a.*
          FROM (SELECT /*+ LEADING(c) USE_NL(o) INDEX(o IX_ORD_BIG_CUS_DT_SEQ) */
                       o.ORD_SEQ, o.ORD_DT, o.CUS_ID, c.CUS_NM, o.ORD_AMT
                  FROM M_CUS c, T_ORD_BIG o
                 WHERE c.CUS_GD = 'A'
                   AND o.CUS_ID = c.CUS_ID
                 ORDER BY o.ORD_DT DESC, o.ORD_SEQ DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20;

-- B · C 등급과 '전체' 보기는 SQL 1 의 모양(날짜 인덱스 순서 + 부분범위 처리)을 그대로 쓴다

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

xplan

[A 등급 · 1쪽 · 10쪽도 같다]
-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |                       |      1 |        |     20 |00:00:00.01 |     101 |          |
|*  1 |  VIEW                    |                       |      1 |     20 |     20 |00:00:00.01 |     101 |          |
|*  2 |   COUNT STOPKEY          |                       |      1 |        |     20 |00:00:00.01 |     101 |          |
|   3 |    VIEW                  |                       |      1 |  10000 |     20 |00:00:00.01 |     101 |          |
|*  4 |     SORT ORDER BY STOPKEY|                       |      1 |  10000 |     20 |00:00:00.01 |     101 | 4096 (0) |
|   5 |      NESTED LOOPS        |                       |      1 |  10000 |  10000 |00:00:00.01 |     101 |          |
|*  6 |       TABLE ACCESS FULL  | M_CUS                 |      1 |     20 |     20 |00:00:00.01 |      30 |          |
|*  7 |       INDEX RANGE SCAN   | IX_ORD_BIG_CUS_DT_SEQ |     20 |    500 |  10000 |00:00:00.01 |      71 |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN">(:PAGE-1)*20)
   2 - filter(ROWNUM<=:PAGE*20)
   4 - filter(ROWNUM<=:PAGE*20)
   6 - filter("C"."CUS_GD"='A')
   7 - access("O"."CUS_ID"="C"."CUS_ID")

모범 답안 서술

SQL 2는 조건이 붙은 조인을 페이징 뒤로 옮겼다. 그러면 먼저 최신 주문 20건을 자른 다음에 A 등급인지 거르게 된다. A 등급 주문은 전체의 1%라 최신 20건 가운데 A 등급은 평균 0.2건이고, 그래서 1쪽이 비거나 한두 건만 남는다(10번 줄 A-Rows 0). 화면이 묻는 것은 "A 등급 주문을 최신순으로 20건씩"인데 SQL 2는 "최신 주문 20건 가운데 A 등급"을 묻는다. 페이징 후 조인은 조인이 행 수를 바꾸지 않을 때, 곧 주문마다 고객이 정확히 하나이고 조인 상대에 조건이 없을 때만 쓸 수 있다. SQL 1은 결과가 맞다. IX_ORD_BIG_DT_SEQ를 거꾸로 읽으며 정렬을 생략하고, 주문마다 테이블에 가서 CUS_ID를 읽은 뒤 고객을 찾아 등급을 본다. A 등급이 1%이므로 20건을 채우려고 주문 2,013건을 지나간다. 클러스터링 팩터가 나빠 인덱스 9블록, 테이블 2,011블록, 고객 확인 2,029블록이 들어 1쪽이 4,049블록이다. 10쪽은 200건을 채우려고 약 2만 건을 지나가므로 약 40,400블록이 되어 쪽 번호에 비례해 늘어난다. 등급이 B(9%)면 1쪽에서 약 220건, C(90%)면 약 22건만 지나가면 되므로 그때는 SQL 1의 모양이 오히려 맞다. A 등급 화면은 드라이빙을 바꾼다. A 등급 고객 20명을 먼저 고르고, 고객별 인덱스로 그 고객들의 주문 10,000건을 읽어 정렬한 뒤 자른다. 기존 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)를 (CUS_ID, ORD_DT, ORD_SEQ, ORD_AMT)로 넓혀 정렬 키와 화면 칸을 모두 담으면 테이블을 읽지 않는다. M_CUS 30블록과 인덱스 71블록, 모두 101블록이고 1쪽이든 10쪽이든 같다(1쪽 4,049 → 101, 10쪽 약 40,400 → 101). 정렬할 행은 1만 건이지만 STOPKEY가 있어 쪽 분량만 남긴다. 대가는 셋이다. 첫째, 인덱스 항목이 ORD_SEQ와 ORD_AMT만큼 커지고, 주문을 넣을 때마다 더 큰 항목을 고치므로 적재 비용이 는다. 둘째, 기존 인덱스를 지우기 전에 CS 화면(고객 한 명의 기간 조회)이 새 인덱스로 같은 범위를 읽는지 확인해야 한다. 앞 두 컬럼이 같으므로 그대로 쓸 수 있다. 셋째, 클릭마다 A 등급 주문 전체를 정렬하므로 A 등급 주문이 늘수록 정렬이 커진다. 이 안은 대상이 소수(1%)일 때의 답이다. B, C 등급이나 '전체'에서는 정렬할 행이 9만, 90만 건으로 커지므로 SQL 1의 모양(날짜 인덱스 순서와 부분범위 처리)을 쓰도록 등급에 따라 SQL을 나눈다.

해설

ROWID로 늦게 찾아가는 답: 인덱스를 (CUS_ID, ORD_DT, ORD_SEQ)까지만 넓히고, 정렬과 자르기는 인덱스의 ROWID로 한 뒤 쪽 분량 20건만 테이블에 가도 된다(약 121블록). 인덱스는 덜 커지는 대신 SQL이 길어지고 조인 방법을 힌트로 고정해야 한다.
IN 서브쿼리로 써도 같다: o.CUS_ID IN (SELECT CUS_ID FROM M_CUS WHERE CUS_GD = 'A')는 세미 조인으로 펼쳐져 같은 계획이 될 수 있다. 다만 화면에 고객명이 필요하므로 조인이 자연스럽다.
옵티마이저에게 맡기려면 CUS_GD에 히스토그램이 있어야 A 등급이 20명뿐인 것을 안다. 히스토그램이 없으면 등급마다 3분의 1(약 667명)로 보고 날짜 인덱스 쪽을 고르기 쉽다. 등급을 바인드 변수로 받으면 첫 값으로 만든 계획을 계속 쓸 수 있으므로(교재 10화), 화면이 A 등급 전용이면 SQL을 따로 둔다.
정합성은 SQL 1의 결과를 기준으로 확인한다. 1쪽, 10쪽, 마지막 쪽(500쪽)을 새 SQL과 MINUS 양방향으로 대조하고, A 등급 주문 10,000건을 쪽으로 끝까지 넘겨 모은 건수가 10,000인지 본다(교재 09화 정합성 체크리스트).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황