Skip to main content

Search

Welcome to Tibero GTS!

Applying Text Index (CTXSYS.CONTEXT)

Document Type | Technical Information

Category | App Development

Applicable Product Version | Tibero 6F

Document Number | TDETI008

 

Overview

This guide explains the use of one of the text indexes, CTXSYS.CONTEXT, 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 CTXSYS.CONTEXT Index

SQL> CREATE INDEX CONTEXT_IDX_BOOK ON BOOK(name) INDEXTYPE IS CTXSYS.CONTEXT;
Index 'CONTEXT_IDX_BOOK' created.

 

3. TEXT Query

Use CONTAINS() to query text.

SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'little') > 0 ORDER BY ID;

ID NAME
---------- ----------------------------------------
1 The little boat.
2 The little yellow digger.
2 rows selected.

2 rows selected.

 

Limitations (Query Not Possible)

  • Partial word search
    You must enter the entire word to query.

-- Example: Querying 'little' partial spelling input is not possible
SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'lit') > 0 ORDER BY ID;
0 row selected.

 

  • Common English words 
    Common English words such as articles and forms of the verb "to be" are not searchable.

SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'The') > 0 ORDER BY ID;
0 row selected.

SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'was') > 0 ORDER BY ID;
0 row selected.

 

  • Two-letter words
    Words composed of two letters such as "in" and "an" are not searchable.

SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'in') > 0 ORDER BY ID;
0 row selected.

SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'an') > 0 ORDER BY ID;
0 row selected.

 

  • Newly inserted data

-- Additional data insert
SQL> INSERT INTO BOOK VALUES(8,'The little prince.');
1 row inserted.

SQL> COMMIT;
Commit completed.



-- Data query 
SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'little') > 0 ORDER BY ID;

ID NAME
---------- ----------------------------------------
1 The little boat.
2 The little yellow digger.
2 rows selected.
-- Newly inserted data is not searchable
Note
To query newly inserted data as well, synchronize the index using CTX_DDL.SYNC_INDEX().
SQL> EXEC CTX_DDL.SYNC_INDEX('CONTEXT_IDX_BOOK');
PSM completed.

-- Query newly inserted data 

SQL> SELECT * FROM BOOK WHERE CONTAINS(name, 'little') > 0 ORDER BY ID;
ID NAME
---------- ----------------------------------------
1 The little boat.
2 The little yellow digger.
8 The little prince.
3 rows selected.

 

 

 

 

Related to