VIP 관리 화면 묶음 세 개가 대용량 주문 테이블을 하루 4,600번 통째로 읽는다

실전DDL한빛유통252 / 700

상황

CRM팀의 VIP 관리 화면에는 SQL 세 개가 있다. Q1 「VIP 목록」(A 등급 20명의 올해 주문 건수와 금액)은 하루 300번, Q2 「고객 최근 주문 10건」은 콜센터가 하루 4,000번, Q3 「상품별 평균 평점」은 하루 50번 돈다. 고객사는 "스칼라 서브쿼리가 있는 SQL마다 인덱스를 하나씩 만들어 달라"라고 요청했다. 박팀장은 인덱스를 최소한으로 정하고, 필요 없는 곳은 왜 필요 없는지 근거를 대라고 했다.

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · 인덱스 없음 · 고객마다 500건(2,000행 간격으로 흩어져 있다) · 2024년 333,245건(VIP 20명은 3,330건, 1명당 약 167건)
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · CUS_GD A 20명
M_ITM: 100행 · 5블록 · PK_M_ITM · T_ITM_EVL: 5,000행 · 48블록 · PK_T_ITM_EVL (ITM_ID, EVL_LST_NO) · 상품당 50건
주문은 하루 약 900건 들어온다 · 통계 최신 · 스칼라 서브쿼리 Unnesting은 켜져 있다

원본 SQL

sql

-- Q1 「VIP 목록」 (하루 300 번)
SELECT c.CUS_ID, c.CUS_NM,
       (SELECT COUNT(*) FROM T_ORD_BIG o
         WHERE o.CUS_ID = c.CUS_ID
           AND o.ORD_DT >= DATE '2024-01-01' AND o.ORD_DT < DATE '2025-01-01') AS ord_cnt,
       (SELECT SUM(o.ORD_AMT) FROM T_ORD_BIG o
         WHERE o.CUS_ID = c.CUS_ID
           AND o.ORD_DT >= DATE '2024-01-01' AND o.ORD_DT < DATE '2025-01-01') AS ord_amt
  FROM M_CUS c
 WHERE c.CUS_GD = 'A'
 ORDER BY c.CUS_ID;

-- Q2 「고객 최근 주문 10건」 (하루 4,000 번)
SELECT *
  FROM (SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, o.ORD_AMT
          FROM T_ORD_BIG o
         WHERE o.CUS_ID = :cus
         ORDER BY o.ORD_DT DESC)
 WHERE ROWNUM <= 10;

-- Q3 「상품별 평균 평점」 (하루 50 번)
SELECT i.ITM_ID, i.ITM_NM,
       (SELECT ROUND(AVG(e.EVL_PT), 2) FROM T_ITM_EVL e WHERE e.ITM_ID = i.ITM_ID) AS avg_pt
  FROM M_ITM i
 ORDER BY i.ITM_ID;

실행계획 학습용 예시

xplan

[Q1]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |     20 |00:00:00.29 |   11682 |
|   1 |  SORT ORDER BY         |           |      1 |     20 |     20 |00:00:00.29 |   11682 |
|*  2 |   HASH JOIN OUTER      |           |      1 |     20 |     20 |00:00:00.29 |   11682 |
|*  3 |    HASH JOIN OUTER     |           |      1 |     20 |     20 |00:00:00.15 |    5856 |
|*  4 |     TABLE ACCESS FULL  | M_CUS     |      1 |     20 |     20 |00:00:00.01 |      30 |
|   5 |     VIEW               | VW_SSQ_1  |      1 |   2000 |   2000 |00:00:00.14 |    5826 |
|   6 |      HASH GROUP BY     |           |      1 |   2000 |   2000 |00:00:00.14 |    5826 |
|*  7 |       TABLE ACCESS FULL| T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |
|   8 |    VIEW                | VW_SSQ_2  |      1 |   2000 |   2000 |00:00:00.14 |    5826 |
|   9 |     HASH GROUP BY      |           |      1 |   2000 |   2000 |00:00:00.14 |    5826 |
|* 10 |      TABLE ACCESS FULL | T_ORD_BIG |      1 |    333K|    333K|00:00:00.05 |    5826 |
-----------------------------------------------------------------------------------------------
   2 - access("VW_SSQ_2"."ITEM_1"(+)="C"."CUS_ID")
   3 - access("VW_SSQ_1"."ITEM_1"(+)="C"."CUS_ID")
   4 - filter("C"."CUS_GD"='A')
   7 - filter(("O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
  10 - filter((같은 모양))

[Q2]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |     10 |00:00:00.05 |    5826 |
|*  1 |  COUNT STOPKEY         |           |      1 |        |     10 |00:00:00.05 |    5826 |
|   2 |   VIEW                 |           |      1 |    500 |     10 |00:00:00.05 |    5826 |
|*  3 |    SORT ORDER BY STOPKEY|          |      1 |    500 |     10 |00:00:00.05 |    5826 |
|*  4 |     TABLE ACCESS FULL  | T_ORD_BIG |      1 |    500 |    500 |00:00:00.05 |    5826 |
-----------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   4 - filter("O"."CUS_ID"=:CUS)

[Q3]
-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |    100 |00:00:00.01 |      53 |
|   1 |  SORT ORDER BY         |           |      1 |    100 |    100 |00:00:00.01 |      53 |
|*  2 |   HASH JOIN OUTER      |           |      1 |    100 |    100 |00:00:00.01 |      53 |
|   3 |    TABLE ACCESS FULL   | M_ITM     |      1 |    100 |    100 |00:00:00.01 |       5 |
|   4 |    VIEW                | VW_SSQ_1  |      1 |    100 |    100 |00:00:00.01 |      48 |
|   5 |     HASH GROUP BY      |           |      1 |    100 |    100 |00:00:00.01 |      48 |
|   6 |      TABLE ACCESS FULL | T_ITM_EVL |      1 |   5000 |   5000 |00:00:00.01 |      48 |
-----------------------------------------------------------------------------------------------
   2 - access("VW_SSQ_1"."ITEM_1"(+)="I"."ITM_ID")

문제

세 SQL의 일량이 어디서 나오는지 줄 번호와 Buffers로 설명하고, 하루 실행 횟수를 곱해 어느 SQL이 가장 급한지 정하시오.
인덱스를 최소한으로 설계하고(DDL), 고쳐야 할 SQL은 고치시오. 인덱스가 필요 없는 SQL은 그 이유를 쓰시오.
인덱스 컬럼 순서와 ORD_AMT를 인덱스에 넣을지를 비교하고, 인덱스를 더하는 대가를 서술하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 인덱스 하나 : 고객번호(=) → 주문일(범위 · 정렬) → 금액(Q1 이 테이블에 가지 않게)
CREATE INDEX IX_ORD_BIG_CUS_DT_AMT ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_AMT);

-- Q1 : 같은 주문을 두 번 읽던 스칼라 서브쿼리 두 개 → 조인 + GROUP BY 하나 (VIP 20 명만 인덱스로)
--      주문이 없는 VIP 도 스칼라 서브쿼리처럼 0 이 나오게 외부 조인 + COUNT(o.CUS_ID)
SELECT c.CUS_ID, c.CUS_NM,
       COUNT(o.CUS_ID) AS ord_cnt,
       SUM(o.ORD_AMT)  AS ord_amt
  FROM M_CUS c, T_ORD_BIG o
 WHERE c.CUS_GD = 'A'
   AND o.CUS_ID(+) = c.CUS_ID
   AND o.ORD_DT(+) >= DATE '2024-01-01'
   AND o.ORD_DT(+) <  DATE '2025-01-01'
 GROUP BY c.CUS_ID, c.CUS_NM
 ORDER BY c.CUS_ID;

-- Q2 : SQL 은 그대로 둔다 (인덱스를 거꾸로 읽다 10 건에서 멈춘다)
-- Q3 : 그대로 둔다 (이미 평가 5,000 건을 한 번 묶어 붙인다 · 인덱스 불필요)

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

xplan

[Q1 — 인덱스 + 조인 하나]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |      1 |        |     20 |00:00:00.01 |     100 |
|   1 |  SORT GROUP BY               |                       |      1 |     20 |     20 |00:00:00.01 |     100 |
|   2 |   NESTED LOOPS OUTER         |                       |      1 |   3340 |   3330 |00:00:00.01 |     100 |
|*  3 |    TABLE ACCESS FULL         | M_CUS                 |      1 |     20 |     20 |00:00:00.01 |      30 |
|*  4 |    INDEX RANGE SCAN          | IX_ORD_BIG_CUS_DT_AMT |     20 |    167 |   3330 |00:00:00.01 |      70 |
---------------------------------------------------------------------------------------------------------
   3 - filter("C"."CUS_GD"='A')
   4 - access("O"."CUS_ID"="C"."CUS_ID" AND "O"."ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND "O"."ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

[Q2 — SQL 그대로 · 새 인덱스]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                       |      1 |        |     10 |00:00:00.01 |      13 |
|*  1 |  COUNT STOPKEY               |                       |      1 |        |     10 |00:00:00.01 |      13 |
|   2 |   VIEW                       |                       |      1 |     10 |     10 |00:00:00.01 |      13 |
|   3 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG            |      1 |    500 |     10 |00:00:00.01 |      13 |
|*  4 |     INDEX RANGE SCAN DESCENDING| IX_ORD_BIG_CUS_DT_AMT|      1 |    500 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=10)
   4 - access("O"."CUS_ID"=:CUS)

[하루 일량]  Q1 300 × 11,682 + Q2 4,000 × 5,826 = 약 2,680만 블록  →  300 × 100 + 4,000 × 13 = 약 8만 블록
             Q3 50 × 53 = 2,650 블록 (그대로)

모범 답안 서술

Q1은 VIP 20명의 2024년 주문 건수와 금액을 스칼라 서브쿼리 두 개로 구한다. 옵티마이저가 두 서브쿼리를 각각 펼쳐(5번과 8번 줄의 VW_SSQ_1, VW_SSQ_2) T_ORD_BIG을 두 번 통째로 읽고, 고객 2,000명을 다 묶은 뒤 VIP 20명에만 붙인다(5,826 × 2 = 11,652블록). 하루 300번이면 약 350만 블록이다. Q2는 고객 한 명의 최근 주문 10건을 보려고 100만 행을 다 읽어 정렬한다(5,826블록). 콜센터가 하루 4,000번 열어 약 2,330만 블록이 되므로 가장 급하다. Q3는 평가 5,000건을 한 번 묶어(VW_SSQ_1, 48블록) 상품 100개에 붙이므로 이미 53블록이다. Q1과 Q2는 둘 다 '고객번호 = 값'과 '주문일 범위 또는 주문일 정렬'로 같은 주문을 찾는다. 그래서 인덱스 하나 (CUS_ID, ORD_DT, ORD_AMT)로 둘을 함께 해결한다. 고객번호가 = 조건이라 앞에, 주문일은 범위와 정렬이라 그 뒤에 둔다. Q2는 SQL을 고치지 않아도 인덱스를 거꾸로 읽다 10건에서 멈춘다(5,826 → 13블록). Q1은 같은 주문을 두 번 읽는 스칼라 서브쿼리 두 개를 조인 + GROUP BY 하나로 합치고, VIP 20명만 인덱스로 찾아간다(고객당 2024년 주문 약 167건, 리프 1 ~ 2블록). 11,682 → 100블록이다. 주문이 없는 VIP가 생겨도 스칼라 서브쿼리처럼 0이 나오도록 외부 조인과 COUNT(o.CUS_ID)를 쓴다. ORD_AMT를 인덱스에 넣으면 Q1이 테이블에 가지 않는다(커버링). 넣지 않으면 VIP 주문 3,330건마다 테이블을 찾아가 약 3,400블록이 된다. Q2는 다른 컬럼(ORD_ST)도 보여 주므로 어차피 10건은 테이블에 간다. Q3에는 인덱스가 필요 없다. 옵티마이저가 이미 평가를 한 번만 읽어 묶고 있어서, (ITM_ID, EVL_PT) 인덱스를 만들어도 48블록에서 몇 블록 줄일 뿐이다. 대가는 100만 행 테이블에 인덱스가 하나 생긴다는 것이다. 리프가 약 4,500블록이고, 주문이 들어올 때마다 인덱스 블록도 고치므로 적재 배치가 그만큼 느려진다(교재 14화). 하루 약 2,680만 블록을 8만 블록으로 줄이는 대가로 이 비용을 승인서에 적는다.

해설

컬럼 순서를 바꾸면: (ORD_DT, CUS_ID)로 만들면 Q1은 2024년 전체(333,245건)를 훑으며 고객번호를 거르고, Q2는 정렬을 대신하지 못한다. 등치 조건 컬럼을 앞에, 범위와 정렬 컬럼을 뒤에 둔다.
Q1을 스칼라 서브쿼리로 남기는 답: 인덱스가 생기면 두 서브쿼리가 VIP 20명마다 인덱스만 읽어 약 140블록이 된다. 이것도 답이지만 같은 주문을 두 번 읽고, 옵티마이저가 다시 펼치지 않는지 계획으로 확인해야 한다.
Q3에 인덱스를 더하는 함정: 스칼라 서브쿼리라고 모두 인덱스가 필요한 것은 아니다. 옵티마이저가 이미 한 번 묶어 붙이고 있으면(VW_SSQ) 인덱스는 적재 비용만 늘린다. '서브쿼리마다 인덱스 하나'라는 요청은 SQL을 보지 않고 세운 규칙이다.
인덱스를 만든 뒤에는 세 SQL의 계획이 기대대로인지, T_ORD_BIG을 읽는 다른 SQL의 계획이 새 인덱스로 옮겨 가며 나빠지지 않았는지 확인한다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황