앞% LIKE 튜닝 - 편집 역사

2026년 3월 13일 (금) 02:02에 Oracle님의 편집

2026-03-13T02:02:21Z

← 이전 판		2026년 3월 13일 (금) 11:02 판
4번째 줄:		4번째 줄:
	{{요점		{{요점
	\|내용=		\|내용=
			# Oracle Text
	# Oracle Text ~~(CTXSYS.CONTEXT)~~
	#: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식		#: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식
	# Parallel Query		# Parallel Query
19번째 줄:		18번째 줄:
	# 단순히 인덱스를 생성하는 것을 넘어, **실제 운영 환경에서 성능과 데이터 정합성을 모두 잡기 위해 반드시 설정해야 할 사항 정리		# 단순히 인덱스를 생성하는 것을 넘어, **실제 운영 환경에서 성능과 데이터 정합성을 모두 잡기 위해 반드시 설정해야 할 사항 정리

	----		---
	=== 데이터 클린징 ===		=== 데이터 클린징 ===
	=== 정규표현식을 사용한 Function Based Index 생성 ===		=== 정규표현식을 사용한 Function Based Index 생성 ===
43번째 줄:		42번째 줄:
	* SYNC (ON COMMIT): 데이터 변경이 잦다면 이 옵션을 넣어야 조회가 실시간으로 반영됩니다. 만약 쓰기 작업이 너무 많아 성능 저하가 우려된다면 `SYNC (EVERY "FREQ=MINUTELY;INTERVAL=5")` 처럼 주기적 동기화 방식을 쓸 수도 있습니다.		* SYNC (ON COMMIT): 데이터 변경이 잦다면 이 옵션을 넣어야 조회가 실시간으로 반영됩니다. 만약 쓰기 작업이 너무 많아 성능 저하가 우려된다면 `SYNC (EVERY "FREQ=MINUTELY;INTERVAL=5")` 처럼 주기적 동기화 방식을 쓸 수도 있습니다.

	----		---

	### 2. 검색 패턴별 쿼리 작성법		### 2. 검색 패턴별 쿼리 작성법
64번째 줄:		63번째 줄:
	주의: Oracle Text에서 특정 특수문자는 예약어이므로 중괄호(`{ }`)로 감싸야 정확히 검색됩니다.		주의: Oracle Text에서 특정 특수문자는 예약어이므로 중괄호(`{ }`)로 감싸야 정확히 검색됩니다.

	----		---

	### 3. 운영 시 고려해야 할 리스크 관리		### 3. 운영 시 고려해야 할 리스크 관리
82번째 줄:		81번째 줄:
	너무 짧은 단어(예: 한 글자) 검색이 잦다면 인덱스 설정 시 `BASIC_LEXER`에서 별도의 처리가 필요할 수 있습니다.		너무 짧은 단어(예: 한 글자) 검색이 잦다면 인덱스 설정 시 `BASIC_LEXER`에서 별도의 처리가 필요할 수 있습니다.

	----		---

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

2026년 3월 13일 (금) 02:01에 Oracle님의 편집

2026-03-13T02:01:32Z

← 이전 판		2026년 3월 13일 (금) 11:01 판
3번째 줄:		3번째 줄:

	{{요점		{{요점
	\|내용= ~~앞% LIKE 튜닝 방안~~		\|내용=
	~~# B-Tree Index는 LIKE '%~' 사용시 인덱스 사용할수 없음.~~
	~~#: :LIKE '%...' 형태에서는 사실상 무용지물~~		# Oracle Text (CTXSYS.CONTEXT)
	~~# REGEXP_SUBSTR + FUNCTION BASED INDEX를 이용한 인덱싱 처리~~
	# Oracle Text (CTXSYS.CONTEXT) 이용
	#: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식		#: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식
	# Parallel Query		# Parallel Query
	#: :높음낮음인덱스 없이 하드웨어 자원으로 해결		#: :높음낮음인덱스 없이 하드웨어 자원으로 해결
			# B-Tree Index는 LIKE '%~' 사용시 인덱스 사용할수 없음.
			#: :LIKE '%...' 형태에서는 사실상 무용지물
	}}		}}

Oracle: 새 문서: == 앞% LIKE 튜닝 방안 == {{요점 |내용= 앞% LIKE 튜닝 방안 # B-Tree Index는 LIKE '%~' 사용시 인덱스 사용할수 없음. #: :LIKE '%...' 형태에서는 사실상 무용지물 # REGEXP_SUBSTR + FUNCTION BASED INDEX를 이용한 인덱싱 처리 # Oracle Text (CTXSYS.CONTEXT) 이용 #: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식 # Parallel Query #: :높음낮음인덱스 없이 하드웨어 자원으로 해결 }} * 성...

2026-03-13T02:01:08Z

새 문서: == 앞% LIKE 튜닝 방안 == {{요점 |내용= 앞% LIKE 튜닝 방안 # B-Tree Index는 LIKE '%~' 사용시 인덱스 사용할수 없음. #: :LIKE '%...' 형태에서는 사실상 무용지물 # REGEXP_SUBSTR + FUNCTION BASED INDEX를 이용한 인덱싱 처리 # Oracle Text (CTXSYS.CONTEXT) 이용 #: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식 # Parallel Query #: :높음낮음인덱스 없이 하드웨어 자원으로 해결 }} * 성...

새 문서

== 앞% LIKE 튜닝 방안 ==

{{요점
|내용= 앞% LIKE 튜닝 방안
# B-Tree Index는 LIKE '%~' 사용시 인덱스 사용할수 없음.
#: :LIKE '%...' 형태에서는 사실상 무용지물
# REGEXP_SUBSTR + FUNCTION BASED INDEX를 이용한 인덱싱 처리
# Oracle Text (CTXSYS.CONTEXT) 이용
#: :매우 높음보통대량의 텍스트 검색에 최적화된 표준 방식
# Parallel Query
#: :높음낮음인덱스 없이 하드웨어 자원으로 해결

}}

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

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

----
=== 데이터 클린징 ===
=== 정규표현식을 사용한 Function Based Index 생성 ===

=== Oracle Text 인덱스 생성 및 최적화 전략 ===
* 일반 B-Tree 인덱스와 달리 Context 인덱스는 생성 시 몇 가지 파라미터를 지정하는 것이 중요합니다.
*:<source lang=sql>
-- 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;

</source>

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

----

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

`LIKE`와 `CONTAINS`는 문법이 조금 다릅니다. 사용자의 검색 요구사항에 맞춰 아래와 같이 대응하세요.
{| class="wikitable"
|+ 검색 패턴별 쿼리 작성법
|-
! 검색 요구사항 !! 기존 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에서 특정 특수문자는 예약어이므로 중괄호(`{ }`)로 감싸야 정확히 검색됩니다.

----

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

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

1. **인덱스 단편화 (Fragmentation):**
데이터 변경(DML)이 잦으면 인덱스가 조각납니다. 성능 유지를 위해 주기적으로 최적화가 필요합니다.
<source lang=sql>
EXEC CTX_DDL.OPTIMIZE_INDEX('idx_tb_name_text', 'FULL');
</source>

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

----

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

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

<source lang=sql>
-- 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)');

</source>

----

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

* 인덱스를 생성하기 전에, 현재 해당 컬럼(`name`)의 데이터 중 가장 긴 문자열의 길이나 평균적인 데이터 형태를 알면
그에 맞춰 `LEXER` 설정을 더 정교하게 다듬을수 있음