고객 한 명의 주문 50건을 보려고 주문상세 10만 건을 다 묶는다

중급SELECT한빛유통240 / 700

상황

콜센터의 「고객 주문 내역」 화면은 상담원 40명이 통화할 때마다 고객번호로 연다. 하루 약 6,000번 열리는데, 주문이 50건뿐인 고객도 0.1초씩 걸린다. 인라인 뷰 안의 한 줄에는 -- 뷰를 먼저 만들게 (2019.03 강도현)라는 주석이 달려 있다. 그해 3월 뷰가 풀리면서 계획이 흔들린 일이 있었고, 그 뒤에 넣은 줄이다. 그때 T_ORD에는 CUS_ID 인덱스가 없었다.

테이블과 인덱스

T_ORD 주문: 100,000행 · 896블록 · PK_T_ORD (ORD_SEQ) · IX_ORD_CUS (CUS_ID) (2022년 추가) · 고객 CUS_000123의 주문 50건 (블록 50개에 흩어져 있다)
T_ORD_DET 주문상세: 200,000행 · 전체 스캔 807블록 · 주문당 1~3건 · PK_T_ORD_DET (ORD_SEQ, ORD_DET_NO)
통계 최신 · 아래 실행계획은 :cus = 'CUS_000123'

원본 SQL

sql

SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, d.det_cnt, d.det_amt
  FROM T_ORD o,
       (SELECT ORD_SEQ, COUNT(*) AS det_cnt, SUM(ORD_QTY * UNT_PRC) AS det_amt
          FROM T_ORD_DET
         WHERE ROWNUM > 0             -- 뷰를 먼저 만들게 (2019.03 강도현)
         GROUP BY ORD_SEQ) d
 WHERE o.CUS_ID = :cus
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_DT DESC

실행계획 학습용 예시 · 교재 08화 규모

xplan

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name       | Starts | E-Rows | A-Rows |    A-Time   | Buffers |  Used-Mem |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |            |      1 |        |     50 | 00:00:00.09 |     860 |           |
|   1 |  SORT ORDER BY                         |            |      1 |     50 |     50 | 00:00:00.09 |     860 | 6144  (0) |
|*  2 |   HASH JOIN                            |            |      1 |     50 |     50 | 00:00:00.09 |     860 | 1270K (0) |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD      |      1 |     50 |     50 | 00:00:00.01 |      53 |           |
|*  4 |     INDEX RANGE SCAN                   | IX_ORD_CUS |      1 |     50 |     50 | 00:00:00.01 |       2 |           |
|   5 |    VIEW                                |            |      1 |   100K |   100K | 00:00:00.08 |     807 |           |
|   6 |     HASH GROUP BY                      |            |      1 |   100K |   100K | 00:00:00.07 |     807 | 5383K (0) |
|   7 |      COUNT                             |            |      1 |        |   200K | 00:00:00.03 |     807 |           |
|*  8 |       FILTER                           |            |      1 |        |   200K | 00:00:00.02 |     807 |           |
|   9 |        TABLE ACCESS FULL               | T_ORD_DET  |      1 |   200K |   200K | 00:00:00.01 |     807 |           |
----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."ORD_SEQ"="O"."ORD_SEQ")
   4 - access("O"."CUS_ID"=:CUS)
   8 - filter(ROWNUM>0)

문제

860블록 가운데 어느 줄이 쓸데없는 일인지 찾으시오. 고객 조건이 계획의 어디까지 들어가고 어디서 멈췄는지 그 까닭과 함께 설명하시오.
이 고객의 주문 50건에 딸린 상세만 묶도록 SQL을 고치시오(방법을 둘 이상 제시하면 좋다). 고친 뒤에도 결과가 같다는 것을 보이시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① ROWNUM 을 지우고, 조인 조건을 뷰 안으로 밀어 넣는다 (JPPD — 주문 한 건마다 뷰를 연다)
SELECT /*+ LEADING(o) USE_NL(d) NO_MERGE(d) PUSH_PRED(d) */
       o.ORD_SEQ, o.ORD_DT, o.ORD_ST, d.det_cnt, d.det_amt
  FROM T_ORD o,
       (SELECT ORD_SEQ, COUNT(*) AS det_cnt, SUM(ORD_QTY * UNT_PRC) AS det_amt
          FROM T_ORD_DET
         GROUP BY ORD_SEQ) d
 WHERE o.CUS_ID = :cus
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_DT DESC;

-- ② 힌트 없이 SQL 로 — 고객 조건을 뷰 안에 직접 적는다 (T_ORD 를 두 번 읽는다)
SELECT o.ORD_SEQ, o.ORD_DT, o.ORD_ST, d.det_cnt, d.det_amt
  FROM T_ORD o,
       (SELECT ORD_SEQ, COUNT(*) AS det_cnt, SUM(ORD_QTY * UNT_PRC) AS det_amt
          FROM T_ORD_DET
         WHERE ORD_SEQ IN (SELECT ORD_SEQ FROM T_ORD WHERE CUS_ID = :cus)
         GROUP BY ORD_SEQ) d
 WHERE o.CUS_ID = :cus
   AND d.ORD_SEQ = o.ORD_SEQ
 ORDER BY o.ORD_DT DESC;

개선 후 실행계획 학습용 예시 · 교재 08화 규모

xplan

[① — JPPD]
------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name         | Starts | E-Rows | A-Rows |    A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |              |      1 |        |     50 | 00:00:00.01 |     155 |
|   1 |  SORT ORDER BY                         |              |      1 |     50 |     50 | 00:00:00.01 |     155 |
|   2 |   NESTED LOOPS                         |              |      1 |     50 |     50 | 00:00:00.01 |     155 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD        |      1 |     50 |     50 | 00:00:00.01 |      53 |
|*  4 |     INDEX RANGE SCAN                   | IX_ORD_CUS   |      1 |     50 |     50 | 00:00:00.01 |       2 |
|   5 |    VIEW PUSHED PREDICATE               |              |     50 |      1 |     50 | 00:00:00.01 |     102 |
|   6 |     SORT GROUP BY                      |              |     50 |      1 |     50 | 00:00:00.01 |     102 |
|   7 |      TABLE ACCESS BY INDEX ROWID       | T_ORD_DET    |     50 |      2 |    100 | 00:00:00.01 |     102 |
|*  8 |       INDEX RANGE SCAN                 | PK_T_ORD_DET |     50 |      2 |    100 | 00:00:00.01 |      52 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("O"."CUS_ID"=:CUS)
   8 - access("ORD_SEQ"="O"."ORD_SEQ")

[결과 대조]  고치기 전 50 행 · det_amt 합 13,983,900  =  고친 뒤 50 행 · 13,983,900 · MINUS 양방향 0 / 0
[②]         같은 결과 · Buffers 약 210 (T_ORD 를 뷰 안에서 한 번 더 읽는다)

모범 답안 서술

5번과 6번 줄이 쓸데없는 일이다. 뷰가 주문상세 200K 행을 전부 읽어 주문 100K 건을 다 묶고(A-Rows 100K), 2번 줄의 해시 조인은 그중 50건만 쓰고 나머지를 버린다. 860블록 중 807블록이 이 몫이다. 고객 조건은 4번 줄(T_ORD)에서만 쓰이고 뷰 안으로는 들어가지 못했다. 까닭은 뷰 안의 ROWNUM > 0(8번 줄 filter(ROWNUM>0))이다. ROWNUM은 '나오는 순서대로 번호를 매긴다'는 뜻이므로, 뷰를 바깥과 합치거나(뷰 머징) 바깥 조건을 뷰 안으로 밀어 넣으면(조건절 푸싱, JPPD) 번호가 달라질 수 있다. 그래서 오라클은 둘 다 하지 않는다. 2019년에 일부러 막아 둔 그대로다. ROWNUM을 지우고 NO_MERGE(d) PUSH_PRED(d)로 조인 조건 ORD_SEQ = o.ORD_SEQ를 뷰 안으로 밀어 넣으면, 주문 한 건마다 뷰를 열어(VIEW PUSHED PREDICATE, Starts 50) 그 주문의 상세 1~3건만 PK로 읽고 묶는다. Buffers는 860 → 155로 준다. 힌트 없이 쓰려면 뷰 안에 고객 조건(ORD_SEQ IN (SELECT … WHERE CUS_ID = :cus))을 직접 적는다. ROWNUM > 0은 언제나 참이므로 지워도 결과는 같다. 50행, det_amt 합 13,983,900, MINUS 0 / 0으로 확인할 수 있다. 2019.03에는 T_ORD에 CUS_ID 인덱스가 없어 주문을 전부 읽어야 했으므로, 뷰를 통째로 만들어 해시 조인으로 붙이는 편이 나았다. 2022년에 인덱스가 생기면서 그 줄을 넣은 이유가 사라졌다.

해설

두 변환은 서로를 대신할 수 있다: ROWNUM만 지우고 힌트를 주지 않으면 옵티마이저가 복합 뷰 머징(뷰를 풀어 조인한 뒤 GROUP BY, VM_NWVW_)과 JPPD 중 싼 쪽을 고른다. 교재 08화 실측(같은 모양으로 T_ORD를 전체 스캔할 때)에서는 힌트 없음 931, NO_MERGE만 준 경우 930(JPPD), NO_MERGE NO_PUSH_PRED 1,635였다.
ROWNUM으로 계획을 묶는 버릇: 힌트보다 눈에 덜 띄는 고정 방법이다. 힌트 유효기간 대장처럼 '왜 넣었나, 언제 다시 보나'를 함께 적어 둔다 (교재 06화). 이 경우에는 인덱스가 생긴 2022년이 재검토 시점이었다.
②의 대가: T_ORD를 두 번 읽는다(약 210블록). 대신 힌트 없이도 계획이 덜 흔들린다.
뷰를 풀어 FROM T_ORD o, T_ORD_DET t … GROUP BY o.ORD_SEQ, o.ORD_DT, o.ORD_ST로 써도 된다. 복합 뷰 머징을 손으로 한 셈이며, 바깥 컬럼을 GROUP BY에 모두 넣어야 한다.
JPPD는 바깥 집합이 작을 때 맞다. '전 고객 월 집계'처럼 바깥이 크면 뷰를 통째로 한 번 만들어 해시 조인으로 붙이는 쪽이 낫다. 같은 SQL이라도 규모에 따라 답이 바뀐다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 교재 08화 규모 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 교재 08화 규모 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황