How to Search by Keyword with Oracle Text

Semantic search understands descriptions, but some questions are exact: a tax number such as GSTIN, a product of another company such as Intune, a header name such as Retry-After, or a version such as 8.4.1. An embedding model may never have learned these words, while a word-for-word search finds them at once.

This guide shows how to build keyword search with Oracle Text: create a text index, query it with CONTAINS and SCORE, turn a user's question into a safe text query, and see where keyword search beats semantic search.

Code for This Guide

The examples are files 05 to 08 in the examples/ch08 folder of the Oracle AI code repository on GitHub, each with its output.

These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.

They search the 24 knowledge base articles of the sample schema from setup/atlas. The schema user needs the CTXAPP role of Oracle Text. The comparison with semantic search uses the EVAL_QUESTIONS table from how to choose an embedding model.

Create a Text Index

Oracle Text is the database's full-text search. A text index lists every word of a text column and the rows it occurs in. It is a domain index of the type CTXSYS.CONTEXT, which indexes VARCHAR2, CLOB, and BLOB columns, including PDF and Word files.

Syntax:

create index index_name on table_name (text_column)
  indextype is ctxsys.context [ parameters ('...') ];

Example:

create index kb_articles_text on kb_articles (body)
  indextype is ctxsys.context;

Output:

Index KB_ARTICLES_TEXT created.

A text index is not updated by each DML statement the way a B-tree index is. New and changed rows are indexed when the index is synchronized, by default at the next CTX_DDL.SYNC_INDEX. For a table that changes a few rows at a time, add parameters ('sync (on commit)') to synchronize at every commit.

Query with CONTAINS

CONTAINS returns a number greater than 0 for each row whose text matches a query, and SCORE returns how well it matches. The query language has words, AND, OR, NOT, phrases, and operators such as $ for all forms of a word.

Syntax:

contains(text_column, 'query' [, label]) > 0
score(label)

Example:

select article_id, title from kb_articles where contains(body, 'refund') > 0;

select article_id, title from kb_articles where contains(body, '$refund') > 0;

select article_id, title, score(1) as score
from   kb_articles
where  contains(body, 'invoice and (duplicate or twice)', 1) > 0
order  by score desc;

Output:

no rows selected

ARTICLE_ID    TITLE
_____________ ________________________________
KB-201        Duplicate charges and refunds

ARTICLE_ID    TITLE                               SCORE
_____________ ________________________________ ________
KB-201        Duplicate charges and refunds          11

The first query finds nothing: the article says "refunds", and a text index matches words, not meanings or forms. $refund matches every form of the word and finds it. The score grows with how often the words occur, relative to the length of the text.

Turn a Question into a Text Query

Users type questions, not CONTAINS queries. TEXT_QUERY turns a question into a query that matches any of its words:

• Each word goes in braces, which make Oracle Text take it literally. "8.4.1" and "Retry-After" contain characters that are operators in the query language.
• Words are joined with ACCUM, which scores a row higher the more of the words it contains.
• Punctuation is removed, but not the dots inside a version number.

Example:

-- turns a question into an Oracle Text query: every word in braces, joined with ACCUM
create or replace function text_query (p_text in varchar2) return varchar2
deterministic
is
  l_words varchar2(4000);
begin
  l_words := regexp_replace(p_text, '[{}?!,;:"()]|\.$', '');   -- punctuation, not 8.4.1
  l_words := trim(regexp_replace(l_words, '\s+', ' '));
  return '{' || replace(l_words, ' ', '} accum {') || '}';
end;
/

select text_query('Is 8.4.1 released yet?') as text_query from dual;

select article_id, title, score(1) as score
from   kb_articles
where  contains(body, text_query('What does the Retry-After header mean?'), 1) > 0
order  by score desc;

Output:

Function TEXT_QUERY compiled

TEXT_QUERY
__________________________________________________
{Is} accum {8.4.1} accum {released} accum {yet}

ARTICLE_ID    TITLE                 SCORE
_____________ __________________ ________
KB-602        API rate limits          36

Common words such as "is", "does", and "the" are stopwords that the index does not list, so they match nothing. "Retry-After" and "header" find the article on API rate limits, the only one that contains them.

Where Keyword Search Wins

This example adds eight questions that name exact things, then ranks the right article for each by meaning and by words.

Example:

-- questions that name exact things: codes, versions, products of other companies
insert into eval_questions (question_id, question, article_id) values
  (29, 'Where do we enter our GSTIN?',            'KB-203'),
  (30, 'Can we roll it out with Intune?',         'KB-702'),
  (31, 'What does the Retry-After header mean?',  'KB-602'),
  (32, 'Is 8.4.1 released yet?',                  'KB-301'),
  (33, 'Defender keeps holding our mails',        'KB-102'),
  (34, 'Does 3.9.1 fix it?',                      'KB-401'),
  (35, 'Our MSI install fails',                   'KB-702'),
  (36, 'What is a weighted forecast?',            'KB-303');

update eval_questions
set    minilm = vector_embedding(all_minilm_l12_v2 using question as data)
where  question_id > 28;
commit;

-- the rank of the right article by meaning and by words
with semantic as (
  select q.question_id, a.article_id,
         rank() over (partition by q.question_id
                      order by vector_distance(a.embedding, q.minilm, cosine)) as rnk
  from   eval_questions q cross join kb_articles a
  where  q.question_id > 28),
keyword as (
  select q.question_id, a.article_id,
         rank() over (partition by q.question_id order by score(1) desc) as rnk
  from   eval_questions q join kb_articles a
         on contains(a.body, text_query(q.question), 1) > 0
  where  q.question_id > 28)
select q.question, s.rnk as semantic_rank, k.rnk as keyword_rank
from   eval_questions q
join   semantic s on s.question_id = q.question_id and s.article_id = q.article_id
left   join keyword k on k.question_id = q.question_id and k.article_id = q.article_id
where  q.question_id > 28
order  by q.question_id;

Output:

8 rows inserted.

8 rows updated.

Commit complete.

QUESTION                                     SEMANTIC_RANK    KEYWORD_RANK
_________________________________________ ________________ _______________
Where do we enter our GSTIN?                             1               1
Can we roll it out with Intune?                          2               1
What does the Retry-After header mean?                   4               1
Is 8.4.1 released yet?                                   3               1
Defender keeps holding our mails                         2               1
Does 3.9.1 fix it?                                       1               1
Our MSI install fails                                    1               1
What is a weighted forecast?                             1               1

8 rows selected.

Keyword search put the right article first for all eight. Semantic search did so for four, and placed the others second to fourth: Intune, Defender, and Retry-After mean little to an embedding model that never learned them, and to it 8.4.1 is just a number.

The weakness runs the other way too. For questions described in other words than the articles use, such as "the phone app closes right after I open it", keyword search can rank the right article third or fifth, where semantic search finds it first.

Semantic search itself is covered in how to build semantic search in Oracle Database.

Conclusion

Oracle Text gives Oracle Database full-text search: CREATE INDEX ... INDEXTYPE IS CTXSYS.CONTEXT builds the index, CONTAINS and SCORE query it, and a small function turns a user's question into braces-and-ACCUM query text that handles codes like 8.4.1 safely. Keyword search finds exact names and codes that embedding models miss, and misses descriptions that semantic search finds, which is why most search boxes need both.