How to Build Semantic Search in Oracle Database

Most questions typed into a help desk search box are descriptions, such as "the phone app closes right after I open it", in words that rarely match the article that answers them. Semantic search finds answers by meaning instead of by words, and in Oracle AI Database 26ai it is one SQL query.

This guide builds semantic search over a knowledge base and a ticket table, measures how near is near enough, uses a distance cutoff to detect questions the data cannot answer, finds similar records, and checks that filters do not cost accuracy.

Code for This Guide

The examples are files 01 to 04 and 14 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.

A Semantic Search Query

Embed the question once, with the same model that embedded the data, and order the rows by their distance from it.

Example:

with q as (select vector_embedding(all_minilm_l12_v2
                    using 'Our card was charged two times for one invoice' as data) as v
           from   dual)
select a.article_id, a.title,
       round(vector_distance(a.embedding, q.v, cosine), 3) as distance
from   kb_articles a, q
order  by distance
fetch  first 3 rows only;

Output:

ARTICLE_ID    TITLE                                         DISTANCE
_____________ __________________________________________ ___________
KB-201        Duplicate charges and refunds                     0.38
KB-205        Custom fields on invoices                        0.575
KB-503        Why Analytics and Billing totals differ          0.654

The right article, "Duplicate charges and refunds", comes first. The other two are merely the next nearest, and neither answers the question. A search always returns as many rows as you ask for; deciding which are close enough to show is up to you.

How Near Is Near Enough?

Distances only mean something for one model and one kind of data, so measure them. With a table of questions whose right articles are known, this query compares the distance of the right article with the distance of the nearest wrong one, for 20 English questions.

Example:

-- 20 English questions: the distance of the right article and of the nearest wrong one
with d as (
  select q.question_id, q.article_id as right_id, a.article_id,
         vector_distance(a.embedding, q.minilm, cosine) as distance
  from   eval_questions q cross join kb_articles a
  where  q.lang = 'en' and q.question_id <= 20),
per_question as (
  select question_id,
         min(case when article_id = right_id  then distance end)
           as right_article,
         min(case when article_id <> right_id then distance end)
           as nearest_wrong
  from   d
  group  by question_id)
select 'right article' as distance_of,
       round(min(right_article), 3) as lowest, round(avg(right_article), 3) as average,
       round(max(right_article), 3) as highest
from   per_question
union all
select 'nearest wrong article',
       round(min(nearest_wrong), 3), round(avg(nearest_wrong), 3),
       round(max(nearest_wrong), 3)
from   per_question;

Output:

DISTANCE_OF                 LOWEST    AVERAGE    HIGHEST
________________________ _________ __________ __________
right article                0.256       0.46      0.749
nearest wrong article         0.45      0.671      0.812

The right articles lie between 0.256 and 0.749 from their questions, and a wrong article can be as near as 0.45. The ranges overlap, so a cutoff that keeps every right article, say 0.75, would also keep many wrong ones. Distance alone cannot separate right from wrong. That is why a search page shows several results, and why RAG passes several articles to the language model.

Detect Questions Your Data Cannot Answer

A cutoff is useful at the other end: recognizing questions the data knows nothing about.

Example:

with questions (question) as (
  values ('What is the capital of France?'),
         ('How do I bake sourdough bread?'),
         ('Who won the football world cup?'),
         ('Can I pay my invoice in Bitcoin?'))
select q.question,
       (select round(min(vector_distance(a.embedding,
                 vector_embedding(all_minilm_l12_v2 using q.question as data), cosine)), 3)
        from   kb_articles a) as nearest_article
from   questions q;

Output:

QUESTION                               NEAREST_ARTICLE
___________________________________ __________________
What is the capital of France?                    0.93
How do I bake sourdough bread?                   0.876
Who won the football world cup?                  0.913
Can I pay my invoice in Bitcoin?                 0.453

The three questions unrelated to software are 0.876 or more from every article, farther than any right answer above, so a cutoff of about 0.8 lets the search say "nothing found" for them.

The Bitcoin question is the warning. Its nearest article is 0.453 away, as near as many right answers, because several articles are about invoices, yet none mentions Bitcoin. No distance can tell that an article does not contain the answer; only reading it can. In a RAG application, the language model does that reading and should be told to say so when the answer is not there.

Find Similar Records

Semantic search does not need a question: a stored embedding can be the query. Finding similar tickets, possible duplicates, or related articles is a search with another row's vector.

Example:

-- the tickets most like ticket 9, except ticket 9 itself
select t.ticket_id, t.subject, t.status,
       round(vector_distance(t.embedding, s.embedding, cosine), 3) as distance
from   tickets t, tickets s
where  s.ticket_id = 9
and    t.ticket_id <> s.ticket_id
order  by distance
fetch  first 5 rows only;

Output:

   TICKET_ID SUBJECT                            STATUS         DISTANCE
____________ __________________________________ ___________ ___________
         154 Duplicate charge on credit card    Closed            0.008
         361 Duplicate charge on credit card    Closed            0.045
          15 Duplicate charge on credit card    Resolved          0.047
         375 Invoice paid two times             Resolved          0.158
         104 Invoice paid two times             Resolved          0.162

Three tickets are nearly identical, between 0.008 and 0.047: the same problem reported by other customers. The next two describe it in other words. An agent working on ticket 9 can see at once how the earlier ones were resolved.

Combine Semantic Search with Filters

Real searches add ordinary conditions: one product, one language, documents a user may see. With a vector index, the worry is that an approximate search might find the nearest rows of the whole table first and then drop those that fail the filter, returning too few or the wrong rows.

This test searches 50,000 archived tickets for 28 questions, restricted to one product and one year, with an exact and an approximate search, and counts how many approximate results are right.

Example:

select count(*) as matching_tickets
from   ticket_archive
where  product_id = 6
and    created_on >= date '2025-01-01';

-- the 28 questions, searching only archived Atlas Sync tickets (product 6) of 2025:
-- how many approximate results are as near as the 10th result of the exact search?
with q as (
  select question_id, minilm as v from eval_questions where question_id <= 28),
exact as (
  select q.question_id, max(e.distance) as tenth
  from   q cross apply (select vector_distance(embedding, q.v, cosine) as distance
                        from   ticket_archive
                        where  product_id = 6
                        and    created_on >= date '2025-01-01'
                        order  by distance
                        fetch  exact first 10 rows only) e
  group  by q.question_id),
approx as (
  select q.question_id, a.distance
  from   q cross apply (select vector_distance(embedding, q.v, cosine) as distance
                        from   ticket_archive
                        where  product_id = 6
                        and    created_on >= date '2025-01-01'
                        order  by distance
                        fetch  approx first 10 rows only) a)
select count(*) as results,
       sum(case when a.distance <= e.tenth + 1e-6 then 1 else 0 end) as right_results
from   approx a join exact e on e.question_id = a.question_id;

Output:

   MATCHING_TICKETS
___________________
               1284

   RESULTS    RIGHT_RESULTS
__________ ________________
       280              280

The filter keeps 1,284 of the 50,000 tickets, and the approximate search returned all 280 right results: the database combined the filter with the index without losing a row. Filters can still lower accuracy in other cases, such as a filter that keeps a few rows scattered over many index partitions, so test filtered searches as you test the others.

Where Semantic Search Falls Short

Some questions are exact: a tax number, a version such as 8.4.1, a product name of another company, or an error code. An embedding model may never have seen these, while a word-for-word search finds them at once. A good search box handles both kinds of question, which is what keyword search with Oracle Text and hybrid search add.

Conclusion

Semantic search in Oracle is ORDER BY VECTOR_DISTANCE against an embedded question, with FETCH FIRST to keep the top rows. Measure distances on your own questions: right and wrong answers overlap, so show several results, but a cutoff can still flag questions your data cannot answer. Search with a stored row's vector to find similar records, add filters with an ordinary WHERE clause, and test their accuracy like any other search.