앞% LIKE 튜닝

앞% LIKE 튜닝 방안

menu_book # Oracle Text (CTXSYS.CONTEXT)

1. :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식

2. Parallel Query

   :높음낮음인덱스 없이 하드웨어 자원으로 해결

3. B-Tree Index는 LIKE '%~' 사용시 인덱스 사용할수 없음.

   :LIKE '%...' 형태에서는 사실상 무용지물

• 성능 저하 테이블 정보
  • (대용량) 5천만 건의 데이터와 하루 1만 건의 입력,수정 발생

1. Context Index 도입이 성능 문제를 해결할 가장 현실적이고 강력한 방법
2. 단순히 인덱스를 생성하는 것을 넘어, **실제 운영 환경에서 성능과 데이터 정합성을 모두 잡기 위해 반드시 설정해야 할 사항 정리

---

Oracle Text 인덱스 생성 및 최적화 전략

• 일반 B-Tree 인덱스와 달리 Context 인덱스는 생성 시 몇 가지 파라미터를 지정하는 것이 중요합니다.

  -- 1. 인덱스 생성 (대용량 테이블은  병렬 처리 권장)
  -- SYNC (ON COMMIT): 데이터가 INSERT/UPDATE 될 때 인덱스에 즉시 반영
  CREATE INDEX idx_tb_name_text ON tb_name(name) 
  INDEXTYPE IS CTXSYS.CONTEXT 
  PARAMETERS ('SYNC (ON COMMIT) MEMORY 500M'); 
  
  -- 2. 쿼리 실행 (반드시 CONTAINS 함수 사용)
  SELECT name 
  FROM tb_name 
  WHERE CONTAINS(name, 'test') > 0;

• **MEMORY 500M:** 인덱스 생성 시 사용할 메모리 크기입니다. 5천만 건이라면 기본값보다 크게 잡아야 생성 속도가 빠릅니다.
• **SYNC (ON COMMIT):** 데이터 변경이 잦다면 이 옵션을 넣어야 조회가 실시간으로 반영됩니다. 만약 쓰기 작업이 너무 많아 성능 저하가 우려된다면 `SYNC (EVERY "FREQ=MINUTELY;INTERVAL=5")` 처럼 주기적 동기화 방식을 쓸 수도 있습니다.

---

1. 1. 1. 2. 검색 패턴별 쿼리 작성법

`LIKE`와 `CONTAINS`는 문법이 조금 다릅니다. 사용자의 검색 요구사항에 맞춰 아래와 같이 대응하세요.

검색 패턴별 쿼리 작성법

검색 요구사항	기존 LIKE (Full Scan)		Oracle Text (Index Scan)
**단어 포함**	`LIKE '%test%'`	`CONTAINS(name, 'test') > 0`
**여러 단어 포함 (AND)**	`LIKE '%test%' AND LIKE '%kim%'`	`CONTAINS(name, 'test AND kim') > 0`
**여러 단어 중 하나 (OR)**	`LIKE '%test%' OR LIKE '%kim%'`	`CONTAINS(name, 'test OR kim') > 0`
**특수문자 포함**	`LIKE '%=3=3=3=%'`	`CONTAINS(name, '\{=3=3=3=\}') > 0`

• • 주의:** Oracle Text에서 특정 특수문자는 예약어이므로 중괄호(`{ }`)로 감싸야 정확히 검색됩니다.

---

1. 1. 1. 3. 운영 시 고려해야 할 리스크 관리

5천만 건 규모에서 Context 인덱스를 운영할 때 놓치지 말아야 할 포인트입니다.

1. **인덱스 단편화 (Fragmentation):** 데이터 변경(DML)이 잦으면 인덱스가 조각납니다. 성능 유지를 위해 주기적으로 최적화가 필요합니다.

EXEC CTX_DDL.OPTIMIZE_INDEX('idx_tb_name_text', 'FULL');

2. **저장 공간 (Storage):** 이 인덱스는 내부적으로 `$I`, `$R` 등의 보조 테이블을 생성합니다. 원본 데이터 크기의 약 10~20% 정도 추가 저장 공간을 확보해 두세요. 3. **검색어 길이:** 너무 짧은 단어(예: 한 글자) 검색이 잦다면 인덱스 설정 시 `BASIC_LEXER`에서 별도의 처리가 필요할 수 있습니다.

---

1. 1. 1. 4. 만약 '부분 일치'가 더 까다롭다면?

만약 `test`가 아니라 `est`처럼 단어의 중간 일부만 검색해야 하는 상황이 많다면, 인덱스 생성 시 **`SUBSTRING_INDEX`** 옵션을 활성화해야 합니다.

-- Wordlist 설정 (중간 문자열 검색 최적화)
BEGIN
  CTX_DDL.CREATE_PREFERENCE('my_wordlist', 'BASIC_WORDLIST');
  CTX_DDL.SET_ATTRIBUTE('my_wordlist', 'SUBSTRING_INDEX', 'YES');
END;
/

-- 인덱스 생성 시 적용
CREATE INDEX idx_tb_name_text ON tb_name(name) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS ('WORDLIST my_wordlist SYNC (ON COMMIT)');

---

이 방법은 현재 상황에서 **Full Table Scan 비용을 획기적으로 줄일 수 있는 마법 같은 해결책**이 될 것입니다.

• 인덱스를 생성하기 전에, 현재 해당 컬럼(`name`)의 데이터 중 가장 긴 문자열의 길이나 평균적인 데이터 형태를 알면

그에 맞춰 `LEXER` 설정을 더 정교하게 다듬을수 있음