How to Build Hybrid Search in Oracle Database

Semantic search finds descriptions in the user's own words but misses exact codes and names. Keyword search finds the codes but misses descriptions. Hybrid search runs both and combines the results, so each covers the other's weakness.

This guide builds hybrid search in Oracle AI Database 26ai two ways: by hand, with reciprocal rank fusion in SQL, and with a hybrid vector index searched through DBMS_HYBRID_VECTOR. It measures every method on the same 28 questions.

Code for This Guide

The examples are files 09 to 13 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 build on the two searches being combined: the article embeddings from how to build semantic search in Oracle Database, and the text index and TEXT_QUERY function from how to search by keyword with Oracle Text. The 28 English test questions are in EVAL_QUESTIONS.

Combine Ranks with Reciprocal Rank Fusion

The two scores cannot simply be added: a vector distance and a text score are on different scales. Their ranks can be combined. Reciprocal rank fusion (RRF) gives each row, from each search that found it, 1 / (60 + rank), and orders rows by the sum:

• A row that both searches rank high wins.
• A row ranked first by one search and missed by the other still scores well.
• The constant 60 keeps the first few ranks from dominating.

This example fuses the semantic and keyword rankings for the 28 English questions and counts, per method, how often the right article came first and how often it was in the top three.

Example:

-- reciprocal rank fusion: each method adds 1 / (60 + rank) for every article it finds
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.lang = 'en'),
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.lang = 'en'),
fused as (
  select s.question_id, s.article_id, s.rnk as semantic_rank, k.rnk as keyword_rank,
         1 / (60 + s.rnk) + nvl(1 / (60 + k.rnk), 0) as rrf_score
  from   semantic s
  left   join keyword k on k.question_id = s.question_id and k.article_id = s.article_id),
ranked as (
  select f.*, rank() over (partition by question_id order by rrf_score desc) as hybrid_rank
  from   fused f),
right_ranks as (
  select r.semantic_rank, r.keyword_rank, r.hybrid_rank
  from   ranked r join eval_questions q
         on q.question_id = r.question_id and q.article_id = r.article_id)
select 'semantic' as method,
       count(case when semantic_rank = 1 then 1 end)  as first,
       count(case when semantic_rank <= 3 then 1 end) as in_top_3
from   right_ranks
union all
select 'keyword', count(case when keyword_rank = 1 then 1 end),
       count(case when keyword_rank <= 3 then 1 end)
from   right_ranks
union all
select 'hybrid (RRF)', count(case when hybrid_rank = 1 then 1 end),
       count(case when hybrid_rank <= 3 then 1 end)
from   right_ranks;

Output:

METHOD             FIRST    IN_TOP_3
_______________ ________ ___________
semantic              23          27
keyword               26          27
hybrid (RRF)          26          28

Each method alone missed the top three once: semantic search on "Retry-After", keyword search on "the phone app closes right after I open it". Hybrid search put the right article in the top three for all 28 questions, and first as often as keyword search. For RAG, which hands the language model the first few articles, the top three is the number that matters.

Create a Hybrid Vector Index

A hybrid vector index does all of this in one index: a text index and a vector index on the same column, maintained together. It also does the embedding itself. It splits each document into chunks, embeds them with the model you name, and keeps the vectors, so you need no embedding column and no trigger.

Syntax:

create hybrid vector index index_name on table_name (text_column)
  parameters ('model model_name [ vector_idxtype { ivf | hnsw } ] ...');

A column can have only one domain index, and creating a second raises ORA-29880, so this example drops the text index first.

Example:

drop index kb_articles_text;

set timing on
create hybrid vector index kb_articles_hybrid on kb_articles (body)
  parameters ('model ALL_MINILM_L12_V2 vector_idxtype ivf');
set timing off

select index_name, index_type, ityp_owner || '.' || ityp_name as indextype
from   user_indexes
where  index_name = 'KB_ARTICLES_HYBRID';

Output:

Index KB_ARTICLES_TEXT dropped.

Hybrid VECTOR created.

Elapsed: 00:00:01.213

INDEX_NAME            INDEX_TYPE    INDEXTYPE
_____________________ _____________ ____________________
KB_ARTICLES_HYBRID    DOMAIN        CTXSYS.CONTEXT_V2

The index is a domain index of the type CTXSYS.CONTEXT_V2. It answers CONTAINS queries like the text index it replaced, and hybrid searches through DBMS_HYBRID_VECTOR.

Search with DBMS_HYBRID_VECTOR.SEARCH

SEARCH runs a hybrid search and returns a JSON array, one object per row, with the row ID, the combined score, the vector and text scores, and the matching chunk.

Syntax:

dbms_hybrid_vector.search(params json) return json

{ "hybrid_index_name" : "index_name",
  "search_text"       : "question",
  "search_scorer"     : "rsf" | "rrf" | "wrrf",
  "search_fusion"     : "union" | "intersect" | "text_only" | "vector_only" | ...,
  "vector"            : { "search_text": "...", "score_weight": n, ... },
  "text"              : { "contains": "...", "score_weight": n, ... },
  "return"            : { "topN": n, "values": [ "rowid", "score", ... ] } }

search_text is used for both searches, and the vector and text objects override or tune each one separately. The default scorer, RSF (relative score fusion), combines normalized scores; RRF combines ranks.

This example turns the three best results into rows with JSON_TABLE and joins them to the articles by row ID.

Example:

select a.article_id, a.title, r.score, r.vector_score, r.text_score
from   json_table(
         dbms_hybrid_vector.search(json('{
           "hybrid_index_name": "KB_ARTICLES_HYBRID",
           "search_text": "What does the Retry-After header mean?",
           "return": {"topN": 3,
                      "values": ["rowid", "score", "vector_score", "text_score"]}}')),
         '$[*]' columns (row_id        varchar2(18) path '$.rowid',
                         score         number       path '$.score',
                         vector_score  number       path '$.vector_score',
                         text_score    number       path '$.text_score')) r
join   kb_articles a on a.rowid = chartorowid(r.row_id);

Output:

ARTICLE_ID    TITLE                                      SCORE    VECTOR_SCORE    TEXT_SCORE
_____________ _______________________________________ ________ _______________ _____________
KB-602        API rate limits                            55.48           55.93            51
KB-201        Duplicate charges and refunds              53.88           56.67            26
KB-601        Webhooks are disabled after failures       52.89           55.58            26

The article on API rate limits comes first, carried by its high text score: hybrid search found what semantic search alone ranked fourth.

Measure the Hybrid Index

Example:

-- the rank of the right article in the results of DBMS_HYBRID_VECTOR.SEARCH
with ranks as (
  select q.question_id, q.article_id as right_id, a.article_id, r.hybrid_rank
  from   eval_questions q,
         json_table(
           dbms_hybrid_vector.search(json_object(
             'hybrid_index_name' value 'KB_ARTICLES_HYBRID',
             'search_text'       value q.question,
             'return'            value json_object('topN' value 10,
                                                   'values' value json_array('rowid'))
             returning json)),
           '$[*]' columns (hybrid_rank for ordinality,
                           row_id varchar2(18) path '$.rowid')) r
         join kb_articles a on a.rowid = chartorowid(r.row_id)
  where  q.lang = 'en')
select count(distinct question_id) as questions,
       count(case when article_id = right_id and hybrid_rank = 1 then 1 end) as first,
       count(case when article_id = right_id and hybrid_rank <= 3 then 1 end) as in_top_3
from   ranks;

Output:

   QUESTIONS    FIRST    IN_TOP_3
____________ ________ ___________
          28       26          27

Twenty-six first and 27 in the top three: as good as keyword search, better than semantic search, and one short of the hand-made fusion. The miss is "Is 8.4.1 released yet?": by default the combined score leans on the vector score, and the text match of a version number counts for little.

Tune the Weights

The weights of the two searches are parameters. This example gives them equal weight.

Example:

-- the same test, with the vector and text scores weighted equally
with ranks as (
  select q.question_id, q.article_id as right_id, a.article_id, r.hybrid_rank
  from   eval_questions q,
         json_table(
           dbms_hybrid_vector.search(json_object(
             'hybrid_index_name' value 'KB_ARTICLES_HYBRID',
             'search_text'       value q.question,
             'vector'            value json_object('score_weight' value 1),
             'text'              value json_object('score_weight' value 1),
             'return'            value json_object('topN' value 10,
                                                   'values' value json_array('rowid'))
             returning json)),
           '$[*]' columns (hybrid_rank for ordinality,
                           row_id varchar2(18) path '$.rowid')) r
         join kb_articles a on a.rowid = chartorowid(r.row_id)
  where  q.lang = 'en')
select count(distinct question_id) as questions,
       count(case when article_id = right_id and hybrid_rank = 1 then 1 end) as first,
       count(case when article_id = right_id and hybrid_rank <= 3 then 1 end) as in_top_3
from   ranks;

Output:

   QUESTIONS    FIRST    IN_TOP_3
____________ ________ ___________
          28       25          27

Equal weights lose one first place and keep 27 in the top three, but miss different questions: the phone-app question falls to fifth. Every setting trades some questions for others.

Compare the Methods

Either hybrid approach is a clear gain over semantic search alone. The hybrid index costs one statement and no code, and handles chunking and embedding for long documents. Hand-made fusion needs an embedding column and a text index, but gives you full control of the query and the formula. No method and no setting is best for every question; the only way to choose is to measure on questions like your users'.

Conclusion

Hybrid search combines semantic and keyword search so neither's blind spot reaches the user. Fuse the two rankings by hand with reciprocal rank fusion, 1 / (60 + rank) summed per row, or create a hybrid vector index and query it with DBMS_HYBRID_VECTOR.SEARCH, tuning the scorer and weights. On 28 test questions, hand-made RRF put the right article in the top three every time; measure on your own questions before you pick.