「비회원 유입」 차트 하나가 1분마다 상품 조회 로그 10만 건을 다 읽는다

중급SELECT이커머스44 / 700

이 문제는 교재 18화 졸업 과제와 같은 이커머스 데이터베이스를 씁니다. 『튜너의 1년』을 순서대로 읽고 있다면 졸업 과제를 먼저 풀고 오세요. 아래 「테이블과 인덱스」 칸에 과제의 답 일부(제약 조건, 인덱스, 통계의 실제 상태)가 나옵니다.

상황

마케팅팀 대시보드의 「비회원 유입」 차트는 최근 30일 동안 로그인하지 않은 방문자의 상품 조회 수를 날짜별로 그린다. 마케터 열 명의 화면이 1분마다 새로 고친다. 개발자는 회원별 「최근 본 상품」 화면용으로 만든 customer_id 인덱스를 이 차트에도 쓰려고 INDEX 힌트를 붙였지만 계획은 FULL 그대로다. 개발자는 "IS NULL은 인덱스를 못 타니 비회원을 'GUEST' 같은 값으로 채우자"고 제안했다.

테이블과 인덱스

product_views 상품 조회 로그: 100,000행 · 367블록 · view_id(PK) · product_id · customer_id(비회원 조회는 NULL) · view_date DATE(시분초 있음, NOT NULL)
최근 1년치만 남긴다 · 하루 약 274건 · 비회원 조회 약 31%(30일에 약 2,550건) (문제 설정: 보관 기간과 비회원 비율은 이 문제에서 정한 값이다)
인덱스: PK와 IX_PV_CUST (customer_id)(회원 화면용 · 리프 약 160블록) · 통계 최신
회원 화면 SQL: WHERE customer_id = :c ORDER BY view_date DESC FETCH FIRST 20 ROWS ONLY(하루 수만 번)

원본 SQL

sql

SELECT /*+ INDEX(v IX_PV_CUST) */ TRUNC(view_date) AS view_day, COUNT(*) AS guest_views
  FROM product_views v
 WHERE customer_id IS NULL
   AND view_date >= TRUNC(:v_to) - 29
   AND view_date <  TRUNC(:v_to) + 1
 GROUP BY TRUNC(view_date)
 ORDER BY view_day

실행계획 학습용 예시

xplan

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     30 |00:00:00.02 |     367 |
|   1 |  SORT GROUP BY      |               |      1 |     30 |     30 |00:00:00.02 |     367 |
|*  2 |   FILTER            |               |      1 |        |   2548 |00:00:00.02 |     367 |
|*  3 |    TABLE ACCESS FULL| PRODUCT_VIEWS |      1 |   2534 |   2548 |00:00:00.02 |     367 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(:V_TO)-29<TRUNC(:V_TO)+1)
   3 - filter(("CUSTOMER_ID" IS NULL AND "VIEW_DATE">=TRUNC(:V_TO)-29 AND "VIEW_DATE"<TRUNC(:V_TO)+1))

Hint Report (identified by operation id / Query Block Name / Object Alias):
Total hints for statement: 1 (U - Unused (1))
   3 -  SEL$1 / "V"@"SEL$1"
         U -  INDEX(v IX_PV_CUST)

문제

INDEX 힌트가 쓰이지 않은 이유를 IX_PV_CUST에 무엇이 들어 있는지로 설명하시오.
비회원을 다른 값으로 채우지 않고, 인덱스 하나를 바꿔 회원 화면과 이 차트를 둘 다 범위로 읽게 하시오.
바꾼 인덱스에서 customer_id IS NULL이 어떻게 access 조건이 되는지 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- 회원 화면용 (customer_id) 를 (customer_id, view_date) 로 바꾼다 (인덱스 개수는 그대로)
CREATE INDEX IX_PV_CUST_DT ON product_views (customer_id, view_date) INVISIBLE;
-- 내 세션에서 두 화면을 재 본 뒤 바꾼다
ALTER INDEX IX_PV_CUST_DT VISIBLE;
DROP INDEX IX_PV_CUST;

SELECT TRUNC(view_date) AS view_day, COUNT(*) AS guest_views
  FROM product_views
 WHERE customer_id IS NULL
   AND view_date >= TRUNC(:v_to) - 29
   AND view_date <  TRUNC(:v_to) + 1
 GROUP BY TRUNC(view_date)
 ORDER BY view_day;

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

xplan

-----------------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |      1 |        |     30 |00:00:00.01 |      10 |
|   1 |  SORT GROUP BY      |               |      1 |     30 |     30 |00:00:00.01 |      10 |
|*  2 |   FILTER            |               |      1 |        |   2548 |00:00:00.01 |      10 |
|*  3 |    INDEX RANGE SCAN | IX_PV_CUST_DT |      1 |   2534 |   2548 |00:00:00.01 |      10 |
-----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter(TRUNC(:V_TO)-29<TRUNC(:V_TO)+1)
   3 - access("CUSTOMER_ID" IS NULL AND "VIEW_DATE">=TRUNC(:V_TO)-29 AND "VIEW_DATE"<TRUNC(:V_TO)+1)

모범 답안 서술

IX_PV_CUST는 customer_id 하나짜리 인덱스라, 키가 NULL인 비회원 조회 약 31,000건을 담지 않는다. 비회원 행이 인덱스 어디에도 없으므로 customer_id IS NULL로 찾아갈 곳이 없고, 힌트는 쓰이지 않는다(Hint Report의 U). 그래서 3번 줄이 10만 행을 다 읽는다(Buffers 367). IX_PV_CUST를 (customer_id, view_date)로 바꾼다. 결합 인덱스는 키 하나라도 NULL이 아니면 그 행을 담는다. view_date는 늘 값이 있으므로 비회원 행도 (NULL, 조회 일시) 항목으로 인덱스에 들어간다. NULL은 정렬의 맨 끝에 한 값처럼 모여 저장되므로, customer_id IS NULL은 이 인덱스에서 등치 조건처럼 쓰이고 뒤 컬럼 view_date의 30일 범위까지 access가 된다. 비회원 30일 치 2,548개 항목만 읽고, 세는 데 필요한 컬럼이 인덱스에 다 있어 테이블에도 가지 않는다(Buffers 367 → 10). 1분마다 열 대가 부르므로 하루 약 530만 → 14만 블록이다. 회원 화면도 이 인덱스로 받친다. customer_id = :c가 access가 되고, 인덱스를 view_date 역순으로 읽어 20건에서 멈추므로 정렬도 하지 않는다. 인덱스 개수는 그대로이고, 비회원 행이 들어가는 만큼 리프가 약 160 → 260블록으로 커진다. 'GUEST'로 채우자는 제안은 필요 없다. customer_id는 고객을 가리키는 값인데 고객 테이블에 없는 값이 생기고, 회원 수나 회원별 집계 SQL이 모두 'GUEST'를 빼야 한다. 단일 인덱스에 NULL이 없다는 문제는 모델을 바꿀 일이 아니라 결합 인덱스로 풀 문제다.

해설

(view_date, customer_id)도 이 차트의 답이 된다. 30일 치 항목 8,220개를 읽으며 customer_id IS NULL을 인덱스 안에서 거르므로 약 25블록이다. 다만 회원 화면(customer_id = :c)은 선두 컬럼이 없어 받치지 못하므로 인덱스가 하나 더 는다.
(customer_id, 0)도 비회원 행을 담는다. 그러나 날짜가 access가 되지 않아 1년 치 비회원 약 31,000건을 다 읽고, 날짜를 거르려 테이블에 간다.
IDX-004의 (PAY_DT, 0)은 상수를 붙여 NULL 행을 담았다. 이 문제처럼 SQL이 함께 쓰는 컬럼이 있으면 상수 대신 그 컬럼을 붙여야 같은 인덱스가 NULL 행을 담으면서 범위까지 받친다.
반영할 때는 새 인덱스를 INVISIBLE로 만들어 세션에서 두 화면을 잰 뒤 바꾼다(교재 04화 §8).

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황