주문 보관 테이블이 생긴 뒤로 매장 월 정산 집계가 4초씩 걸리고 TEMP를 쓴다

기초DDL버거295 / 700

상황

버거 본사는 작년 말 2012년 주문을 보관 테이블 ORD_ARC로 옮기고, 화면과 배치가 두 해를 함께 볼 수 있게 뷰 V_ORD_ALL을 만들었다. 그 뒤로 「매장 월 정산 집계」(2년 치 매장 × 월 주문 수, 하루 40번)가 0.7초에서 4.1초로 느려졌고, 돌 때마다 TEMP를 40MB 넘게 쓴다. 뷰를 만든 개발자는 "두 테이블에 같은 주문이 들어 있을까 봐 UNION으로 묶었다"라고 했다.

테이블과 인덱스

ORD 주문 (이 문제의 설정): 2013년 주문만 남았다 · 500,218행 · 11,216블록 · ORD_PK (ORD_NO)
ORD_ARC 보관 주문 (파생 테이블): ORD와 같은 구조 · 2012년 주문 499,782행 · 11,206블록 · ORD_ARC_PK (ORD_NO) · 옮긴 주문은 ORD에서 지웠다
두 테이블을 합치면 schemas.md의 ORD(1,000,000행 · 22,422블록)와 같다 · 주문번호는 한 번 쓰면 다시 쓰지 않는다
pga_aggregate_target 512M · AUTO · 통계 최신

원본 SQL

sql

CREATE OR REPLACE VIEW V_ORD_ALL AS
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO FROM ORD
UNION
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO FROM ORD_ARC;

-- 매장 월 정산 집계 (하루 40번)
SELECT SHOP_NO, SUBSTR(ORD_DT, 1, 6) AS ORD_YM, COUNT(*) AS ORD_CNT
  FROM V_ORD_ALL
 GROUP BY SHOP_NO, SUBSTR(ORD_DT, 1, 6);

실행계획 학습용 예시

xplan

[매장 월 정산 집계]
----------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers | Reads | Writes |  Used-Mem | Used-Tmp |
----------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |           |      1 |        |   4800 | 00:00:04.12 |   22422 |  5376 |   5376 |           |          |
|   1 |  HASH GROUP BY          |           |      1 |   4800 |   4800 | 00:00:04.12 |   22422 |  5376 |   5376 | 1934K (0) |          |
|   2 |   VIEW                  | V_ORD_ALL |      1 |  1000K |  1000K | 00:00:03.87 |   22422 |  5376 |   5376 |           |          |
|   3 |    SORT UNIQUE          |           |      1 |  1000K |  1000K | 00:00:03.71 |   22422 |  5376 |   5376 |   23M (1) |      42M |
|   4 |     UNION-ALL           |           |      1 |        |  1000K | 00:00:00.38 |   22422 |     0 |      0 |           |          |
|   5 |      TABLE ACCESS FULL  | ORD       |      1 |   500K |   500K | 00:00:00.17 |   11216 |     0 |      0 |           |          |
|   6 |      TABLE ACCESS FULL  | ORD_ARC   |      1 |   500K |   500K | 00:00:00.16 |   11206 |     0 |      0 |           |          |
----------------------------------------------------------------------------------------------------------------------------------------

문제

4.1초와 TEMP 사용이 어느 줄에서 나오는지 설명하고, 개발자가 걱정한 '같은 주문'이 실제로 생길 수 있는지 판단하시오.
뷰를 고치고, 고친 뷰가 원래 뷰와 같은 결과를 낸다는 것을 어떻게 확인할지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 두 테이블은 겹치지 않는다 (2013 / 2012, 옮긴 주문은 지웠다) → 중복 제거가 필요 없다
CREATE OR REPLACE VIEW V_ORD_ALL AS
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO FROM ORD
UNION ALL
SELECT ORD_NO, ORD_DT, ORD_HMS, SHOP_NO FROM ORD_ARC;

-- 바꾸기 전 확인 : 두 테이블에 함께 있는 주문번호 (0 건이어야 한다)
SELECT COUNT(*) FROM ORD o WHERE EXISTS (SELECT 1 FROM ORD_ARC a WHERE a.ORD_NO = o.ORD_NO);

-- 매장 월 정산 집계 : SQL 은 그대로 둔다
SELECT SHOP_NO, SUBSTR(ORD_DT, 1, 6) AS ORD_YM, COUNT(*) AS ORD_CNT
  FROM V_ORD_ALL
 GROUP BY SHOP_NO, SUBSTR(ORD_DT, 1, 6);

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

xplan

[매장 월 정산 집계 · 뷰를 고친 뒤]
-----------------------------------------------------------------------------------------------------------
| Id  | Operation              | Name      | Starts | E-Rows | A-Rows |      A-Time | Buffers |  Used-Mem |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |           |      1 |        |   4800 | 00:00:00.61 |   22422 |           |
|   1 |  HASH GROUP BY         |           |      1 |   4800 |   4800 | 00:00:00.61 |   22422 | 1934K (0) |
|   2 |   VIEW                 | V_ORD_ALL |      1 |  1000K |  1000K | 00:00:00.42 |   22422 |           |
|   3 |    UNION-ALL           |           |      1 |        |  1000K | 00:00:00.33 |   22422 |           |
|   4 |     TABLE ACCESS FULL  | ORD       |      1 |   500K |   500K | 00:00:00.16 |   11216 |           |
|   5 |     TABLE ACCESS FULL  | ORD_ARC   |      1 |   500K |   500K | 00:00:00.15 |   11206 |           |
-----------------------------------------------------------------------------------------------------------

모범 답안 서술

3번 줄 SORT UNIQUE가 원인이다. UNION은 두 결과를 이은 뒤 겹치는 행을 하나로 줄여야 하므로, 두 테이블의 100만 행을 네 컬럼 전체로 정렬한다. 정렬할 양이 work area에 다 들어가지 않아 23M (1), 곧 onepass로 돌았고 TEMP에 42M를 썼다(Writes, Reads 5,376). 4.12초 가운데 3.3초가 이 정렬이고, 두 테이블을 읽는 데는 0.4초도 걸리지 않는다(4번 줄). 개발자가 걱정한 '같은 주문'은 생길 수 없다. 보관 테이블에는 2012년 주문만 있고 옮긴 주문은 ORD에서 지웠으며, 주문번호는 다시 쓰지 않는다. 각 테이블 안에서도 ORD_NO가 PK라 네 컬럼이 모두 같은 행이 나올 수 없다. 없앨 중복이 없는데 중복을 찾으려고 100만 행을 정렬한 것이다. 뷰를 UNION ALL로 바꾼다. 두 결과를 이어 붙이기만 하므로 정렬이 사라지고, 집계는 HASH GROUP BY 하나로 끝난다. A-Time 4.12 → 0.61초, TEMP 42M → 0이다. Buffers는 22,422 그대로다. 집계 SQL은 고치지 않는다. 바꾸기 전에 두 테이블에 함께 있는 주문번호가 0건인지 확인하고, 바꾼 뒤에는 뷰 전체 건수(1,000,000 = 1,000,000)와 집계 결과 4,800행의 주문 수 합계를 원래 뷰와 대조한다. 이 뷰를 쓰는 다른 화면도 결과가 같아야 하므로 확인 결과를 리포트에 남긴다.

해설

UNION ALL로 바꾸면 안 되는 경우: 보관 작업이 '옮긴 뒤 지우기'가 아니라 '복사만' 하는 방식이라면 한동안 두 테이블에 같은 주문이 있다. 그때 UNION ALL은 그 주문을 두 번 센다. 뷰를 바꾸기 전에 보관 절차부터 확인한다.
겹칠 수 있는데 정렬은 피하고 싶다면: 둘째 갈래에 WHERE NOT EXISTS (SELECT 1 FROM ORD o WHERE o.ORD_NO = a.ORD_NO)를 붙여 겹치는 주문만 뺀다. 100만 행 정렬 대신 해시 안티 조인 한 번이다.
UNION의 대가는 TEMP만이 아니다: 정렬이 끝나야 첫 행이 나오므로 이 뷰 위에 ROWNUM <= 20 같은 부분범위 처리를 해도 100만 행을 다 정렬한다. UNION ALL이면 앞 갈래에서 20건을 읽고 멈출 수 있다(교재 09화).
PGA를 늘려 정렬을 optimal로 끝내는 것은 증상만 가린다. 할 필요가 없는 정렬을 없애는 것이 먼저다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황