Oracle AI Vector Search: Querying Vectors and Optimizing with Indexes

Oracle Database 23ai's AI Vector Search enables powerful semantic search capabilities, but to truly harness its potential, you need to understand how to query vector data efficiently and when to use vector indexes. This guide explores querying vectors and optimizing performance with specialized vector indexes.

Running Basic Queries on Vectors

Combining Vector and Relational Data

One of Oracle AI Vector Search's most powerful features is the ability to combine vector data with traditional relational data in a single query. This unified approach eliminates data fragmentation and allows you to perform semantic searches alongside business logic filters.

Basic Query Pattern:

-- Create table with both relational and vector data
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(200),
    description CLOB,
    price NUMBER(10,2),
    category VARCHAR2(100),
    in_stock VARCHAR2(1),
    description_vector VECTOR(384, FLOAT32)
);

-- Insert sample data
INSERT INTO products VALUES (
    1001,
    'Wireless Headphones',
    'Premium noise-cancelling Bluetooth headphones with 30-hour battery life',
    299.99,
    'Electronics',
    'Y',
    VECTOR_EMBEDDING(doc_model USING 
        'Premium noise-cancelling Bluetooth headphones with 30-hour battery life' AS data)
);

-- Query combining vector similarity with relational filters
SELECT 
    product_name,
    price,
    category,
    VECTOR_DISTANCE(
        description_vector,
        VECTOR_EMBEDDING(doc_model USING 'wireless audio device' AS data),
        COSINE
    ) AS similarity_score
FROM products
WHERE price < 500
    AND in_stock = 'Y'
    AND category = 'Electronics'
ORDER BY similarity_score
FETCH FIRST 10 ROWS ONLY;

Selecting Vector Data

You can select vector data using the asterisk (*) keyword just like any other column:

-- Select all columns including vectors
SELECT * FROM products;

-- Select specific columns including the vector
SELECT product_id, product_name, description_vector 
FROM products;

-- Vector dimensions can be extracted
SELECT 
    product_name,
    VECTOR_DIMENSION_COUNT(description_vector) AS dimensions,
    VECTOR_DIMENSION_FORMAT(description_vector) AS format
FROM products;

Important Restriction: No Comparison Operators Between Vectors

Standard comparison operators (such as =, >, <, >=, <=, !=) are not allowed between vectors. You cannot directly compare vectors using these operators.

** Invalid Operations:**

-- These will cause errors
SELECT * FROM products WHERE description_vector = other_vector;
SELECT * FROM products WHERE description_vector > other_vector;
SELECT * FROM products WHERE description_vector < other_vector;

** Valid Operations:**

Instead, use specialized vector distance functions:

-- Correct: Use VECTOR_DISTANCE function
SELECT 
    product_name,
    VECTOR_DISTANCE(description_vector, :query_vector, COSINE) AS distance
FROM products
ORDER BY distance
FETCH FIRST 5 ROWS ONLY;

-- Correct: Use shorthand operators
SELECT product_name
FROM products
ORDER BY description_vector <=> :query_vector  -- Cosine distance
FETCH FIRST 5 ROWS ONLY;

Understanding Vector Indexes

Vector indexes are specialized data structures that dramatically improve query performance on vector data. Without indexes, every similarity search performs an exhaustive comparison against all vectors in the table—a process that becomes prohibitively slow as datasets grow.

Why Vector Indexes Matter

The Problem:

• Exhaustive Search: Compares query vector against every vector in the table
• Time Complexity: O(n) - grows linearly with dataset size
• Real Impact: Searches that take seconds can become hours-long operations

The Solution:
Vector indexes use sophisticated techniques to reduce the search space:

• Clustering: Groups similar vectors together
• Partitioning: Divides the vector space into manageable regions
• Neighbor Graphs: Creates efficient navigation paths through vector space

The Result:
Vector indexes greatly reduce search space, making searches appear quick and extremely efficient, often reducing search time from hours to milliseconds.

Types of Vector Indexes

Oracle AI Vector Search supports three types of vector indexes:

1. In-Memory Neighbor Graph (HNSW) Index

HNSW (Hierarchical Navigable Small World) is a graph-based index that provides the fastest query performance.

Characteristics:

• In-memory only index that can require a lot of memory for large datasets
• Extremely fast query performance (milliseconds)
• Uses hierarchical layers for efficient navigation
• Best for datasets that fit in available memory

Creating an HNSW Index:

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

Memory Requirements:

To roughly determine the memory size needed to store an HNSW index, use the following formula: 1.3 * number of vectors * number of dimensions * size of your vector dimension type

Example Calculation:

1 million vectors × 384 dimensions × 4 bytes (FLOAT32) × 1.3 = ~2 GB

2. Neighbor Partition (IVF) Index

IVF (Inverted File Flat) is a storage-based index that balances search quality with reasonable speed.

Characteristics:

• Storage-based index not constrained by memory like HNSW
• Uses buffer cache and disk storage
• Can be used for very large datasets and still provide excellent performance compared to exhaustive similarity search
• Supports global and local partitioning

Creating an IVF Index:

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

When to Use IVF:

• Dataset is too large to fit in memory
• RAM is limited or constrained
• Need to support very large (billion-scale) datasets
• Working with partitioned tables

3. Hybrid Vector Index

Hybrid indexes combine full-text search with semantic vector search in a single index.

Creating a Hybrid Index:

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

Benefits:

• Single index for both keyword and semantic searches
• Run textual queries, vector similarity queries, or hybrid queries
• Ideal for applications needing both search approaches

Vector Pool Memory Configuration

The Vector Pool is memory allocated in SGA to store Hierarchical Navigable Small World (HNSW) vector indexes and all associated metadata. Proper configuration is essential for HNSW index performance.

Configuring Vector Memory Size

At CDB (Container Database) Level

At the CDB level, VECTOR_MEMORY_SIZE specifies the current size of the Vector Pool:

-- Set vector memory size at CDB level
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 1G SCOPE=BOTH;

Note: A database restart is required to apply this parameter change.

At PDB (Pluggable Database) Level

The maximum PDB VECTOR_MEMORY_SIZE value is limited to 70% of the PDB sga_target. PDBs inherit settings from the CDB but can specify their own maximum usage.

-- Check current SGA target
SELECT value FROM V$PARAMETER WHERE name='sga_target';

-- PDB inherits from CDB and can set maximum
-- Maximum = 70% of PDB's sga_target

Automatic Vector Pool Growth

Oracle 23ai supports automatic vector pool management:

If VECTOR_MEMORY_SIZE is set to 1 and the sga_target is greater than 0 at CDB initialization, HNSW index creation will automatically grow the vector memory pool to satisfy the new index

-- Enable automatic growth
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 1 SCOPE=SPFILE;

Memory Considerations

Important Points:

• Vector indexes are stored in this pool along with vector metadata
• HNSW indexes require significant RAM
• RAM constrains the vector index size
• IVF indexes use buffer cache and disk, not heavily dependent on Vector Pool
• IVF indexes use memory either from the vector pool if defined or the shared pool to speed up index creation and during DML operations

Monitoring Vector Pool:

-- Check vector pool allocation and usage
SELECT 
    pool,
    alloc_bytes,
    used_bytes,
    populate_status
FROM V$VECTOR_MEMORY_POOL;

-- Check individual index memory usage
SELECT 
    owner,
    index_name,
    index_organization,
    allocated_bytes,
    used_bytes,
    num_vectors
FROM V$VECTOR_INDEX;

Choosing the Right Index Type

Decision Matrix

Practical Guidelines

Use HNSW When:

• Dataset fits comfortably in available memory
• Query speed is critical (real-time applications)
• You can allocate sufficient Vector Pool memory
• Working with up to millions of vectors

Use IVF When:

• Dataset is too large for available memory
• Working with billions of vectors
• RAM is limited or expensive
• Using partitioned tables for very large datasets

Use Hybrid When:

• Application needs both keyword and semantic search
• Users perform mixed query types
• Want to simplify index management

Performance Optimization Tips

1. Index Configuration

Target Accuracy:

-- Higher accuracy = slower but more precise
WITH TARGET ACCURACY 99;  -- Very precise

-- Lower accuracy = faster but less precise  
WITH TARGET ACCURACY 90;  -- Still good for most cases

Balance accuracy with performance needs. TARGET ACCURACY between 90-99% provides excellent results.

2. Memory Allocation

-- Allocate adequate memory for HNSW indexes
-- Use formula: 1.3 × vectors × dimensions × format_size

-- Example for 1M vectors, 384 dimensions, FLOAT32:
-- 1.3 × 1,000,000 × 384 × 4 = ~2 GB
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 2G SCOPE=BOTH;

3. Query Optimization

-- Use FETCH FIRST for better performance
SELECT product_name, similarity_score
FROM (
    SELECT 
        product_name,
        VECTOR_DISTANCE(description_vector, :query_vec, COSINE) AS similarity_score
    FROM products
    WHERE category = 'Electronics'  -- Apply filters early
)
ORDER BY similarity_score
FETCH FIRST 10 ROWS ONLY;  -- Limit results

4. Approximate vs. Exact Search

-- Approximate search (uses index, faster)
SELECT product_name
FROM products
ORDER BY VECTOR_DISTANCE(description_vector, :query_vec, COSINE)
FETCH FIRST 10 ROWS ONLY;

-- Exact search (no index, 100% accurate, slower)
SELECT product_name
FROM products
ORDER BY VECTOR_DISTANCE(description_vector, :query_vec, COSINE)
FETCH EXACT FIRST 10 ROWS ONLY;

Use approximate search for most queries; reserve exact search for critical accuracy requirements.

Real-World Example: E-commerce Product Search

-- Step 1: Create table
CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    name VARCHAR2(500),
    description CLOB,
    price NUMBER(10,2),
    category VARCHAR2(100),
    rating NUMBER(3,2),
    reviews_count NUMBER,
    desc_vector VECTOR(384, FLOAT32)
);

-- Step 2: Configure memory (1M products × 384 dims × 4 bytes × 1.3 = ~2GB)
ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 2G SCOPE=BOTH;

-- Step 3: Create HNSW index
CREATE VECTOR INDEX products_hnsw_idx ON products (desc_vector)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;

-- Step 4: Query with combined filters
SELECT 
    name,
    price,
    rating,
    VECTOR_DISTANCE(
        desc_vector,
        VECTOR_EMBEDDING(model USING 'comfortable running shoes' AS data),
        COSINE
    ) AS relevance
FROM products
WHERE price BETWEEN 50 AND 200
    AND category = 'Footwear'
    AND rating >= 4.0
    AND reviews_count > 100
ORDER BY relevance
FETCH FIRST 10 ROWS ONLY;

Key Takeaways

1. Queries can combine vector and relational data in a single SQL statement, eliminating data fragmentation
2. Standard comparison operators (=, >, <) cannot be used between vectors - use VECTOR_DISTANCE functions instead
3. Vector indexes dramatically improve performance by reducing search space through clustering, partitioning, and graphs
4. HNSW indexes provide fastest queries but require significant memory (Formula: 1.3 × vectors × dimensions × format_size)
5. IVF indexes work for very large datasets using disk storage, not constrained by RAM
6. Vector Pool memory must be configured for HNSW indexes at CDB level with VECTOR_MEMORY_SIZE parameter
7. Database restart required when changing VECTOR_MEMORY_SIZE (unless using automatic growth)
8. PDB maximum is 70% of sga_target, inheriting from CDB configuration
9. Choose index type based on dataset size and memory availability - HNSW for speed, IVF for scale

By understanding these concepts and properly configuring vector indexes, you can build high-performance semantic search applications that seamlessly integrate AI capabilities with traditional relational database operations.