PART-006

구 POS 판매까지 보이게 통합 뷰로 바꾼 뒤 매장 판매 이력이 0.01초에서 0.09초가 되었다

중급SELECT유통381 / 700

상황

본사 영업관리팀의 「매장 판매 이력」 화면은 매장과 기간을 고르면 일자별 판매 건수를 보여 준다. 매장 담당자들이 하루 수백 번 연다. 지난달 조민우가 구 POS 시절의 2021년 판매(CF10_ARC)도 같은 화면에서 보이도록 두 테이블을 합친 통합 뷰 V_SLE_HIST를 만들고, 화면이 그 뷰를 읽게 바꿨다. 처음 만든 뷰는 UNION ALL에서 ORA-01790으로 거부되어 한 줄을 고쳐 다시 만들었다. 그 뒤로 2023년 6월 한 달을 고르면 0.01초이던 화면이 0.09초가 되었다. 화면 SQL에는 함수도 형변환도 없다.

테이블과 인덱스

CF10_P 판매(파티션 복사본, 문제 설정): CF10 500,000행을 PARTITION BY RANGE (SLE_DT) 월별 24칸(P202201 ~ P202312)과 빈 PMAX로 나눠 CTAS로 지었다. 칸 하나는 약 20,800행이고 FULL로 약 192블록, 24칸 전체는 약 4,610블록이다. P202306은 18번째 칸이다. SLE_DT는 VARCHAR2(8) '20230615'이다.
CF10_ARC 판매 보관(문제 설정): 구 POS에서 옮긴 2021년 판매 249,600행 · 2,290블록. 컬럼은 CF10과 같지만 SLE_DT가 NUMBER(8) 20210615이다. 앞으로 DML이 없다. 인덱스 CF10_ARC_X01 (SLE_DT): BLEVEL 1.
매장은 6곳이고 한 매장의 한 달 판매는 약 3,470건이다. CF10_P에는 이 조회에 쓸 인덱스가 없다. 통계는 최신이다.
화면은 뷰의 SLE_DT가 숫자라서 기간을 숫자(:v_from = 20230601, :v_to = 20230630)로 넘긴다.

원본 SQL

sql

-- 통합 판매 이력 뷰 (ORA-01790 이 난 뒤 고쳐 만든 판)
CREATE OR REPLACE VIEW V_SLE_HIST AS
SELECT SLE_NO, TO_NUMBER(SLE_DT) AS SLE_DT, SLE_HMS, SHP_NO
  FROM CF10_P
UNION ALL
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10_ARC;

-- 「매장 판매 이력」 화면
SELECT SLE_DT, COUNT(*) AS sle_cnt
  FROM V_SLE_HIST
 WHERE SHP_NO = :v_shp
   AND SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SLE_DT
 ORDER BY SLE_DT

실행계획 학습용 예시

xplan

-------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name         | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
-------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |              |      1 |        |     30 | 00:00:00.09 |    4612 |        |       |
|   1 |  SORT GROUP BY                           |              |      1 |     85 |     30 | 00:00:00.09 |    4612 |        |       |
|   2 |   VIEW                                   | V_SLE_HIST   |      1 |    312 |   3468 | 00:00:00.09 |    4612 |        |       |
|   3 |    UNION-ALL                             |              |      1 |        |   3468 | 00:00:00.09 |    4612 |        |       |
|*  4 |     FILTER                               |              |      1 |        |   3468 | 00:00:00.09 |    4610 |        |       |
|   5 |      PARTITION RANGE ALL                 |              |      1 |    208 |   3468 | 00:00:00.09 |    4610 |      1 |    25 |
|*  6 |       TABLE ACCESS FULL                  | CF10_P       |     24 |    208 |   3468 | 00:00:00.08 |    4610 |      1 |    25 |
|*  7 |     FILTER                               |              |      1 |        |      0 | 00:00:00.01 |       2 |        |       |
|*  8 |      TABLE ACCESS BY INDEX ROWID BATCHED | CF10_ARC     |      1 |    104 |      0 | 00:00:00.01 |       2 |        |       |
|*  9 |       INDEX RANGE SCAN                   | CF10_ARC_X01 |      1 |    624 |      0 | 00:00:00.01 |       2 |        |       |
-------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(:V_FROM<=:V_TO)
   6 - filter(("SHP_NO"=:V_SHP AND TO_NUMBER("SLE_DT")>=:V_FROM AND TO_NUMBER("SLE_DT")<=:V_TO))
   7 - filter(:V_FROM<=:V_TO)
   8 - filter("SHP_NO"=:V_SHP)
   9 - access("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO)

[바인드 — V$SQL_BIND_CAPTURE]
   NAME     DATATYPE_STRING  VALUE_STRING
   :V_FROM  NUMBER           20230601
   :V_TO    NUMBER           20230630

문제

화면 SQL에는 함수가 없는데 CF10_P의 칸을 전부 여는 까닭을 Predicate Information과 뷰 정의로 설명하시오. 뷰를 만든 사람이 왜 그렇게 고쳤는지도 쓰시오.
결과가 바뀌지 않게 뷰를 고쳐 6월 칸 하나만 읽게 하시오. 그때 보관 테이블 쪽 갈래가 어떻게 되는지와 그것을 처리하는 방법도 쓰시오.
뷰를 고친 뒤 화면 쪽에서 함께 바꿔야 할 것과 Buffers가 어떻게 바뀌는지 쓰시오.

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

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

모범답안

모범 답안 SQL

sql

-- 파티션 키 SLE_DT 는 맨몸으로 두고, 타입은 보관 쪽(변경이 없는 테이블)에서 맞춘다
CREATE OR REPLACE VIEW V_SLE_HIST AS
SELECT SLE_NO, SLE_DT, SLE_HMS, SHP_NO
  FROM CF10_P
UNION ALL
SELECT SLE_NO, TO_CHAR(SLE_DT, 'FM00000000') AS SLE_DT, SLE_HMS, SHP_NO
  FROM CF10_ARC;

-- 보관 쪽 갈래도 인덱스로 찾도록 뷰와 같은 식의 함수 기반 인덱스 (DML 이 없어 유지 비용이 없다)
CREATE INDEX CF10_ARC_FX01 ON CF10_ARC (TO_CHAR(SLE_DT, 'FM00000000'));
DROP INDEX CF10_ARC_X01;   -- 보관 테이블을 숫자로 직접 찾는 다른 SQL 이 없을 때만

-- 화면 : 기간을 문자로 넘긴다 (:v_from = '20230601', :v_to = '20230630' · setString)
SELECT SLE_DT, COUNT(*) AS sle_cnt
  FROM V_SLE_HIST
 WHERE SHP_NO = :v_shp
   AND SLE_DT BETWEEN :v_from AND :v_to
 GROUP BY SLE_DT
 ORDER BY SLE_DT;

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

xplan

--------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name          | Starts | E-Rows | A-Rows |      A-Time | Buffers | Pstart | Pstop |
--------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |               |      1 |        |     30 | 00:00:00.01 |     194 |        |       |
|   1 |  SORT GROUP BY                           |               |      1 |     30 |     30 | 00:00:00.01 |     194 |        |       |
|   2 |   VIEW                                   | V_SLE_HIST    |      1 |   3471 |   3468 | 00:00:00.01 |     194 |        |       |
|   3 |    UNION-ALL                             |               |      1 |        |   3468 | 00:00:00.01 |     194 |        |       |
|*  4 |     FILTER                               |               |      1 |        |   3468 | 00:00:00.01 |     192 |        |       |
|   5 |      PARTITION RANGE ITERATOR            |               |      1 |   3470 |   3468 | 00:00:00.01 |     192 |    KEY |   KEY |
|*  6 |       TABLE ACCESS FULL                  | CF10_P        |      1 |   3470 |   3468 | 00:00:00.01 |     192 |    KEY |   KEY |
|*  7 |     FILTER                               |               |      1 |        |      0 | 00:00:00.01 |       2 |        |       |
|*  8 |      TABLE ACCESS BY INDEX ROWID BATCHED | CF10_ARC      |      1 |      1 |      0 | 00:00:00.01 |       2 |        |       |
|*  9 |       INDEX RANGE SCAN                   | CF10_ARC_FX01 |      1 |      1 |      0 | 00:00:00.01 |       2 |        |       |
--------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter(:V_FROM<=:V_TO)
   6 - filter(("SLE_DT">=:V_FROM AND "SLE_DT"<=:V_TO AND "SHP_NO"=:V_SHP))
   7 - filter(:V_FROM<=:V_TO)
   8 - filter("SHP_NO"=:V_SHP)
   9 - access(TO_CHAR("SLE_DT",'FM00000000')>=:V_FROM AND TO_CHAR("SLE_DT",'FM00000000')<=:V_TO)

  (실행 때 고른 칸 — 18번 P202306 하나)

모범 답안 서술

6번 줄의 조건은 TO_NUMBER("SLE_DT")>=:V_FROM이다. 화면 SQL에는 없는 함수이고, 뷰 정의의 판매 쪽 갈래에 있는 TO_NUMBER(SLE_DT) AS SLE_DT에서 왔다. 화면의 SLE_DT BETWEEN :v_from AND :v_to가 UNION ALL의 두 갈래 안으로 밀려 들어가면서, 판매 쪽에서는 뷰 컬럼의 정의인 TO_NUMBER("SLE_DT")에 붙었다. 파티션 키 SLE_DT가 숫자로 바뀌어 맨몸이 아니므로 칸 경계와 비교하지 못하고, 5번 줄처럼 PARTITION RANGE ALL(Pstart 1, Pstop 25)로 24칸을 다 열었다(Starts 24, 4,610블록). 보관 쪽 갈래는 컬럼이 맨몸이라 CF10_ARC_X01로 2블록 만에 끝났다. 뷰를 만든 사람이 이렇게 고친 까닭은 UNION ALL이 위아래 갈래의 같은 자리 컬럼 타입이 같아야 하기 때문이다. 판매는 VARCHAR2, 보관은 NUMBER라서 처음 뷰는 ORA-01790으로 거부되었고, 판매 쪽을 숫자로 바꿔 맞췄다. 타입을 맞추는 쪽을 잘못 고른 것이다. 고치려면 파티션 키가 있는 판매 쪽을 맨몸으로 두고, 변경이 없는 보관 쪽에서 TO_CHAR(SLE_DT, 'FM00000000')으로 문자로 맞춘다. 그러면 판매 쪽 조건은 "SLE_DT">=:V_FROM이 되어 KEY로 6월 칸 하나만 연다(Starts 1, 192블록). 다만 변환을 옮긴 보관 쪽은 이제 컬럼이 가공되어 CF10_ARC_X01을 못 쓰고, 그대로 두면 보관 테이블을 FULL로 읽는다(2,290블록, 합계 약 2,482블록). 보관 테이블은 바뀌지 않으므로 뷰와 같은 식의 함수 기반 인덱스 CF10_ARC_FX01을 만든다. 유지 비용 없이 보관 쪽도 2블록으로 끝난다. 뷰의 SLE_DT가 문자가 되었으므로 화면도 기간을 문자로 넘겨야 한다(setString, '20230601'). 숫자로 계속 넘기면 이번에는 문자 컬럼 쪽이 숫자로 바뀌어 TO_NUMBER("SLE_DT")가 두 갈래 모두에 다시 붙고, 칸 고르기도 함수 기반 인덱스도 막힌다. 뷰와 화면을 함께 배포한다. Buffers는 4,612 → 194블록으로 준다.

해설

타입은 칸 고르기와 상관없는 쪽에서 맞춘다. UNION ALL 뷰, 조인, 서브쿼리 어디서든 두 타입을 맞춰야 하면, 파티션 키나 인덱스 선두 컬럼이 아닌 쪽, 데이터가 적고 바뀌지 않는 쪽을 바꾼다.
근본 처방: 보관 테이블을 SLE_DT VARCHAR2(8)로 다시 만들면(CTAS 뒤 인덱스 재생성) 뷰에 변환이 하나도 남지 않는다. 한 번 옮기는 비용이 들고, 보관 테이블의 SLE_DT를 숫자로 읽는 옛 리포트가 있다면 함께 고쳐야 한다.
TO_CHAR에는 형식('FM00000000')을 주고, 함수 기반 인덱스도 뷰와 글자까지 같은 식으로 만든다. 식이 조금이라도 다르면 옵티마이저는 그 인덱스를 쓰지 못한다. 보관 쪽 값이 모두 8자리라서 바꾼 문자의 순서가 날짜 순서와 같고, 결과도 바뀌지 않는다.
이 증상은 화면 SQL만 보아서는 찾을 수 없다. Predicate Information에 SQL에 없는 함수가 보이면 뷰 정의(USER_VIEWS.TEXT)를 먼저 연다. 컬럼 쪽이 변환되면 인덱스도 칸 고르기도 막힌다는 원리는 뷰 안에서도 같다(교재 03화 §1).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황