인덱스가 하나도 없는 정산 테이블에 정산 모니터 화면 SQL 다섯 개가 붙는다

실전DDL한빛유통322 / 700

상황

정산 모니터 화면을 새로 만든다. 화면 SQL 다섯 개(S1 ~ S5)가 모두 T_ORD_STLM을 읽는데, 이 테이블에는 인덱스가 하나도 없다. 매일 밤 비우고 직접 경로(/*+ APPEND */)로 10만 건을 다시 싣기 때문이다(DML-001). 박팀장의 주문은 이렇다. "인덱스는 최소로. 다섯 SQL이 각각 어느 인덱스를 어떻게 쓰는지 줄 단위로 보여 주고, 밤 적재에 무엇이 늘어나는지도 적어 줘" 화면 SQL은 개발자가 쓴 그대로다. 정산 담당 20명이 낮 동안 이 화면을 연다.

테이블과 인덱스

T_ORD_STLM 정산: 100,000행 · 640블록 · STLM_SEQ NOT NULL(정산 번호, 유일) · ORD_SEQ · CUS_ID · STLM_DT DATE NOT NULL(주문 시각을 옮긴 값, 시분초 있음, 2023-01-01 ~ 2024-12-31, 하루 약 137건) · STLM_AMT
야간 배치가 TRUNCATE 뒤 T_ORD 순서대로 적재한다. 하루치 정산과 한 고객의 정산 50건은 테이블 곳곳에 흩어져 있다
인덱스 없음 · 통계는 적재 직후 수집 · 화면은 낮에만 쓴다

원본 SQL

sql

-- S1 「최근 정산 20건」
SELECT STLM_SEQ, CUS_ID, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 ORDER BY STLM_DT DESC, STLM_SEQ DESC
 FETCH FIRST 20 ROWS ONLY;

-- S2 「고객 마지막 정산일」
SELECT MAX(STLM_DT) FROM T_ORD_STLM WHERE CUS_ID = :cus;

-- S3 「고객 최근 정산 5건」
SELECT STLM_SEQ, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE CUS_ID = :cus
 ORDER BY STLM_DT DESC, STLM_SEQ DESC
 FETCH FIRST 5 ROWS ONLY;

-- S4 「그날 고액 정산 10건」
SELECT STLM_SEQ, CUS_ID, STLM_DT, STLM_AMT
  FROM T_ORD_STLM
 WHERE STLM_DT >= :d AND STLM_DT < :d + 1
   AND ROWNUM <= 10
 ORDER BY STLM_AMT DESC;

-- S5 「정산 데이터 기간」
SELECT MIN(STLM_DT), MAX(STLM_DT) FROM T_ORD_STLM;

실행계획 학습용 예시

xplan

[S1 · S2 · S3 · S5 는 TABLE ACCESS FULL 640블록 · S4 는 FULL 도중 10건에서 멈춤]
-----------------------------------------------------------------------------------------------------
| Id  | Operation                | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------
S1
|   0 | SELECT STATEMENT         |            |      1 |        |     20 |00:00:00.03 |     640 |
|*  1 |  VIEW                    |            |      1 |     20 |     20 |00:00:00.03 |     640 |
|*  2 |   WINDOW SORT PUSHED RANK|            |      1 |    100K|     20 |00:00:00.03 |     640 |
|   3 |    TABLE ACCESS FULL     | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |
S2
|   0 | SELECT STATEMENT         |            |      1 |        |      1 |00:00:00.01 |     640 |
|   1 |  SORT AGGREGATE          |            |      1 |      1 |      1 |00:00:00.01 |     640 |
|*  2 |   TABLE ACCESS FULL      | T_ORD_STLM |      1 |     50 |     50 |00:00:00.01 |     640 |
S3
|   0 | SELECT STATEMENT         |            |      1 |        |      5 |00:00:00.01 |     640 |
|*  1 |  VIEW                    |            |      1 |      5 |      5 |00:00:00.01 |     640 |
|*  2 |   WINDOW SORT PUSHED RANK|            |      1 |     50 |      5 |00:00:00.01 |     640 |
|*  3 |    TABLE ACCESS FULL     | T_ORD_STLM |      1 |     50 |     50 |00:00:00.01 |     640 |
S4
|   0 | SELECT STATEMENT         |            |      1 |        |     10 |00:00:00.01 |      47 |
|   1 |  SORT ORDER BY           |            |      1 |     10 |     10 |00:00:00.01 |      47 |
|*  2 |   COUNT STOPKEY          |            |      1 |        |     10 |00:00:00.01 |      47 |
|*  3 |    FILTER                |            |      1 |        |     10 |00:00:00.01 |      47 |
|*  4 |     TABLE ACCESS FULL    | T_ORD_STLM |      1 |    137 |     10 |00:00:00.01 |      47 |
S5
|   0 | SELECT STATEMENT         |            |      1 |        |      1 |00:00:00.01 |     640 |
|   1 |  SORT AGGREGATE          |            |      1 |      1 |      1 |00:00:00.01 |     640 |
|   2 |   TABLE ACCESS FULL      | T_ORD_STLM |      1 |    100K|    100K|00:00:00.01 |     640 |
-----------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   S1 2 - filter(ROW_NUMBER() OVER ( ORDER BY INTERNAL_FUNCTION("STLM_DT") DESC ,INTERNAL_FUNCTION("STLM_SEQ") DESC )<=20)
   S2 2 - filter("CUS_ID"=:CUS)
   S3 3 - filter("CUS_ID"=:CUS)
   S4 2 - filter(ROWNUM<=10)
      3 - filter(:D+1>:D)
      4 - filter(("STLM_DT">=:D AND "STLM_DT"<:D+1))

문제

다섯 SQL 가운데 결과가 틀린 SQL을 찾아 고치고, 나머지 넷이 지금 각각 무엇을 읽는지 설명하시오.
다섯 SQL을 모두 처리하는 최소 인덱스 세트를 DDL로 제안하고, SQL마다 어느 인덱스를 어떤 방식(정렬 대신, FIRST ROW, 범위를 읽은 뒤 정렬)으로 쓰는지 쓰시오. 만들지 않기로 한 인덱스와 그 이유도 쓰시오.
이 인덱스 세트가 밤 적재에 더하는 일과 그 대처를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① STLM_DT 선두 : S1 (정렬 대신) · S4 (그날 범위) · S5 (양 끝 FIRST ROW) · STLM_SEQ 까지 넣어 S1 정렬 키 전체를 담는다
CREATE INDEX IX_STLM_DT_SEQ ON T_ORD_STLM (STLM_DT, STLM_SEQ);
-- ② CUS_ID + STLM_DT : S2 (고객 범위의 끝 FIRST ROW) · S3 (고객 범위를 거꾸로 5 건)
CREATE INDEX IX_STLM_CUS_DT_SEQ ON T_ORD_STLM (CUS_ID, STLM_DT, STLM_SEQ);
-- 만들지 않는다 : (STLM_AMT) 는 S4 가 그날 137 건만 정렬하면 되므로 · (CUS_ID) 단독은 ② 가 대신하므로

-- S4 : 정렬한 뒤에 10 건 (지금은 아무 10 건을 정렬한다)
SELECT STLM_SEQ, CUS_ID, STLM_DT, STLM_AMT
  FROM (SELECT STLM_SEQ, CUS_ID, STLM_DT, STLM_AMT
          FROM T_ORD_STLM
         WHERE STLM_DT >= :d AND STLM_DT < :d + 1
         ORDER BY STLM_AMT DESC, STLM_SEQ)
 WHERE ROWNUM <= 10;

-- S5 : 끝 하나씩 따로
SELECT (SELECT MIN(STLM_DT) FROM T_ORD_STLM) AS first_dt,
       (SELECT MAX(STLM_DT) FROM T_ORD_STLM) AS last_dt
  FROM DUAL;

-- S1 · S2 · S3 은 SQL 그대로

-- 밤 적재 : 인덱스를 UNUSABLE 로 두고 실은 뒤 REBUILD (단계별로 시간을 따로 잰다)
-- ALTER INDEX IX_STLM_DT_SEQ UNUSABLE;  ALTER INDEX IX_STLM_CUS_DT_SEQ UNUSABLE;
-- INSERT APPEND 적재 → COMMIT
-- ALTER INDEX IX_STLM_DT_SEQ REBUILD;   ALTER INDEX IX_STLM_CUS_DT_SEQ REBUILD;

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

xplan

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
S1 · ① 을 거꾸로 · 정렬 없음
|   0 | SELECT STATEMENT                     |                    |      1 |        |     20 |00:00:00.01 |      23 |
|*  1 |  VIEW                                |                    |      1 |     20 |     20 |00:00:00.01 |      23 |
|*  2 |   WINDOW NOSORT STOPKEY              |                    |      1 |     20 |     20 |00:00:00.01 |      23 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | T_ORD_STLM         |      1 |    100K|     20 |00:00:00.01 |      23 |
|   4 |     INDEX FULL SCAN DESCENDING       | IX_STLM_DT_SEQ     |      1 |     20 |     20 |00:00:00.01 |       3 |
S2 · ② FIRST ROW
|   0 | SELECT STATEMENT                     |                    |      1 |        |      1 |00:00:00.01 |       2 |
|   1 |  SORT AGGREGATE                      |                    |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   FIRST ROW                          |                    |      1 |      1 |      1 |00:00:00.01 |       2 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)        | IX_STLM_CUS_DT_SEQ |      1 |      1 |      1 |00:00:00.01 |       2 |
S3 · ② 를 거꾸로 5 건
|   0 | SELECT STATEMENT                     |                    |      1 |        |      5 |00:00:00.01 |       7 |
|*  1 |  VIEW                                |                    |      1 |      5 |      5 |00:00:00.01 |       7 |
|*  2 |   WINDOW NOSORT STOPKEY              |                    |      1 |      5 |      5 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID       | T_ORD_STLM         |      1 |     50 |      5 |00:00:00.01 |       7 |
|*  4 |     INDEX RANGE SCAN DESCENDING      | IX_STLM_CUS_DT_SEQ |      1 |      5 |      5 |00:00:00.01 |       2 |
S4 · ① 로 그날 범위를 읽고 금액순 Top-N
|   0 | SELECT STATEMENT                     |                    |      1 |        |     10 |00:00:00.01 |     139 |
|*  1 |  COUNT STOPKEY                       |                    |      1 |        |     10 |00:00:00.01 |     139 |
|   2 |   VIEW                               |                    |      1 |    137 |     10 |00:00:00.01 |     139 |
|*  3 |    SORT ORDER BY STOPKEY             |                    |      1 |    137 |     10 |00:00:00.01 |     139 |
|*  4 |     FILTER                           |                    |      1 |        |    137 |00:00:00.01 |     139 |
|   5 |      TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_STLM      |      1 |    137 |    137 |00:00:00.01 |     139 |
|*  6 |       INDEX RANGE SCAN               | IX_STLM_DT_SEQ     |      1 |    137 |    137 |00:00:00.01 |       2 |
S5 · ① 의 양 끝을 하나씩
|   0 | SELECT STATEMENT                     |                    |      1 |        |      1 |00:00:00.01 |       4 |
|   1 |  SORT AGGREGATE                      |                    |      1 |      1 |      1 |00:00:00.01 |       2 |
|   2 |   INDEX FULL SCAN (MIN/MAX)          | IX_STLM_DT_SEQ     |      1 |      1 |      1 |00:00:00.01 |       2 |
|   3 |  SORT AGGREGATE                      |                    |      1 |      1 |      1 |00:00:00.01 |       2 |
|   4 |   INDEX FULL SCAN (MIN/MAX)          | IX_STLM_DT_SEQ     |      1 |      1 |      1 |00:00:00.01 |       2 |
|   5 |  FAST DUAL                           |                    |      1 |      1 |      1 |00:00:00.01 |       0 |
------------------------------------------------------------------------------------------------------------------

모범 답안 서술

S4가 틀렸다. ROWNUM <= 10과 ORDER BY가 같은 쿼리 블록에 있어서, 그날 정산 137건 가운데 FULL로 먼저 만난 아무 10건에 번호가 매겨진 뒤에 금액순으로 정렬된다. 원본 S4의 실행계획에서 2번 줄 COUNT STOPKEY가 1번 줄 SORT ORDER BY 아래에 있는 것이 그 증거다. 테이블을 47블록만 읽고 멈춰 빨라 보이지만 '고액 10건'이 아니다. 정렬을 인라인 뷰 안에 넣고 바깥에서 10건을 자르도록 고치고, 동점이 10번째에 걸려도 결과가 정해지도록 STLM_SEQ를 정렬 끝에 붙였다. 나머지 넷은 인덱스가 없어 모두 테이블 전체(640블록)를 읽는다. S1은 10만 건을 읽어 Top-N 정렬(WINDOW SORT PUSHED RANK)로 20건을 남기고, S2와 S3은 고객 한 명의 50건을 찾으려고 10만 건을 거르며, S5는 10만 건에서 최솟값과 최댓값을 고른다. 인덱스는 두 개면 된다. ① (STLM_DT, STLM_SEQ): S1의 정렬 키 STLM_DT DESC, STLM_SEQ DESC 전체가 이 인덱스를 거꾸로 읽은 순서와 같다. 두 컬럼이 NOT NULL이라 모든 행이 인덱스에 있으므로 정렬을 대신하고 20건에서 멈춘다(640 → 23). S4는 그날 범위 137건을 이 인덱스로 찾은 뒤 정렬한다(범위를 읽은 뒤 정렬, 47 → 139). 원본보다 많이 읽지만 원본은 틀린 답이었고, FULL 640보다는 싸다. S5는 MIN과 MAX를 스칼라 서브쿼리 둘로 나눠 이 인덱스의 양 끝을 FIRST ROW로 하나씩 읽는다(640 → 4). ② (CUS_ID, STLM_DT, STLM_SEQ): S2는 고객 범위의 오른쪽 끝을 FIRST ROW로 읽고(640 → 2), S3은 같은 범위를 거꾸로 읽다 5건에서 멈춘다(640 → 7). 정렬 키의 STLM_SEQ까지 담아야 S3에서 정렬이 사라진다. 만들지 않는 인덱스도 있다. 금액 순서의 (STLM_AMT) 인덱스는 만들지 않는다. S4는 하루치 137건만 정렬하면 되므로 금액 순서의 인덱스가 필요 없고, 만들어도 날짜 조건을 함께 처리하지 못한다. (CUS_ID) 단독도 ②가 대신하므로 만들지 않는다. 밤 적재에는 일이 늘어난다. 지금은 인덱스가 없어 APPEND 적재가 블록만 쓰고 끝나지만, 인덱스 두 개가 생기면 적재 끝에 10만 건을 두 번 정렬해 인덱스에 반영해야 하고 그만큼 시간과 TEMP, redo가 든다. 10만 건짜리 인덱스 하나를 만드는 데 1초 안팎이라 두 개를 더해도 적재가 몇 초 늘어나는 정도로 본다. 적재 전에 인덱스를 UNUSABLE로 두고 적재 뒤에 REBUILD하면 적재 단계와 인덱스 단계의 시간을 따로 재고 관리할 수 있다. 화면은 낮에만 쓰므로 밤 적재 중에 인덱스를 잠시 못 쓰는 것은 문제가 없다.

해설

S4의 함정: 고친 S4는 원본보다 블록을 더 읽는다(47 → 139). 틀린 SQL이 빠른 것은 개선의 기준이 될 수 없다. 튜닝 전후를 비교할 때는 결과가 같은지부터 확인한다(교재 08화 정합성 회귀 검증).
S4를 더 줄이고 싶다면 (STLM_DT, STLM_AMT)처럼 금액을 담은 인덱스로 정렬 대상을 인덱스 안에서 고를 수 있다. 하지만 하루 137건 정렬은 작고, 화면 칸(CUS_ID)을 가지러 테이블에 가야 하는 것도 같다. 세 번째 인덱스의 적재 비용과 견주면 만들 이유가 약하다.
①을 (STLM_DT) 단독으로 만들면 S1의 정렬 키 STLM_SEQ가 인덱스에 없어 정렬이 다시 생긴다. S1에서 STLM_SEQ를 빼면 같은 시각의 정산 둘이 20번째 자리에 걸릴 때 순서가 흔들린다. 정렬 키는 끝까지 인덱스에 담는다.
매일 밤 비우고 다시 싣는 테이블이라 인덱스의 클러스터링 팩터와 통계도 매일 새로 생긴다. 적재 뒤 통계 수집에 인덱스 통계가 포함되는지 확인한다(교재 10화).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황