상품 수불 화면의 「발주」 탭이 57블록짜리 발주상품을 보는데 판매상품 110만 줄을 읽는다

기초DDL유통283 / 700

상황

상품팀의 「상품 수불」 화면에는 판매, 반품, 발주, 입출고 탭이 있다. 탭을 고르면 공통 뷰 V_ITM_MOVE에서 그 구분(MV_TP)만 골라 읽는다. 「발주」 탭은 상품 하나의 발주 65줄을 보여 주는데 0.1초 가까이 걸린다. 발주상품 테이블은 57블록뿐인데 이상하다는 문의가 왔고, 「입출고」 탭을 열 때는 판매상품 테이블을 읽지 않는다는 것도 함께 알려 왔다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) · SLE_QTY는 NOT NULL이고 반품은 수량이 음수로 들어간다
CF13 발주상품: 7,102행 · 57블록 · PK (ORD_NO, ITM_NO)
CF15 입출고상품: 51,497행 · 349블록 · PK (INOUT_NO, ITM_NO)
세 테이블 모두 ITM_NO가 선두인 인덱스는 없다 · 상품 109개 · 아래 상품의 발주 65줄 · 통계 최신

원본 SQL

sql

-- 공통 뷰: 상품 수불
CREATE OR REPLACE VIEW V_ITM_MOVE AS
SELECT ITM_NO,
       CASE WHEN SLE_QTY < 0 THEN 'RTN' ELSE 'SLE' END AS MV_TP,
       SLE_NO AS REF_NO, SLE_QTY AS QTY
  FROM CF11
UNION ALL
SELECT ITM_NO, 'ORD', ORD_NO, ORD_QTY
  FROM CF13
UNION ALL
SELECT ITM_NO, 'IO', INOUT_NO, INOUT_QTY
  FROM CF15;

-- 상품 수불 화면 (탭마다 :tp 만 바뀐다)
SELECT REF_NO, QTY
  FROM V_ITM_MOVE
 WHERE ITM_NO = :itm
   AND MV_TP  = :tp;

실행계획 학습용 예시

xplan

[:tp = 'ORD']
--------------------------------------------------------------------------------------------
| Id  | Operation            | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |            |      1 |        |     65 |00:00:00.08 |    8261 |
|   1 |  VIEW                | V_ITM_MOVE |      1 |    638 |     65 |00:00:00.08 |    8261 |
|   2 |   UNION-ALL          |            |      1 |        |     65 |00:00:00.08 |    8261 |
|*  3 |    TABLE ACCESS FULL | CF11       |      1 |    101 |      0 |00:00:00.08 |    8204 |
|*  4 |    FILTER            |            |      1 |        |     65 |00:00:00.01 |      57 |
|*  5 |     TABLE ACCESS FULL| CF13       |      1 |     65 |     65 |00:00:00.01 |      57 |
|*  6 |    FILTER            |            |      1 |        |      0 |00:00:00.01 |       0 |
|*  7 |     TABLE ACCESS FULL| CF15       |      0 |    472 |      0 |00:00:00.01 |       0 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(("ITM_NO"=:ITM AND CASE  WHEN "SLE_QTY"<0 THEN 'RTN' ELSE 'SLE' END =:TP))
   4 - filter(:TP='ORD')
   5 - filter("ITM_NO"=:ITM)
   6 - filter(:TP='IO')
   7 - filter("ITM_NO"=:ITM)

문제

바깥 조건 MV_TP = :tp가 세 갈래에 각각 어떤 모양으로 들어갔는지 Predicate Information으로 설명하시오. 7번 줄의 Starts가 0인데 3번 줄은 1인 이유도 설명하시오.
「발주」 탭이 판매상품을 읽지 않도록 뷰를 고치시오. 고친 뷰가 다른 탭과 화면에 주는 영향도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 판매와 반품을 갈래 둘로 나눠, 갈래마다 구분 값이 상수가 되게 한다
CREATE OR REPLACE VIEW V_ITM_MOVE AS
SELECT ITM_NO, 'SLE' AS MV_TP, SLE_NO AS REF_NO, SLE_QTY AS QTY
  FROM CF11
 WHERE SLE_QTY >= 0
UNION ALL
SELECT ITM_NO, 'RTN', SLE_NO, SLE_QTY
  FROM CF11
 WHERE SLE_QTY < 0
UNION ALL
SELECT ITM_NO, 'ORD', ORD_NO, ORD_QTY
  FROM CF13
UNION ALL
SELECT ITM_NO, 'IO', INOUT_NO, INOUT_QTY
  FROM CF15;
-- 화면 SQL은 그대로 둔다

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

xplan

[:tp = 'ORD']
---------------------------------------------------------------------------------------------
| Id  | Operation             | Name       | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |            |      1 |        |     65 |00:00:00.01 |      57 |
|   1 |  VIEW                 | V_ITM_MOVE |      1 |  10599 |     65 |00:00:00.01 |      57 |
|   2 |   UNION-ALL           |            |      1 |        |     65 |00:00:00.01 |      57 |
|*  3 |    FILTER             |            |      1 |        |      0 |00:00:00.01 |       0 |
|*  4 |     TABLE ACCESS FULL | CF11       |      0 |   9962 |      0 |00:00:00.01 |       0 |
|*  5 |    FILTER             |            |      1 |        |      0 |00:00:00.01 |       0 |
|*  6 |     TABLE ACCESS FULL | CF11       |      0 |    100 |      0 |00:00:00.01 |       0 |
|*  7 |    FILTER             |            |      1 |        |     65 |00:00:00.01 |      57 |
|*  8 |     TABLE ACCESS FULL | CF13       |      1 |     65 |     65 |00:00:00.01 |      57 |
|*  9 |    FILTER             |            |      1 |        |      0 |00:00:00.01 |       0 |
|* 10 |     TABLE ACCESS FULL | CF15       |      0 |    472 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter(:TP='SLE')
   4 - filter(("ITM_NO"=:ITM AND "SLE_QTY">=0))
   5 - filter(:TP='RTN')
   6 - filter(("ITM_NO"=:ITM AND "SLE_QTY"<0))
   7 - filter(:TP='ORD')
   8 - filter("ITM_NO"=:ITM)
   9 - filter(:TP='IO')
  10 - filter("ITM_NO"=:ITM)

모범 답안 서술

바깥 조건은 UNION ALL의 갈래마다 따로 들어갔다(조건절 Pushing). 발주 갈래와 입출고 갈래의 MV_TP는 상수 'ORD', 'IO'라서 조건이 :TP='ORD', :TP='IO'처럼 테이블 컬럼이 없는 모양이 되었고, 4번과 6번 줄의 FILTER가 바인드 값만 보고 실행 전에 참과 거짓을 정한다. 거짓인 입출고 갈래는 통째로 건너뛰어 7번 줄의 Starts가 0이다. 판매상품 갈래는 MV_TP가 CASE 식이다. 조건이 CASE WHEN "SLE_QTY"<0 THEN 'RTN' ELSE 'SLE' END = :TP가 되어, 행마다 SLE_QTY를 보고 식을 계산해야만 참인지 알 수 있다. 그래서 「발주」 탭에서도 3번 줄이 판매상품 110만 줄(8,204블록)을 다 읽고 0줄을 돌려준다. 화면 일량의 99%가 답과 상관없는 갈래에서 나왔다. 판매와 반품을 갈래 둘로 나눠 갈래마다 구분 값을 상수로 준다. 반품 구분은 갈래 안의 WHERE SLE_QTY < 0이 맡는다. 이제 「발주」 탭에서는 두 판매상품 갈래도 FILTER에서 거짓으로 정해져 건너뛰고(Starts 0), 발주상품 57블록만 읽는다. Buffers는 8,261 → 57로 준다. SLE_QTY가 NOT NULL이라 두 갈래를 합치면 원래 갈래와 같다. NULL이 들어올 수 있다면 'SLE' 쪽 갈래에 OR SLE_QTY IS NULL을 더해야 한다. 부작용도 있다. 구분 없이 상품의 전체 수불을 읽는 화면은 이제 판매상품을 두 번(갈래 둘) 통째로 읽는다. 그런 화면이 있다면 갈래를 나누는 대신 상수 출처 컬럼('CF11', 'CF13', 'CF15')을 더하고, 탭은 출처와 구분을 함께 거는 방식이 낫다.

해설

다른 정답: 뷰에 SRC 같은 상수 출처 컬럼을 더하고 「발주」 탭이 SRC = 'CF13'을 함께 걸게 해도 판매상품 갈래를 건너뛴다. 판매상품을 한 번만 읽는 갈래 구조가 유지되므로 전체 수불 화면에 손해가 없다. 대신 화면 SQL도 고쳐야 한다.
판매 탭은 여전히 무겁다: 「판매」 탭은 판매상품 갈래를 읽어야 하는데 ITM_NO 선두 인덱스가 없어 통째로 읽는다. 이것은 갈래 구조가 아니라 인덱스 설계의 문제다(CF11_PK의 선두는 SLE_NO).
상수 갈래의 원리: 갈래마다 상수를 두고 바깥 조건으로 갈래를 고르는 모양은 파티션이 없던 시절의 '파티션 뷰'와 같다. 바인드 변수여도 FILTER가 실행 시점에 판정하므로 커서 하나로 모든 탭을 받는다.
계획에서 확인하는 법: 갈래 위 FILTER 줄의 조건에 컬럼이 없고 그 아래 줄의 Starts가 0이면 건너뛴 것이다(교재 08화 §5).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황