oracle text

sql like 연산 사용시 index 활용

where ename like '%길동'; --index 타지 않음..

 

Oracle Text 사용 (Intermedia Text, Domain Index, Text Index)

1. CTXSYS유저 LOCK 해제 .

    SQL> alter user CTXSYS account unlock ;

 

 2. 쿼리를 수행할 유저에 CTXAPP 권한 부여

    SQL> grant CTXAPP to SCOTT ;

 

 3. Table 과 Oracle Text index 생성.

    SQL> create table emp_text

           ( emp_c1 number(15) primary key , 

            emp_c2 varchar2(1000) ) ;

 

    SQL> insert into emp_text 

            values(1,'오라클 텍스트 테스트') ;

    SQL> insert into emp_text

            values(2,'투씨에스지 블로그입니다') ;

    SQL> insert into emp_text

            values(3,'TOCSG DSC부 블로그입니다') ;

    SQL> insert into emp_text

            values(4,'CTXAPP 테스트 입니다') ; commit ;

 

    SQL> create index emp_text_idx on emp_text(emp_c2)

            indextype is ctxsys.context ;

 

 4. 쿼리수행 과 실행계획

 - emp_text 테이블에서 'TOCSG'라는 text를 찾을때

    SQL> select * from emp_text

             where contains(emp_c2, 'TOCSG') >0 ;

 

 - CTXSYS유저를 활성화 시켜 주면 검색어에 LIKE 대신 contains(컬럼,'검색할text') 를 사용할 수 있습니다.

   이는 원하는 내용필드를 가지고 검색어들을 Oracle text 에서 인덱싱 하도록 만들어 주게 되면

   like검색이 index scan 이 안먹힐때 index scan 이 가능하도록 해줍니다.

 

- 실행계획

Execution Plan
------------------------------------
Plan hash value: 2069932105

--------------------------------------------------------------------------------------------------

| Id  | Operation                   | Name         | Rows  | Bytes | Cost (%CPU) | Time     |

--------------------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |              |     1 |   527  |     4   (0)    | 00:00:01 |

|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_TEXT     |     1 |   527 |     4   (0)| 00:00:01 |

|*  2 |   DOMAIN INDEX              | EMP_TEXT_IDX |       |       |     4   (0)  | 00:00:01 |

--------------------------------------------------------------------------------------------------

출처: https://tocsg.tistory.com/82 [투씨에스지 기술 블로그]