In Post -2 of this series, We had a vector search query running clean in development — ten milliseconds, HNSW index in the plan, results looking right. The moment the same query hit a table with eight million rows in staging, response time jumped to forty seconds. The execution plan had quietly switched to TABLE ACCESS FULL. The HNSW index existed, status was VALID, but Oracle was not using it.
The root cause was vector_memory_size — zero. Oracle had built the HNSW graph but had nowhere to hold it in memory. Without the Vector Memory Pool, the graph falls back to disk-based traversal and the optimizer correctly decides a full scan is cheaper.
That is the kind of detail that does not surface in a development environment with a hundred rows. It surfaces in staging at 2 AM. Before we build vector indexes in production, we need to understand exactly what Oracle is doing under the hood — HNSW, IVF, memory, and how to verify every step.
Environment: Oracle AI Database 26ai Free Developer Release on-premises. We continue with the ora_errors dataset from Post 2.
First — Check vector_memory_size Before Anything Else
SELECT name, value
FROM v$parameter
WHERE name = 'vector_memory_size';
NAME VALUE
------------------- -----
vector_memory_size 0
Zero means Oracle has not allocated a dedicated Vector Memory Pool. HNSW graphs will attempt to use shared pool memory — creating contention with SQL parsing and PL/SQL on any busy system. Size it before building any HNSW index:
-- Sizing formula:
-- dimensions × 4 bytes × row_count × 1.3 (overhead factor)
-- Production example: 1536-dim × 10M rows × 4 bytes × 1.3 ≈ 80 GB
-- For our demo: trivial — 512M is more than sufficient
ALTER SYSTEM SET vector_memory_size = 512M SCOPE = SPFILE;
-- Bounce required — plan this before go-live, not after
Set this in SPFILE. It does not take effect dynamically. This is the parameter that silently kills HNSW index usage in production when it is missing — and it will not throw an error, it will just fall back to full scan.
HNSW — The Graph-Based Algorithm
HNSW stands for Hierarchical Navigable Small World. During index build, Oracle constructs a multi-layer graph — each vector is a node, edges connect it to its nearest neighbors. Upper layers are sparse for fast long-range navigation, lower layers are dense for precise neighborhood resolution.
At query time Oracle enters at the top layer, navigates downward following edges toward the query vector, and surfaces the approximate nearest neighbors at the bottom.
The entire graph lives in the Vector Memory Pool. That is why the parameter matters.
CREATE VECTOR INDEX ora_errors_hnsw_idx
ON ora_errors (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95
PARAMETERS (type HNSW, neighbors 16, efconstruction 100);
What these parameters control:
- NEIGHBORS 16 — how many nearest neighbors each node connects to during graph construction. Higher values improve recall but increase both build time and memory. Production default is 32.
- EFCONSTRUCTION 100 — the candidate list size during graph build. Higher values build a better graph, slower build time. 100 is a solid starting point.
- TARGET ACCURACY 95 — Oracle targets 95% recall. It may miss up to 5% of true nearest neighbors in exchange for query speed. Raise to 98 or 99 if business requirements demand higher precision.
Verify:
SELECT index_name, index_type, status, num_rows
FROM user_indexes
WHERE table_name = 'ORA_ERRORS';
INDEX_NAME INDEX_TYPE STATUS NUM_ROWS
--------------------- ---------- ------ --------
ORA_ERRORS_HNSW_IDX VECTOR VALID 10
SYS_C008471 NORMAL VALID 10
Now confirm the execution plan actually uses it:
EXPLAIN PLAN FOR
SELECT error_code, error_title,
VECTOR_DISTANCE(embedding,
VECTOR('[0.90, 0.87, 0.11, 0.09, 0.06, 0.05, 0.10, 0.08]', 8, FLOAT32),
COSINE) AS distance
FROM ora_errors
ORDER BY distance
FETCH FIRST 3 ROWS ONLY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Plan hash value: 3194827401
---------------------------------------------------------------------------
| Id | Operation | Name | Rows | Cost |
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 3 |
| 1 | COUNT STOPKEY | | | |
| 2 | VIEW | | 3 | 3 |
| 3 | SORT ORDER BY STOPKEY | | 3 | 3 |
| 4 | VECTOR INDEX HNSW SCAN | ORA_ERRORS_HNSW_IDX| 3 | 2 |
---------------------------------------------------------------------------
VECTOR INDEX HNSW SCAN — that is the confirmation we need. If this shows TABLE ACCESS FULL instead, the first thing to check is vector_memory_size. Second thing — index STATUS in user_indexes.
IVF — The Partition-Based Algorithm
IVF stands for Inverted File Index. During build, Oracle clusters the vector space into partitions using k-means. Each vector is assigned to its nearest centroid. The index records which vectors belong to which partition.
At query time Oracle identifies the closest centroids to the query vector and searches only those partitions — skipping everything else.
Our four semantic clusters map directly to four IVF partitions:
Partition 1 — Memory: ORA-04031, ORA-00823
Partition 2 — Space/Undo: ORA-01555, ORA-00257, ORA-01654
Partition 3 — Locking: ORA-00060, ORA-04021
Partition 4 — Connectivity: ORA-12541, ORA-12170
A query for “database memory allocation failure” identifies Partition 1 as closest and searches only those two rows — not the full table.
DROP INDEX ora_errors_hnsw_idx;
CREATE VECTOR INDEX ora_errors_ivf_idx
ON ora_errors (embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 90
PARAMETERS (type IVF, neighbor partitions 4);
Key differences from HNSW syntax:
- ORGANIZATION NEIGHBOR PARTITIONS — IVF keyword, contrasted with
INMEMORY NEIGHBOR GRAPHfor HNSW - NEIGHBOR PARTITIONS 4 — number of centroids. Production rule of thumb: square root of total row count. One million rows → 1000 partitions as a starting point
- No Vector Memory Pool dependency — IVF is disk-based. It works without
vector_memory_size
Verify and check the plan:
SELECT index_name, index_type, status
FROM user_indexes
WHERE table_name = 'ORA_ERRORS';
INDEX_NAME INDEX_TYPE STATUS
-------------------- ---------- ------
ORA_ERRORS_IVF_IDX VECTOR VALID
SYS_C008471 NORMAL VALID
| 4 | VECTOR INDEX IVF SCAN | ORA_ERRORS_IVF_IDX | 3 | 2 |
VECTOR INDEX IVF SCAN confirms IVF is active.
HNSW vs IVF — The Decision That Matters
| Factor | HNSW | IVF |
|---|---|---|
| Query speed | Very fast — graph traversal | Fast — partition-scoped |
| Recall accuracy | Higher | Slightly lower at partition boundaries |
| Memory requirement | High — full graph in SGA | None — disk-based |
| Index build time | Slower | Faster — k-means is parallelizable |
| DML sensitivity | Sensitive — inserts degrade graph over time | Tolerant — vectors assigned to nearest centroid on insert |
| Best workload | Read-heavy, stable datasets | Write-heavy, large datasets, memory-constrained |
Our production starting point: HNSW for RAG implementations and semantic search — these are typically read-heavy with stable embedding tables. IVF when the table exceeds 50 million rows or when Vector Memory Pool sizing becomes a constraint.
One thing we have seen in practice — teams that skip the Vector Memory Pool sizing conversation before go-live and then wonder why HNSW is not being used in production. Set vector_memory_size in SPFILE, bounce the instance during the maintenance window, then build the HNSW index. In that order, not the reverse.
Verifying Recall — Exact vs Approximate Side by Side
TARGET ACCURACY 95 means Oracle may miss up to 5% of true nearest neighbors. We can measure that directly by comparing approximate search output against exact search output on the same query.
-- Exact search baseline (temporarily drop the index or use NO_VECTOR_INDEX hint)
SELECT /*+ NO_VECTOR_INDEX(e ora_errors_ivf_idx) */
error_code,
ROUND(VECTOR_DISTANCE(embedding,
VECTOR('[0.90, 0.87, 0.11, 0.09, 0.06, 0.05, 0.10, 0.08]', 8, FLOAT32),
COSINE), 6) AS exact_distance
FROM ora_errors e
ORDER BY exact_distance
FETCH FIRST 5 ROWS ONLY;
ERROR_CODE EXACT_DISTANCE
---------- --------------
ORA-04031 0.000187
ORA-00823 0.000423
ORA-01555 0.089341
ORA-00257 0.091204
ORA-01654 0.093817
-- Approximate search with index active
SELECT error_code,
ROUND(VECTOR_DISTANCE(embedding,
VECTOR('[0.90, 0.87, 0.11, 0.09, 0.06, 0.05, 0.10, 0.08]', 8, FLOAT32),
COSINE), 6) AS approx_distance
FROM ora_errors
ORDER BY approx_distance
FETCH FIRST 5 ROWS ONLY;
ERROR_CODE APPROX_DISTANCE
---------- ---------------
ORA-04031 0.000187
ORA-00823 0.000423
ORA-01555 0.089341
ORA-00257 0.091204
ORA-01654 0.093817
Results are identical on our ten-row dataset — expected. At scale with millions of vectors, positions 4 and 5 will occasionally diverge. That divergence is what TARGET ACCURACY 95 budgets for. If the use case demands higher precision — medical records similarity, legal document search, fraud pattern matching — push TARGET ACCURACY to 98 or 99. Oracle widens the search beam at query time, slightly higher CPU per query in exchange for better recall.
Cleanup
DROP INDEX ora_errors_ivf_idx;
DROP TABLE ora_errors PURGE;
What Is Next
We now have the index engine covered — HNSW, IVF, memory sizing, plan verification, recall measurement.
Post 4 moves to the query layer — Hybrid Search. Pure vector similarity alone is rarely enough in production. We almost always need to combine semantic search with relational filters — find the most semantically similar errors, but only within a specific error category, or above a severity threshold, or within a date window. That combination is where Oracle AI Database 26ai’s native SQL integration delivers its biggest practical advantage over standalone vector databases.
The ora_errors dataset carries forward into Post 4 with an expanded schema to support the hybrid query pattern.
Where We Go From Here
Post 3 goes deeper into the index layer itself.
The Pillar 1 series goes hands-on, in this order:
- Post 1: Oracle 23ai — Why Oracle Calls It The AI Database
- Post 2: Oracle Vector Search — First Hands-On Walkthrough (setup + live SQL)
- Post 3: Vector Indexes — How HNSW and IVF Work Under the Hood
- Post 4: Hybrid Search — Combining SQL Filters with Semantic Queries
- Post 5: Oracle AI Vector Search vs PostgreSQL pgvector — Honest DBA Comparison
- Post 6: JSON Relational Duality in Depth
- Post 7: Select AI — Talking to Your Database in Plain English
- Post 8: Building an AI-Ready Oracle Database — Production Checklist
If you are running Oracle 19c in production today and trying to understand what the 23ai upgrade actually requires of the DBA team — not the developers, not the architects, the DBA team — this series is built for that conversation.
Sizing HNSW Vector Memory Pool on Exadata vs commodity hardware? Drop your numbers in the comments — real-world sizing data from production environments is what the DBA community actually needs.