Question

Oracle AI Vector Search Workflow: From Data to Semantic Search

Oracle Database 23ai introduces AI Vector Search, enabling semantic search capabilities directly within the database. Unlike traditional keyword-based searches, vector search understands the meaning and context behind your queries, allowing you to find relevant information even when exact keywords don't match.

What Are Vector Embeddings?

Vector embeddings are mathematical representations that capture the semantic meaning of your data. They convert unstructured data like text, images, or audio into numerical vectors that can be mathematically compared.

Key Properties:

• Each embedding is an array of numbers (typically 128 to 1536 dimensions)
• Semantically similar content has vectors that are close together in vector space
• Distance between vectors indicates similarity in meaning

The Oracle AI Vector Search Workflow

The workflow consists of four primary steps:

Step 1: Generate Vector Embeddings

You can generate embeddings either within or outside Oracle Database.

Option A: Within Oracle Database (ONNX)

Load ONNX embedding models directly into the database:

-- Load the embedding model
EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL(
    'DM_DUMP',
    'all_MiniLM_L12_v2.onnx',
    'doc_model'
);

-- Generate embedding from text string
SELECT TO_VECTOR(
    VECTOR_EMBEDDING(doc_model USING 'hello world' AS data)
) AS embedding;

Common Embedding Patterns:

1. Convert Text String to Embedding:

SELECT VECTOR_EMBEDDING(doc_model USING 'semantic search' AS data) AS vec;

2. Convert File → Text → Chunks → Embeddings:

INSERT INTO doc_chunks (doc_id, chunk_id, chunk_data, embedding)
SELECT 
    d.id,
    JSON_VALUE(c.column_value, '$.chunk_id' RETURNING NUMBER),
    JSON_VALUE(c.column_value, '$.chunk_data'),
    VECTOR_EMBEDDING(doc_model USING 
        JSON_VALUE(c.column_value, '$.chunk_data') AS data)
FROM documents d,
    DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
        DBMS_VECTOR_CHAIN.UTL_TO_TEXT(d.pdf_content),
        JSON('{"by":"words", "max":"100", "overlap":"20", 
              "split":"recursively", "language":"american"}')
    ) c;

Chunking Parameters:

• by: Chunking method (words, sentences, paragraphs)
• max: Maximum size per chunk (e.g., 100 words)
• overlap: Overlapping units between chunks (prevents context loss)
• split: Splitting strategy (recursively, custom)

3. Convert Files to Embeddings:

SELECT VECTOR_EMBEDDING(image_model USING file_content AS data) AS vec
FROM media_files;

4. End-to-End Pipeline:

INSERT INTO vectorized_docs
SELECT 
    d.doc_id,
    chunks.chunk_data,
    VECTOR_EMBEDDING(doc_model USING chunks.chunk_data AS data)
FROM source_documents d,
    DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
        DBMS_VECTOR_CHAIN.UTL_TO_TEXT(d.pdf_blob),
        JSON('{"by":"words", "max":"100", "overlap":"20"}')
    ) chunks;

Option B: Outside Oracle Database

Generate embeddings using third-party services or local models:

from sentence_transformers import SentenceTransformer
import oracledb

model = SentenceTransformer('all-MiniLM-L6-v2')
embedding = model.encode("Oracle AI Vector Search")

connection = oracledb.connect(user="user", password="pwd", dsn="dsn")
cursor = connection.cursor()
cursor.execute(
    "INSERT INTO documents (id, text, embedding) VALUES (:1, :2, :3)",
    [1, "Oracle AI Vector Search", embedding.tolist()]
)
connection.commit()

In-Database Advantages: No data movement, reduced latency, simplified architecture, enterprise security

External Advantages: Flexibility in model choice, access to latest models, independent scaling

Step 2: Store Vector Embeddings

Store embeddings in columns with the VECTOR data type within standard relational tables:

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(200),
    description CLOB,
    price NUMBER(10,2),
    category VARCHAR2(100),
    description_vector VECTOR(384, FLOAT32)
);

-- Insert with inline embedding generation
INSERT INTO products (product_id, product_name, description, description_vector)
VALUES (
    1001,
    'Wireless Headphones',
    'Noise-cancelling Bluetooth headphones',
    VECTOR_EMBEDDING(doc_model USING 'Noise-cancelling Bluetooth headphones' AS data)
);

Supported Formats: INT8 (memory efficient), FLOAT32 (balanced), FLOAT64 (highest precision)

Step 3: Create Vector Indexes

For large datasets, vector indexes are essential for performance.

Without Index: O(n) complexity - compares against every vector (slow for large datasets)
With Index: O(log n) complexity - checks subset of vectors (dramatically faster)

Index Types:

1. HNSW (In-Memory, Fast):

CREATE VECTOR INDEX product_hnsw_idx ON products (description_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- Configure memory
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 2G SCOPE=BOTH;

2. IVF (Storage-Based, Scalable):

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

3. Hybrid (Text + Semantic):

CREATE VECTOR INDEX product_hybrid_idx ON products (description)
ORGANIZATION HYBRID
PARAMETERS('EMBEDDING MODEL doc_model');

Choosing the Right Index:

• HNSW: Dataset fits in memory, need maximum speed
• IVF: Very large datasets or limited memory
• Hybrid: Need both keyword and semantic search

Step 4: Query with Similarity Search

Use native SQL with specialized functions for similarity searches:

Basic Similarity Search:

SELECT 
    product_name,
    description,
    VECTOR_DISTANCE(
        description_vector,
        VECTOR_EMBEDDING(doc_model USING 'wireless audio device' AS data),
        COSINE
    ) AS similarity_score
FROM products
ORDER BY similarity_score
FETCH FIRST 5 ROWS ONLY;

Shorthand Operators:

-- <=> is cosine distance
SELECT product_name
FROM products
ORDER BY description_vector <=> 
    VECTOR_EMBEDDING(doc_model USING 'wireless audio device' AS data)
FETCH FIRST 5 ROWS ONLY;

Combining Semantic and Relational Searches:

This is Oracle's key advantage—combining semantic similarity with business logic:

-- Find similar products within price range and category
SELECT 
    product_name,
    price,
    category,
    VECTOR_DISTANCE(description_vector, :query_vec, COSINE) AS similarity
FROM products
WHERE price BETWEEN 100 AND 500
    AND category = 'Electronics'
ORDER BY similarity
FETCH FIRST 10 ROWS ONLY;

Advanced Hybrid Query:

SELECT 
    p.category,
    COUNT(*) AS matching_products,
    AVG(p.price) AS avg_price,
    MIN(VECTOR_DISTANCE(p.description_vector, :search_vec, COSINE)) AS best_match
FROM products p
WHERE p.price < 1000 AND p.in_stock = 'Y'
GROUP BY p.category
HAVING COUNT(*) > 5
ORDER BY best_match
FETCH FIRST 3 ROWS ONLY;

Distance Metrics:

Metric	Operator	Best For
COSINE	<=>	Text embeddings
EUCLIDEAN	<->	General purpose
DOT	N/A	Normalized vectors
MANHATTAN	N/A	Specific applications

Complete End-to-End Example

Building a semantic search system for customer support:

-- 1. Load model
EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL('MODEL_DIR', 'all-MiniLM-L12-v2.onnx', 'support_model');

-- 2. Create table
CREATE TABLE support_articles (
    article_id NUMBER PRIMARY KEY,
    title VARCHAR2(500),
    content CLOB,
    category VARCHAR2(100),
    content_vector VECTOR(384, FLOAT32)
);

-- 3. Insert with embeddings
INSERT INTO support_articles (article_id, title, content, category, content_vector)
VALUES (
    1,
    'How to Reset Your Password',
    'To reset your password, navigate to the login page...',
    'Account Management',
    VECTOR_EMBEDDING(support_model USING 'To reset your password...' AS data)
);

-- 4. Create index
CREATE VECTOR INDEX support_vec_idx ON support_articles (content_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- 5. Semantic search
SELECT 
    title,
    category,
    VECTOR_DISTANCE(
        content_vector,
        VECTOR_EMBEDDING(support_model USING 'cannot log into my account' AS data),
        COSINE
    ) AS relevance_score
FROM support_articles
ORDER BY relevance_score
FETCH FIRST 5 ROWS ONLY;

Even though the query "cannot log into my account" doesn't match exact keywords, it will find articles about password reset, account recovery, and login troubleshooting.

Document Processing Pipeline

Process PDFs into searchable chunks:

-- Source documents table
CREATE TABLE source_docs (
    doc_id NUMBER PRIMARY KEY,
    filename VARCHAR2(500),
    file_content BLOB
);

-- Chunked content table
CREATE TABLE doc_chunks (
    chunk_id NUMBER PRIMARY KEY,
    doc_id NUMBER,
    chunk_text VARCHAR2(4000),
    chunk_vector VECTOR(384, FLOAT32)
);

-- Process: PDF → Text → Chunks → Vectors
INSERT INTO doc_chunks (chunk_id, doc_id, chunk_text, chunk_vector)
SELECT 
    ROW_NUMBER() OVER (ORDER BY d.doc_id),
    d.doc_id,
    JSON_VALUE(c.column_value, '$.chunk_data'),
    VECTOR_EMBEDDING(doc_model USING 
        JSON_VALUE(c.column_value, '$.chunk_data') AS data)
FROM source_docs d,
    DBMS_VECTOR_CHAIN.UTL_TO_CHUNKS(
        DBMS_VECTOR_CHAIN.UTL_TO_TEXT(d.file_content),
        JSON('{"by":"words", "max":"100", "overlap":"20"}')
    ) c;

Best Practices

Chunking:

• Use 20-30% overlap to prevent context loss
• 50-100 words for precise matching, 200-300 for broader context

Index Configuration:

• Set TARGET ACCURACY between 90-99%
• Allocate sufficient memory for HNSW indexes
• Monitor and rebuild IVF indexes periodically

Query Optimization:

• Use FETCH FIRST n ROWS ONLY for better performance
• Combine vector search with filters early
• Use bind variables for query vectors
• Prefer approximate search for most queries

Vector Formats:

• FLOAT32: Best balance (default choice)
• INT8: When memory is constrained
• FLOAT64: Rarely needed

Performance Considerations

Memory Requirements (HNSW):

Memory = 1.3 × (format_size) × (dimensions) × (row_count)
Example: 1M vectors × 384 dims × 4 bytes × 1.3 = ~2 GB

Typical Performance:

• Exact search: Seconds to hours
• Approximate (HNSW): Milliseconds to seconds
• Approximate (IVF): Seconds for billion-scale datasets

Use Cases

• E-commerce product search
• Customer support article matching
• Document management systems
• Fraud detection
• Recommendation engines
• Image similarity search
• Code search with natural language

Workflow Summary

1. Generate Vector Embeddings: Load ONNX model or use external APIs, convert data to vectors
2. Store Vector Embeddings: Create tables with VECTOR columns alongside business data
3. Create Vector Indexes: Choose HNSW, IVF, or Hybrid based on dataset size and requirements
4. Query with Similarity Search: Use native SQL combining semantic and relational searches

Key Advantages:

• Unified system (vectors + business data)
• Native SQL with vector extensions
• Enterprise features (security, availability, performance)
• Flexible deployment (in-database or external)
• Scalable (thousands to billions of vectors)
• Hybrid queries (semantic + relational filters)

Oracle Database 23ai eliminates data fragmentation by keeping AI algorithms where the data lives, reducing complexity while providing enterprise-grade security and performance.