With Oracle Database 26ai, SQL gains native support for AI workloads through Oracle AI Vector Search. At the heart of similarity search lies two foundational concepts: constructing vectors directly in SQL and calculating the mathematical distance between them.

1. Creating Vectors on the Fly: The VECTOR Constructor

Real-world embedding models (like OpenAI’s text-embedding-3-small or Cohere’s embed-english-v3.0) produce vectors with 1,024 to 3,072 dimensions. While database tables store these large arrays, you do not need to create or populate a table just to experiment.

Oracle provides the VECTOR constructor literal, allowing you to generate vectors dynamically inside a query using standard string representations.

Basic 2D Example

SQL

SELECT VECTOR('[0, 0]') AS start_point,
       VECTOR('[3, 4]') AS end_point;

Specifying Dimensions and Numeric Formats

By default, values inside vectors are represented in standard scientific notation as FLOAT32 (or FLOAT64/INT8 if specified). You can enforce dimension count and data storage formats directly:

SQL

-- Syntax: VECTOR('[values]', <dimension_count>, <format>)
SELECT VECTOR('[1.25, 3.45, -0.85]', 3, FLOAT32) AS custom_vector;

2. Measuring Similarity: The VECTOR_DISTANCE Function

Vectors represent meaning in multi-dimensional space: similar concepts sit close together, while unrelated concepts sit far apart.

The VECTOR_DISTANCE() function takes two vectors as input and returns a BINARY_DOUBLE representing the distance between them.

Default Distance Metric: Cosine

If no metric is passed, Oracle defaults to Cosine Distance:

SQL

SELECT VECTOR_DISTANCE(
    VECTOR('[1, 0]'), 
    VECTOR('[0, 1]')
) AS default_distance;
-- Returns 1.0 (90-degree angle = completely orthogonal)

3. Distance Metrics Breakdown

Distance metrics determine how “nearness” is calculated. Each metric uses a distinct mathematical model tailored to specific data types and business cases.

Deep Dive on Core Metrics

1. Cosine Distance

Cosine distance measures the angle between two vectors, regardless of their magnitude or length.

• Range: 0.0 (identical direction) to 2.0 (completely opposite directions).
• Key Relationship: Inverse to cosine similarity. As similarity approaches 1.0, distance approaches 0.0.

SQL

SELECT VECTOR_DISTANCE(VECTOR('[1, 1]'), VECTOR('[2, 2]'), COSINE) AS distance;
-- Returns 0.0 (identical direction, different magnitudes)

2. Euclidean and Euclidean Squared Distance

Calculates the shortest straight-line path between two coordinates using the Pythagorean theorem ($L_2$ norm).

• Sensitive to both vector magnitude and direction.
• EUCLIDEAN_SQUARED skips the final square root operation, saving compute cycles when ranking results in an ORDER BY clause.

SQL

-- 3-4-5 Triangle: Distance between (0,0) and (3,4) is 5
SELECT VECTOR_DISTANCE(VECTOR('[0, 0]'), VECTOR('[3, 4]'), EUCLIDEAN) AS straight_line;
-- Returns 5.0

3. Dot Product

Calculates the sum of coordinate-wise products ($\sum A_i B_i$), scaling with both the angle and vector lengths.

• Larger values mean greater similarity.
• Commonly used when embeddings are pre-normalized to unit length ($\vert{}\vert{}V\vert{}\vert{} = 1$), where Dot Product directly mirrors Cosine Similarity.

4. Manhattan Distance ($L_1$ Norm)

Also known as “taxicab” or “city block” distance. It sums the absolute differences of vector coordinates along right-angle grid paths rather than measuring straight lines.

• Ideal for uniform grids (power grids, chessboards, robotics).
• Faster to compute than Euclidean because it avoids square roots and exponents.

SQL

-- Path: |3 - 0| + |4 - 0| = 7
SELECT VECTOR_DISTANCE(VECTOR('[0, 0]'), VECTOR('[3, 4]'), MANHATTAN) AS grid_distance;
-- Returns 7.0

5. Hamming & Jaccard (Binary Vectors)

Used strictly on binary vectors (e.g., bit strings representing feature sets, perceptual hashes, or network packet parity checks).

• Hamming: Counts the number of positions where corresponding bits differ.
• Jaccard: Computes the ratio of shared non-zero bits against the union of all active bits between two vectors.

4. Shorthand Distance Operators

To keep SQL queries concise and readable, Oracle provides symbolic operators matching standard metrics:

• <-> : Euclidean Distance
• <=> : Cosine Distance
• <#> : Negative Dot Product

SQL

-- Find the 5 most similar documents using cosine shorthand
SELECT doc_id, content
FROM documents
ORDER BY embedding <=> VECTOR('[0.12, -0.45, 0.88, ...]')
FETCH FIRST 5 ROWS ONLY;

(Note: <#> outputs negative dot product so that ascending ORDER BY clauses naturally place the most similar vectors at the top).

5. Custom Distance Functions & Exact Search

Custom JavaScript Distance Metrics

For proprietary matching algorithms, Oracle Database 23ai allows developers to define custom distance metrics using JavaScript directly inside the database via the Multilingual Engine (MLE):

SQL

CREATE OR REPLACE FUNCTION my_custom_dist(v1 VECTOR, v2 VECTOR) 
RETURN BINARY_DOUBLE 
AS MLE LANGUAGE JAVASCRIPT Q'#
  // Custom multi-dimensional distance logic
  return 0.42;
#';

Exact vs. Approximate Similarity Search

When running queries without specialized vector indexes (like HNSW or IVF), Oracle performs an Exact Similarity Search (also called a Flat Search):

• Computes the mathematical distance between your query vector and every single row in the table.
• Advantage: 100% recall accuracy. No relevant results are missed.
• Trade-off: High computational cost on massive datasets. For multi-million-row production systems, vector indexes are typically added to switch to Approximate Nearest Neighbor (ANN) search.