Overview

Context Index is an index organized to easily find information about a specific context or topic. It is used to enhance the search functionality of documents or databases.

A usage example of Context Index has been prepared regarding this.

Test Environment

• DB: Tibero 7 (DB 7.2.4)
• OS: Rocky Linux 9.6

Method

Example Scenario

1. CTXSYS INDEX

# Data input
CREATE TABLE BOOK(ID NUMBER, NAME VARCHAR(4000));
INSERT INTO BOOK VALUES(2,'The little yellow digger.');
INSERT INTO BOOK VALUES(3,'The magic pasta pot : an old tale.');
INSERT INTO BOOK VALUES(4,'The man who was thursday.');
INSERT INTO BOOK VALUES(5,'The pear in the pear tree.');
INSERT INTO BOOK VALUES(6,'The rainbow fish.');
INSERT INTO BOOK VALUES(7,'The story about Ping.');
commit;

# Create CTXSYS Index
CREATE INDEX CONTEXT_IDX_BOOK ON BOOK(name) INDEXTYPE IS CTXSYS.CONTEXT;

# When querying data containing little, 2 rows are retrieved
SELECT * FROM BOOK WHERE CONTAINS(name, 'little') > 0 ORDER BY ID;
   
   ID NAME
----- ----------------------------------------
    1 The little boat.
    2 The little yellow digger.

# No results when searching by part of a word
SELECT * FROM BOOK WHERE CONTAINS(name, 'lit') > 0 ORDER BY ID;
0 row selected.

# When using Text Index, words like The, who, in are not retrieved
SELECT * FROM BOOK WHERE CONTAINS(name, 'The') > 0 ORDER BY ID;
0 row selected.

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

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

# Add data and synchronize (CTXSYS INDEX requires synchronization after data addition)
INSERT INTO BOOK VALUES(8,'The little prince.');
EXEC CTX_DDL.SYNC_INDEX('CONTEXT_IDX_BOOK');

You must search for the entire word, and words such as the, in, on, for, who, is, of ... are included in the STOP LIST and will not be retrieved.

2. CTXCAT INDEX

# Data input
CREATE TABLE BOOK(ID NUMBER, NAME VARCHAR(4000));
INSERT INTO BOOK VALUES(2,'The little yellow digger.');
INSERT INTO BOOK VALUES(3,'The magic pasta pot : an old tale.');
INSERT INTO BOOK VALUES(4,'The man who was thursday.');
INSERT INTO BOOK VALUES(5,'The pear in the pear tree.');
INSERT INTO BOOK VALUES(6,'The rainbow fish.');
INSERT INTO BOOK VALUES(7,'The story about Ping.');

# Create CTXCAT INDEX
CREATE INDEX CTXCATINDEX_BOOK ON BOOK(NAME) CTXCATINDEX;

# Search data starting with litt
SELECT * FROM BOOK WHERE CATSEARCH(name, 'litt*', NULL) = 0 ORDER BY ID;
   ID NAME
----- ----------------------------------------
    1 The little boat.
    2 The little yellow digger.

Comparison between CTXSYS INDEX and CTXCAT INDEX