매출 분석 개편 SQL 네 개 가운데 셋이 대용량 주문을 통째로 읽고, 고객 구매 달력 적재는 TEMP까지 쓴다

실전DDL한빛유통348 / 700

상황

영업기획팀의 「매출 분석」 개편으로 T_ORD_BIG을 읽는 SQL 네 개(S1 ~ S4)가 새로 붙는다. 화면 S1은 하루 약 300번, 정산팀 화면 S3은 하루 약 600번, 야간 적재 S2와 월 리포트 S4는 한 번씩 돈다. 개발 DB에서 잰 실행계획을 보면 S1, S2, S4가 테이블을 통째로 읽고 S2는 TEMP까지 쓴다. 박팀장의 주문은 이렇다. "인덱스는 지금 있는 두 개에서 늘리지 않는 것이 목표다. SQL마다 정렬이나 묶기가 사라지는지 남는지 적고, 남기는 것은 왜 남겨도 되는지 써 달라."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_SEQ, CUS_ID, ORD_ST, ORD_AMT는 NOT NULL이고 ORD_DT는 NULL 허용(실제 0건) · ORD_DT는 전부 자정이고 하루 약 913건 · 2024년 4분기 83,941건 · 고객 2,000명, 고객당 약 500건 · 같은 날짜의 주문도 한 고객의 주문도 테이블 곳곳에 흩어져 있다
지금 있는 인덱스: IX_ORD_BIG_DT_SEQ (ORD_DT, ORD_SEQ)(09화, 주문 장부의 키 기반 페이징이 쓴다) · BLEVEL 2 · 리프 3,344블록 · CF 1,000,000 / IX_ORD_BIG_CUS_DT (CUS_ID, ORD_DT)(CS 화면의 고객 한 명 기간 조회가 쓴다) · BLEVEL 2 · 리프 약 3,000블록
T_CUS_DAY(고객 구매 달력, 이 문제용 테이블): 매일 밤 TRUNCATE 뒤 S2로 다시 채운다. 고객과 날짜의 조합은 약 80만 개다.
02시의 work area 상한은 약 20MB다. 통계는 최신이다.

원본 SQL

sql

-- S1 「분기 일자별 상태별 매출」 (화면, 하루 약 300번)
SELECT ORD_DT, ORD_ST, COUNT(*) AS CNT, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :q_from AND ORD_DT < :q_to
 GROUP BY ORD_DT, ORD_ST
 ORDER BY ORD_DT, ORD_ST;

-- S2 「고객 구매 달력」 적재 (야간 한 번)
INSERT /*+ APPEND */ INTO T_CUS_DAY (CUS_ID, ORD_DT, CNT, AMT)
SELECT CUS_ID, ORD_DT, COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 GROUP BY CUS_ID, ORD_DT;

-- S3 「일 주문 장부」 (정산팀 화면, 하루 약 600번)
SELECT ORD_SEQ, CUS_ID, ORD_AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d AND ORD_DT < :d + 1
 ORDER BY ORD_SEQ;

-- S4 「고객별 연간 매출 순위」 (월 리포트)
SELECT CUS_ID, SUM(ORD_AMT) AS AMT
  FROM T_ORD_BIG
 WHERE ORD_DT >= DATE '2024-01-01' AND ORD_DT < DATE '2025-01-01'
 GROUP BY CUS_ID
 ORDER BY AMT DESC;

실행계획 학습용 예시

xplan

[S1 · 2024년 4분기]
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |    276 |00:00:00.21 |    5826 |       |       |          |         |
|   1 |  SORT GROUP BY                       |                   |      1 |    276 |    276 |00:00:00.21 |    5826 | 31744 | 31744 |28672  (0)|         |
|*  2 |   TABLE ACCESS FULL                  | T_ORD_BIG         |      1 |  83941 |  83941 |00:00:00.12 |    5826 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------
   2 - filter(("ORD_DT">=:Q_FROM AND "ORD_DT"<:Q_TO))

[S2]
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                     |                   |      1 |        |      0 |00:00:06.31 |    5941 |       |       |          |         |
|   1 |  LOAD AS SELECT                      | T_CUS_DAY         |      1 |        |      0 |00:00:06.31 |    5941 |       |       |          |         |
|   2 |   OPTIMIZER STATISTICS GATHERING     |                   |      1 |   1000K|    802K|00:00:05.12 |    5833 |       |       |          |         |
|   3 |    HASH GROUP BY                     |                   |      1 |   1000K|    802K|00:00:04.97 |    5833 |    46M|  6118K|   20M (1)|      39M|
|   4 |     TABLE ACCESS FULL                | T_ORD_BIG         |      1 |   1000K|   1000K|00:00:00.19 |    5826 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------

[S3 · 하루]
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |    913 |00:00:00.01 |     918 |       |       |          |         |
|   1 |  SORT ORDER BY                       |                   |      1 |    913 |    913 |00:00:00.01 |     918 | 59392 | 59392 |53248  (0)|         |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG         |      1 |    913 |    913 |00:00:00.01 |     918 |       |       |          |         |
|*  3 |    INDEX RANGE SCAN                  | IX_ORD_BIG_DT_SEQ |      1 |    913 |    913 |00:00:00.01 |       5 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------
   3 - access("ORD_DT">=:D AND "ORD_DT"<:D+1)

[S4]
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem | Used-Tmp|
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |                   |      1 |        |   2000 |00:00:00.15 |    5826 |       |       |          |         |
|   1 |  SORT ORDER BY                       |                   |      1 |   2000 |   2000 |00:00:00.15 |    5826 | 83968 | 83968 |74752  (0)|         |
|   2 |   HASH GROUP BY                      |                   |      1 |   2000 |   2000 |00:00:00.15 |    5826 |  1201K|  1201K| 1421K (0)|         |
|*  3 |    TABLE ACCESS FULL                 | T_ORD_BIG         |      1 |    334K|    333K|00:00:00.09 |    5826 |       |       |          |         |
------------------------------------------------------------------------------------------------------------------------------------------------
   3 - filter(("ORD_DT">=TO_DATE(' 2024-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "ORD_DT"<TO_DATE(' 2025-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

문제

S1 ~ S4가 지금 무엇 때문에 테이블을 통째로 읽거나 정렬하고 묶는지 SQL마다 실행계획의 줄로 설명하시오.
인덱스 개수를 두 개로 유지하면서 S1 ~ S4의 일량을 줄이는 인덱스를 DDL로 쓰고, SQL마다 그 인덱스를 어떻게 쓰는지와 정렬이나 묶기가 사라지는지 남는지 쓰시오.
남기는 정렬은 왜 남겨도 되는지, 바꾼 인덱스가 원래 쓰던 화면과 적재에 무엇을 바꾸는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 주문일 인덱스를 넓힌다 : 앞 두 컬럼(ORD_DT, ORD_SEQ)이 같아 S3 과 키 기반 페이징은 그대로 · S1 은 테이블 없이 분기 범위만
CREATE INDEX IX_ORD_BIG_DT_SEQ_ST_AMT ON T_ORD_BIG (ORD_DT, ORD_SEQ, ORD_ST, ORD_AMT) INVISIBLE;
-- ② 고객 인덱스를 넓힌다 : 앞 두 컬럼(CUS_ID, ORD_DT)이 같아 CS 화면은 그대로 · S2 는 고객, 날짜 순서로 읽으며 묶는다 · S4 는 인덱스만
CREATE INDEX IX_ORD_BIG_CUS_DT_AMT ON T_ORD_BIG (CUS_ID, ORD_DT, ORD_AMT) INVISIBLE;
-- 세션에서 OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE 로 S1 ~ S4, CS 화면, 키 기반 페이징을 잰 뒤
ALTER INDEX IX_ORD_BIG_DT_SEQ_ST_AMT VISIBLE;
ALTER INDEX IX_ORD_BIG_CUS_DT_AMT VISIBLE;
DROP INDEX IX_ORD_BIG_DT_SEQ;
DROP INDEX IX_ORD_BIG_CUS_DT;

-- S2 : 고객, 날짜 순서로 인덱스만 읽으며 묶는다 (SORT GROUP BY NOSORT · 작업 공간 없음)
INSERT /*+ APPEND */ INTO T_CUS_DAY (CUS_ID, ORD_DT, CNT, AMT)
SELECT /*+ INDEX(o IX_ORD_BIG_CUS_DT_AMT) */
       o.CUS_ID, o.ORD_DT, COUNT(*), SUM(o.ORD_AMT)
  FROM T_ORD_BIG o
 GROUP BY o.CUS_ID, o.ORD_DT;

-- S1 · S3 · S4 : SQL 그대로 (S1 의 276개 묶음, S3 의 913건 정렬, S4 의 2,000줄 정렬은 남긴다)

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

xplan

[S1 · ① 의 분기 범위만]
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                          |      1 |        |    276 |00:00:00.04 |     385 |       |       |          |
|   1 |  SORT GROUP BY                   |                          |      1 |    276 |    276 |00:00:00.04 |     385 | 31744 | 31744 |28672  (0)|
|*  2 |   INDEX RANGE SCAN               | IX_ORD_BIG_DT_SEQ_ST_AMT |      1 |  83941 |  83941 |00:00:00.02 |     385 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------
   2 - access("ORD_DT">=:Q_FROM AND "ORD_DT"<:Q_TO)

[S2 · ② 를 순서대로 읽으며 묶기]
---------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------
|   0 | INSERT STATEMENT                 |                          |      1 |        |      0 |00:00:01.42 |    4288 |       |       |          |
|   1 |  LOAD AS SELECT                  | T_CUS_DAY                |      1 |        |      0 |00:00:01.42 |    4288 |       |       |          |
|   2 |   OPTIMIZER STATISTICS GATHERING |                          |      1 |   1000K|    802K|00:00:01.10 |    4173 |       |       |          |
|   3 |    SORT GROUP BY NOSORT          |                          |      1 |   1000K|    802K|00:00:00.96 |    4173 |       |       |          |
|   4 |     INDEX FULL SCAN              | IX_ORD_BIG_CUS_DT_AMT    |      1 |   1000K|   1000K|00:00:00.41 |    4173 |       |       |          |
---------------------------------------------------------------------------------------------------------------------------------------

[S3]  ① 의 앞부분(ORD_DT)으로 하루 범위 · 테이블 913번 · SORT ORDER BY 913건(53K) 그대로 · Buffers 918 → 919
[S4]  ② 를 INDEX FAST FULL SCAN(4,180) · HASH GROUP BY 2,000 · SORT ORDER BY 2,000 그대로 · Buffers 5,826 → 4,180

모범 답안 서술

S1은 분기 범위 83,941건을 고를 알맞은 인덱스가 없어 2번 줄이 테이블을 통째로 읽는다(5,826블록). 지금 있는 IX_ORD_BIG_DT_SEQ로 범위를 잡으면 상태와 금액을 보러 테이블을 8만 번 넘게 찾아가야 해서(클러스터링 팩터 1,000,000) FULL이 낫다고 본 것이다. 1번 줄 SORT GROUP BY는 276개 묶음이라 작업 공간이 28K로 작다. S2는 3번 줄 HASH GROUP BY가 문제다. 100만 행을 고객과 날짜 조합 약 80만 개로 묶는데, 해시로 묶으면 작업 공간이 묶음 수에 비례해 46MB가 필요하고 02시 상한 20MB에서 one-pass로 TEMP를 39MB 쓴다(6.3초). S3은 IX_ORD_BIG_DT_SEQ로 하루 913건을 찾아 테이블에 913번 가지만, 조건이 범위(>=, <)라서 옵티마이저는 인덱스 순서를 ORD_SEQ 순서로 볼 수 없어 1번 줄에서 913건을 다시 정렬한다. S4는 연간 33만 행을 읽으려고 테이블을 통째로 읽고, 고객 2,000명으로 묶은 뒤 합계순으로 정렬한다. 인덱스는 새로 만들지 않고 두 개를 넓힌다. ① (ORD_DT, ORD_SEQ, ORD_ST, ORD_AMT)는 S1에 필요한 컬럼을 모두 담아 분기 범위의 리프 약 385블록만 읽게 한다(5,826 → 385). ② (CUS_ID, ORD_DT, ORD_AMT)는 S2가 인덱스를 고객, 날짜 순서대로 읽으며 묶게 한다. 들어오는 순서가 이미 묶음 순서라 SORT GROUP BY NOSORT가 조합이 바뀔 때마다 한 줄씩 내보내므로 작업 공간도 TEMP도 필요 없다(6.3초 → 1.4초, 5,833 → 4,173). CUS_ID가 NOT NULL이라 모든 행이 ②에 있다는 것이 보장되므로 조건 없는 전체 집계를 인덱스로 처리할 수 있다. S4는 ②를 FAST FULL SCAN으로 읽어 테이블에 가지 않는다(5,826 → 4,180). 남기는 정렬과 묶기는 작아서 남겨도 된다. S1의 SORT GROUP BY는 276개 묶음이다. ①에서 ORD_SEQ가 ORD_ST보다 앞이라 NOSORT는 안 되지만, 그 정렬을 없애려고 (ORD_DT, ORD_ST, ORD_AMT) 인덱스를 하나 더 만들 가치는 없다. S3의 913건 정렬(53K)은 하루 600번이어도 작다. S4는 합계(AMT)로 정렬하므로 집계가 끝나기 전에는 순서를 알 수 없어 정렬을 없앨 수 없고, 2,000줄이라 작다. 두 인덱스 모두 앞 두 컬럼이 원래 인덱스와 같아서, 원래 인덱스를 쓰던 CS 화면(고객 한 명 기간 조회)과 키 기반 페이징은 같은 범위를 읽는다. 원래 인덱스 둘을 지우므로 인덱스는 2개 그대로다. 대가는 인덱스가 넓어지는 것이다. ①은 리프가 3,344 → 약 4,550블록, ②는 약 3,000 → 약 4,170블록으로 커져 주문을 넣을 때마다 고치는 항목이 조금 무거워지고, CS 화면과 페이징도 같은 범위에서 리프를 조금 더 읽는다. 개수가 늘지 않으므로 적재 비용의 증가는 작다.

해설

S3을 등치로 바꾸면: ORD_DT가 전부 자정이므로 WHERE ORD_DT = :d로 써도 결과가 같고, 등치 뒤의 ORD_SEQ는 인덱스 순서 그대로라 913건 정렬이 사라진다. 다만 '전부 자정'은 지금 데이터의 성질일 뿐 제약이 아니다. 시각이 든 주문이 한 건이라도 들어오면 그 주문이 장부에서 조용히 빠진다. 913건 정렬과 맞바꿀 위험이 아니므로 범위 조건을 둔다. 굳이 바꾸려면 CHECK (ORD_DT = TRUNC(ORD_DT)) 같은 제약부터 건다.
인덱스를 셋으로 늘리는 안: S1용으로 (ORD_DT, ORD_ST, ORD_AMT)를 따로 만들면 S1도 NOSORT가 되지만 줄어드는 것은 276개 묶음의 정렬뿐이다. 인덱스 하나만큼의 적재 비용(교재 14화 적재 청구서)과 견주면 손해다.
S2를 인덱스 순서로 읽게 하는 힌트는 지금의 판단(80만 묶음, 상한 20MB)에서 나온 것이다. 고객이 늘어 INDEX FULL SCAN과 FAST FULL SCAN + 해시의 차이가 바뀌면 다시 잰다. 이 버전에서는 INVISIBLE 인덱스로 정렬을 대신하는 계획을 스스로 고르지 않는 일이 있었으므로 힌트로 따로 확인한다(교재 09화 §7).
S4가 상위 몇 명만 보면 된다면 FETCH FIRST n ROWS ONLY로 Top-N 정렬을 쓴다(SORT s1의 주제).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황