결제 내역 화면은 검색 한 번에 SQL 세 개가 돌고, 셋 다 주문 테이블을 통째로 읽는다

실전DDL한빛유통335 / 700

상황

정산팀의 「결제수단별 결제 내역」은 결제수단과 기간(기본 최근 1개월)을 넣으면 세 가지를 보여 준다. 위에는 「총 n건, 결제 합계 m원」, 아래에는 결제일시 최신순 목록(한 쪽 20건, 쪽 번호), 오른쪽 위에는 「엑셀 받기」 버튼이 있다. 화면은 검색할 때 Q1과 Q2를, 버튼을 누르면 Q3을 보낸다. 검색은 하루 약 3,000번, 엑셀 받기는 약 150번이다. 합계는 월말 대사에 그대로 쓰므로 정확해야 한다. T_ORD에는 PK 말고 인덱스가 없다. 박팀장은 "화면마다 인덱스를 하나씩 만들지 말고 꼭 필요한 만큼만"이라고 했다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · ORD_SEQ · CUS_ID · ORD_DT · ORD_ST · PAY_DT(결제일시, 시분초 있음, 결제 전에는 NULL) · PAY_TP(결제수단 4종: CARD 70%, BANK 20%, PHONE 7%, POINT 3%) · ORD_AMT · PAY_AMT
하루 주문 약 137건, 2년치다. 최근 1개월 결제는 약 4,100건이고 그중 CARD가 약 2,870건이다.
결제는 대부분 주문 당일에 끝나서 PAY_DT 순서와 테이블에 쌓인 순서가 거의 같다.
인덱스는 PK_T_ORD (ORD_SEQ)뿐이다. 통계는 최신이다.

원본 SQL

sql

-- Q1 : 총건수와 결제 합계
SELECT COUNT(*), SUM(PAY_AMT)
  FROM T_ORD
 WHERE PAY_TP = :tp
   AND PAY_DT >= :from_dt AND PAY_DT < :to_dt;

-- Q2 : 목록 (:page 쪽, 20건)
SELECT *
  FROM (SELECT ROWNUM rn, a.*
          FROM (SELECT ORD_SEQ, CUS_ID, PAY_DT, PAY_AMT, ORD_ST
                  FROM T_ORD
                 WHERE PAY_TP = :tp
                   AND PAY_DT >= :from_dt AND PAY_DT < :to_dt
                 ORDER BY PAY_DT DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20;

-- Q3 : 엑셀 받기 (기간 전체)
SELECT ORD_SEQ, CUS_ID, PAY_DT, PAY_AMT, ORD_ST
  FROM T_ORD
 WHERE PAY_TP = :tp
   AND PAY_DT >= :from_dt AND PAY_DT < :to_dt
 ORDER BY PAY_DT DESC;

실행계획 학습용 예시

xplan

[Q1 · CARD · 최근 1개월]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |      1 |        |      1 |00:00:00.01 |     896 |          |
|   1 |  SORT AGGREGATE           |       |      1 |      1 |      1 |00:00:00.01 |     896 |          |
|*  2 |   FILTER                  |       |      1 |        |   2871 |00:00:00.01 |     896 |          |
|*  3 |    TABLE ACCESS FULL      | T_ORD |      1 |   2917 |   2871 |00:00:00.01 |     896 |          |
---------------------------------------------------------------------------------------------------------------

[Q2 · 1쪽]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |      1 |        |     20 |00:00:00.02 |     896 |          |
|*  1 |  VIEW                     |       |      1 |     20 |     20 |00:00:00.02 |     896 |          |
|*  2 |   COUNT STOPKEY           |       |      1 |        |     20 |00:00:00.02 |     896 |          |
|   3 |    VIEW                   |       |      1 |   2917 |     20 |00:00:00.02 |     896 |          |
|*  4 |     SORT ORDER BY STOPKEY |       |      1 |   2917 |     20 |00:00:00.02 |     896 | 2048 (0) |
|*  5 |      FILTER               |       |      1 |        |   2871 |00:00:00.01 |     896 |          |
|*  6 |       TABLE ACCESS FULL   | T_ORD |      1 |   2917 |   2871 |00:00:00.01 |     896 |          |
---------------------------------------------------------------------------------------------------------------

[Q3 · 엑셀]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |       |      1 |        |   2871 |00:00:00.03 |     896 |          |
|   1 |  SORT ORDER BY            |       |      1 |   2917 |   2871 |00:00:00.03 |     896 |  267K (0)|
|*  2 |   FILTER                  |       |      1 |        |   2871 |00:00:00.01 |     896 |          |
|*  3 |    TABLE ACCESS FULL      | T_ORD |      1 |   2917 |   2871 |00:00:00.01 |     896 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (세 SQL 공통)
---------------------------------------------------
   FILTER            - filter(:FROM_DT<:TO_DT)
   TABLE ACCESS FULL - filter(("PAY_TP"=:TP AND "PAY_DT">=:FROM_DT AND "PAY_DT"<:TO_DT))
   (Q2) SORT ORDER BY STOPKEY - filter(ROWNUM<=:PAGE*20)

문제

검색 한 번과 엑셀 받기 한 번이 각각 몇 블록을 읽는지 쓰고, 세 SQL이 모두 테이블 전체를 읽는 까닭을 설명하시오.
세 SQL을 함께 처리할 인덱스를 설계하고 컬럼 순서의 근거를 쓰시오. 그 인덱스를 제대로 쓰도록 SQL에서 고칠 곳이 있으면 고치시오.
고친 뒤 세 SQL의 예상 일량을 쓰고, 그 인덱스의 대가와 총건수와 합계를 상한이나 근사값으로 바꾸지 않은 까닭을 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 세 SQL 이 같은 조건(PAY_TP =, PAY_DT 범위)과 같은 순서를 쓴다 → 인덱스 하나로
--   등치(PAY_TP) → 범위이자 정렬 키(PAY_DT) → 동점 깨기(ORD_SEQ) → Q1 의 합계 대상(PAY_AMT)
CREATE INDEX IX_T_ORD_PAY ON T_ORD (PAY_TP, PAY_DT, ORD_SEQ, PAY_AMT);

-- Q1 : 그대로 · 인덱스만 읽는다 (합계가 월말 대사에 쓰이므로 상한 없이 정확히 센다)
SELECT COUNT(*), SUM(PAY_AMT)
  FROM T_ORD
 WHERE PAY_TP = :tp
   AND PAY_DT >= :from_dt AND PAY_DT < :to_dt;

-- Q2 : 정렬 키를 유일하게 · 인덱스를 거꾸로 읽다가 멈춘다
SELECT *
  FROM (SELECT ROWNUM rn, a.*
          FROM (SELECT ORD_SEQ, CUS_ID, PAY_DT, PAY_AMT, ORD_ST
                  FROM T_ORD
                 WHERE PAY_TP = :tp
                   AND PAY_DT >= :from_dt AND PAY_DT < :to_dt
                 ORDER BY PAY_DT DESC, ORD_SEQ DESC) a
         WHERE ROWNUM <= :page * 20)
 WHERE rn > (:page - 1) * 20;

-- Q3 : 같은 인덱스를 거꾸로 끝까지 · 정렬 없음
SELECT ORD_SEQ, CUS_ID, PAY_DT, PAY_AMT, ORD_ST
  FROM T_ORD
 WHERE PAY_TP = :tp
   AND PAY_DT >= :from_dt AND PAY_DT < :to_dt
 ORDER BY PAY_DT DESC, ORD_SEQ DESC;

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

xplan

[Q1]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |      1 |00:00:00.01 |      13 |
|   1 |  SORT AGGREGATE                 |              |      1 |      1 |      1 |00:00:00.01 |      13 |
|*  2 |   FILTER                        |              |      1 |        |   2871 |00:00:00.01 |      13 |
|*  3 |    INDEX RANGE SCAN             | IX_T_ORD_PAY |      1 |   2917 |   2871 |00:00:00.01 |      13 |
------------------------------------------------------------------------------------------------------------------

[Q2 · 1쪽]   (마지막 144쪽도 같은 모양 · A-Rows 2,871 · Buffers 약 50)
--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |              |      1 |        |     20 |00:00:00.01 |       4 |
|*  1 |  VIEW                             |              |      1 |     20 |     20 |00:00:00.01 |       4 |
|*  2 |   COUNT STOPKEY                   |              |      1 |        |     20 |00:00:00.01 |       4 |
|   3 |    VIEW                           |              |      1 |   2917 |     20 |00:00:00.01 |       4 |
|*  4 |     FILTER                        |              |      1 |        |     20 |00:00:00.01 |       4 |
|   5 |      TABLE ACCESS BY INDEX ROWID  | T_ORD        |      1 |   2917 |     20 |00:00:00.01 |       4 |
|*  6 |       INDEX RANGE SCAN DESCENDING | IX_T_ORD_PAY |      1 |   2917 |     20 |00:00:00.01 |       3 |
--------------------------------------------------------------------------------------------------------------------

[Q3 · 엑셀]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |              |      1 |        |   2871 |00:00:00.01 |      50 |
|*  1 |  FILTER                         |              |      1 |        |   2871 |00:00:00.01 |      50 |
|   2 |   TABLE ACCESS BY INDEX ROWID   | T_ORD        |      1 |   2917 |   2871 |00:00:00.01 |      50 |
|*  3 |    INDEX RANGE SCAN DESCENDING  | IX_T_ORD_PAY |      1 |   2917 |   2871 |00:00:00.01 |      13 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (세 SQL 공통)
---------------------------------------------------
   FILTER           - filter(:FROM_DT<:TO_DT)
   INDEX RANGE SCAN - access("PAY_TP"=:TP AND "PAY_DT">=:FROM_DT AND "PAY_DT"<:TO_DT)

모범 답안 서술

세 SQL 모두 T_ORD 전체(896블록)를 읽는다. 조건에 쓸 인덱스가 PK뿐이라서 Q1은 2,871건을 세고 더하려고, Q2는 20건을 보여 주려고 2,871건을 정렬해(SORT ORDER BY STOPKEY), Q3은 2,871건 전체를 정렬해 내려고 테이블을 처음부터 끝까지 읽는다. 검색 한 번에 Q1과 Q2로 1,792블록, 엑셀을 받으면 896블록이 더해진다. 하루로는 검색 3,000번에 약 538만 블록, 엑셀 150번에 약 13만 블록이다. 세 SQL은 조건(PAY_TP =, PAY_DT 범위)과 순서(PAY_DT 최신순)가 같다. 그래서 인덱스 하나 (PAY_TP, PAY_DT, ORD_SEQ, PAY_AMT)로 셋을 함께 처리한다. 등치 조건인 PAY_TP를 앞에 두어야 결제수단 하나의 기간이 인덱스에서 한 구간으로 모이고, 범위 조건이자 정렬 키인 PAY_DT를 그 뒤에 둔다. ORD_SEQ는 같은 결제일시의 동점을 깨는 컬럼이다. Q2와 Q3의 정렬 키도 PAY_DT DESC, ORD_SEQ DESC로 고쳐야 쪽 경계에서 빠지거나 겹치는 결제가 없고, 인덱스 순서와 같아 정렬이 사라진다. PAY_AMT는 Q1의 합계 대상이라서, 넣으면 Q1이 테이블을 읽지 않고 인덱스만으로 끝난다. 고친 뒤 Q1은 인덱스만 13블록, Q2 1쪽은 인덱스를 거꾸로 읽다가 20건에서 멈춰 4블록이다. 결제가 날짜순으로 쌓여 있어 마지막 쪽(144쪽)도 약 50블록이다. Q3은 같은 인덱스를 거꾸로 끝까지 읽고 테이블은 한 달치 블록만 찾아가 약 50블록이며 정렬이 없다. 검색 한 번 1,792 → 17블록, 엑셀 896 → 50블록이다. Q1의 합계는 월말 대사에 쓰이므로 상한이나 근사값으로 바꾸지 않고, 인덱스로 정확한 값을 싸게 센다. 대가는 인덱스 하나만큼의 유지 비용이다. 주문이 하루 137건 들어올 때마다 항목을 하나 넣고, 결제가 끝나 PAY_TP와 PAY_DT가 채워질 때마다 인덱스 항목을 지우고 다시 넣는다(하루 약 274번). 화면 세 개에 인덱스를 하나씩 만들었다면 그 비용이 세 배다.

해설

인덱스를 (PAY_DT, PAY_TP, …) 순서로 만들면 한 달의 모든 결제수단(약 4,100건)을 훑으며 PAY_TP를 거르게 된다. 1개월에서는 차이가 작지만 기간이 길어지고 결제수단이 드물수록(POINT 3%) 버리는 항목이 늘어난다. 등치 조건을 앞에 둔다(교재 04화 §6).
목록을 키 기반으로 바꿀 필요는 없다. 결제수단 하나의 한 달은 2,871건(144쪽)이 상한이고 테이블이 결제일시 순으로 쌓여 있어 마지막 쪽도 약 50블록이다. 기본 기간이 1년으로 늘거나 클러스터링 팩터가 나빠지면 그때 다시 잰다.
화면마다 인덱스를 따로 만들면 Q1용 (PAY_TP, PAY_DT, PAY_AMT)와 Q2, Q3용 (PAY_TP, PAY_DT, ORD_SEQ)처럼 앞부분이 같은 인덱스가 둘 생긴다. 앞부분이 같으면 하나로 합치고 뒤에 컬럼을 붙인다.
화면 칸(CUS_ID, ORD_ST)까지 넣으면 Q2와 Q3도 테이블을 읽지 않지만, 이 테이블은 클러스터링 팩터가 좋아 얻는 것이 수십 블록뿐이다. 넣지 않는다.
기간을 1년으로 넓혀 합계를 자주 본다면 일별 결제 합계를 미리 쌓아 두는 집계 테이블이 다음 단계다. 그때는 적재 배치와 정합성 확인이 새로 생긴다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황