Tested on: Oracle AI Database 26ai (23.26.3) on Oracle Autonomous AI Database, Transaction Processing, 2 ECPU | Level: Intermediate
Most AI Vector Search articles show how to find "similar" rows. Few answer the questions a DBA gets asked: how fast is it, does it need an index, which index, how long does it take to build, and how much memory does it use? I measured exact search, an IVF index and an HNSW index on Oracle AI Database 26ai with 50,000 and 200,000 vectors. At 200,000 vectors, HNSW answered top-10 queries in about 1.3 ms instead of 78 ms, roughly 60 times faster than a full scan. IVF was slower than a full scan on the small table, and HNSW took more than eight times longer to build than IVF.
Results at a glance
| Top-10 search, 20 queries | 50,000 vectors | 200,000 vectors |
|---|---|---|
| Table size | 19 MB | 80 MB |
| Exact search (no index) | 17.51 ms | 78.08 ms |
| IVF index, target accuracy 80 / 90 / 95 | 36.26 / 38.53 / 39.31 ms | 46.53 / 48.09 / 52.82 ms |
| HNSW index, target accuracy 80 / 90 / 95 | 0.77 / 0.88 / 0.88 ms | 1.10 / 1.30 / 1.37 ms |
| IVF build time | 5.5 s | 13.5 s |
| HNSW build time | 18.7 s | 113 s |
| HNSW vector memory used | 34.6 MB | 121.6 MB |
| Recall (all methods) | 100% | 100% |
Exact vs approximate search in one minute
A vector search finds the rows whose vectors are closest to a query vector. Exact search compares the query with every row, so it always returns the true nearest neighbours but its cost grows with the table. Approximate search uses a vector index to look at only part of the data, which is much faster but may occasionally miss a true neighbour. The share of true neighbours it still finds is called recall.
Oracle offers two vector index types:
- IVF (Inverted File Flat, Neighbor Partitions): groups vectors around centroids and stores them in partitioned tables on disk. A query scans only the partitions closest to the query.
- HNSW (Hierarchical Navigable Small World, In-Memory Neighbor Graph): builds a graph of neighbours in the vector memory pool. A query walks the graph to the nearest vectors.
The lab
I used synthetic 64-dimension vectors grouped into 50 clusters, so similar items sit close together as they do with real embeddings, and no AI model download was needed. Each test ran 20 query vectors, asked for the top 10 neighbours by Euclidean distance, and was run twice: the first pass warmed the cache, the second was timed. Recall compares each approximate result with the exact top 10 for the same query.
CREATE TABLE vec_items (
id NUMBER CONSTRAINT vec_items_pk PRIMARY KEY,
cluster_id NUMBER,
embedding VECTOR(64, FLOAT32)
);
Exact search, the baseline:
SELECT id
FROM vec_items
ORDER BY VECTOR_DISTANCE(embedding, :q, EUCLIDEAN)
FETCH EXACT FIRST 10 ROWS ONLY;
Approximate search, using whichever vector index exists:
SELECT id
FROM vec_items
ORDER BY VECTOR_DISTANCE(embedding, :q, EUCLIDEAN)
FETCH APPROX FIRST 10 ROWS ONLY WITH TARGET ACCURACY 90;
The two indexes, built one at a time on the same column:
CREATE VECTOR INDEX vec_ivf_idx ON vec_items (embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE EUCLIDEAN
WITH TARGET ACCURACY 95;
CREATE VECTOR INDEX vec_hnsw_idx ON vec_items (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE EUCLIDEAN
WITH TARGET ACCURACY 95;
The data dictionary shows the index types:
INDEX_NAME INDEX_TYPE INDEX_SUBTYPE
-------------- ---------- ----------------------------
VEC_IVF_IDX VECTOR NEIGHBOR_PARTITIONS_IVF
VEC_HNSW_IDX VECTOR INMEMORY_NEIGHBOR_GRAPH_HNSW
Result 1: HNSW is in a different league for query speed
At 200,000 vectors, HNSW returned the top 10 in 1.1 to 1.4 ms, against 78 ms for exact search: about 60 times faster. At 50,000 vectors the gap was about 20 times (0.88 ms vs 17.51 ms). With four times more data, exact search became 4.5 times slower, while HNSW became only about 1.5 times slower. That is the main reason to use a vector index at all: it keeps response time almost flat as the table grows.
The plan confirms the optimizer used the graph (trimmed to the relevant lines):
| Id | Operation | Name |
| 0 | SELECT STATEMENT | |
| 1 | COUNT STOPKEY | |
| 2 | VIEW | |
| 5 | SORT ORDER BY STOPKEY | |
| 6 | TABLE ACCESS BY INDEX ROWID| VEC_ITEMS |
| 7 | VECTOR INDEX HNSW SCAN | VEC_HNSW_IDX |
Result 2: IVF can be slower than no index at all
On the 50,000-row table, IVF took 36 to 39 ms per query, about twice as slow as exact search (17.5 ms). At 200,000 rows it became faster than exact search, but only by about 1.6 times (48 ms vs 78 ms).
The execution plan explains why. An IVF search is not one index probe: it scans the centroid table, joins to the matching partitions, sorts the candidates and then fetches rows by ROWID (trimmed, names shortened):
| 7 | HASH JOIN | |
| 8 | PART JOIN FILTER CREATE | :BF0000 |
| 13 | TABLE ACCESS STORAGE FULL| VECTOR$VEC_IVF_IDX$..._IVF_FLAT_CENTROIDS |
| 14 | PARTITION LIST JOIN-FILTER | |
| 15 | TABLE ACCESS STORAGE FULL | VECTOR$..._IVF_FLAT_CENTROID_PARTITIONS |
| 16 | TABLE ACCESS BY USER ROWID | VEC_ITEMS |
On a small table, a full scan of 19 MB on Exadata storage (TABLE ACCESS STORAGE FULL) is cheaper than all that work. The IVF index itself was stored as a centroid table plus about 100 list partitions of roughly 1 to 2 MB each. The lesson for DBAs: do not create a vector index just because the column is a vector. Measure first. On small tables exact search may be the fastest and simplest choice.
Result 3: HNSW speed has a price, in build time and memory
- Build time: HNSW took 113 seconds at 200,000 vectors, more than eight times longer than IVF (13.5 s). On a 2 ECPU database this matters for maintenance windows and for rebuilds.
- Memory: HNSW lives in the vector memory pool. Used memory grew from 34.6 MB at 50,000 vectors to 121.6 MB at 200,000 vectors. Plan for this to grow with your data, and multiply by the number of HNSW indexes.
-- V$VECTOR_MEMORY_POOL after building HNSW on 200,000 vectors
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
--------- ----------- ---------- ---------------
1MB POOL 194168311 121634816 DONE
64KB POOL 25426802 196608 DONE
Result 4: on Autonomous, watch the pool, not the parameter
Before the first test, vector_memory_size showed 0 and the vector pool reported OUT OF MEMORY, which suggests HNSW cannot be built. Trying to set the parameter as ADMIN failed:
ALTER SYSTEM SET vector_memory_size = 512M SCOPE = BOTH;
ORA-01031: insufficient privileges
Yet the HNSW index built successfully, and V$VECTOR_MEMORY_POOL showed memory allocated and the index populated. The parameter value also changed between runs (it read 114,527,800 at the start of the second run). On Autonomous AI Database, the vector pool is managed by the service. For monitoring, rely on V$VECTOR_MEMORY_POOL, not on the parameter.
About recall: an honest limitation
Recall was 100% for every method and every target accuracy in both runs, so this test did not show the accuracy trade-off. My synthetic clusters were well separated, which makes the nearest neighbours easy to find. Raising the target accuracy only added a little time (IVF 46.5 to 52.8 ms, HNSW 1.10 to 1.37 ms). Real embeddings from a language model are usually less tidy, so measure recall on your own data. The method is simple: store the exact top 10 for a set of test queries, run the approximate query, and count how many of the exact results it returned.
Which option should you use?
| Situation | Recommendation |
|---|---|
| Small table (tens of thousands of vectors), occasional queries | Exact search. Simplest, always 100% correct, and in this test faster than IVF. |
| Interactive search, low latency matters, vectors fit in memory | HNSW. About 60 times faster than exact search here. Budget build time and vector pool memory. |
| Very large data, limited memory, or frequent rebuilds | IVF. Stored on disk and quicker to build, but test that it actually beats a full scan at your size. |
| Any choice | Measure recall on your own data and check the plan shows the vector index scan. |
Clean-up
DROP TABLE vec_items PURGE;
DROP TABLE vec_queries PURGE;
DROP TABLE vec_truth PURGE;
DROP TABLE vec_results PURGE;
DROP TABLE vec_build PURGE;
Summary
On Oracle AI Database 26ai, an HNSW vector index answered top-10 similarity queries on 200,000 vectors in about 1.3 ms, roughly 60 times faster than exact search, and its query time barely grew when the data grew four times. That speed costs build time (113 s vs 13.5 s for IVF) and vector pool memory (122 MB). IVF was slower than a plain full scan on a small table, so a vector index is not automatically a win. Treat vector search like any other access path: measure response time, recall, build time and memory before choosing.
Lab design, testing and all results are my own, run on an Oracle LiveLabs sandbox.
Tamilselvan Perumal is an Oracle ACE Apprentice and Senior DBA with 12+ years in banking databases, working on database performance and benchmarking. Connect on LinkedIn.

