Oracle Text (오라클 텍스트) - "like" SQL문 빠르게 하는 방법

SQL문 만들때 like 연산을 많이 사용합니다. 데이터가 정확히 일치하지 않아서 like '%...%' 형태로 조회해야 하는 경우가 많기 때문이죠~

 

하지만, 이 like SQL문에서 검색문자열 앞에 % 가 있는 경우에는 인덱스를 타지 않거나, Index Fast Full Scan 을 하게 됩니다. 인덱스를 통째로 읽어서 찾는 겁니다. Tabel Full Scan 과 비슷합니다. 따라서 많이 느리겠죠~~

  ex) where A like '%노을'

 

이런 경우에, 빠르게 할 수 있는 방법이 있습니다.

바로 "Oracle Text" 라는 기능입니다. 예전에는 Intermedia Text 라고 불렸었습니다.

또는 Domain Index 라고 부르는 사람도 있고, Text Index 라고 부르는 사람도 있습니다. 모두 같은 겁니다.^^

 

이 기능을 사용하게 되면 내부적으로 키워드 딕셔너리를 구성합니다. 그래서 키워드 테이블에서 검색해오기 때문에 매우 빠릅니다.

하지만, 사용자는 마치 일반 인덱스(Index) 만들듯이 만들면 되고, 모든 필요한 작업들은 Oracle DBMS 가 알아서 자동으로 해 줍니다.

 

11g 이상의 DB에 보면 Oracle Text 가 디폴트로 설치되어 있습니다. 그래서 별도로 설치할 필요가 없습니다.

 

 

위와 같이 조회해봐서 STATUS 가 VALID 로 나오면 사용할 수 있습니다.

DBA_USERS 딕셔너리를 조회해보면 CTXSYS 라는 DB 유저가 보이는데, 이게 이 Oracle Text 와 관련된 기능을 수행하기 위한 DB 유저입니다.

 

 

그럼, 한번 Oracle Text 기능이 얼마나 빠른지 확인해 볼까요??

 

먼저, 아래와 같이 테스트를 위한 테이블과 데이터를 생성합니다.

 

-- 테스트 테이블 T1 생성
create table T1 (
  line  number,
  text  varchar2(4000)
);

-- 테스트 데이터 입력
insert into T1 values ( 1, '붉게 물든 노을 바라보면 슬픈 그대 얼굴 생각이나');
insert into T1 values ( 2, '고개 숙이네 눈물 흘러 아무 말 할 수가 없지만');
insert into T1 values ( 3, '난 너를 사랑하네 이 세상은 너 뿐이야 소리쳐 부르지만');
insert into T1 values ( 4, '저 대답 없는 노을만 붉게 타는데');
insert into T1 values ( 5, '그 세월 속에 잊어야할 기억들이 다시 생각나면');
insert into T1 values ( 6, '눈감아요 소리 없이 그 이름 불러요');
insert into T1 values ( 7, '아름다웠던 그대모습 다시 볼 수 없는 것 알아요');
insert into T1 values ( 8, '후회 없어 저 타는 노을 붉은 노을처럼');
insert into T1 values ( 9, '난 너를 사랑하네 이 세상은 너 뿐이야 소리쳐 부르지만');
insert into T1 values (10, '저 대답 없는 노을만 붉게 타는데');
insert into T1 values (11, '어디로 갔을까 사랑하던 슬픈 그대 얼굴 보고싶어');
insert into T1 values (12, '깊은 사랑 후회 없어 저 타는 붉은 노을처럼');
insert into T1 values (13, '난 너를 사랑하네 이 세상은 너 뿐이야 소리쳐 부르지만');
insert into T1 values (14, '저 대답 없는 노을만 붉게 타는데');
insert into T1 values (15, '그 세월 속에 잊어야할 기억들이 다시 생각나면 눈감아요…');

insert /*+ parallel(4) */ into T1 select * from T1;
   --> 데이터 증식을 위해 이거 20번 반복 (15,728,640 건)


-- 테스트 테이블 T2 생성
create table T2 as select * from T1;

 

좀더, 극적인 연출을 위해^^ 데이터를 좀 크게 증식해서 만들었습니다. 약 1천5백만건. 너무 큰가 ^^;

 

성능 비교 검증을 위해 T2 테이블을 생성합니다.

T2 테이블은 T1 테이블을 복제해서 똑같이 만들어 줬습니다.

 

 

T1, T2 테이블에 인덱스를 만들어줍니다.

 

-- 테이블 T1 에 인덱스 생성 (Oracle Text 기능 사용)
create index T1_IDX1 on T1(text) indextype is ctxsys.context;

-- 테이블 T2 에 인덱스 생성 (일반 인덱스)
create index T2_IDX1 on T2(text);

 

T1 테이블에는 Oracle Text 인덱스를 T2 테이블에는 일반 인덱스를 생성합니다.

데이터가 많으니 인덱스 생성 작업도 좀 오래 걸립니다. 빠르게 하려면 둘다 뒤에 parallel(10) 와 같이 병렬처리 옵션을 커맨드 맨뒤에 붙여주기만 하면 됩니다. 

 

 

자~ 이제 준비끝~~~

함 테스트 해볼까요???

 

-- 일반 like 문으로 조회
select count(*) from T2 where text like '%아름다웠%';

-- Oracle Text 방식으로 조회
select count(*) from T1 where CONTAINS (text, '%아름다웠%') > 0;

 

Oracle Text 에서는 contains( ) 라는 함수를 사용하는게 좀 다릅니다. 하지만, 키워드 앞뒤로 % 붙이는 것은 같습니다.

 

So, 결과는???

 

 

와우, Oracle Text 의 경우는 0.28초, 일반 Index 의 경우는 3.61초 걸렸네요.

12배 정도 차이가 나네요^^   와우~ 쓸만한데요~~

((공정한 테스트를 위해 둘다 여러번 실행해서 버퍼캐시에 캐싱되도록 한 결과입니다.))

 

 

혹, Index 생성/삭제 중에 에러가 발생하면 아래 참조하세요.

>> Oracle Text 인덱스 생성/삭제시 Error ORA-29855, ORA-20000, DRG-50857, DRG-10502, DRG-10507

 

이것도 참고요

>> Oracle Text Index 관리할때 주의사항