개요
예시와 함께 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.
SQL
복사
2. CTXCAT Index 생성
SQL> CREATE INDEX CTXCATINDEX_BOOK ON BOOK(NAME) CTXCATINDEX;
Index 'CTXCATINDEX_BOOK' created.
SQL
복사
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.
SQL
복사
참고
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.
SQL
복사