Document Type | Technical Information
Category | App Development
Applicable Product Version | Tibero 6F
Document Number | TDETI009
Overview
This guide explains the use of one of the text indexes, CTXCAT INDEX, with examples.
Method
1. Create Test Table and Insert Data
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. Create CTXCAT Index
SQL> CREATE INDEX CTXCATINDEX_BOOK ON BOOK(NAME) CTXCATINDEX; Index 'CTXCATINDEX_BOOK' created.
3. TEXT Query
Query text using CATSEARCH().
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.
Note
The CTXCAT Index allows querying newly inserted data without a separate synchronization process.-- Insert additional data SQL> INSERT INTO BOOK VALUES(9,'the little mermaid.'); 1 row inserted. SQL> COMMIT; Commit completed. -- Query newly inserted 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.