Oracle AI Vector Search in 23ai: A DBA’s Step-by-Step Guide from ONNX to HNSW Indexes

 When Oracle 23ai first introduced AI Vector Search, my initial reaction as a DBA was a mix of excitement and skepticism. Great, another buzzword, I thought. Are we now expected to manage vector spaces alongside tablespaces, undo logs, and RMAN backups?

Turns out, yes—and honestly, it's pretty brilliant.

Instead of moving millions of documents out of the database into a separate external vector store (like Pinecone or Milvus) and dealing with synchronization issues, network latency, and security headaches, Oracle lets us run semantic similarity searches inside the database engine. Your business data and your embeddings live together.

If you’re an Oracle DBA wondering how this actually works under the hood and how to set it up without breaking production, grab a coffee. Let’s break it down step-by-step.

Architecture Breakdown: What’s Happening Inside Oracle 23ai?

If you are wondering how this all fits together visually, let's break down the two core pillars shown in our architecture graphic: In-Database ONNX Execution on the left, and the Multi-Layer HNSW Graph Index on the right.

Part 1: ONNX Model & In-Database Pipeline (Left Side)

• Bringing the Brain to the Data: In traditional microservice architectures, your application extracts text, sends it across the network to an external Python service or cloud API, receives the vector array back, and then writes it to the database.

• The Oracle 23ai Approach: As illustrated on the left side of the diagram, the .onnx embedding model file is loaded directly into the database's file directory and managed within the database engine via DBMS_VECTOR.

• Zero Data Movement: When raw text or files hit your tables, the database executes the model internally (VECTOR_EMBEDDING). This ensures that your sensitive business data never leaves the secure boundaries of your database storage, maintaining strict enterprise compliance and zero network overhead.

Part 2: The Multi-Layer HNSW Index Structure (Right Side)

• Navigating High-Dimensional Space: When you run a semantic search across millions of rows, scanning every single vector (O(n)) destroys query performance. The HNSW (Hierarchical Navigable Small World) index solves this by building a multi-tier transit map in your SGA's Vector Memory Pool (VECTOR_MEMORY_SIZE).

• Layered Navigation ($O(\log n)$ Performance):

  • Top Layers (Express Routes): Contain sparse, long-distance connections. When a search query is initiated, the algorithm starts here to make massive "jumps" across distant regions of the vector space, instantly skipping irrelevant data clusters.

  • Middle Layers (Regional Transit): The search path narrows down, stepping closer to the target cluster.

  • Base Layer (Layer 0): This contains all your actual data vectors densely interconnected. The search finishes here by evaluating immediate neighbors to pinpoint the exact closest semantic match with near-100% precision.

Wait, why should a DBA care about Vector Search?

That was the first question I asked myself. As DBAs, we manage structured relational data. Why are we talking about math vectors?

The reality is simple: application developers are building Retrieval-Augmented Generation (RAG) and semantic search features into everything. If we don’t understand how vectors, ONNX models, and HNSW indexes consume memory and impact the SGA, we’re going to be left troubleshooting performance issues we've never seen before.

Keeping this workload in-database gives us massive advantages:

1. Zero Data Movement: No moving sensitive enterprise data to external third-party cloud APIs.

2. ACID Compliance: Your vector data is transactional. If a row is deleted or updated, your vector state stays consistent.

3. Enterprise Security: Row-level security (RLS) and existing Oracle audit policies automatically apply to your vector queries.

Step 1: Demystifying ONNX — Bringing the Brain to the Data

Self-check: What actually is an ONNX model, and why do we load it into Oracle instead of using Python scripts externally?

ONNX (Open Neural Network Exchange) is an open standard format for machine learning models. Instead of calling an external Python script or a cloud API every time a user inputs text, Oracle 23ai lets you load a pre-trained embedding model (like all_MiniLM_L12_v2) directly into the database container storage.

Let's load our ONNX model into a database directory:

SQL

-- 1. Create or map your directory where the .onnx file is stored on the OS
CREATE OR REPLACE DIRECTORY dm_dump AS '/u01/app/oracle/model_files';

-- 2. Load the ONNX model into the database repository
EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL(
    'DM_DUMP',
    'all_MiniLM_L12_v2.onnx',
    'doc_model'
);

Once that's loaded, you can test generating a vector embedding straight from SQL:

SQL

SELECT TO_VECTOR(
    VECTOR_EMBEDDING(doc_model USING 'Oracle AI Vector Search is powerful' AS data)
) AS sample_embedding;

Boom. Just like that, text becomes a dense array of numbers (typically 384 dimensions for this model).

Step 2: Storing Vectors in Relational Tables

Now, how do we store these? Do we need a new data type? Yes, Oracle 23ai introduced the native VECTOR data type. Let's create a sample product catalog table that combines traditional relational columns with our new vector column.

SQL

CREATE TABLE products (
    product_id          NUMBER PRIMARY KEY,
    product_name        VARCHAR2(200),
    description         CLOB,
    price               NUMBER(10,2),
    category            VARCHAR2(100),
    -- Vector column storing 384 dimensions in 32-bit floating point format
    description_vector  VECTOR(384, FLOAT32)
);

Tip: You can use INT8, FLOAT32, or FLOAT64 depending on your precision needs and memory constraints. For most use cases, FLOAT32 hits the sweet spot.

Let's insert a row and generate the vector inline using our ONNX model:

SQL

INSERT INTO products (product_id, product_name, description, price, category, description_vector)
VALUES (
    1001,
    'Enterprise Wireless Headset',
    'Noise-cancelling Bluetooth headset with 30-hour battery and mic for office calls',
    149.99,
    'Electronics',
    VECTOR_EMBEDDING(doc_model USING 'Noise-cancelling Bluetooth headset with 30-hour battery and mic for office calls' AS data)
);
COMMIT;

Step 3: Demystifying HNSW — Navigating the Vector Space Fast

Brainstorming moment: If I have 10 million rows, what happens when a user runs a similarity search without an index?

The database has to perform an exhaustive search—calculating the mathematical distance between the query vector and every single vector in the table ($O(n)$ complexity). In a production environment with millions of rows, that query will crawl to a halt.

To fix this, we build Approximate Nearest Neighbor (ANN) indexes. As detailed in our architecture overview above, HNSW (Hierarchical Navigable Small World) builds a multi-layer graph structure inside your SGA's Vector Memory Pool to achieve logarithmic $O(\log n)$ query speeds.

First, ensure your vector memory pool is configured:

SQL

ALTER SYSTEM SET VECTOR_MEMORY_SIZE = 2G SCOPE=BOTH;

Then create the HNSW index:

SQL

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

(Alternative: If your dataset is massive and won't fit entirely in memory, you can use an IVF index (ORGANIZATION NEIGHBOR PARTITIONS) which clusters vectors and stores them on disk instead.)

Step 4: Querying Data with Semantic & Hybrid Search

This is where everything comes together. Let's run a similarity search to find products similar to a vague user query like "comfortable wireless audio device".

SQL

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

(Note: You can also use the handy shorthand operator <=> for cosine distance: ORDER BY description_vector <=> VECTOR_EMBEDDING(...))

The Real DBA Superpower: True Hybrid Queries

Other dedicated vector databases struggle when you want to mix semantic matching with hard relational business logic. In Oracle, it's just standard SQL:

SQL

-- Find semantically similar products, but strictly enforce price and category filters!
SELECT 
    product_name,
    price,
    category,
    VECTOR_DISTANCE(description_vector, VECTOR_EMBEDDING(doc_model USING 'wireless audio device' AS data), COSINE) AS distance
FROM products
WHERE price BETWEEN 50 AND 200
  AND category = 'Electronics'
ORDER BY distance
FETCH FIRST 5 ROWS ONLY;

Recommended Further Reading & Backlinks

To expand your Oracle 23ai and performance tuning setup, check out these technical resources:

• For deep-dives into foundational AI vector concepts and comprehensive pipelines, review the official Oracle AI Vector Search Guide.

• If you are managing database memory pools or troubleshooting Linux kernel parameters alongside your SGA configurations, check out my previous post on Linux Kernel Tweaks for High-Performance Oracle Instances to ensure your system memory is fully optimized.

• For complete step-by-step code workflows regarding text extraction and chunking, refer to Derrick Ryan's breakdown on the Oracle AI Vector Search Workflow on Medium.

Wrapping Up

Oracle AI Vector Search bridges the gap between traditional enterprise relational databases and modern AI applications. As DBAs, we don't need to fear vector workloads—we just need to understand how to size our VECTOR_MEMORY_SIZE, leverage native ONNX models, and index data using multi-layer HNSW graphs.

Have you tried setting up vector search in 23ai yet? What kind of performance numbers are you seeing? Drop a comment below and let’s talk database internals!