고칠 수 없는 BI 차트 SQL이 월 판매를 볼 때마다 판매 전체를 읽는다

실전DDL유통18 / 700

상황

유통 본사가 셀프서비스 BI 도구를 들였다. MD들이 「월 판매」 차트에서 연도와 월을 고르면 도구가 SQL을 만든다. BI 관리자는 데이터 원본 정의(계산 필드, 필드 타입)와 필터 종류는 바꿀 수 있지만, 차트마다 SQL을 손으로 쓸 수는 없다. 판매일자가 문자라서 관리자는 TO_DATE(SLE_DT, 'YYYYMMDD')를 계산 필드 「판매일」로 정의해 두었다. MD 30명이 차트를 열 때마다 아래 SQL이 나간다.

테이블과 인덱스

CF10 판매: 500,000행 · 4,583블록(전체 스캔 4,510) · SLE_DT VARCHAR2(8) · SHP_NO 매장 5곳 · 2023년 6월 20,953건
인덱스 CF10_PK (SLE_NO) · IX_CF10_SLE_DT (SLE_DT): BLEVEL 2 · 리프 1,393 · CF 9,921 · 통계 최신
판매는 POS 판매 등록 프로그램이 한 건씩 넣고(하루 약 700건), 지난 자료는 sqlldr로 넣었다(CF10.ctl에는 컬럼 목록이 적혀 있다). POS 프로그램의 INSERT 모양은 아직 확인하지 않았다

원본 SQL

sql

SELECT "SHP_NO", COUNT(*) AS "CNT"
  FROM "CF10"
 WHERE EXTRACT(YEAR FROM TO_DATE("SLE_DT", 'YYYYMMDD')) = 2023
   AND EXTRACT(MONTH FROM TO_DATE("SLE_DT", 'YYYYMMDD')) = 6
 GROUP BY "SHP_NO"

실행계획 학습용 예시 · 건수는 적재 데이터, 가상 컬럼 수치는 교재 03화 실측

xplan

-----------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      5 |00:00:00.09 |    4510 |
|   1 |  HASH GROUP BY     |      |      1 |      5 |      5 |00:00:00.09 |    4510 |
|*  2 |   TABLE ACCESS FULL| CF10 |      1 |     50 |  20953 |00:00:00.08 |    4510 |
-----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter((EXTRACT(YEAR FROM TO_DATE("SLE_DT",'YYYYMMDD'))=2023 AND
              EXTRACT(MONTH FROM TO_DATE("SLE_DT",'YYYYMMDD'))=6))

문제

BI가 만든 SQL이 IX_CF10_SLE_DT를 쓰지 못하는 이유와 E-Rows가 50으로 나온 이유를 설명하시오.
SQL을 손으로 고칠 수 없다는 조건에서 쓸 수 있는 방법(BI가 만든 식 그대로의 함수 기반 인덱스, 가상 컬럼과 BI 필터 설정, 요약 테이블)을 비교하고, 고른 방법의 DDL과 BI 쪽 설정을 쓰시오.
고른 방법이 POS 판매 등록과 적재 작업에 주는 영향과, 반영 전에 확인할 것을 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- ① 날짜형 가상 컬럼을 두고 판매일과 매장으로 인덱싱한다 (집계에 쓰는 SHP_NO까지 담는다)
ALTER TABLE CF10 ADD (SLE_D DATE GENERATED ALWAYS AS (TO_DATE(SLE_DT, 'YYYYMMDD')) VIRTUAL);
CREATE INDEX IX_CF10_SLE_D_SHP ON CF10 (SLE_D, SHP_NO) INVISIBLE;
EXEC DBMS_STATS.GATHER_TABLE_STATS(USER, 'CF10', method_opt => 'FOR COLUMNS SLE_D SIZE 1');

-- ② BI 데이터 원본 : 계산 필드 「판매일」을 지우고 날짜형 컬럼 SLE_D에 연결한다
--    차트 필터 : '연도 · 월 선택' → '날짜 범위(월 단위)'로 바꾼다
--    → BI가 만드는 SQL
SELECT "SHP_NO", COUNT(*) AS "CNT"
  FROM "CF10"
 WHERE "SLE_D" >= TO_DATE('2023-06-01', 'YYYY-MM-DD')
   AND "SLE_D" <  TO_DATE('2023-07-01', 'YYYY-MM-DD')
 GROUP BY "SHP_NO";

-- ③ 반영 전 : POS 판매 등록의 INSERT가 컬럼 목록형인지, SELECT * 로 파일을 내리는 작업이 있는지 확인한다
--    → 세션에서 OPTIMIZER_USE_INVISIBLE_INDEXES = TRUE로 재고 → ALTER INDEX IX_CF10_SLE_D_SHP VISIBLE

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터, 가상 컬럼 수치는 교재 03화 실측

xplan

---------------------------------------------------------------------------------------------------
| Id  | Operation          | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                   |      1 |        |      5 |00:00:00.01 |      56 |
|   1 |  HASH GROUP BY     |                   |      1 |      5 |      5 |00:00:00.01 |      56 |
|*  2 |   INDEX RANGE SCAN | IX_CF10_SLE_D_SHP |      1 |  21261 |  20953 |00:00:00.01 |      56 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("SLE_D">=TO_DATE(' 2023-06-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
              "SLE_D"<TO_DATE(' 2023-07-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))

모범 답안 서술

2번 줄 filter를 보면 SLE_DT가 TO_DATE로 바뀐 뒤 다시 EXTRACT로 연도와 월이 뽑혀 비교된다. IX_CF10_SLE_DT에는 SLE_DT 문자열 원래 값이 정렬되어 있으므로 이렇게 가공한 값으로는 인덱스에서 범위를 지목할 수 없고, 차트를 열 때마다 50만 행을 다 읽는다(Buffers 4,510). E-Rows 50은 함수 등치 조건의 기본 선택도 1%가 두 조건에 한 번씩 곱해진 값이다(500,000 × 1% × 1%). 실제 20,953건의 400분의 1이라, 이 SQL이 조인에 들어가면 계획까지 틀어진다. SQL을 고칠 수 없으므로 데이터베이스 쪽 수단과 BI 설정을 함께 쓴다. ① BI가 만든 식 그대로 함수 기반 인덱스 (EXTRACT(YEAR FROM TO_DATE(SLE_DT, 'YYYYMMDD')), EXTRACT(MONTH FROM …))를 만들면 이 차트는 빨라진다. 하지만 함수 기반 인덱스는 식이 글자까지 같아야 쓰인다. MD가 분기나 일 단위로 바꾸면 BI는 다른 식을 만들고, 관리자가 계산 필드의 포맷 하나만 바꿔도 인덱스는 쓰이지 않는다. 차트마다 인덱스가 늘어난다. ② 날짜형 가상 컬럼 SLE_D를 두고 (SLE_D, SHP_NO)로 인덱싱한 뒤, BI 데이터 원본의 「판매일」을 이 DATE 컬럼에 연결하고 필터를 '날짜 범위'로 바꾼다. 그러면 BI는 "SLE_D" >= 6월 1일 AND "SLE_D" < 7월 1일 같은 범위 조건을 만들고, 일, 월, 분기 어느 단위든 같은 인덱스를 범위로 읽는다. 매장 번호까지 인덱스에 있어 테이블에 가지 않으므로 4,510 → 56블록이다. ③ 월별 매장별 요약 테이블(또는 구체화 뷰)은 차트를 가장 싸게 만들지만 갱신 주기와 정합성 관리가 따로 든다. ②를 고른다. 대가는 테이블에 보이는 컬럼이 하나 늘어난다는 것이다. 컬럼 목록 없이 값만 쓰는 INSERT INTO CF10 VALUES (...)는 ORA-00947로 깨지고, SELECT *로 파일을 내리는 작업에는 컬럼이 하나 더 나온다. 지난 자료를 넣은 sqlldr는 컨트롤 파일에 컬럼 목록이 있어 영향이 없다. 반영 전에 POS 판매 등록 프로그램과 SELECT *를 쓰는 작업을 찾아 컬럼 목록형인지 확인하고, 인덱스는 INVISIBLE로 만들어 세션에서 잰 뒤 VISIBLE로 바꾼다. 판매 등록은 하루 700건 남짓이라 인덱스 하나의 유지 비용은 작다.

해설

03화와 결론이 다른 이유: 03화에서는 SQL을 고칠 수 있어서 가상 컬럼을 재 보고도 채택하지 않았다(62와 59, 3블록 차이). 여기서는 SQL을 고칠 수 없고, 차트가 범위 조건을 만들게 하려면 날짜형 컬럼이 있어야 한다. 같은 도구라도 SQL을 고칠 수 있는지에 따라 답이 갈린다.
가상 컬럼만 만들고 필터를 연도와 월 선택으로 두면 BI는 EXTRACT(YEAR FROM "SLE_D")를 만든다. 가상 컬럼도 컬럼이므로 EXTRACT로 감싸면 다시 가공이다. 필터 종류까지 바꿔야 끝난다.
계산 필드를 문자열 SLE_DT 그대로 두고 '문자열 시작 값' 필터를 쓰면 "SLE_DT" LIKE '202306%'가 나와 기존 인덱스로도 읽힌다. 다만 MD가 쓰는 날짜 선택 화면을 포기해야 해서 현업이 받아들이기 어렵다.
BI 도구가 만드는 SQL은 V$SQL에서 모양을 확인할 수 있다. 차트를 하나 고칠 때마다 실제로 나가는 SQL과 계획을 다시 본다.

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 · 건수는 적재 데이터, 가상 컬럼 수치는 교재 03화 실측 ​

문제 ​

모범 답안 SQL ​

개선 후 실행계획 학습용 예시 · 건수는 적재 데이터, 가상 컬럼 수치는 교재 03화 실측 ​

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황