2022년 주문을 아카이브로 떼어 낸 다음 날, 기간 매출은 22배, 고객 최근 주문은 175배 무거워졌다

실전SELECT한빛유통291 / 700

상황

지난 주말 한빛유통은 T_ORD_BIG의 2022년 주문을 아카이브 테이블로 떼어 냈다. 화면 SQL을 고치지 않으려고 원래 테이블은 CTAS로 두 개(T_ORD_BIG_CUR, T_ORD_BIG_ARC)로 나눠 만든 뒤 지우고, 원래 이름으로 UNION ALL 뷰를 만들었다. 아카이브는 "조회가 거의 없을 테니" 인덱스를 만들지 않았다. 월요일에 잰 결과는 다음과 같다. (A) 정산 화면 「기간 매출」(하루 3,000번) 91 → 2,033블록, (B) 콜센터 「고객 최근 주문 10건」(하루 30,000번) 13 → 2,278블록, (C) 월 리포트 「3년 등급별 매출」(하루 20번) 5,856 → 5,856블록. 박팀장은 뷰를 유지할지, 파티션으로 갈지까지 포함해 안을 내라고 했다.

테이블과 인덱스

T_ORD_BIG_CUR(2023 ~ 2024년): 666,000행 · 3,884블록 · IX_CUR_DT_AMT (ORD_DT, ORD_AMT) · IX_CUR_CUS_DT (CUS_ID, ORD_DT) (원래 테이블의 인덱스를 다시 만들었다)
T_ORD_BIG_ARC(2022년): 334,000행 · 1,942블록 · 인덱스 없음 · 앞으로 DML이 없다
고객 2,000명이 약 500건씩(현재 약 333건, 아카이브 약 167건) · 한 고객의 주문은 테이블 곳곳에 흩어져 있다 · 2024년 6월 27,390건
M_CUS: 2,000행 · 30블록 · 두 테이블 모두 통계 최신 · Partitioning 옵션은 라이선스가 있다

원본 SQL

sql

-- 원래 이름의 뷰
CREATE OR REPLACE VIEW T_ORD_BIG AS
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG_CUR
UNION ALL
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT FROM T_ORD_BIG_ARC;

-- (A) 기간 매출 (하루 3,000번)
SELECT COUNT(*), SUM(ORD_AMT)
  FROM T_ORD_BIG
 WHERE ORD_DT >= :d1
   AND ORD_DT <  :d2;

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

-- (C) 3년 등급별 매출 (하루 20번) : M_CUS와 해시 조인, 두 테이블 전체 스캔 · 5,856블록 · 고칠 것 없음

실행계획 학습용 예시

xplan

[A · 2024년 6월]
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |      1 |00:00:00.03 |    2033 |
|   1 |  SORT AGGREGATE       |               |      1 |      1 |      1 |00:00:00.03 |    2033 |
|   2 |   VIEW                | T_ORD_BIG     |      1 |  27391 |  27390 |00:00:00.03 |    2033 |
|   3 |    UNION-ALL          |               |      1 |        |  27390 |00:00:00.03 |    2033 |
|*  4 |     INDEX RANGE SCAN  | IX_CUR_DT_AMT |      1 |  27390 |  27390 |00:00:00.01 |      91 |
|*  5 |     TABLE ACCESS FULL | T_ORD_BIG_ARC |      1 |      1 |      0 |00:00:00.02 |    1942 |
----------------------------------------------------------------------------------------------
   4 - access("ORD_DT">=:D1 AND "ORD_DT"<:D2)
   5 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2))

[B]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |      1 |        |     10 |00:00:00.04 |    2278 |
|*  1 |  COUNT STOPKEY                        |               |      1 |        |     10 |00:00:00.04 |    2278 |
|   2 |   VIEW                                |               |      1 |    500 |     10 |00:00:00.04 |    2278 |
|*  3 |    SORT ORDER BY STOPKEY              |               |      1 |    500 |     10 |00:00:00.04 |    2278 |
|   4 |     VIEW                              | T_ORD_BIG     |      1 |    500 |    500 |00:00:00.04 |    2278 |
|   5 |      UNION-ALL                        |               |      1 |        |    500 |00:00:00.04 |    2278 |
|   6 |       TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_BIG_CUR |   1 |    333 |    333 |00:00:00.01 |     336 |
|*  7 |        INDEX RANGE SCAN               | IX_CUR_CUS_DT |      1 |    333 |    333 |00:00:00.01 |       3 |
|*  8 |       TABLE ACCESS FULL               | T_ORD_BIG_ARC |      1 |    167 |    167 |00:00:00.03 |    1942 |
-----------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   7 - access("CUS_ID"=:CUS)
   8 - filter("CUS_ID"=:CUS)

문제

(A)와 (B)에서 바깥 조건이 UNION ALL의 두 갈래에 어떻게 들어갔고, 무엇 때문에 일량이 늘었는지 줄 번호로 설명하시오. (B)에서 ROWNUM <= 10이 어디에 걸렸는지도 쓰시오.
뷰 정의와 인덱스, (B)의 SQL을 고쳐 (A)와 (B)를 아카이브 이전 수준으로 되돌리고, 하루 읽기량이 얼마나 바뀌는지 쓰시오.
UNION ALL 뷰 대신 T_ORD_BIG을 연도별 RANGE 파티션 테이블로 바꾸는 안과 비교해 장단점을 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 뷰: 갈래마다 담긴 기간을 상수 조건으로 적는다 (바깥 기간 조건과 합쳐져 갈래를 건너뛴다)
CREATE OR REPLACE VIEW T_ORD_BIG AS
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG_CUR
 WHERE ORD_DT >= DATE '2023-01-01'
UNION ALL
SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_ST, ORD_AMT
  FROM T_ORD_BIG_ARC
 WHERE ORD_DT <  DATE '2023-01-01';

-- ② 아카이브: 고객 조회용 인덱스 하나 (DML이 없어 만든 뒤 유지 비용이 없다)
CREATE INDEX IX_ARC_CUS_DT ON T_ORD_BIG_ARC (CUS_ID, ORD_DT);

-- ③ (B): 갈래마다 10건씩 먼저 뽑고, 합친 20건에서 다시 10건
SELECT *
  FROM (SELECT *
          FROM (SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
                  FROM T_ORD_BIG_CUR
                 WHERE CUS_ID = :cus
                 ORDER BY ORD_DT DESC)
         WHERE ROWNUM <= 10
        UNION ALL
        SELECT *
          FROM (SELECT ORD_SEQ, ORD_DT, ORD_ST, ORD_AMT
                  FROM T_ORD_BIG_ARC
                 WHERE CUS_ID = :cus
                 ORDER BY ORD_DT DESC)
         WHERE ROWNUM <= 10
         ORDER BY ORD_DT DESC)
 WHERE ROWNUM <= 10;
-- (A)와 (C)는 SQL을 고치지 않는다

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

xplan

[A · ① 뒤]
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |               |      1 |        |      1 |00:00:00.01 |      91 |
|   1 |  SORT AGGREGATE       |               |      1 |      1 |      1 |00:00:00.01 |      91 |
|   2 |   VIEW                | T_ORD_BIG     |      1 |  27391 |  27390 |00:00:00.01 |      91 |
|   3 |    UNION-ALL          |               |      1 |        |  27390 |00:00:00.01 |      91 |
|*  4 |     INDEX RANGE SCAN  | IX_CUR_DT_AMT |      1 |  27390 |  27390 |00:00:00.01 |      91 |
|*  5 |     FILTER            |               |      1 |        |      0 |00:00:00.01 |       0 |
|*  6 |      TABLE ACCESS FULL| T_ORD_BIG_ARC |      0 |      1 |      0 |00:00:00.01 |       0 |
----------------------------------------------------------------------------------------------
   4 - access("ORD_DT">=:D1 AND "ORD_DT"<:D2)
       filter("ORD_DT">=TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   5 - filter(:D1<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   6 - filter(("ORD_DT">=:D1 AND "ORD_DT"<:D2 AND "ORD_DT"<TO_DATE(' 2023-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))

[B · ② ③ 뒤]
---------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |               |      1 |        |     10 |00:00:00.01 |      26 |
|*  1 |  COUNT STOPKEY                    |               |      1 |        |     10 |00:00:00.01 |      26 |
|   2 |   VIEW                            |               |      1 |     20 |     10 |00:00:00.01 |      26 |
|*  3 |    SORT ORDER BY STOPKEY          |               |      1 |     20 |     10 |00:00:00.01 |      26 |
|   4 |     UNION-ALL                     |               |      1 |        |     20 |00:00:00.01 |      26 |
|*  5 |      COUNT STOPKEY                |               |      1 |        |     10 |00:00:00.01 |      13 |
|   6 |       VIEW                        |               |      1 |     10 |     10 |00:00:00.01 |      13 |
|   7 |        TABLE ACCESS BY INDEX ROWID| T_ORD_BIG_CUR |      1 |    333 |     10 |00:00:00.01 |      13 |
|*  8 |         INDEX RANGE SCAN DESCENDING| IX_CUR_CUS_DT |     1 |     10 |     10 |00:00:00.01 |       3 |
|*  9 |      COUNT STOPKEY                |               |      1 |        |     10 |00:00:00.01 |      13 |
|  10 |       VIEW                        |               |      1 |     10 |     10 |00:00:00.01 |      13 |
|  11 |        TABLE ACCESS BY INDEX ROWID| T_ORD_BIG_ARC |      1 |    167 |     10 |00:00:00.01 |      13 |
|* 12 |         INDEX RANGE SCAN DESCENDING| IX_ARC_CUS_DT |     1 |     10 |     10 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------------------
   1 - filter(ROWNUM<=10)
   3 - filter(ROWNUM<=10)
   5 - filter(ROWNUM<=10)
   8 - access("CUS_ID"=:CUS)
   9 - filter(ROWNUM<=10)
  12 - access("CUS_ID"=:CUS)

모범 답안 서술

(A)의 기간 조건은 UNION ALL의 두 갈래에 각각 들어갔다(조건절 Pushing). 현재 갈래는 4번 줄처럼 IX_CUR_DT_AMT로 6월만 읽었다(91블록). 아카이브 갈래에도 같은 조건이 들어갔지만 인덱스가 없고, 아카이브에 2022년만 있다는 사실이 뷰 어디에도 없어서 옵티마이저는 그 갈래를 읽을지 말지 판단할 근거가 없다. 그래서 5번 줄이 아카이브 1,942블록을 다 읽고 0건을 돌려줬다. (B)도 고객 조건은 두 갈래에 들어갔지만, ROWNUM <= 10과 정렬은 1번과 3번 줄, 곧 뷰 위에 걸렸다. 갈래 안에는 '10건이면 멈춘다'는 조건이 없으므로 현재 갈래는 고객 주문 333건을 테이블에서 다 읽고(336블록), 아카이브 갈래는 인덱스가 없어 통째로 읽은 뒤(1,942블록) 500건을 정렬해 10건만 남긴다. 아카이브 전에는 인덱스를 역순으로 10건만 읽고 멈췄다(13블록). 첫째, 뷰의 갈래마다 담긴 기간을 상수 조건으로 적는다. 아카이브 갈래의 ORD_DT < DATE '2023-01-01'이 바깥의 ORD_DT >= :d1과 합쳐져 :D1 < 2023-01-01이라는 FILTER(5번 줄)가 생기고, 2023년 이후를 조회하면 바인드 값만 보고 갈래를 건너뛴다(6번 줄 Starts 0). (A)는 2,033 → 91블록으로 돌아온다. 둘째, 아카이브에 (CUS_ID, ORD_DT) 인덱스를 만들고, (B)는 갈래마다 인덱스 역순으로 10건씩 먼저 뽑은 뒤 합친 20건에서 다시 10건을 고른다. 갈래마다 13블록, 합계 26블록이다(2,278 → 26). 아카이브는 앞으로 DML이 없으므로 인덱스는 만들 때 한 번만 비용이 든다. 하루 읽기량은 (B) 2,278 × 30,000 = 약 6,834만 → 26 × 30,000 = 78만, (A) 2,033 × 3,000 = 약 610만 → 91 × 3,000 = 약 27만 블록이다. (C)는 두 테이블을 한 번씩 읽으므로 그대로다(5,856블록). 파티션 안과 비교하면, 연도별 RANGE 파티션은 기간 조건으로 파티션 프루닝이 저절로 되어 뷰에 상수 조건을 적을 필요가 없고, 2022년을 떼어 낼 때도 파티션 교환이나 DROP으로 끝난다. 하지만 (B)는 로컬 인덱스만 있으면 파티션마다 고객 주문을 다 모아 정렬해야 하므로 (CUS_ID, ORD_DT)를 글로벌 인덱스로 만들어야 13블록이 되고, 글로벌 인덱스는 파티션을 DROP할 때마다 관리해야 한다. 테이블을 다시 이관하는 작업과 Partitioning 라이선스 비용도 든다. 아카이브가 한 번 떼어 내고 끝나는 데이터라면 지금의 뷰에 ①②③을 적용하는 쪽이 작업이 작고, 매년 떼어 낼 계획이라면 파티션이 낫다.

해설

상수 조건이 하는 일: 뷰에 적은 ORD_DT < DATE '2023-01-01'은 결과를 바꾸지 않는다(아카이브에는 원래 2022년만 있다). 옵티마이저에게 갈래의 범위를 알려 주는 표시다. CHECK 제약에 기대기보다 뷰 정의에 조건으로 적어 두어야 계획에서 FILTER로 확인할 수 있다.
함정: 아카이브 이관 뒤에 2022년 주문이 현재 테이블에 다시 들어오면(소급 입력) 상수 조건 때문에 그 행은 뷰에서 사라진다. 소급 입력이 생기는 업무라면 적재 프로그램에서 막거나 CHECK 제약을 함께 건다.
(B)의 다른 정답: 화면에서 현재 테이블을 먼저 읽고 10건이 안 될 때만 아카이브를 읽는 방법도 있다. 고객마다 현재 주문이 수백 건이라 사실상 언제나 한 번(13블록)에 끝나지만, 분기가 앱 코드로 간다.
ROWNUM은 갈래 안으로 들어가지 않는다: 계획에서 COUNT STOPKEY가 VIEW 위에만 있으면 Top-N이 갈래까지 내려가지 않은 것이다. 이럴 때는 갈래마다 Top-N을 직접 적는다(교재 09화 Top-N과 STOPKEY).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황