A Vector Function by Any Other Name…

Uncategorized Leave a comment

In my ongoing review of Oracle Database 23ai’s vector functions, there was some confusion about which distance functions are equivalent, what the aliases are for a few of the specific vector distance functions, and which distance functions had shortcut operators.

Going to the official Oracle documentation for the VECTOR_DISTANCE function was helpful, but it still didn’t give me a good overall summary of the distance functions and their possible arguments. As I did in my previous post on vector functions, I used ChatGPT to help. It was not to answer a specific question I had, but to summarize the Oracle documentation page (which I don’t yet see in any of Oracle’s documentation). All I had to provide to ChatGPT was the URL and what columns I wanted in the summary, and I wanted it in a table. Within moments, I had my one-pager:

VECTOR_DISTANCE options

VECTOR_DISTANCE ( expr1 , expr2 [ , metric ] )

MetricShorthand function call (if any)Shorthand operatorSpecial defaults / exceptions
COSINECOSINE_DISTANCE (expr1, expr2)<=>Default metric when none is specified (unless both vectors are BINARY); also used when indexed columns don’t share a metric.
DOT— (use -1 * INNER_PRODUCT (expr1, expr2) to obtain the negated dot‑product distance)<#>Computes the negated dot product; larger value → less alignment.
EUCLIDEANL2_DISTANCE (expr1, expr2)<->Standard L2 (straight-line) distance between vectors.
EUCLIDEAN_SQUARED (alias L2_SQUARED)——Same as Euclidean but without the square‑root (returns squared distance).
HAMMINGHAMMING_DISTANCE (expr1, expr2)—Becomes the default metric whenever the input vectors are BINARY. Counts dimensions that differ.
MANHATTANL1_DISTANCE (expr1, expr2)—Also called taxicab or L1 distance.
JACCARDJACCARD_DISTANCE (expr1, expr2)—Both vectors must be BINARY; otherwise an error is raised.

It’s good to have options for multiple vector distance functions, though I could see having the “metric” argument be a string value instead of a keyword for making development more generalized. Other than that, my initial confusion was why some of the metrics had a shortcut, and some don’t. These reasons include how frequently the metric is used, whether vector indexes can support the metric, and so forth.

I verified that ChatGPT summarized the documentation correctly and at the same time verifying that the functionality of VECTOR_DISTANCE in 23ai (version 23.8 in Autonomous Database) matches the documentation too!

Given that the COSINE metric is the one of the most useful metrics since it measures the angle between two vectors instead of the difference in size or position, there are four ways to compute it, including a shorthand operator. I verified this with some sample vector distance calculations:

select VECTOR_DISTANCE('[17,2.33,12.1]','[8,19,-7.1]',COSINE);
select VECTOR_DISTANCE('[17,2.33,12.1]','[8,19,-7.1]');
select COSINE_DISTANCE('[17,2.33,12.1]','[8,19,-7.1]');
select '[17,2.33,12.1]' <=> '[8,19,-7.1]';

VECTOR_DISTANCE('[17,2.33,12.1]','[8,19,-7.1]',COSINE)
------------------------------------------------------
                                            7.939E-001

VECTOR_DISTANCE('[17,2.33,12.1]','[8,19,-7.1]')
-----------------------------------------------
                                     7.939E-001

COSINE_DISTANCE('[17,2.33,12.1]','[8,19,-7.1]')
-----------------------------------------------
                                     7.939E-001

'[17,2.33,12.1]'<=>'[8,19,-7.1]'
--------------------------------
                      7.939E-001

For the VECTOR_DISTANCE function, if you don’t specify a metric type, COSINE is the default. And COSINE not only has its own function but also its own shorthand operator “<=>”.

What’s Next?

I’m still surprised on a daily basis how useful the summarization features are in most LLMs and how fast they produce results. Using any of the popular LLMs such as ChatGPT has become an indispensable tool for not only summarizing Oracle documentation, but also for helping with Oracle Support (now with a built-in AI search feature), and for generating PL/SQL and SQL code snippets when I can’t quite remember how CONNECT BY or GROUP BY GROUPING SETS works!

Addendum

In reference to the Orphan Black TV series, the COSINE_DISTANCE between the Leda clones Sarah and Helena will be closer to zero than to any of the other clones.

Leave a Reply