VIP 고객 20명의 미결제 주문을 보는 화면이 결제 대기 주문 8만 건을 모두 만든다

중급SELECT한빛유통284 / 700

상황

영업팀의 「VIP 미결제 주문」 화면은 A 등급 고객 20명의 결제 대기 주문을 고객명, 대기 일수와 함께 보여 준다. 영업 담당 15명이 수시로 새로 고치는데 한 번에 0.15초쯤 걸린다. SQL을 짠 조민우는 이렇게 설명한다. "인라인 뷰는 먼저 실행되는 블록이니까, 결제 대기 주문을 먼저 뽑아 놓고 고객을 붙이려고 NO_MERGE를 넣었어요. 뷰가 풀려 버리면 순서가 엉킬까 봐서요."

테이블과 인덱스

T_ORD_BIG: 1,000,000행 · 5,826블록 · ORD_ST PENDING 8%(약 80,000건) · 고객당 약 500건, 그중 결제 대기 약 40건 · 한 고객의 주문은 테이블 곳곳에 흩어져 있다
인덱스 IX_ORD_BIG_CUS_ST (CUS_ID, ORD_ST): 높이 3 · CS 화면용으로 작년에 만들었다
M_CUS: 2,000행 · 30블록 · PK_M_CUS (CUS_ID) · A 등급 20명 · 통계 최신

원본 SQL

sql

SELECT c.CUS_ID, c.CUS_NM, v.ORD_SEQ, v.ORD_DT, v.ORD_AMT,
       TRUNC(SYSDATE) - v.ORD_DT AS WAIT_DAYS
  FROM M_CUS c,
       (SELECT /*+ NO_MERGE */ ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_ST = 'PENDING') v
 WHERE c.CUS_GD = 'A'
   AND v.CUS_ID = c.CUS_ID
 ORDER BY c.CUS_ID, v.ORD_DT DESC

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name      | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |           |      1 |        |    803 |00:00:00.14 |    5856 |          |
|   1 |  SORT ORDER BY        |           |      1 |    800 |    803 |00:00:00.14 |    5856 |75776  (0)|
|*  2 |   HASH JOIN           |           |      1 |    800 |    803 |00:00:00.14 |    5856 | 1245K (0)|
|*  3 |    TABLE ACCESS FULL  | M_CUS     |      1 |     20 |     20 |00:00:00.01 |      30 |          |
|   4 |    VIEW               |           |      1 |  80000 |  80127 |00:00:00.12 |    5826 |          |
|*  5 |     TABLE ACCESS FULL | T_ORD_BIG |      1 |  80000 |  80127 |00:00:00.11 |    5826 |          |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("V"."CUS_ID"="C"."CUS_ID")
   3 - filter("C"."CUS_GD"='A')
   5 - filter("ORD_ST"='PENDING')

문제

4번 VIEW 줄이 결제 대기 주문 80,127건을 모두 만든 이유를 NO_MERGE와 관련지어 설명하시오. "인라인 뷰는 먼저 실행된다"는 설명이 왜 틀렸는지도 쓰시오.
고객 20명의 결제 대기 주문만 인덱스로 읽도록 SQL을 고치고, 고친 뒤의 처리 순서와 읽은 블록 수를 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- NO_MERGE를 뗀다: 단순 뷰는 바깥과 합쳐지고, 조인 조건이 인덱스 access가 된다
SELECT c.CUS_ID, c.CUS_NM, v.ORD_SEQ, v.ORD_DT, v.ORD_AMT,
       TRUNC(SYSDATE) - v.ORD_DT AS WAIT_DAYS
  FROM M_CUS c,
       (SELECT ORD_SEQ, CUS_ID, ORD_DT, ORD_AMT
          FROM T_ORD_BIG
         WHERE ORD_ST = 'PENDING') v
 WHERE c.CUS_GD = 'A'
   AND v.CUS_ID = c.CUS_ID
 ORDER BY c.CUS_ID, v.ORD_DT DESC

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

xplan

-----------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                   |      1 |        |    803 |00:00:00.01 |     893 |          |
|   1 |  SORT ORDER BY                |                   |      1 |    800 |    803 |00:00:00.01 |     893 |75776  (0)|
|   2 |   NESTED LOOPS                |                   |      1 |    800 |    803 |00:00:00.01 |     893 |          |
|   3 |    NESTED LOOPS               |                   |      1 |    800 |    803 |00:00:00.01 |      90 |          |
|*  4 |     TABLE ACCESS FULL         | M_CUS             |      1 |     20 |     20 |00:00:00.01 |      30 |          |
|*  5 |     INDEX RANGE SCAN          | IX_ORD_BIG_CUS_ST |     20 |     40 |    803 |00:00:00.01 |      60 |          |
|   6 |    TABLE ACCESS BY INDEX ROWID| T_ORD_BIG         |    803 |     40 |    803 |00:00:00.01 |     803 |          |
-----------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("C"."CUS_GD"='A')
   5 - access("CUS_ID"="C"."CUS_ID" AND "ORD_ST"='PENDING')

모범 답안 서술

NO_MERGE는 이 뷰를 바깥과 합치지 말고 따로 최적화하라는 지시다. 따로 만들어지는 뷰 안에는 바깥의 조인 조건 v.CUS_ID = c.CUS_ID가 없으므로, 4번 VIEW 줄은 ORD_ST = 'PENDING' 하나로 테이블 전체를 읽어 결제 대기 주문 80,127건을 모두 만들었다(5,826블록). 옵티마이저는 이 큰 집합을 A 등급 20명과 해시 조인하고 803건만 남겼다. 읽은 결제 대기 주문의 99%가 버려졌다. "인라인 뷰는 먼저 실행된다"는 설명은 오해다. 인라인 뷰도 FROM 절의 테이블 하나처럼 다뤄지고, 조인 순서와 방식은 옵티마이저가 정한다. 이 뷰처럼 조건만 있는 단순한 뷰는 힌트가 없으면 바깥과 합쳐져(단순 뷰 머징) 뷰라는 경계 자체가 사라진다. 합쳐진다고 순서가 엉키지 않고, 오히려 바깥 조건과 조인 조건을 함께 보고 더 나은 순서를 고를 수 있다. NO_MERGE를 떼면 뷰가 합쳐지고, 옵티마이저는 A 등급 고객 20명(30블록)을 먼저 읽은 뒤 고객마다 IX_ORD_BIG_CUS_ST에서 (고객번호, PENDING) 범위만 찾는다(5번 줄 Starts 20, 60블록). 그 803건만 테이블에서 읽어(803블록) 정렬한다. Buffers는 5,856 → 893으로 준다. 뷰를 꼭 남겨야 한다면 NO_MERGE(v) PUSH_PRED(v)로 조인 조건을 뷰 안으로 밀어 넣어도(JPPD) 같은 일량이 된다. 하지만 이 뷰는 남겨야 할 까닭이 없으므로 힌트를 떼는 것이 가장 단순하다.

해설

NO_MERGE가 맞는 경우: 뷰 안에서 먼저 줄여야 하는 계산(행마다 부르는 함수, 집계)이 있어서 합쳐지면 그 계산이 조인 결과 행마다 되풀이될 때다(PLSQL-009). 조건만 있는 단순한 뷰에 붙이면 얻는 것 없이 조인 조건이 뷰 안으로 들어가는 길만 막는다.
NO_MERGE와 바깥 조건: 뷰가 따로 있어도 바깥의 상수 조건(v.CUS_ID = :cus)은 뷰 안으로 밀려 들어간다(조건절 Pushing). 막힌 것은 다른 테이블 컬럼과의 조인 조건이다. 그래서 고객 한 명을 넘기는 CS 화면에서는 이 힌트가 드러나지 않았다.
더 줄이려면: 남은 803블록은 결제 대기 주문을 테이블에서 읽는 몫이다. ORD_DT, ORD_AMT까지 인덱스에 넣으면 테이블에 가지 않지만(약 90블록), 100만 행 인덱스가 커지고 적재 비용이 는다.
힌트로 처리 순서를 정하고 싶다면 LEADING(c v) USE_NL(v)처럼 조인 순서와 방식을 직접 적는다. NO_MERGE는 순서를 정하는 힌트가 아니다(교재 06화 힌트 유효기간 대장).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황