상담원이 리뷰에서 「환불」을 찾을 때마다 리뷰 1만 5천 건을 다 읽는다

실전SELECT이커머스46 / 700

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

상황

이커머스 고객센터와 MD팀은 「리뷰 키워드 검색」 화면으로 불만 리뷰를 찾는다(전체 리뷰 검색, 하루 약 400번). 고객용 상품 상세 페이지의 「리뷰 내 검색」도 같은 모양의 SQL에 상품 번호를 더해 쓴다(하루 약 3만 번). 개발자는 "LIKE 앞뒤에 %가 있어서 어떤 인덱스로도 안 된다. 검색 엔진을 들이든지 리뷰 검색을 빼자"고 한다. 박팀장은 두 화면을 나눠 각각 무엇으로 받칠지 대가까지 정리해 오라고 했다.

테이블과 인덱스

reviews: 15,000행 · 775블록 · PK만 있다 · product_id(상품 280개, 상품당 34 ~ 76건) · customer_id · rating 1 ~ 5 · content CLOB(평균 약 600바이트, 행 안에 저장) · created_at DATE
리뷰는 하루 약 40건씩 는다. 상담원 화면은 전날까지 쓴 리뷰가 검색되면 된다(현업 확인)
통계 최신

원본 SQL

sql

-- S1 고객센터 · MD 「리뷰 키워드 검색」 (전체)
SELECT review_id, product_id, rating, created_at
  FROM reviews
 WHERE content LIKE '%' || :v_kw || '%'
 ORDER BY created_at DESC;

-- S2 상품 상세 「리뷰 내 검색」
SELECT review_id, rating, created_at
  FROM reviews
 WHERE product_id = :v_prod
   AND content LIKE '%' || :v_kw || '%'
 ORDER BY created_at DESC;

실행계획 학습용 예시

xplan

[S1 · '환불']
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |    212 |00:00:00.19 |     775 |
|   1 |  SORT ORDER BY     |         |      1 |    750 |    212 |00:00:00.19 |     775 |
|*  2 |   TABLE ACCESS FULL| REVIEWS |      1 |    750 |    212 |00:00:00.19 |     775 |
----------------------------------------------------------------------------------------
   2 - filter("CONTENT" LIKE '%'||:V_KW||'%')

[S2 · 상품 117 · '사이즈']
----------------------------------------------------------------------------------------
| Id  | Operation          | Name    | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |      1 |        |      6 |00:00:00.17 |     775 |
|   1 |  SORT ORDER BY     |         |      1 |      3 |      6 |00:00:00.17 |     775 |
|*  2 |   TABLE ACCESS FULL| REVIEWS |      1 |      3 |      6 |00:00:00.17 |     775 |
----------------------------------------------------------------------------------------
   2 - filter(("PRODUCT_ID"=:V_PROD AND "CONTENT" LIKE '%'||:V_KW||'%'))

문제

S1과 S2가 똑같이 FULL이지만 고칠 길이 다른 이유를 설명하시오.
S2를 B-tree 인덱스로 받치는 DDL을 작성하시오.
S1을 받칠 방법(Oracle Text 인덱스와 CONTAINS, 외부 검색 엔진, 요구 조정)을 비교하고, 고른 방법의 SQL과 대가를 설명하시오.

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

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

모범답안

모범 답안 SQL

sql

-- S2 : 상품으로 좁히는 B-tree. 작성일까지 담아 역순으로 읽으면 정렬도 없다. LIKE 는 그대로 filter
CREATE INDEX IX_REVIEWS_PROD_DT ON reviews (product_id, created_at);

SELECT review_id, rating, created_at
  FROM reviews
 WHERE product_id = :v_prod
   AND content LIKE '%' || :v_kw || '%'
 ORDER BY created_at DESC;

-- S1 : 전문 검색 인덱스. 한국어 렉서를 쓰고, 상담원 화면은 전날까지면 되므로 새벽에 한 번 동기화한다
BEGIN
  CTX_DDL.CREATE_PREFERENCE('ko_lexer', 'KOREAN_MORPH_LEXER');
END;
/
CREATE INDEX IX_REVIEWS_TXT ON reviews (content)
  INDEXTYPE IS CTXSYS.CONTEXT
  PARAMETERS ('LEXER ko_lexer SYNC (EVERY "FREQ=DAILY; BYHOUR=3")');

SELECT review_id, product_id, rating, created_at
  FROM reviews
 WHERE CONTAINS(content, :v_kw) > 0
 ORDER BY created_at DESC;

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

xplan

[S2 · 고친 뒤 · 상품 117 · '사이즈']
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |      1 |        |      6 |00:00:00.01 |      56 |
|*  1 |  TABLE ACCESS BY INDEX ROWID | REVIEWS            |      1 |      3 |      6 |00:00:00.01 |      56 |
|*  2 |   INDEX RANGE SCAN DESCENDING| IX_REVIEWS_PROD_DT |      1 |     54 |     54 |00:00:00.01 |       2 |
---------------------------------------------------------------------------------------------------------
   1 - filter("CONTENT" LIKE '%'||:V_KW||'%')
   2 - access("PRODUCT_ID"=:V_PROD)

[S1 · 고친 뒤 · '환불']
---------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                    |      1 |        |    212 |00:00:00.02 |     241 |
|   1 |  SORT ORDER BY               |                    |      1 |    150 |    212 |00:00:00.02 |     241 |
|   2 |   TABLE ACCESS BY INDEX ROWID| REVIEWS            |      1 |    150 |    212 |00:00:00.02 |     241 |
|*  3 |    DOMAIN INDEX              | IX_REVIEWS_TXT     |      1 |        |    212 |00:00:00.01 |      29 |
---------------------------------------------------------------------------------------------------------
   3 - access("CTXSYS"."CONTAINS"("CONTENT",:V_KW)>0)

모범 답안 서술

두 SQL은 똑같이 FULL이지만 사정이 다르다. S2에는 product_id = :v_prod라는, 범위를 좁혀 주는 조건이 있다. 지금은 product_id에 인덱스가 없어서 FULL이 되었을 뿐이다. 상품 하나의 리뷰는 34 ~ 76건(평균 54건)이므로 product_id로 찾아가면 앞뒤 % LIKE는 그 54건만 따져 보는 filter로 충분하다. (product_id, created_at) 인덱스를 만들면 역순으로 읽어 정렬도 없어지고, Buffers는 775 → 56, 시간은 0.17초 → 0.01초가 된다. 하루 3만 번이므로 이쪽이 훨씬 급하다. S1에는 LIKE 말고 조건이 없다. 앞뒤가 다 %인 조건은 B-tree를 정순으로 정렬하든 역순으로 정렬하든 시작점이 없으므로, 어떤 B-tree로도 범위가 되지 않는다. 글 속의 단어를 찾으려면 단어마다 그 단어가 든 행을 적어 둔 인덱스(전문 검색 인덱스)가 있어야 한다. 세 방법을 비교한다. Oracle Text의 CONTEXT 인덱스와 CONTAINS는 DB 안에서 끝나고 규모에도 맞다. 단어 '환불'로 행 212개를 찾아가므로 Buffers는 775 → 241이고, 무엇보다 리뷰가 늘어도 일량은 맞은 건수만큼만 는다. 대가는 셋이다. 첫째, 결과가 LIKE와 다를 수 있다. CONTAINS는 렉서가 나눈 단어(토큰)를 찾으므로, 한국어 렉서가 '환불이', '환불은'의 조사를 떼어 주는지, '미환불'처럼 붙은 말을 찾을지 현업과 결과를 맞춰 봐야 한다. 둘째, 동기화 주기가 있다. ON COMMIT은 리뷰를 쓸 때마다 텍스트 인덱스를 고쳐 쓰기가 느려지고 조각이 늘어 정기 최적화가 필요하다. 상담원 화면은 전날까지면 되므로 새벽에 한 번 동기화한다. 셋째, 텍스트 인덱스 테이블($I 등)만큼 저장 공간이 든다. 외부 검색 엔진은 순위 매기기나 오타 교정이 필요할 때 쓸 도구다. 1만 5천 건 규모에는 과하고, 데이터를 보내는 연계와 시스템 하나를 더 운영하는 비용이 크다. 요구 조정(최근 3개월만 검색)은 FULL을 조금 줄일 뿐 앞뒤 %의 문제는 그대로 남는다. S2는 B-tree로, S1은 Oracle Text로 받친다. 리뷰 검색을 빼자는 제안은 필요 없다.

해설

함정: S1을 CONTAINS로 바꾸고 S2도 따라 바꾸면, 상품 상세 페이지에 동기화 전 리뷰가 검색되지 않는 불만이 생긴다. S2는 B-tree와 LIKE로 두면 방금 쓴 리뷰도 바로 찾는다.
앞 %만 있는 조건(뒷자리 검색)은 역순 인덱스나 뒷자리 컬럼으로 풀 수 있지만(IDX-045), 앞뒤가 다 %인 조건은 B-tree로 풀 길이 없다. 다른 조건으로 좁히거나(S2), 단어 인덱스를 쓴다(S1).
지금은 FULL도 775블록, 0.2초다. 리뷰가 100만 건이 되면 FULL은 5만 블록대로 늘고 CLOB을 행마다 훑는 CPU도 그만큼 늘지만, CONTAINS의 일량은 맞은 건수만큼만 는다. 설계는 지금 정해 두는 것이 싸다.
CONTAINS(content, :v_kw)에 사용자가 넣은 말을 그대로 넘기면 AND, OR, % 같은 Oracle Text 연산자로 해석될 수 있다. 입력은 중괄호로 감싸 글자 그대로 찾게 한다('{' || :v_kw || '}').

상황 ​

테이블과 인덱스 ​

원본 SQL ​

실행계획 학습용 예시 ​

문제 ​

모범 답안 SQL ​

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

모범 답안 서술 ​

해설 ​

관련 교재 ​

상황