A classic Oracle Text index covers one column of one table. A ubiquitous search index, created with DBMS_SEARCH, covers whole tables and views, every column, and several sources at once. One CONTAINS query then searches all of them, and the results say which table and row each match came from.
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
dbms_search.create_index('INDEX_NAME');
dbms_search.add_source('INDEX_NAME', 'TABLE_OR_VIEW');
-- the index is also a table with DATA and METADATA columns
select metadata from index_name where contains(data, 'words') > 0;
dbms_search.get_document('INDEX_NAME', metadata)
dbms_search.drop_index('INDEX_NAME');The example indexes AIRPORTS and the TRAVEL_NOTES table from How to Sync Oracle Text Indexes with CTX_DDL.SYNC_INDEX.
Search Two Tables at Once
Example:
begin
dbms_search.create_index('NIMBUS_SEARCH');
dbms_search.add_source('NIMBUS_SEARCH', 'AIRPORTS'); -- every column is searchable
dbms_search.add_source('NIMBUS_SEARCH', 'TRAVEL_NOTES');
end;
/
-- one query over both tables: which rows mention Dubai?
select json_value(metadata, '$.SOURCE') as source,
json_serialize(json_query(metadata, '$.KEY')) as row_key
from nimbus_search
where contains(data, 'Dubai') > 0
order by 1, 2;
select json_serialize(dbms_search.get_document('NIMBUS_SEARCH', metadata) pretty)
as document
from nimbus_search
where contains(data, 'laksa') > 0;Output:
PL/SQL procedure successfully completed.
SOURCE ROW_KEY
_______________ _________________________
AIRPORTS {"AIRPORT_CODE":"DXB"}
TRAVEL_NOTES {"NOTE_ID":1}
TRAVEL_NOTES {"NOTE_ID":6}
DOCUMENT
________________________________________________________________________________________
{
"NIMBUS" :
{
"TRAVEL_NOTES" :
{
"NOTE_ID" : 3,
"NOTE" : "Our Singapore lounge serves laksa and has a quiet room for sleeping."
}
}
}- ADD_SOURCE makes every column of both tables searchable, without naming them.
- One query for Dubai finds the airport DXB and two travel notes, and METADATA gives each match's source table and primary key.
- GET_DOCUMENT returns the matching row as a JSON document, here the note about laksa.
The example drops the search index afterward.
Things to Know
- The index follows changes in its sources automatically in the background.
- Sources need a primary key, which identifies each row in METADATA.
- Use a view as a source to search joined data or only some columns.
Cleanup of the TRAVEL_NOTES table and its lexer preference:
drop table travel_notes purge;
exec ctx_ddl.drop_preference('NIMBUS_LEXER')Related Guides
- How to Write Oracle Text CONTAINS Queries (CTX_QUERY)
- How to Highlight Search Results with CTX_DOC.SNIPPET
Conclusion
DBMS_SEARCH builds one search index over many tables and views, every column included. Query it with CONTAINS on its DATA column, find each match's source in METADATA, and get the full row with GET_DOCUMENT.
