How to Sync Oracle Text Indexes with CTX_DDL.SYNC_INDEX

An Oracle Text CONTEXT index makes words in text columns searchable with CONTAINS. Unlike normal indexes, it is not updated by each insert: new and changed rows wait until the index is synchronized. CTX_DDL.SYNC_INDEX does that on demand, the SYNC parameter can do it automatically, and OPTIMIZE_INDEX keeps the index compact.

Code for This Guide

The main examples are in the examples/pkg-vector-text folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

create index name on table (column) indextype is ctxsys.context
  parameters ('sync (manual)');            -- or sync (on commit), sync (every "interval")

ctx_ddl.sync_index('INDEX_NAME');
ctx_ddl.optimize_index('INDEX_NAME', ctx_ddl.optlevel_full);

Creating Oracle Text preferences needs the CTXAPP role, which DB_DEVELOPER_ROLE includes.

Create a Text Index

TRAVEL_NOTES holds six travel tips. A lexer preference keeps Wi-Fi as one word, and the index is created with manual sync.

Example:

create table travel_notes (note_id number primary key, note varchar2(400));
insert into travel_notes values
  (1, 'The lounge in Dubai is open all night; showers are free for Gold members.'),
  (2, 'Wi-Fi on board is free on flights longer than six hours.'),
  (3, 'Our Singapore lounge serves laksa and has a quiet room for sleeping.'),
  (4, 'Pets travel in the hold, except guide dogs, which travel in the cabin.'),
  (5, 'Gold and Platinum members board first and may bring two cabin bags.'),
  (6, 'The e-gates in Dubai and London speed up passport control.');
commit;

begin
  ctx_ddl.create_preference('NIMBUS_LEXER', 'BASIC_LEXER');
  ctx_ddl.set_attribute('NIMBUS_LEXER', 'PRINTJOINS', '-');     -- keep "Wi-Fi" as one word
end;
/
create index travel_notes_ctx on travel_notes (note) indextype is ctxsys.context
  parameters ('lexer nimbus_lexer sync (manual)');

select score(1) as score, note_id, note
from   travel_notes
where  contains(note, 'lounge', 1) > 0
order  by score desc;

Output:

Table TRAVEL_NOTES created.

6 rows inserted.

Commit complete.

PL/SQL procedure successfully completed.

Index TRAVEL_NOTES_CTX created.

   SCORE    NOTE_ID NOTE
________ __________ ____________________________________________________________________________
       4          3 Our Singapore lounge serves laksa and has a quiet room for sleeping.
       4          1 The lounge in Dubai is open all night; showers are free for Gold members.

CONTAINS finds the two notes that mention a lounge, and SCORE(1) gives their relevance.

Sync after New Rows

Example:

insert into travel_notes values
  (7, 'A new lounge opens in London Heathrow Terminal 3 in April.');
commit;

select count(*) as found_before_sync from travel_notes where contains(note, 'Heathrow') > 0;
exec ctx_ddl.sync_index('TRAVEL_NOTES_CTX')

select count(*) as found_after_sync from travel_notes where contains(note, 'Heathrow') > 0;

exec ctx_ddl.optimize_index('TRAVEL_NOTES_CTX', ctx_ddl.optlevel_full)

Output:

1 row inserted.

Commit complete.

   FOUND_BEFORE_SYNC
____________________
                   0

PL/SQL procedure successfully completed.

   FOUND_AFTER_SYNC
___________________
                  1

PL/SQL procedure successfully completed.

The new note about Heathrow is committed, but CONTAINS does not find it until SYNC_INDEX adds it to the index. OPTIMIZE_INDEX then merges the index fragments that many small syncs leave behind.

Sync on Commit

Example:

create table lounge_news (news_id number primary key, text varchar2(200));
create index lounge_news_ctx on lounge_news (text) indextype is ctxsys.context
  parameters ('sync (on commit)');

insert into lounge_news values (1, 'Spa treatments now in the Dubai lounge');
commit;                                       -- the index is synchronized here

select count(*) as found_at_once from lounge_news where contains(text, 'spa') > 0;

select idx_name, idx_sync_type from ctx_user_indexes
where  idx_table = 'LOUNGE_NEWS';

Output:

Table LOUNGE_NEWS created.

Index LOUNGE_NEWS_CTX created.

1 row inserted.

Commit complete.

   FOUND_AT_ONCE
________________
               1

IDX_NAME           IDX_SYNC_TYPE
__________________ ________________
LOUNGE_NEWS_CTX    ON COMMIT

With SYNC (ON COMMIT), the commit synchronizes the index, and the new row is found at once. CTX_USER_INDEXES shows the sync type. The example drops the table afterward.

Things to Know

• ON COMMIT makes every commit a little slower; a scheduled SYNC (EVERY ...) suits busy tables.
• Run OPTIMIZE_INDEX regularly on indexes that sync often.
• CTX_USER_PENDING lists rows waiting for the next sync.

Related Guides

• How to Create Jobs with DBMS_SCHEDULER.CREATE_JOB
• How to Use DB_DEVELOPER_ROLE in Oracle

Conclusion

Oracle Text indexes see new and changed rows only after a sync. Call CTX_DDL.SYNC_INDEX for manual indexes, or choose ON COMMIT or a scheduled sync, and optimize indexes that sync often.