A similarity search returns the rows nearest to a question. In a database that is only the start: the nearest rows can be joined, grouped, and counted like any other rows. An ordinary join matches rows by equal values; a semantic join matches them by meaning.
This guide uses semantic joins in Oracle AI Database 26ai to answer two questions a help desk manager asks: how much of what customers write does the knowledge base already answer, and which agents have solved problems like a new one, and how fast.
Code for This Guide
The examples are files 01 to 03 in the examples/ch15 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 join the tickets, knowledge base articles, and agents of the sample schema, with the embeddings from how to generate embeddings in SQL with VECTOR_EMBEDDING.
Measure What the Knowledge Base Covers
This semantic join finds, for every ticket, the distance of its nearest knowledge base article, then counts the tickets an article answers, partly covers, or does not cover. The cutoffs, 0.4 and 0.6, were measured on this model and data beforehand, as shown in how to build semantic search in Oracle Database.
Example:
-- for every ticket, the distance of its nearest knowledge base article
with nearest as (
select t.ticket_id,
(select min(vector_distance(a.embedding, t.embedding, cosine))
from kb_articles a) as distance
from tickets t)
select case when distance < 0.4 then '1: answered by an article'
when distance < 0.6 then '2: partly covered'
else '3: no article near' end as coverage,
count(*) as tickets
from nearest
group by case when distance < 0.4 then '1: answered by an article'
when distance < 0.6 then '2: partly covered'
else '3: no article near' end
order by coverage;Output:
COVERAGE TICKETS ____________________________ __________ 1: answered by an article 271 2: partly covered 108 3: no article near 21
Two thirds of the tickets have an article that answers them: customers who searched the knowledge base first might not have needed to write. Twenty-one tickets have no article near them at all.
Find the Gaps
The same join, filtered to tickets with no article near, lists what the knowledge base is missing.
Example:
-- the subjects of the tickets that no article is near: candidates for new articles
select t.subject, count(*) as tickets
from tickets t
where (select min(vector_distance(a.embedding, t.embedding, cosine))
from kb_articles a) >= 0.6
group by t.subject
order by tickets desc, t.subject
fetch first 8 rows only;Output:
SUBJECT TICKETS ______________________________ __________ Request: dark theme 8 Night mode for the web app 6 Please add dark mode 6 Deals page stuck on spinner 1
Twenty of the 21 are requests for a dark mode, in three different wordings: a topic customers raise again and again and no article addresses. That is the next article to write, found by a query rather than by reading 400 tickets. Run it every month and the knowledge base follows what customers ask.
Group the Nearest Rows
The nearest rows are an ordinary result set, ready for GROUP BY. This example takes a new ticket, finds the 20 most similar resolved tickets, and groups them by the agent who resolved them, with average time to resolve and average satisfaction.
Example:
-- who has solved problems like this new one, and how fast?
with similar as (
select t.agent_id, t.created_at, t.resolved_at, t.satisfaction
from tickets t
where t.resolved_at is not null
order by vector_distance(t.embedding,
vector_embedding(all_minilm_l12_v2 using
'The pipeline board keeps spinning and never shows our deals' as data),
cosine)
fetch first 20 rows only)
select a.name as agent, count(*) as similar_tickets,
round(avg(extract(day from (s.resolved_at - s.created_at)) * 24
+ extract(hour from (s.resolved_at - s.created_at)))) as avg_hours,
round(avg(s.satisfaction), 1) as avg_satisfaction
from similar s join agents a on a.agent_id = s.agent_id
group by a.name
order by similar_tickets desc, avg_hours;Output:
AGENT SIMILAR_TICKETS AVG_HOURS AVG_SATISFACTION ________________ __________________ ____________ ___________________ Yusuf Demir 5 28 3.5 Priya Raman 4 17 3 Daniel Okafor 4 23 4 Sofia Marquez 3 64 3 Arjun Mehta 2 5 4 Hannah Becker 2 38 4 6 rows selected.
Yusuf Demir has resolved the most tickets like this one; Arjun Mehta was the fastest, at five hours on average for two tickets. A routing rule can use exactly this query: send a new ticket to the agent with the most experience of similar problems, or the best results with them.
Patterns for Semantic Joins
| Pattern | How | Answers |
|---|---|---|
| Nearest distance per row | A scalar subquery with MIN(VECTOR_DISTANCE(...)) | Is this row covered by the other table? |
| Rows with nothing near | The same subquery in WHERE, with a cutoff | Gaps, outliers, new topics |
| Nearest rows, then aggregate | ORDER BY distance FETCH FIRST n in a WITH clause, then GROUP BY | Who, how often, how fast, for similar cases |
On large tables, the per-row nearest-neighbor subquery is where a vector index pays off.
Conclusion
A semantic join in Oracle matches rows by meaning instead of equal values, with VECTOR_DISTANCE inside ordinary SQL. Join tickets to their nearest articles to measure what the knowledge base covers and list its gaps, and group the nearest resolved tickets by agent to find who solves a kind of problem best. The similarity search is the first step of the query, not the whole of it.
