고객 한 명의 배송 현황을 띄우는 데 배송 이력 24만 줄의 현재 상태를 다 매긴다

실전SELECT한빛유통367 / 700

상황

작년 물류 개편 때 배송 상태가 바뀔 때마다 한 줄씩 쌓는 이력 테이블 T_ORD_DLV_HIS를 만들고, 주문마다 지금 상태는 뷰 V_ORD_DLV_CUR로 보게 했다. 주문 상세 화면(Q1)은 이 뷰를 주문번호로, 고객센터 상담 화면 「이 고객의 배송 현황」(Q2)은 고객번호로 읽는다. 물류팀의 야간 배치 「배송 상태별 건수」도 이 뷰를 조건 없이 읽는다. Q2는 하루 약 2만 번 열리고 AWR 논리 읽기 3위다. 박팀장은 뷰를 고치는 안, 인덱스를 넓히는 안, MAX로 다시 쓰는 안, 주문 테이블에 현재 상태 칸을 두는 안을 견주어 달라고 했다.

테이블과 인덱스

T_ORD_DLV_HIS 배송 상태 이력(이 문제에서 쓰는 테이블 · T_ORD에서 파생): 240,000행 · 1,520블록 · ORD_SEQ · HIS_NO(주문마다 1부터) · CUS_ID(주문의 고객을 복사 · 바뀌지 않는다) · DLV_ST(접수 · 출고 · 배송중 · 배송완료 · 반품) · CHG_DT
주문 10만 건마다 1 ~ 5줄(평균 2.4) · 하루 약 330줄씩 쌓인다 · 쌓인 순서대로 적재되어 한 고객의 이력은 테이블 곳곳의 블록에 흩어져 있다 · 고객마다 주문 약 50건, 이력 약 120줄
인덱스 PK_T_ORD_DLV_HIS (ORD_SEQ, HIS_NO) · IX_DLV_HIS_CUS (CUS_ID, ORD_SEQ): 둘 다 BLEVEL 1 · 통계 최신
T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · 배송 상태 칸은 없다

원본 SQL

sql

-- 뷰 (물류 개편 때 만든 그대로)
CREATE OR REPLACE VIEW V_ORD_DLV_CUR AS
SELECT ORD_SEQ, CUS_ID, DLV_ST, CHG_DT
  FROM (SELECT h.ORD_SEQ, h.CUS_ID, h.DLV_ST, h.CHG_DT,
               ROW_NUMBER() OVER (PARTITION BY h.ORD_SEQ ORDER BY h.HIS_NO DESC) AS rn
          FROM T_ORD_DLV_HIS h)
 WHERE rn = 1;

-- Q1 주문 상세 (빠르다)
SELECT DLV_ST, CHG_DT FROM V_ORD_DLV_CUR WHERE ORD_SEQ = :ord;

-- Q2 상담 화면 「이 고객의 배송 현황」 (하루 약 2만 번 · 느리다)
SELECT ORD_SEQ, DLV_ST, CHG_DT
  FROM V_ORD_DLV_CUR
 WHERE CUS_ID = :cus
 ORDER BY ORD_SEQ DESC;

-- 야간 배치 「배송 상태별 건수」 : SELECT DLV_ST, COUNT(*) FROM V_ORD_DLV_CUR GROUP BY DLV_ST;

실행계획 학습용 예시

xplan

[Q1 · 주문 하나]
--------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name             | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                  |      1 |        |      1 |00:00:00.01 |       4 |          |
|*  1 |  VIEW                                 |                  |      1 |      2 |      1 |00:00:00.01 |       4 |          |
|*  2 |   WINDOW SORT PUSHED RANK             |                  |      1 |      2 |      1 |00:00:00.01 |       4 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_DLV_HIS    |      1 |      2 |      3 |00:00:00.01 |       4 |          |
|*  4 |     INDEX RANGE SCAN                  | PK_T_ORD_DLV_HIS |      1 |      2 |      3 |00:00:00.01 |       2 |          |
--------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("RN"=1)
   2 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."ORD_SEQ" ORDER BY INTERNAL_FUNCTION("H"."HIS_NO") DESC )<=1)
   4 - access("H"."ORD_SEQ"=:ORD)

[Q2 · 고객 CUS_000777 (주문 52건)]
---------------------------------------------------------------------------------------------------------------
| Id  | Operation                  | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
---------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |               |      1 |        |     52 |00:00:00.19 |    1520 |          |
|   1 |  SORT ORDER BY             |               |      1 |      2 |     52 |00:00:00.19 |    1520 | 4096  (0)|
|*  2 |   VIEW                     |               |      1 |    100K|     52 |00:00:00.19 |    1520 |          |
|*  3 |    WINDOW SORT PUSHED RANK |               |      1 |    240K|    100K|00:00:00.17 |    1520 | 6421K (0)|
|   4 |     TABLE ACCESS FULL      | T_ORD_DLV_HIS |      1 |    240K|    240K|00:00:00.02 |    1520 |          |
---------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(("RN"=1 AND "CUS_ID"=:CUS))
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."ORD_SEQ" ORDER BY INTERNAL_FUNCTION("H"."HIS_NO") DESC )<=1)

문제

Q2의 실행계획에서 고객 조건(CUS_ID = :cus)이 몇 번 줄에 걸려 있는지 짚고, 같은 뷰를 주문번호로 찾는 Q1은 4블록에 끝나는 까닭을 뷰의 PARTITION BY와 견주어 설명하시오.
상담 화면이 그 고객의 이력만 읽도록 뷰를 고치고, 고친 뷰가 Q1, Q2와 야간 배치의 결과를 바꾸지 않는 근거와 확인 방법을 쓰시오.
인덱스를 넓히는 안, MAX로 다시 쓰는 안, T_ORD에 현재 상태 칸을 두는 안을 2번 안과 견주어 일량과 대가로 비교하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 바꾸기 전 확인 (0 건이어야 묶음이 같다) :
--   SELECT ORD_SEQ FROM T_ORD_DLV_HIS GROUP BY ORD_SEQ HAVING COUNT(DISTINCT CUS_ID) > 1;

-- 뷰 : PARTITION BY 에 CUS_ID 를 더한다 (주문마다 고객은 하나라 묶음은 그대로) → CUS_ID 조건도 뷰 안으로 들어간다
CREATE OR REPLACE VIEW V_ORD_DLV_CUR AS
SELECT ORD_SEQ, CUS_ID, DLV_ST, CHG_DT
  FROM (SELECT h.ORD_SEQ, h.CUS_ID, h.DLV_ST, h.CHG_DT,
               ROW_NUMBER() OVER (PARTITION BY h.CUS_ID, h.ORD_SEQ ORDER BY h.HIS_NO DESC) AS rn
          FROM T_ORD_DLV_HIS h)
 WHERE rn = 1;

-- Q1 · Q2 · 야간 배치 SQL 은 그대로
SELECT ORD_SEQ, DLV_ST, CHG_DT
  FROM V_ORD_DLV_CUR
 WHERE CUS_ID = :cus
 ORDER BY ORD_SEQ DESC;

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

xplan

[Q2 · 뷰를 고친 뒤 · 같은 고객]
-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |                |      1 |        |     52 |00:00:00.01 |     124 |          |
|   1 |  SORT ORDER BY                         |                |      1 |     50 |     52 |00:00:00.01 |     124 | 4096  (0)|
|*  2 |   VIEW                                 |                |      1 |    120 |     52 |00:00:00.01 |     124 |          |
|*  3 |    WINDOW SORT PUSHED RANK             |                |      1 |    120 |     52 |00:00:00.01 |     124 |   15K (0)|
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| T_ORD_DLV_HIS  |      1 |    120 |    124 |00:00:00.01 |     124 |          |
|*  5 |      INDEX RANGE SCAN                  | IX_DLV_HIS_CUS |      1 |    120 |    124 |00:00:00.01 |       2 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("RN"=1)
   3 - filter(ROW_NUMBER() OVER ( PARTITION BY "H"."CUS_ID","H"."ORD_SEQ" ORDER BY INTERNAL_FUNCTION("H"."HIS_NO") DESC )<=1)
   5 - access("H"."CUS_ID"=:CUS)

[Q1]  ORD_SEQ 가 PARTITION BY 에 그대로 있어 4 - access("H"."ORD_SEQ"=:ORD) · Buffers 4 그대로
[야간 배치]  조건이 없어 고치기 전과 같은 모양 · Buffers 1,520

모범 답안 서술

Q1의 Predicate Information을 보면 ORD_SEQ = :ord가 4번 줄 인덱스의 access 조건으로 들어가 있다. 뷰 바깥의 조건이 뷰 안으로 밀려 들어가, 그 주문의 이력 3줄만 읽고 순위를 매긴다(4블록). Q2는 다르다. CUS_ID = :cus는 2번 VIEW 줄의 filter로 남아 있고, 3번 줄 WINDOW SORT PUSHED RANK가 이력 24만 행을 전부 읽어(1,520블록) 주문 10만 건의 현재 상태를 다 매긴 뒤에야 그 고객의 52줄을 고른다. 갈림길은 PARTITION BY다. 바깥 조건을 분석 함수보다 먼저 걸어도 답이 같으려면, 그 조건이 묶음 하나를 통째로 남기거나 통째로 버려야 한다. ORD_SEQ는 PARTITION BY 컬럼이라 그렇게 되지만, CUS_ID는 PARTITION BY에 없어서 옵티마이저는 묶음의 일부만 남길 수도 있다고 보고 순위를 다 매긴 뒤에 거른다. 그래서 분석 함수가 있는 뷰에는 PARTITION BY 컬럼의 조건만 밀어 넣을 수 있다. 뷰의 PARTITION BY를 (CUS_ID, ORD_SEQ)로 바꾸면 CUS_ID 조건도 뷰 안으로 들어가 IX_DLV_HIS_CUS의 access가 된다. 그 고객의 이력 124줄만 읽어 순위를 매기므로 Q2의 Buffers는 1,520 → 124, 하루 약 3,040만 → 250만 블록이다. ORD_SEQ가 PARTITION BY에 그대로 있으므로 Q1도 4블록 그대로다. 결과가 바뀌지 않는 근거는 데이터 규칙에 있다. CUS_ID는 주문의 고객을 복사한 값이고 바뀌지 않으므로, 주문 하나의 이력은 모두 같은 CUS_ID를 가진다. 묶음 (CUS_ID, ORD_SEQ)와 묶음 (ORD_SEQ)가 같은 행들을 묶으니 순위도 같다. 바꾸기 전에 GROUP BY ORD_SEQ HAVING COUNT(DISTINCT CUS_ID) > 1이 0건인지 확인하고, 바꾼 뒤에는 야간 배치의 상태별 건수와 몇몇 고객의 Q2 결과를 바꾸기 전과 대조한다(건수, MINUS 양방향 0 / 0). 다른 안과 견준다. ① IX_DLV_HIS_CUS를 (CUS_ID, ORD_SEQ, HIS_NO, DLV_ST, CHG_DT)로 넓히면, 뷰를 고친 뒤라면 Q2가 인덱스만 읽어 4블록 안팎이 된다. 하지만 뷰를 그대로 두면 조건이 뷰 안으로 들어가지 못하므로 넓힌 인덱스도 쓰이지 않고 1,520블록 그대로다. 인덱스 크기는 두 배 가까이 되고 이력이 쌓일 때마다 더 큰 항목을 고친다. ② MAX로 다시 쓰면 (ORD_SEQ, HIS_NO) IN (SELECT ORD_SEQ, MAX(HIS_NO) … WHERE CUS_ID = :cus GROUP BY ORD_SEQ)처럼 고객의 이력을 한 번 읽고 주문마다 PK로 다시 찾아 약 280블록이고, 이 뷰를 쓰는 화면을 모두 고쳐야 한다. ③ T_ORD에 현재 상태 칸을 두면 읽기는 가장 싸지만, 배송 이벤트마다 이력 INSERT와 주문 UPDATE를 두 번 써야 하고 둘이 어긋나면 화면마다 다른 상태를 보인다. 뷰의 PARTITION BY 한 줄을 고치는 2번 안은 쓰는 쪽 비용이 0이고 Q1과 배치도 그대로라 먼저 적용한다. 넓힌 인덱스는 Q2를 더 줄여야 할 때 고른다.

해설

함정: 인덱스부터 넓히는 답: Q2의 원인은 인덱스가 아니라 조건이 뷰 안으로 들어가지 못하는 데 있다. 조건이 VIEW 줄에 남아 있는 한 어떤 인덱스를 만들어도 3번 줄은 24만 행을 다 읽는다.
Q2만 고치는 답: 상담 화면 SQL을 뷰 대신 T_ORD_DLV_HIS에 WHERE h.CUS_ID = :cus를 둔 인라인 뷰로 바꿔도 같은 일량(124블록)이 된다. 다만 '현재 상태'의 정의가 뷰와 화면 SQL 두 곳에 생긴다. 정의가 바뀌는 날(예: 반품 접수는 상태로 치지 않는다) 한쪽만 고쳐질 위험이 있어 뷰를 고치는 편이 낫다.
뷰 안에 분석 함수가 여럿이면 모든 분석 함수의 PARTITION BY에 그 컬럼이 있어야 조건이 들어간다. 나중에 COUNT(*) OVER (PARTITION BY ORD_SEQ) 같은 칸을 뷰에 더할 때도 같은 컬럼 목록을 쓴다.
JOIN-057은 상수가 아닌 조인 조건이라 분석 함수 뷰로 들어가지 못한 경우다. 이 문제는 상수 조건인데도 PARTITION BY에 없는 컬럼이라 들어가지 못한 경우이고, 뷰의 묶음 컬럼 한 줄로 고친다. 어느 쪽이든 먼저 볼 것은 바깥 조건이 VIEW 줄의 filter로 남아 있는가다(교재 08화 변환 도감의 조건절 Pushing).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황