영수증 한 장의 품목 조회는 판매상품을 통째로 읽는데, 상품명을 붙인 조회는 바로 뜬다

중급DDL유통288 / 700

상황

매장 관리 화면에는 영수증 번호로 여는 화면이 둘 있다. [가] 「영수증 품목」은 한 번에 0.06초, [나] 「영수증 품목 · 상품명」은 0.001초다. 둘 다 공통 뷰 V_SLE_ITM을 읽고, 점장들이 합쳐서 하루 수천 번 연다. 이 뷰는 2019년 야간 상품 매출 리포트용으로 만들었다. 그해 통계가 비어 있던 날 리포트가 인덱스로 판매상품을 다 읽어 30분이 걸렸고, 그 뒤 뷰 정의에 FULL(d) 힌트를 넣었다. 개발자는 "[나]도 같은 뷰인데 왜 [가]만 힌트가 먹느냐"고 묻는다.

테이블과 인덱스

CF11 판매상품: 1,096,708행 · 8,204블록 · CF11_PK (SLE_NO, ITM_NO) 높이 3 · 영수증 한 장에 평균 2.2줄(한 블록 안)
CF01 상품: 109행 · 5블록 · CF01_PK (ITM_NO)
야간 리포트는 한 달 치 판매상품(약 45,000줄)을 상품별로 묶는다 · 통계 최신

원본 SQL

sql

-- 공통 뷰 (2019년 야간 리포트 사고 뒤 힌트를 넣었다)
CREATE OR REPLACE VIEW V_SLE_ITM AS
SELECT /*+ FULL(d) */
       d.SLE_NO, d.ITM_NO, d.SLE_QTY, d.SLE_UPRC,
       d.SLE_QTY * d.SLE_UPRC AS SLE_AMT
  FROM CF11 d;

-- [가] 영수증 품목 (0.06초)
SELECT ITM_NO, SLE_QTY, SLE_AMT
  FROM V_SLE_ITM
 WHERE SLE_NO = :sle_no;

-- [나] 영수증 품목 · 상품명 (0.001초)
SELECT v.ITM_NO, i.ITM_NM, v.SLE_QTY, v.SLE_AMT
  FROM V_SLE_ITM v, CF01 i
 WHERE v.SLE_NO = :sle_no
   AND i.ITM_NO = v.ITM_NO;

실행계획 학습용 예시

xplan

[가]
-----------------------------------------------------------------------------------
| Id  | Operation         | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |      1 |        |      3 |00:00:00.06 |    8204 |
|*  1 |  TABLE ACCESS FULL| CF11 |      1 |      2 |      3 |00:00:00.06 |    8204 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("D"."SLE_NO"=:SLE_NO)

[나]
---------------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |      1 |        |      3 |00:00:00.01 |      10 |
|   1 |  NESTED LOOPS                |         |      1 |      2 |      3 |00:00:00.01 |      10 |
|   2 |   NESTED LOOPS               |         |      1 |      2 |      3 |00:00:00.01 |       7 |
|   3 |    TABLE ACCESS BY INDEX ROWID| CF11   |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN         | CF11_PK |      1 |      2 |      3 |00:00:00.01 |       3 |
|*  5 |    INDEX UNIQUE SCAN         | CF01_PK |      3 |      1 |      3 |00:00:00.01 |       3 |
|   6 |   TABLE ACCESS BY INDEX ROWID| CF01    |      3 |      1 |      3 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - access("D"."SLE_NO"=:SLE_NO)
   5 - access("I"."ITM_NO"="D"."ITM_NO")

문제

두 화면 모두 계획에 VIEW 줄이 없는 이유와, 같은 뷰의 FULL(d) 힌트가 [가]에서는 지켜지고 [나]에서는 지켜지지 않은 이유를 설명하시오.
[가]가 영수증 한 장만 읽도록 고치시오. 야간 리포트가 2019년처럼 흔들리지 않게 하려면 힌트를 어디에 두어야 하는지도 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 뷰에는 힌트를 두지 않는다
CREATE OR REPLACE VIEW V_SLE_ITM AS
SELECT d.SLE_NO, d.ITM_NO, d.SLE_QTY, d.SLE_UPRC,
       d.SLE_QTY * d.SLE_UPRC AS SLE_AMT
  FROM CF11 d;

-- [가]는 그대로 두면 CF11_PK로 영수증 한 장만 읽는다
SELECT ITM_NO, SLE_QTY, SLE_AMT
  FROM V_SLE_ITM
 WHERE SLE_NO = :sle_no;

-- 야간 리포트: 필요한 힌트는 리포트 SQL에 전역 힌트로 둔다
SELECT /*+ FULL(v.d) */ v.ITM_NO, SUM(v.SLE_AMT) AS AMT
  FROM V_SLE_ITM v
 WHERE v.SLE_NO >= :ym || '01'
   AND v.SLE_NO <  :next_ym || '01'
 GROUP BY v.ITM_NO;

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

xplan

[가]
---------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |      1 |        |      3 |00:00:00.01 |       4 |
|   1 |  TABLE ACCESS BY INDEX ROWID| CF11    |      1 |      2 |      3 |00:00:00.01 |       4 |
|*  2 |   INDEX RANGE SCAN          | CF11_PK |      1 |      2 |      3 |00:00:00.01 |       3 |
---------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("D"."SLE_NO"=:SLE_NO)

모범 답안 서술

두 계획 모두 VIEW 줄이 없고 테이블 줄의 별칭이 뷰 안의 "D"다. 조건만 있는 단순한 뷰라서 바깥 SQL에 합쳐졌다(뷰 머징). 뷰 안에 있던 FULL(d) 힌트도 합쳐진 SQL로 따라 올라온다. 그런데 오라클은 저장된 뷰(인라인 뷰가 아닌 뷰) 안의 접근 경로 힌트를, 바깥 SQL의 FROM 절에 그 뷰 하나만 있을 때만 지킨다. 다른 테이블이나 뷰와 조인하면 뷰 안의 접근 경로 힌트는 버려진다. 그래서 FROM 절에 V_SLE_ITM 하나뿐인 [가]는 FULL(d)가 지켜져 영수증 한 장(3줄)을 찾으려고 판매상품 전체 8,204블록을 읽었고, CF01과 조인한 [나]는 힌트가 버려져 CF11_PK로 4블록만 읽었다. 같은 뷰인데 화면마다 계획이 갈린 이유다. 뷰 정의에서 힌트를 뺀다. [가]는 SQL을 고치지 않아도 CF11_PK로 영수증 한 장만 읽는다. Buffers는 8,204 → 4로 준다. 야간 리포트를 고정할 힌트가 필요하면 리포트 SQL 자신에 둔다. FULL(v.d)처럼 '뷰 별칭.뷰 안 별칭'으로 적는 전역 힌트를 쓰면 뷰를 바꾸지 않고 리포트에만 효과가 난다. 2019년 사고의 원인은 빈 통계였으므로 통계 수집 정책을 함께 확인하는 것이 먼저다.

해설

힌트를 뷰에 넣는 대가: 뷰는 여러 SQL이 함께 쓰는 부품이다. 한 SQL을 위해 넣은 힌트가 그 뷰를 쓰는 모든 SQL에 퍼지고, 퍼질지 말지도 바깥 SQL의 모양에 따라 달라진다. 오라클 문서도 뷰 안의 힌트를 권하지 않는다.
인라인 뷰는 다르다: FROM 절의 인라인 뷰 안에 쓴 접근 경로 힌트는 뷰가 합쳐져도 그대로 지켜진다. 이 문제의 규칙은 저장된 뷰에 해당한다.
다른 정답: [가]에 INDEX(V_SLE_ITM.d CF11_PK) 같은 전역 힌트를 넣어 덮어쓰는 방법은 뷰 안의 힌트와 부딪혀 결과를 장담할 수 없고, 같은 뷰를 쓰는 다른 화면은 그대로 남는다. 뷰를 고치는 것이 맞다.
뷰를 고치기 전에 USER_DEPENDENCIES로 이 뷰를 쓰는 SQL을 찾아 힌트에 기대던 것이 있는지 확인한다. 힌트를 뺀 뒤 야간 리포트의 계획과 시간을 한 번 재 둔다(교재 06화 힌트 유효기간 대장, 10화 통계 수집 정책서).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황