정산팀 주문 모니터에 새로 붙은 위젯 셋이 5분마다 주문 테이블을 한 번씩 통째로 읽는다

실전DDL한빛유통365 / 700

상황

정산팀 「주문 모니터」 개편으로 위젯 셋이 새로 붙었다. 정산팀 PC 30대가 08 ~ 20시에 5분마다 새로 고쳐서 위젯마다 하루 약 4,300번 돈다. 오픈 첫 주에 세 위젯의 SQL이 AWR 논리 읽기 1 ~ 3위를 차지했다. 위젯 설명서는 이렇다. W1 「상태별 최근 주문 10건」(같은 날이면 주문번호 역순), W2 「최근 7일 일자별 고액 주문 TOP 5」(금액이 같으면 공동 순위로 모두 보여 준다), W3 「이번 달 등급별 매출 상위 고객 10명」(공동 순위 포함). 박팀장은 "주문 테이블 인덱스는 지금 둘에서 하나까지만 더할 수 있다. 위젯마다 무엇을 정렬하고 어디서 멈추는지 적어 달라"라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_DT 2022-01-01 ~ 2024-12-31(전부 자정, 하루 913건) · ORD_ST COMPLETE 90% · PENDING 8% · CANCEL 2%(NOT NULL, 값은 셋뿐) · ORD_AMT 1만 ~ 50만 원에 고르게 분포 · ORD_SEQ 유일 · 행이 날짜순으로 놓여 있지 않다(날짜 인덱스 CF 약 1,000,000)
인덱스 IX_ORD_BIG_DT_AMT (ORD_DT, ORD_AMT): 「기간 매출」 화면용 · BLEVEL 2 · 리프 3,206블록(리프 한 블록에 약 312항목)
인덱스 IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT): CS 화면용 · BLEVEL 2 · 리프 약 3,000블록
M_CUS: 2,000행 · 30블록 · CUS_GD A 1% · B 9% · C 90% · 이번 달(2024년 12월) 주문은 28,303건 · 통계 최신

원본 SQL

sql

-- W1 상태별 최근 주문 10건
SELECT ORD_ST, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
  FROM (SELECT ORD_ST, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT,
               ROW_NUMBER() OVER (PARTITION BY ORD_ST ORDER BY ORD_DT DESC, ORD_SEQ DESC) AS rn
          FROM T_ORD_BIG)
 WHERE rn <= 10;

-- W2 최근 7일 일자별 고액 주문 TOP 5
SELECT ORD_DT, ORD_SEQ, CUS_ID, ORD_AMT, rn
  FROM (SELECT ORD_DT, ORD_SEQ, CUS_ID, ORD_AMT,
               ROW_NUMBER() OVER (PARTITION BY ORD_DT ORDER BY ORD_AMT DESC, ORD_SEQ DESC) AS rn
          FROM T_ORD_BIG
         WHERE ORD_DT >= :d - 6 AND ORD_DT < :d + 1)
 WHERE rn <= 5;

-- W3 이번 달 등급별 매출 상위 고객 10명
SELECT CUS_GD, CUS_ID, AMT, rk
  FROM (SELECT c.CUS_GD, o.CUS_ID, SUM(o.ORD_AMT) AS AMT,
               RANK() OVER (PARTITION BY c.CUS_GD ORDER BY SUM(o.ORD_AMT) DESC) AS rk
          FROM T_ORD_BIG o, M_CUS c
         WHERE o.ORD_DT >= :m1 AND o.ORD_DT < ADD_MONTHS(:m1, 1)
           AND c.CUS_ID = o.CUS_ID
         GROUP BY c.CUS_GD, o.CUS_ID)
 WHERE rk <= 10;

실행계획 학습용 예시

xplan

[W1]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |     30 |00:00:00.43 |    5826 |          |
|*  1 |  VIEW                    |           |      1 |   1000K|     30 |00:00:00.43 |    5826 |          |
|*  2 |   WINDOW SORT PUSHED RANK|           |      1 |   1000K|     30 |00:00:00.43 |    5826 | 4096  (0)|
|   3 |    TABLE ACCESS FULL     | T_ORD_BIG |      1 |   1000K|   1000K|00:00:00.06 |    5826 |          |
-------------------------------------------------------------------------------------------------------------
   1 - filter("RN"<=10)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ORD_ST" ORDER BY INTERNAL_FUNCTION("ORD_DT") DESC ,
              INTERNAL_FUNCTION("ORD_SEQ") DESC )<=10)

[W2 · :d = 2024-12-31]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |     35 |00:00:00.08 |    5826 |          |
|*  1 |  VIEW                    |           |      1 |   6391 |     35 |00:00:00.08 |    5826 |          |
|*  2 |   WINDOW SORT PUSHED RANK|           |      1 |   6391 |     35 |00:00:00.08 |    5826 | 2048  (0)|
|*  3 |    TABLE ACCESS FULL     | T_ORD_BIG |      1 |   6391 |   6391 |00:00:00.06 |    5826 |          |
-------------------------------------------------------------------------------------------------------------
   1 - filter("RN"<=5)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "ORD_DT" ORDER BY INTERNAL_FUNCTION("ORD_AMT") DESC ,
              INTERNAL_FUNCTION("ORD_SEQ") DESC )<=5)
   3 - filter(("ORD_DT">=:D-6 AND "ORD_DT"<:D+1))

[W3 · :m1 = 2024-12-01]
-------------------------------------------------------------------------------------------------------------
| Id  | Operation                | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |      1 |        |     30 |00:00:00.19 |    5856 |          |
|*  1 |  VIEW                    |           |      1 |   2000 |     30 |00:00:00.19 |    5856 |          |
|*  2 |   WINDOW SORT PUSHED RANK|           |      1 |   2000 |     30 |00:00:00.19 |    5856 | 4096  (0)|
|   3 |    HASH GROUP BY         |           |      1 |   2000 |   2000 |00:00:00.19 |    5856 | 1412K (0)|
|*  4 |     HASH JOIN            |           |      1 |  28303 |  28303 |00:00:00.17 |    5856 | 1236K (0)|
|   5 |      TABLE ACCESS FULL   | M_CUS     |      1 |   2000 |   2000 |00:00:00.01 |      30 |          |
|*  6 |      TABLE ACCESS FULL   | T_ORD_BIG |      1 |  28303 |  28303 |00:00:00.15 |    5826 |          |
-------------------------------------------------------------------------------------------------------------
   1 - filter("RK"<=10)
   2 - filter(RANK() OVER ( PARTITION BY "C"."CUS_GD" ORDER BY SUM("O"."ORD_AMT") DESC )<=10)
   4 - access("C"."CUS_ID"="O"."CUS_ID")
   6 - filter(("O"."ORD_DT">=:M1 AND "O"."ORD_DT"<ADD_MONTHS(:M1,1)))

문제

세 위젯이 각각 몇 행을 읽어 몇 줄을 남기는지 실행계획으로 쓰고, W1이 WINDOW SORT PUSHED RANK인데도 주문 테이블을 다 읽는 까닭을 설명하시오.
인덱스를 하나만 더할 수 있다는 조건에서 세 위젯의 SQL과 인덱스를 설계하시오. W2는 위젯 설명서의 동점 규칙을 지키시오.
설계안의 대가(주문 적재와 기존 화면에 주는 영향)와 위젯마다 남는 일량을 숫자로 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 더하는 인덱스 하나 : W1 용 · 상태마다 최근 순서(같은 날이면 주문번호 역순)로 줄 선다
CREATE INDEX IX_ORD_BIG_ST_DT_SEQ ON T_ORD_BIG (ORD_ST, ORD_DT, ORD_SEQ) INVISIBLE;

-- ② 바꾸는 인덱스 : (ORD_DT, ORD_AMT) 에 CUS_ID 를 더한다 → W2 의 순위와 W3 의 집계가 인덱스만으로 끝난다 (개수는 그대로)
CREATE INDEX IX_ORD_BIG_DT_AMT_CUS ON T_ORD_BIG (ORD_DT, ORD_AMT, CUS_ID) INVISIBLE;
-- 세션에서 W1 ~ W3 과 「기간 매출」 화면을 잰 뒤 → 둘 다 VISIBLE → DROP INDEX IX_ORD_BIG_DT_AMT;

-- W1 : 상태마다 따로 Top-N (상태 셋 · 인덱스를 거꾸로 읽다가 10 건에서 멈춘다)
SELECT * FROM (SELECT ORD_ST, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
                WHERE ORD_ST = 'COMPLETE' ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= 10
UNION ALL
SELECT * FROM (SELECT ORD_ST, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
                WHERE ORD_ST = 'PENDING'  ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= 10
UNION ALL
SELECT * FROM (SELECT ORD_ST, ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT FROM T_ORD_BIG
                WHERE ORD_ST = 'CANCEL'   ORDER BY ORD_DT DESC, ORD_SEQ DESC) WHERE ROWNUM <= 10;

-- W2 : 설명서대로 공동 순위(RANK) → 순위는 인덱스 항목(ORD_DT, ORD_AMT, ROWID)만으로 매기고 남은 줄만 테이블에 간다
SELECT t.ORD_DT, t.ORD_SEQ, t.CUS_ID, t.ORD_AMT, x.rk
  FROM (SELECT rid, rk
          FROM (SELECT ROWID AS rid,
                       RANK() OVER (PARTITION BY ORD_DT ORDER BY ORD_AMT DESC) AS rk
                  FROM T_ORD_BIG
                 WHERE ORD_DT >= :d - 6 AND ORD_DT < :d + 1)
         WHERE rk <= 5) x,
       T_ORD_BIG t
 WHERE t.ROWID = x.rid
 ORDER BY t.ORD_DT DESC, x.rk, t.ORD_SEQ;

-- W3 : SQL 그대로 (이번 달 28,303 항목을 새 인덱스에서만 읽는다 · 묶기와 순위는 남는다)

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

xplan

[W1 · 고친 뒤]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name                 | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                      |      1 |        |     30 |00:00:00.01 |      39 |
|   1 |  UNION-ALL                      |                      |      1 |        |     30 |00:00:00.01 |      39 |
|*  2 |   COUNT STOPKEY                 |                      |      1 |        |     10 |00:00:00.01 |      13 |
|   3 |    VIEW                         |                      |      1 |     10 |     10 |00:00:00.01 |      13 |
|   4 |     TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |    900K|     10 |00:00:00.01 |      13 |
|*  5 |      INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_DT_SEQ |      1 |     10 |     10 |00:00:00.01 |       3 |
|*  6 |   COUNT STOPKEY                 |                      |      1 |        |     10 |00:00:00.01 |      13 |
|   7 |    VIEW                         |                      |      1 |     10 |     10 |00:00:00.01 |      13 |
|   8 |     TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |  80000 |     10 |00:00:00.01 |      13 |
|*  9 |      INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_DT_SEQ |      1 |     10 |     10 |00:00:00.01 |       3 |
|* 10 |   COUNT STOPKEY                 |                      |      1 |        |     10 |00:00:00.01 |      13 |
|  11 |    VIEW                         |                      |      1 |     10 |     10 |00:00:00.01 |      13 |
|  12 |     TABLE ACCESS BY INDEX ROWID | T_ORD_BIG            |      1 |  20000 |     10 |00:00:00.01 |      13 |
|* 13 |      INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_ST_DT_SEQ |      1 |     10 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------------
   2 - filter(ROWNUM<=10)    5 - access("ORD_ST"='COMPLETE')
   6 - filter(ROWNUM<=10)    9 - access("ORD_ST"='PENDING')
  10 - filter(ROWNUM<=10)   13 - access("ORD_ST"='CANCEL')

[W2 · 고친 뒤]
------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                 | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                       |      1 |        |     35 |00:00:00.01 |      67 |          |
|   1 |  SORT ORDER BY            |                       |      1 |     35 |     35 |00:00:00.01 |      67 | 4096  (0)|
|   2 |   NESTED LOOPS            |                       |      1 |     35 |     35 |00:00:00.01 |      67 |          |
|*  3 |    VIEW                   |                       |      1 |   6391 |     35 |00:00:00.01 |      32 |          |
|*  4 |     WINDOW SORT PUSHED RANK|                      |      1 |   6391 |     35 |00:00:00.01 |      32 | 2048  (0)|
|*  5 |      INDEX RANGE SCAN     | IX_ORD_BIG_DT_AMT_CUS |      1 |   6391 |   6391 |00:00:00.01 |      32 |          |
|   6 |    TABLE ACCESS BY USER ROWID| T_ORD_BIG          |     35 |      1 |     35 |00:00:00.01 |      35 |          |
------------------------------------------------------------------------------------------------------------------------
   3 - filter("RK"<=5)
   4 - filter(RANK() OVER ( PARTITION BY "ORD_DT" ORDER BY INTERNAL_FUNCTION("ORD_AMT") DESC )<=5)
   5 - access("ORD_DT">=:D-6 AND "ORD_DT"<:D+1)

[W3 · SQL 그대로 · 새 인덱스]  6번 줄이 INDEX RANGE SCAN IX_ORD_BIG_DT_AMT_CUS (28,303항목 · 134블록) · M_CUS 30 · Buffers 164

모범 답안 서술

세 위젯 모두 T_ORD_BIG을 FULL로 읽는다. W1은 100만 행을 읽어 30줄, W2는 100만 행을 훑어 7일 치 6,391행을 골라 35줄, W3은 100만 행을 훑어 이번 달 28,303행을 고객 2,000명으로 묶은 뒤 30줄을 남긴다. 새로 고침 한 번에 5,826 + 5,826 + 5,856 = 17,508블록이고, 하루 약 4,300번이면 약 7,500만 블록이다. W1에도 WINDOW SORT PUSHED RANK가 보이지만, PUSHED RANK는 정렬하면서 상태마다 위의 10건만 작업 공간에 남겨 메모리를 줄일 뿐이다. 무엇이 상태별 최근 10건인지 알려면 100만 행을 다 읽어 한 번씩은 견줘야 한다. 하나의 분석 함수는 그룹 하나를 다 채웠다고 읽기를 멈추지 못한다. 그룹이 상태 셋뿐이고 상태마다 '최근 순서'로 줄 선 인덱스 (ORD_ST, ORD_DT, ORD_SEQ)가 있다면, 상태마다 그 범위를 거꾸로 읽다가 10건에서 멈출 수 있다. 그래서 W1을 상태별 Top-N 셋의 UNION ALL로 바꾼다. 상태 하나에 인덱스 3블록과 테이블 10블록, 합계 39블록이다. 이 인덱스가 더할 수 있는 하나다. W2는 설명서가 '금액이 같으면 공동 순위로 모두'이므로 RANK가 맞다. 지금의 ROW_NUMBER … ORD_SEQ DESC는 동점 가운데 하나를 떨어뜨려 설명서를 어긴다. RANK로 바꾸면 순위에 필요한 컬럼이 ORD_DT와 ORD_AMT뿐이라 인덱스 항목만으로 순위를 매길 수 있다. 7일 범위의 인덱스 항목 6,391개(32블록)에서 순위를 매겨 날마다 5줄 남짓, 35줄만 ROWID로 테이블에 간다(35블록). 합계 67블록이다. W3은 이번 달 주문을 고객별로 다 더해야 하므로 묶기와 순위는 피할 수 없다. 줄일 수 있는 것은 읽는 양이다. 기존 (ORD_DT, ORD_AMT)에 CUS_ID를 더해 (ORD_DT, ORD_AMT, CUS_ID)로 바꾸면 이번 달 범위의 인덱스 항목만 읽어(134블록) 테이블에 가지 않는다. 같은 인덱스를 W2도 쓰고, 앞 두 컬럼이 같아 「기간 매출」 화면도 그대로 쓴다. 새로 고침 한 번은 39 + 67 + 164 = 약 270블록이 된다(17,508 → 약 270). 대가는 이렇다. 인덱스가 하나 늘어 주문 INSERT마다 고칠 인덱스가 셋이 되고, 결제 확인으로 PENDING이 COMPLETE로 바뀔 때마다 상태 인덱스의 항목을 지우고 다시 넣는다(하루 913건과 상태 변경 수십 건이라 부담은 작다). 넓힌 인덱스는 항목마다 CUS_ID 10바이트가 늘어 리프가 약 3,200 → 4,600블록이 되고, 「기간 매출」 한 달 조회도 91 → 약 130블록으로 조금 늘어난다. 두 인덱스는 INVISIBLE로 만들어 세 위젯과 기간 매출 화면을 세션에서 잰 뒤 공개하고, 옛 (ORD_DT, ORD_AMT)는 지운다.

해설

그룹이 적으면 그룹마다, 많으면 한 번에: W1처럼 그룹이 코드값 몇 개뿐이고 그룹마다 순서를 주는 인덱스가 있으면 그룹마다 Top-N이 가장 싸다. 그룹이 수천 개면(고객별 등) 테이블을 한 번 읽어 분석 함수로 거르는 편이 낫다(SORT-016, JOIN-057).
상태 목록을 SQL에 적는 대가: 상태가 넷이 되는 날 W1은 가지를 하나 더해야 한다. 상태 코드 표가 있다면 그 표를 앞에 두고 CROSS APPLY로 붙이면 SQL을 고치지 않아도 된다. 어느 쪽이든 ORD_ST의 값이 셋뿐이라는 전제는 제약(NOT NULL, CHECK)으로 지킨다(ACC-020).
W2를 ROW_NUMBER로 두고 싶다면: 설명서를 바꿔 동점에 주문번호 순서를 주기로 하면 정렬 키에 ORD_SEQ가 들어가고, 인덱스에도 ORD_SEQ가 있어야 테이블 없이 순위를 매긴다. 동점 규칙이 인덱스 설계를 바꾼다.
W3의 다른 길: 이번 달 고객별 합계를 5분마다 다시 더하지 않으려면 집계를 미리 계산해 두는 방법(구체화 뷰)이 있다. 하지만 주문이 계속 들어오는 이번 달을 실시간으로 보여 줘야 하므로 새로 고침 비용과 신선도를 함께 따져야 한다(SORT-067 ~ 069). 164블록이면 그럴 까닭이 없다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황