오라클 LIKE 속도 - olakeul LIKE sogdo

Question

infoc ・ 2019. 10. 2. 10:21

회사애서 오라클 DB를 사용중인데 쿼리가 문제가 있었다.

like 검색을 했더니 100만건이 넘는 테이블을 full scan 하고 있었기 때문이다.

요 like 검색을 개선할 수 있는 방법을 좀 검색해보니 여러 방법이 있었고

like 검색의 특징을 좀 알 수 있었다.

like 검색은 텍스트를 뽑아서 해당 테이블의 컬럼에 특정 텍스트가 있느냐를 검색하는건데

% 라는 어떤 텍스트든 들어갈 수 있는 조건이 앞에 들어가면 인덱스를 타지 않는다.

https://theone79.tistory.com/900

그래서 like 조건으로 텍스트를 조회할 때는 'A%' 처럼 %가 뒤에 넣어야 된다.

1. 현상

: 리스트 로딩 시 검색어가 추가되면 속도가 많이 느렸다. 답답이....

검색어가 총 3개의 테이블을 검색하는 것이지만 너무 느린감이 있어서 튜닝에 나섰다.

2. 원인

: 처음 쿼리가 이랬다... SELECT 소요 시간은 2.17초 정도

	
    
    SELECT ... FROM 테이블1
    WHERE
    	INSTR(컬럼1_테이블1, '검색어') > 0
        OR PK_테이블1 IN (
        	SELECT FK_테이블2 FROM 테이블2 WHERE INSTR(컬럼2_테이블2, '검색어') > 0
            UNION
            	SELECT FK_테이블3 FROM 테이블3 WHERE INSTR(컬럼3_테이블3, '검색어') > 0
        )

- 검색어를 검색할 때 INSTR 함수를 사용했는데 이 부분을 LIKE 문으로 바꾸자 속도가 개선되었다.

검색어 검색 시에는 INSTR 보다 LIKE 문을 사용하는 게 낫다고 한다. (아래 사이트 참고)

http://database.sarang.net/?inc=read&aid=29513&criteria=mysql&subcrit=&id=24355&limit=20&keyword=lqez&page=16

http://www.gurubee.net/article/66827

- 그러나 더 큰 문제는 UNION이었다 ㅠㅠ

UNION을 사용하면 속도가 많이 떨어지기 때문에 UNION을 꼭 사용해야 하는 경우에는 필요한 최소 컬럼만 검색해야 한다고 한다. 되도록 사용하지 않는 것이 좋다고 한다.

(아래 사이트에 UNION과 UNION ALL에 대한 상세한 설명이 있다.)

http://intomysql.blogspot.com/2011/01/union-union-all.html

3. 개선된 쿼리

: SELECT 소요 시간이 1초 근방으로 바뀌었다.

	
    
    SELECT ... FROM 테이블1
    WHERE
    	컬럼1_테이블1 LIKE CONCAT('%', '검색어','%')
        OR PK_테이블1 IN (
        	SELECT FK_테이블2 FROM 테이블2 WHERE 컬럼2_테이블2 LIKE CONCAT('%', '검색어','%')
        ) OR PK_테이블1 IN (
            	SELECT FK_테이블3 FROM 테이블3 WHERE 컬럼3_테이블3 LIKE CONCAT('%', '검색어','%')
        )

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 관리할때 주의사항

SQL LIKE 속도 오라클 데이터 조회속도 오라클 LIKE 인덱스 Oracle contains 함수 Oracle regexp_like 속도 Oracle CONTAINS