문서유형ㅣ기술정보
분야ㅣ App개발
적용제품버전ㅣ Tibero 6F
문서번호ㅣTDETI009
개요
예시와 함께 Text index 중 하나인 CTXCAT INDEX의 활용을 안내합니다.
방법
1. Test Table 생성 및 Data Insert
SQL> CREATE TABLE BOOK(ID NUMBER, NAME VARCHAR(4000)); Table 'BOOK' created. SQL> INSERT INTO BOOK VALUES(1,'The little boat.'); SQL> INSERT INTO BOOK VALUES(2,'The little yellow digger.'); SQL> INSERT INTO BOOK VALUES(3,'The magic pasta pot : an old tale.'); SQL> INSERT INTO BOOK VALUES(4,'The man who was thursday.'); SQL> INSERT INTO BOOK VALUES(5,'The pear in the pear tree.'); SQL> INSERT INTO BOOK VALUES(6,'The rainbow fish.'); SQL> INSERT INTO BOOK VALUES(7,'The story about Ping.'); SQL> COMMIT; Commit completed.
2. CTXCAT Index 생성
SQL> CREATE INDEX CTXCATINDEX_BOOK ON BOOK(NAME) CTXCATINDEX; Index 'CTXCATINDEX_BOOK' created.
3. TEXT 조회
CATSEARCH() 이용 text를 조회합니다.
SQL> SELECT * FROM BOOK WHERE CATSEARCH(name, 'litt*', NULL) = 0 ORDER BY ID; ID NAME ---------- ---------------------------------------- 1 The little boat. 2 The little yellow digger. 8 The little prince. 3 rows selected.
참고
CTXCAT Index 는 별도의 동기화 과정없이, 신규로 Insert된 데이터 조회가 가능합니다.-- data 추가 Insert SQL> INSERT INTO BOOK VALUES(9,'the little mermaid.'); 1 row inserted. SQL> COMMIT; Commit completed. -- 새로 Insert된 data 조회 SQL> SELECT * FROM BOOK WHERE CATSEARCH(name, 'litt*', NULL) = 0 ORDER BY ID; ID NAME ---------- ---------------------------------------- 1 The little boat. 2 The little yellow digger. 8 The little prince. 9 the little mermaid. 4 rows selected.