How to Measure Vector Distance with VECTOR_DISTANCE in Oracle

Similarity in a vector database means distance: the closer two vectors are, the more alike the things they describe. In Oracle AI Database 26ai, one function measures it, VECTOR_DISTANCE, and a similarity search is nothing more than ORDER BY that distance.

This guide covers VECTOR_DISTANCE and its metrics, which metric to choose, the shorthand functions and operators, how to write a nearest-neighbor search, and the vector arithmetic Oracle supports.

Code for This Guide

The examples are in the examples/ch04 folder of the Oracle AI code repository on GitHub, files 12 to 18, 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 query ARTICLE_TOPICS, a small table of six help desk articles, each with a hand-made vector of three scores: how much it is about account problems, billing, and technical problems. The table is created in how to use the VECTOR data type in Oracle AI Database 26ai. Three dimensions keep the numbers readable; real embeddings with hundreds of dimensions behave the same way.

VECTOR_DISTANCE and Its Metrics

Syntax:

vector_distance( vector1, vector2
                 [, { cosine | dot | euclidean | euclidean_squared
                    | manhattan | hamming | jaccard } ] )

This example measures the distance from a ticket about being charged twice, mostly billing, to every article, by four metrics.

Example:

-- a ticket about being charged twice: mostly billing, a little account and technical
with q as (select vector('[0.10, 0.90, 0.15]') as v from dual)
select a.article_id,
       round(vector_distance(a.topics, q.v, cosine), 4)    as cosine,
       round(vector_distance(a.topics, q.v, euclidean), 4) as euclidean,
       round(vector_distance(a.topics, q.v, dot), 4)       as dot,
       round(vector_distance(a.topics, q.v, manhattan), 4) as manhattan
from   article_topics a, q
order  by cosine;

Output:

ARTICLE_ID       COSINE    EUCLIDEAN        DOT    MANHATTAN
_____________ _________ ____________ __________ ____________
KB-201           0.0018       0.0707      -0.88          0.1
KB-203           0.0029       0.0707     -0.845          0.1
KB-301           0.7751       1.1673    -0.1975         1.65
KB-105             0.78       1.1608      -0.19         1.85
KB-101           0.7936       1.1769      -0.18          1.8
KB-401           0.8209       1.1726      -0.15          1.7

6 rows selected.

All four metrics put the two billing articles first and the rest far behind. They differ in the details: by COSINE, KB-201 is slightly closer than KB-203, while EUCLIDEAN and MANHATTAN find them equally close.

Which Metric to Use

Use the metric your embedding model was trained for; its documentation says which. For almost every text model that is COSINE, or DOT when the vectors are normalized (length 1), where DOT ranks the same and is cheaper to compute. Use HAMMING or JACCARD only for BINARY vectors.

The Default Metric and NULL

Without a metric, VECTOR_DISTANCE uses COSINE, or HAMMING for binary vectors. A distance involving NULL is NULL, as with any SQL expression.

Example:

select round(vector_distance(vector('[1, 0]'), vector('[0, 1]')), 4) as default_metric,
       vector_distance(vector('[1, 0]'), null)                       as with_null
from   dual;

Output:

   DEFAULT_METRIC WITH_NULL
_________________ ____________
                1

[1, 0] and [0, 1] are at a right angle, so their cosine distance is 1. A row whose vector is NULL is never near anything and sorts last in a search, so make an embedding column NOT NULL when every row must be findable.

Both Vectors Need the Same Size

Example:

select vector_distance(topics, vector('[0.10, 0.90]'))
from   article_topics;

Output:

Error starting at line : 1
In command -
select vector_distance(topics, vector('[0.10, 0.90]'))
from   article_topics
Error at Command Line : 1 Column : 8
Error report -
SQL Error: ORA-51808: VECTOR_DISTANCE() or COSINE_DISTANCE() requires all vectors to have
the same dimension count. Encountered (3, 2).

In real applications, ORA-51808 almost always means the query vector came from a different model than the stored ones, for example 768 dimensions against a column filled by a 384-dimension model. Embed the query with the same model that embedded the data.

Shorthand Functions and Operators

Example:

select article_id,
       round(cosine_distance(topics, vector('[0.10, 0.90, 0.15]')), 4) as cosine_distance,
       round(topics <=> vector('[0.10, 0.90, 0.15]'), 4)              as "<=>",
       round(l2_distance(topics, vector('[0.10, 0.90, 0.15]')), 4)     as l2_distance,
       round(topics <-> vector('[0.10, 0.90, 0.15]'), 4)              as "<->",
       round(inner_product(topics, vector('[0.10, 0.90, 0.15]')), 4)   as inner_product,
       round(topics <#> vector('[0.10, 0.90, 0.15]'), 4)              as "<#>"
from   article_topics
where  article_id in ('KB-201', 'KB-301');

Output:

ARTICLE_ID       COSINE_DISTANCE       <=>    L2_DISTANCE       <->    INNER_PRODUCT        <#>
_____________ __________________ _________ ______________ _________ ________________ __________
KB-201                    0.0018    0.0018         0.0707    0.0707             0.88      -0.88
KB-301                    0.7751    0.7751         1.1673    1.1673           0.1975    -0.1975

Watch the sign. INNER_PRODUCT returns the dot product itself, where larger means closer. The <#> operator and VECTOR_DISTANCE with DOT return its negation, where smaller means closer, like every other distance. Mixing the two reverses your ranking.

Write a Similarity Search

A similarity search finds the rows nearest to a query vector: sort by the distance and keep the first rows. This one pattern powers semantic search, recommendations, and the retrieval step of RAG.

Syntax:

select ...
from   table
order  by vector_distance(column, query_vector [, metric])
fetch  first k rows only

This example finds the three articles nearest to a ticket that is mostly about signing in.

Example:

-- the three articles nearest to a ticket that is mostly about signing in
select article_id, k.title,
       round(vector_distance(a.topics, vector('[0.80, 0.10, 0.35]'), cosine), 4) as distance
from   article_topics a join kb_articles k using (article_id)
order  by distance
fetch  first 3 rows only;

Output:

ARTICLE_ID    TITLE                                        DISTANCE
_____________ _________________________________________ ___________
KB-105        Responding to a suspicious sign-in             0.0022
KB-101        Fixing sign-in problems                         0.006
KB-401        Mobile app crashes on startup in 3.9.0         0.4576

The two sign-in articles come first, close together, then there is a long gap to the third. That gap is information: when even the nearest result is far away, your data has nothing on the subject, and an AI answer built on it should say so.

This query compares the query vector with every row, an exact search. On thousands of rows that is instant. On millions, a vector index with FETCH APPROX finds nearly the same rows much faster.

Vector Arithmetic

Vectors of the same size can be added, subtracted, and multiplied dimension by dimension, and aggregated with SUM and AVG. The average of a group of embeddings, its centroid, is a common way to represent the group: the center of all tickets in a category, or of everything a customer has read.

Example:

select vector('[1, 2, 3]') + vector('[10, 20, 30]') as added,
       vector('[10, 20, 30]') - vector('[1, 2, 3]') as subtracted,
       vector('[1, 2, 3]') * vector('[2, 0.5, 0]')  as multiplied
from   dual;

-- the centre of the two billing articles
select avg(topics) as billing_centre
from   article_topics
where  article_id in ('KB-201', 'KB-203');

Output:

ADDED                           SUBTRACTED                      MULTIPLIED
_______________________________ _______________________________ ________________________
[1.1E+001,2.2E+001,3.3E+001]    [9.0E+000,1.8E+001,2.7E+001]    [2.0E+000,1.0E+000,0]

BILLING_CENTRE
___________________________________________________________________________
[7.500000111758709E-002,9.2499998211860657E-001,1.5000000223517418E-001]

The billing center lies halfway between the two billing articles in every dimension. AVG returns a FLOAT64 vector, which is why it has more digits.

Two operations are not supported: multiplying a vector by a number, and dividing vectors.

Example:

select vector('[1, 2, 3]') * 2 from dual;

select vector('[3, 4, 0]') / vector('[1, 2, 1]') from dual;

Output:

Error starting at line : 1
In command -
select vector('[1, 2, 3]') * 2 from dual
Error at Command Line : 1 Column : 30
Error report -
SQL Error: ORA-00932: expression is of data type NUMBER, which is incompatible with
expected data type VECTOR

Error starting at line : 3
In command -
select vector('[3, 4, 0]') / vector('[1, 2, 1]') from dual
Error at Command Line : 3 Column : 28
Error report -
SQL Error: ORA-03001: unimplemented feature
ORA-00722: Feature "Dimension-wise vector division"

To scale a vector, multiply it by a vector of the same size filled with the factor. To normalize one, let the embedding model do it; most can return normalized vectors.

Conclusion

VECTOR_DISTANCE measures how alike two vectors are, with COSINE as the default and the right choice for most text embeddings, or DOT for normalized vectors. Both vectors must come from the same model and have the same size. A similarity search is ORDER BY the distance with FETCH FIRST k ROWS ONLY, and the gaps between distances tell you as much as their order. Shorthands such as COSINE_DISTANCE and <=> do the same job, but mind the sign of INNER_PRODUCT. Vectors can be added, multiplied, and averaged, but not divided or multiplied by a number.