Oracle Database 23ai: Vector Similarity Search - Exact, Approximate, and Multi-Vector Strategies

Oracle Database 23ai's AI Vector Search provides multiple strategies for finding similar vectors, each with different trade-offs between accuracy, speed, and resource usage. Understanding when to use exact search, approximate search, or multi-vector search—and knowing the essential vector functions—is crucial for building high-performance semantic search applications.

Understanding Similarity Search Types

1. Exact Similarity Search (Flat Search)

Exact similarity search calculates a query vector's distance to all other vectors. It's also called flat search or exhaustive search because every vector in the dataset is compared.

Characteristics:

• Gives the most accurate results
• Perfect search quality (100% recall)
• Involves potentially significant time as dataset grows
• No indexes required
• Suitable for small to medium datasets (thousands to hundreds of thousands of vectors)

SQL Example:

SELECT 
  product_id,
  product_name,
  VECTOR_DISTANCE(embedding, :query_vector, COSINE) AS similarity
FROM products
ORDER BY similarity
FETCH FIRST 10 ROWS ONLY;

When to Use:

• Small datasets where performance is acceptable
• When perfect accuracy is required
• Development and testing phases
• Benchmarking approximate search results

2. Approximate Similarity Search

Approximate similarity search uses vector indexes to dramatically speed up searches with minimal accuracy loss. Instead of checking every vector, it leverages specialized data structures to narrow the search space.

Key Requirements:

You must enable vector pool in the SGA for HNSW indexes (in-memory neighbor graph indexes).

ALTER SYSTEM SET vector_memory_size = 800M SCOPE=SPFILE;
-- Restart required

Characteristics:

• Can be more efficient but less accurate than exact search
• Uses target accuracy setting (typically 90-99%)
• Requires vector indexes (HNSW or IVF)
• Scales to millions or billions of vectors
• Typical accuracy: 95%+ with proper configuration

SQL Example:

-- Using FETCH APPROXIMATE
SELECT 
  product_id,
  product_name
FROM products
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Performance Comparison:

According to Oracle benchmarks, exact search on 50,000 vectors took 1.50 seconds versus 0.47 seconds with HNSW index—over 3x faster with the same top-10 results.

Vector Index Types

Oracle AI Vector Search supports two main types of vector indexes:

HNSW (Hierarchical Navigable Small World)

In-Memory Neighbor Graph vector index that creates a navigable graph structure for ultra-fast similarity search.

Creating HNSW Index:

CREATE VECTOR INDEX products_hnsw_idx
ON products(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

Characteristics:

• Fully built in-memory in the vector pool
• Extremely fast queries (sub-100ms)
• Requires substantial memory
• No DML operations allowed after creation
• Not available in RAC environments

Memory Calculation:

Formula: 1.3 times number_of_vectors times number_of_dimensions times dimension_byte_size

Example for 1M vectors, 768 dimensions, FLOAT32 (4 bytes):

1.3 × 1,000,000 × 768 × 4 = 3.99 GB

IVF (Inverted File Flat)

Neighbor Partition vector index built on disk with blocks cached in the buffer cache.

Creating IVF Index:

CREATE VECTOR INDEX products_ivf_idx
ON products(embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 95;

Characteristics:

• Disk-based, blocks cached in buffer cache
• More scalable for very large datasets
• Supports DML operations (may require periodic rebuild)
• Works in RAC environments
• Slightly slower than HNSW but still very fast

How IVF Works:

The index partitions vectors into clusters based on similarity. By default, the number of partitions equals the square root of the dataset size. During search, only relevant clusters are examined.

3. Multi-Vector Similarity Search

Multi-vector similarity search is usually used for multi-document search where documents are split into chunks, and chunks are embedded individually into vectors.

Use Cases:

• Long documents split into paragraphs or sections
• Product catalogs with multiple descriptions
• Research papers chunked for semantic search
• Multi-modal data (text + images)

How It Works:

1. Documents are split into chunks
2. Each chunk is embedded individually into a separate vector
3. Chunks are stored with partition keys linking them to parent documents
4. Search retrieves top chunks across all documents
5. Results can be aggregated by document

Uses partitions to organize chunks belonging to the same document.

Table Structure Example:

CREATE TABLE document_chunks (
  doc_id NUMBER,
  chunk_id NUMBER,
  chunk_text CLOB,
  embedding VECTOR(768, FLOAT32),
  PRIMARY KEY (doc_id, chunk_id)
) PARTITION BY RANGE (doc_id) (
  PARTITION p1 VALUES LESS THAN (1000),
  PARTITION p2 VALUES LESS THAN (2000),
  PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

Multi-Vector Search Query:

-- Find top chunks across all documents
SELECT 
  doc_id,
  chunk_id,
  chunk_text,
  VECTOR_DISTANCE(embedding, :query_vector, COSINE) AS score
FROM document_chunks
ORDER BY score
FETCH APPROXIMATE FIRST 20 ROWS ONLY;

Aggregating by Document:

-- Get top documents based on best chunk match
SELECT 
  doc_id,
  MIN(VECTOR_DISTANCE(embedding, :query_vector, COSINE)) AS best_score
FROM document_chunks
GROUP BY doc_id
ORDER BY best_score
FETCH FIRST 10 ROWS ONLY;

Narrowing Search Results

Attribute Filtering with WHERE Clause

Use the WHERE clause to filter results based on metadata or business attributes. You are not limited by the use of the ORDER BY clause.

This powerful combination enables hybrid search: semantic similarity (vector search) plus exact filters (traditional SQL).

Example: Filter by Category and Date

SELECT 
  product_id,
  product_name,
  category,
  COSINE_DISTANCE(embedding, :query_vector) AS similarity
FROM products
WHERE category = 'Electronics'
  AND launch_date greater-than DATE '2024-01-01'
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Example: Price Range Filter

SELECT 
  product_id,
  product_name,
  price,
  L2_DISTANCE(embedding, :query_vector) AS distance
FROM products
WHERE price BETWEEN 100 AND 500
  AND in_stock = 'Y'
ORDER BY embedding less-than hyphen greater-than :query_vector
FETCH APPROXIMATE FIRST 5 ROWS ONLY;

Best Practices for Filtering:

• Apply filters in WHERE clause before vector search
• Use indexed columns for better performance
• Combine multiple conditions as needed
• Leverage partition pruning for partitioned tables

Testing Other Distance Functions

Oracle provides shorthand distance functions that simplify syntax and improve code readability.

Distance Function Equivalents

L1_DISTANCE(v1, v2) is similar to Manhattan distance

SELECT L1_DISTANCE(
  VECTOR('[1, 2, 3]'),
  VECTOR('[4, 5, 6]')
) AS manhattan_dist
FROM DUAL;

L2_DISTANCE(v1, v2) is similar to Euclidean distance

SELECT L2_DISTANCE(
  VECTOR('[3, 0]'),
  VECTOR('[0, 4]')
) AS euclidean_dist
FROM DUAL;
-- Result: 5.0

COSINE_DISTANCE is the same as cosine

SELECT COSINE_DISTANCE(
  VECTOR('[1, 0]'),
  VECTOR('[1, 1]')
) AS cosine_dist
FROM DUAL;

INNER_PRODUCT(v1, v2) is the same as dot product

SELECT INNER_PRODUCT(
  VECTOR('[2, 3]'),
  VECTOR('[4, 5]')
) AS dot_product
FROM DUAL;
-- Result: 2*4 + 3*5 = 23

Note on DOT vs INNER_PRODUCT:

The VECTOR_DISTANCE function with DOT metric returns the negated inner product, while the INNER_PRODUCT function returns the actual dot product.

-- These are NOT equivalent:
SELECT VECTOR_DISTANCE(v1, v2, DOT) FROM table;     -- Returns -1 * dot_product
SELECT INNER_PRODUCT(v1, v2) FROM table;             -- Returns dot_product

Other Essential Vector Functions

1. Vector Constructors

TO_VECTOR() converts a string or a character large object (CLOB) to a vector

-- From string
SELECT TO_VECTOR('[1.5, 2.3, 4.1]') AS vec FROM DUAL;

-- From CLOB
DECLARE
  my_clob CLOB := '[0.1, 0.2, 0.3, 0.4]';
  my_vector VECTOR;
BEGIN
  my_vector := TO_VECTOR(my_clob);
END;
/

TO_VECTOR also takes another vector as input, adjusts its format, and returns the adjusted vector as output.

VECTOR() converts a string or CLOB into a vector

SELECT VECTOR('[1, 2, 3]', 3, FLOAT32) AS vec FROM DUAL;

TO_VECTOR and VECTOR are synonymous—they perform the same function.

2. Vector Serializer

VECTOR_SERIALIZE() converts a vector into a string or a CLOB

SELECT VECTOR_SERIALIZE(embedding) AS vec_string
FROM products
WHERE product_id = 1;

-- Result: '[0.12, 0.45, 0.78, ...]'

Use Cases:

• Exporting vectors for external processing
• Debugging and inspection
• Logging and auditing
• Integration with non-Oracle systems

3. Vector Norm

VECTOR_NORM() returns the Euclidean norm of a vector—the distance from the origin to the vector.

Also known as magnitude or length, it's calculated as the square root of the sum of squared components.

Formula:

norm = square_root(x1-squared + x2-squared + ... + xn-squared)

SQL Example:

SELECT VECTOR_NORM(TO_VECTOR('[3, 4]')) AS magnitude
FROM DUAL;
-- Result: 5.0 (because square_root(9+16) = 5)

Practical Use: Normalizing Vectors

-- Normalize vector to unit length
SELECT 
  embedding / VECTOR_NORM(embedding) AS normalized_vector
FROM products
WHERE product_id = 1;

Normalized vectors have a magnitude of 1.0, which is required for meaningful dot product similarity comparisons.

4. Vector Dimension Count

VECTOR_DIMENSION_COUNT() returns the number of dimensions of a vector

SELECT VECTOR_DIMENSION_COUNT(embedding) AS dimensions
FROM products
WHERE product_id = 1;

-- Result: 768 (for BERT-style embeddings)

Use Cases:

• Validating embedding dimensions
• Debugging dimension mismatches
• Dynamic schema inspection
• Migration validation

5. Vector Dimension Format

VECTOR_DIMENSION_FORMAT() returns the storage format of the vector

SELECT VECTOR_DIMENSION_FORMAT(embedding) AS format
FROM products
WHERE product_id = 1;

-- Possible results: 'INT8', 'FLOAT32', 'FLOAT64', 'BINARY'

Use Cases:

• Schema documentation
• Storage optimization analysis
• Migration planning
• Model compatibility verification

Complete Working Example

Here's a comprehensive example demonstrating all three search types:

-- Create table
CREATE TABLE research_papers (
  paper_id NUMBER PRIMARY KEY,
  title VARCHAR2(500),
  abstract CLOB,
  category VARCHAR2(100),
  publish_date DATE,
  embedding VECTOR(768, FLOAT32)
);

-- Insert sample data
INSERT INTO research_papers VALUES (
  1,
  'Advances in Vector Search',
  'This paper explores efficient algorithms...',
  'Computer Science',
  DATE '2024-06-15',
  VECTOR('[0.1, 0.2, ...]', 768, FLOAT32)
);
COMMIT;

-- 1. EXACT SIMILARITY SEARCH
-- Most accurate, slower for large datasets
SELECT 
  paper_id,
  title,
  COSINE_DISTANCE(embedding, :query_vector) AS similarity
FROM research_papers
WHERE category = 'Computer Science'
ORDER BY similarity
FETCH FIRST 10 ROWS ONLY;

-- 2. CREATE VECTOR INDEX for approximate search
CREATE VECTOR INDEX papers_hnsw_idx
ON research_papers(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- 3. APPROXIMATE SIMILARITY SEARCH
-- Faster, 95%+ accuracy
SELECT 
  paper_id,
  title,
  publish_date
FROM research_papers
WHERE category = 'Computer Science'
  AND publish_date greater-than DATE '2024-01-01'
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

-- 4. MULTI-VECTOR SEARCH (for chunked documents)
-- Create chunked version
CREATE TABLE paper_chunks (
  paper_id NUMBER,
  chunk_id NUMBER,
  chunk_text CLOB,
  embedding VECTOR(768, FLOAT32),
  PRIMARY KEY (paper_id, chunk_id)
);

-- Find best matching chunks
SELECT 
  paper_id,
  chunk_id,
  L2_DISTANCE(embedding, :query_vector) AS distance
FROM paper_chunks
ORDER BY distance
FETCH APPROXIMATE FIRST 20 ROWS ONLY;

-- Aggregate to document level
SELECT 
  p.paper_id,
  p.title,
  MIN(COSINE_DISTANCE(c.embedding, :query_vector)) AS best_match_score
FROM research_papers p
JOIN paper_chunks c ON p.paper_id = c.paper_id
GROUP BY p.paper_id, p.title
ORDER BY best_match_score
FETCH FIRST 10 ROWS ONLY;

Best Practices

1. Choose the Right Search Strategy

• Exact search: Less than 100K vectors, need perfect accuracy
• Approximate with HNSW: 100K to 10M vectors, need sub-100ms latency, have memory available
• Approximate with IVF: 10M+ vectors, limited memory, can tolerate slightly higher latency
• Multi-vector: Document chunks, multi-modal data, detailed granularity needed

2. Optimize Index Configuration

-- For high accuracy (slower but better results)
CREATE VECTOR INDEX idx1 ON table(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 99;

-- For speed (faster but slightly lower accuracy)
CREATE VECTOR INDEX idx2 ON table(embedding)
ORGANIZATION NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 90;

3. Use Attribute Filtering Effectively

Combine vector similarity with traditional filters to narrow results:

-- Efficient: Filter first, then search
SELECT * FROM products
WHERE price less-than 100              -- Traditional filter
  AND category = 'Electronics'         -- Traditional filter
ORDER BY embedding less-than equals greater-than :query_vector  -- Vector search
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

4. Monitor and Tune Performance

-- Check if index is being used
EXPLAIN PLAN FOR
SELECT * FROM products
ORDER BY embedding less-than equals greater-than :query_vector
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

5. Handle Dimension Mismatches

-- Validate dimensions before comparison
SELECT 
  VECTOR_DIMENSION_COUNT(embedding) AS dims,
  VECTOR_DIMENSION_FORMAT(embedding) AS format
FROM products
WHERE VECTOR_DIMENSION_COUNT(embedding) != 768;

Common Pitfalls and Solutions

Pitfall 1: Forgetting to Enable Vector Pool

Problem: HNSW index creation fails with memory error

Solution: Configure vector pool before creating HNSW indexes

ALTER SYSTEM SET vector_memory_size = 1G SCOPE=SPFILE;
-- Restart database

Pitfall 2: Using Wrong Index Type

Problem: Slow performance despite having an index

Solution: Match your workload to index type:

• HNSW for read-heavy, memory-available scenarios
• IVF for write-heavy or memory-constrained scenarios

Pitfall 3: Metric Mismatch

Problem: Index not being used despite proper syntax

Solution: Ensure query metric matches index metric

-- Index uses COSINE
CREATE VECTOR INDEX idx ON table(embedding)
DISTANCE COSINE;

-- Query must also use COSINE
-- Correct: Uses less-than equals greater-than (COSINE operator)
ORDER BY embedding less-than equals greater-than :query_vector

-- Wrong: Uses less-than hyphen greater-than (EUCLIDEAN operator)
-- This will NOT use the index!
ORDER BY embedding less-than hyphen greater-than :query_vector

Pitfall 4: Not Using FETCH APPROXIMATE

Problem: Query is slow despite having vector index

Solution: Use FETCH APPROXIMATE to enable index usage

-- Without APPROXIMATE - performs exact search
FETCH FIRST 10 ROWS ONLY;

-- With APPROXIMATE - uses index
FETCH APPROXIMATE FIRST 10 ROWS ONLY;

Oracle Database 23ai provides a comprehensive vector search framework with multiple strategies to fit different use cases:

Search Strategies:

• Exact: Perfect accuracy, suitable for smaller datasets
• Approximate: 95%+ accuracy with dramatic speed improvements using HNSW or IVF indexes
• Multi-vector: Chunk-level granularity for document search

Key Requirements:

• Enable vector pool for HNSW indexes: vector_memory_size
• Use FETCH APPROXIMATE for index-accelerated queries
• Match distance metrics between index and query

Essential Functions:

• Constructors: TO_VECTOR(), VECTOR()
• Serializer: VECTOR_SERIALIZE()
• Utilities: VECTOR_NORM(), VECTOR_DIMENSION_COUNT(), VECTOR_DIMENSION_FORMAT()
• Distance Functions: L1_DISTANCE(), L2_DISTANCE(), COSINE_DISTANCE(), INNER_PRODUCT()

Best Practices:

• Choose search strategy based on dataset size and accuracy requirements
• Combine vector search with WHERE clause filters for powerful hybrid queries
• Monitor index usage with EXPLAIN PLAN
• Normalize vectors when using dot product similarity

By understanding these search strategies and vector functions, you can build high-performance semantic search applications that scale from thousands to billions of vectors while maintaining excellent accuracy and query speed.