카카오 시범 광고로 들어온 주문 312건을 보는데 네이버 41만 건과 똑같이 블록 1만 9천 개를 읽는다

모범 답안 SQL ​

-- ① URL에서 채널을 떼어 낸 가상 열 : 히스토그램이 보지 못하는 뒷부분을 짧은 값으로 따로 담는다
ALTER TABLE T_ORD_LND ADD (
  UTM_SRC AS (REGEXP_SUBSTR(LND_URL, 'utm_source=([^&]+)', 1, 1, 'c', 1)) VIRTUAL
);

-- ② 그 열로 찾아가는 인덱스 (채널이 없는 메인 · 목록 28만 행은 NULL 이라 인덱스에 들어가지 않는다)
CREATE INDEX IX_ORD_LND_SRC ON T_ORD_LND (UTM_SRC);

-- ③ 그 열에 히스토그램 (채널 6종 → FREQUENCY) · 선호도로 걸어 밤의 수집도 같은 규칙을 따르게 한다
BEGIN
  DBMS_STATS.SET_TABLE_PREFS(USER, 'T_ORD_LND', 'METHOD_OPT',
                             'FOR ALL COLUMNS SIZE AUTO FOR COLUMNS SIZE 254 UTM_SRC');
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'T_ORD_LND', no_invalidate => FALSE);
END;
/

-- ④ 화면은 고른 URL에서 채널을 떼어 조건을 하나 더 넣는다 (URL 조건은 그대로 → 결과는 같다)
SELECT o.ORD_DT, COUNT(*) AS ord_cnt, SUM(o.ORD_AMT) AS ord_amt
  FROM T_ORD_LND l, T_ORD_BIG o
 WHERE l.LND_URL = 'https://m.hanbitmall.co.kr/display/promotion/membership-benefit/landing.do?utm_source=kakao'
   AND l.UTM_SRC = 'kakao'
   AND o.ORD_SEQ = l.ORD_SEQ
 GROUP BY o.ORD_DT;

개선 후 실행계획 학습용 예시 · 끝점 길이는 12c 이후(64바이트) 기준 ​

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name           | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                |      1 |        |      8 |00:00:00.01 |    1349 |
|   1 |  HASH GROUP BY                          |                |      1 |    225 |      8 |00:00:00.01 |    1349 |
|   2 |   NESTED LOOPS                          |                |      1 |    225 |    312 |00:00:00.01 |    1349 |
|   3 |    NESTED LOOPS                         |                |      1 |    225 |    312 |00:00:00.01 |    1037 |
|*  4 |     TABLE ACCESS BY INDEX ROWID BATCHED | T_ORD_LND      |      1 |    225 |    312 |00:00:00.01 |     101 |
|*  5 |      INDEX RANGE SCAN                   | IX_ORD_LND_SRC |      1 |    312 |    312 |00:00:00.01 |       4 |
|*  6 |     INDEX UNIQUE SCAN                   | IX_ORD_BIG_SEQ |    312 |      1 |    312 |00:00:00.01 |     936 |
|   7 |    TABLE ACCESS BY INDEX ROWID          | T_ORD_BIG      |    312 |      1 |    312 |00:00:00.01 |     312 |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   4 - filter("L"."LND_URL"='https://m.hanbitmall.co.kr/display/promotion/membership-benefit/landing.do?utm_source=kakao')
   5 - access("L"."UTM_SRC"='kakao')
   6 - access("O"."ORD_SEQ"="L"."ORD_SEQ")

[USER_TAB_COL_STATISTICS · T_ORD_LND.UTM_SRC]  NUM_DISTINCT 6 · NUM_NULLS 280,000 · FREQUENCY · NUM_BUCKETS 6 (kakao 칸 312)
[같은 SQL · utm_source=naver]  E-Rows 410,000 × 0.72 = 295K · FULL과 HASH JOIN 그대로 · Buffers 19,146 (이쪽은 이 계획이 맞다)

모범 답안 서술 ​

원인: LND_URL에는 FREQUENCY 히스토그램이 있지만, 히스토그램은 문자열의 앞 64바이트만 끝점으로 기억한다(11g까지는 32바이트, 12c부터 64바이트). 회원 혜택 랜딩 URL 여섯 개는 utm_source= 뒤의 채널만 다르고 앞 86바이트가 같다. 앞 64바이트에서 자르면 여섯 개가 모두 https://m.hanbitmall.co.kr/display/promotion/membership-benefit/가 되어 한 칸으로 합쳐졌다. 증거는 통계에 있다. NUM_DISTINCT는 9인데 NUM_BUCKETS는 4이고, 첫 끝점의 ENDPOINT_ACTUAL_VALUE는 64바이트에서 끊겨 있다. 손계산: 카카오 URL도 앞 64바이트에서 자르면 첫 끝점과 같아서, 옵티마이저는 카카오를 그 칸의 값으로 본다. 그 칸의 건수는 720,000 − 0 = 720,000이고 선택도는 720,000 / 1,000,000 = 0.72다. 그래서 3번 줄의 E-Rows는 1,000,000 × 0.72 = 720K다. 실제 카카오는 312건이다. 네이버(410,000)든 tiktok(9,688)이든 이 랜딩의 채널은 모두 720K로 본다. 고른 계획: 72만 건이면 인덱스로 테이블을 72만 번 오가는 것보다 통째로 읽는 편이 싸다. 그래서 3번 줄에서 T_ORD_LND를 FULL로 읽고(13,320블록) 4번 줄에서 T_ORD_BIG도 FULL로 읽어(5,826블록) 해시 조인했다. 합해서 19,146블록이다. 312건이었다면 인덱스로 찾고 주문마다 IX_ORD_BIG_SEQ로 찾아가는 NL이 훨씬 싸다. 고침: 히스토그램이 보지 못하는 뒷부분(채널)을 떼어 짧은 값의 열로 둔다. REGEXP_SUBSTR로 utm_source 값을 꺼내는 가상 열 UTM_SRC를 만들고, 그 열에 인덱스와 히스토그램을 둔다. 채널은 6종이고 값이 짧아서 FREQUENCY가 kakao 312건을 정확히 기억한다. 화면은 고른 URL에서 채널을 떼어 l.UTM_SRC = 'kakao'를 더한다. URL 조건은 그대로 두므로 결과는 바뀌지 않는다. 개선 후 계획의 5번 줄 E-Rows는 312이고, URL 조건의 0.72를 곱한 4번 줄은 225로 실제 312에 가깝다. IX_ORD_LND_SRC로 312건을 찾고(테이블 끝 100블록 안팎) 주문은 NL로 찾아가므로 Buffers는 19,146 → 1,349다. 네이버는 410,000 × 0.72 = 295K로 보고 지금처럼 FULL과 해시 조인을 고르는데, 네이버에는 그것이 맞는 계획이다. 대가: 가상 열은 테이블의 열로 보이므로, 열 목록 없이 INSERT INTO T_ORD_LND VALUES (…)로 넣는 적재는 깨진다(ORA-00947). 웹 로그 배치의 INSERT가 열 목록형인지 먼저 확인한다. 인덱스가 하나 늘어 적재가 그만큼 무거워지지만 하루 약 910행이라 부담은 작다. 화면은 조건을 하나 더 만들어야 하고, LND_URL만으로 묻는 다른 SQL의 추정은 여전히 틀리다. 히스토그램을 없애는 안과 비교: SIZE 1로 히스토그램을 지우면 9종을 균등하게 보아 어느 URL이든 1,000,000 / 9 = 111,111건이다. 카카오는 여전히 크게 보여 FULL과 해시 조인이 그대로이고, 모바일 메인(200,000)은 반대로 작게 본다. 이 열에서는 얻는 것이 없다.

해설 ​

• 버전 차이: 끝점에 담는 길이는 11g까지 32바이트, 12c부터 64바이트다(이 DB는 23ai). 이 URL들은 앞 86바이트가 같아서 어느 버전에서든 합쳐진다. 앞 40바이트만 같은 코드라면 11g에서는 합쳐지고 12c부터는 구분되므로, 업그레이드 뒤에 추정이 바뀌는 이유가 되기도 한다. 길이는 글자가 아니라 바이트라서 AL32UTF8의 한글(한 글자 3바이트)은 21자 남짓에서 잘린다. 버전마다 끝점을 담는 방식이 다를 수 있으니 NUM_BUCKETS와 LENGTHB(ENDPOINT_ACTUAL_VALUE)를 직접 확인해 두자.
• 함정 둘: 가상 열에 히스토그램만 두고 인덱스를 만들지 않으면 추정은 225건으로 맞아도 T_ORD_LND는 FULL(13,320블록) 그대로다. 반대로 인덱스만 만들고 히스토그램이 없으면 NULL이 아닌 72만 행을 채널 6종으로 나눠 약 120,000건으로 보므로 인덱스를 타지 않는다.
• 동적 샘플링은 표본 행에 조건을 그대로 대어 보므로 64바이트 문제를 비켜 간다. 하지만 312건은 100만 행의 0.03%라 표본 수천 행에 한두 건 걸릴까 말까 해서, 파싱할 때마다 숫자가 흔들리고 표본을 읽는 비용도 든다. INDEX나 USE_NL 힌트는 같은 SQL 틀로 네이버(41만 건)를 고를 때 재앙이 된다.
• 근본은 키 구조다. 적재할 때 경로와 채널을 따로 저장하거나 랜딩 URL마다 숫자 ID를 두면, 히스토그램이 구분하지 못하는 긴 문자열을 조건 열로 쓰지 않게 된다. URL이나 '회사-연도-행사-…'처럼 앞부분이 고정된 긴 코드를 조건 열로 설계할 때 미리 살펴볼 점이다.

관련 교재 ​

• 10화 · 히스토그램 4종이 식을 바꾸는 지점
• 10화 · 상관 컬럼과 확장 통계 (표현식 통계)
• 03화 · 구제책: FBI와 가상 컬럼