Skip to main content

Search

Welcome to Tibero GTS!

Applying Text Index (CTXCAT INDEX)

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.
 
 
 
 
 
 

Related to