Oracle 26ai Vector Search: The HNSW Index Fails Out of the Box. The Fix Takes a Restart.
AI Vector Search is the headline feature of Oracle AI Database 26ai, and Oracle Database Free ships with it. You
add a VECTOR column, load embeddings, and create the index every tutorial recommends, the in-memory HNSW graph:
CREATE VECTOR INDEX docs_hnsw ON docs (emb)
ORGANIZATION INMEMORY NEIGHBOR GRAPH DISTANCE COSINE;
ORA-51962: The vector memory area is out of space for the current container.
Nothing is wrong with the table, and the database has memory to spare. The error is about a memory pool that is switched off by default and, once it’s off, can’t be switched on without a restart. That part is quick to fix. The parts after it are less obvious: how much memory the index actually needs, what an approximate query returns, and what happens to the index after a restart when it no longer fits.
This is the first post in the 26ai in practice series: one new feature at a time, run
on Oracle Database Free in a lab, with the catch. The lab loads 100,000 synthetic vectors of 128 FLOAT32
dimensions (no embedding model, fixed random seeds) and asserts every number below.
Two vector indexes, and only one needs the pool
26ai has two main kinds of vector index, and they live in different places:
- HNSW (
ORGANIZATION INMEMORY NEIGHBOR GRAPH) is a graph held entirely in memory, in the vector pool, a part of the SGA sized byVECTOR_MEMORY_SIZE. It’s the fast one. - IVF (
ORGANIZATION NEIGHBOR PARTITIONS) groups the vectors around centroids and stores them in ordinary tables on disk. It needs no vector pool.
On the image as shipped, the IVF index builds and a FETCH APPROX query uses it; the HNSW index fails. A column can
have only one vector index: creating the other one on the same column fails with ORA-01408: such column list already indexed, so trying HNSW “for comparison” means dropping the IVF index first.
Out of the box: a pool of 0 that can’t be raised online
VECTOR_MEMORY_SIZE defaults to 0, in the CDB and in the PDB. The
reference lists it
as modifiable with ALTER SYSTEM, in a PDB too. From 0, it isn’t:
-- in the CDB root
ALTER SYSTEM SET vector_memory_size = 256M;
-- ORA-02097: parameter cannot be modified because specified value is invalid
-- ORA-51950: The Oracle Database Vector Memory size cannot be increased.
-- in the PDB
ALTER SYSTEM SET vector_memory_size = 128M;
-- ORA-51955: The Oracle Database Vector Memory size cannot be increased more than 0 MB for PDB ID 3.
At the CDB level the parameter is the size of the pool; at the PDB level it’s the most of that pool the PDB may use. With no pool at startup, there’s nothing to grow and no share to give.
The fix: put it in the spfile and restart
-- CDB root
ALTER SYSTEM SET vector_memory_size = 256M SCOPE = SPFILE;
SHUTDOWN IMMEDIATE
STARTUP
-- startup now shows "Vector Memory Area 268435456 bytes"
The pool comes out of the SGA. With SGA_TARGET unchanged at 1,536 MB, the other SGA components get less: the lab’s
buffer cache was smaller after every restart that added the pool (1,104 MB to 832 MB in one run, 944 MB to 864 MB in
another). Size it on purpose, not by guessing high.
Once the instance has started with a non-zero pool, the CDB value can be raised online (256M to 320M worked). It can’t go below its startup size:
ORA-51953: The Oracle Database Vector Memory size must be at least the initial value 272 MB.
How big: the formula undersizes it
The Size the Vector Pool
page gives a rough formula: 1.3 × number of vectors × dimensions × bytes per dimension (4 for FLOAT32). Oracle
also ships an advisor, DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR. For 100,000 vectors of 128 FLOAT32 dimensions,
with the PDB limit as the knob:
estimate / limit HNSW CREATE
docs formula: 1.3 x 100000 x 128 x 4 = 63.5 MiB PDB limit 64M: ORA-51962
PDB limit 90M: ORA-51962
INDEX_VECTOR_MEMORY_ADVISOR: 96.7 MiB PDB limit 128M: built
finished index (V$VECTOR_MEMORY_POOL used): 80.9 MiB
The finished index used 80.9 MiB, already more than the formula. Building it needed more than that again: a 90M limit, larger than the finished index, still failed. Across runs the build started to work somewhere between 95M and 105M, which is about what the advisor says. Use the advisor’s number, not the formula, and leave room above it.
Or let it grow
The same docs page describes a second mode: set VECTOR_MEMORY_SIZE to 1 in the CDB, with SGA_TARGET above 0,
and HNSW index creation grows the pool as needed. The lab confirms it, with two conditions the error messages make
clear:
ALTER SYSTEM SET vector_memory_size = 1 SCOPE = SPFILE; -- CDB root, then restart
-- in the PDB:
ALTER SYSTEM SET vector_memory_size = 200M;
-- ORA-51999: Vector memory size cannot be changed when vector memory autogrow is enabled.
CREATE VECTOR INDEX ...;
-- ORA-51995: Cannot autogrow vector memory size to 128275796. Size exceeds 70% SGA target size.
The 70% is of the PDB’s SGA_TARGET, which is 0 unless someone set it. With ALTER SYSTEM SET sga_target = 512M
in the PDB, the index built, the CDB pool grew to 128M, and after a restart the index was reloaded and used. The docs
note that growth isn’t persisted to the spfile and the pool size is reset at a restart; in the lab the index was
reloaded anyway.
Approximate means approximate
An HNSW search returns the vectors it finds by walking the graph, not by comparing every row. The target accuracy
sets how hard it looks; the index’s default, shown in V$VECTOR_INDEX.DEFAULT_ACCURACY, was 90. The lab compares
each query’s top 10 with the exact top 10 from FETCH EXACT FIRST 10 ROWS ONLY (a full scan), over 20 queries:
data query found of the exact top 10
clustered (100 groups) FETCH APPROX FIRST 10 (default target 90) 100%
uniform random FETCH APPROX FIRST 10 (default target 90) 81.5% to 86.5%, by build
uniform random ... WITH TARGET ACCURACY 95 the same as the default
uniform random ... WITH TARGET ACCURACY 100 95.5% to 97%
On data that falls into groups, like embeddings of similar documents, the default found everything. On uniform random vectors, the hardest case for any graph index, it found less than the 90 it targets, and asking for 95 changed nothing. The HNSW overview says it plainly: a target of 100 “will tend to impose a similar result as an exact search,” but the system may still use the index and “will not perform an exact search.” The range comes from the build: the same data and settings gave different recall each time the index was created.
FETCH FIRST without APPROX used the index too
The documentation shows FETCH EXACT FIRST for exact search and says the EXACT keyword is optional: if it’s
omitted on Autonomous Database Serverless, an approximate search is attempted when a vector index exists
(Perform Exact Similarity Search).
It doesn’t say what happens elsewhere. On Oracle Database Free 26ai, a query with neither keyword:
SELECT id FROM docs
ORDER BY VECTOR_DISTANCE(emb, :q, COSINE)
FETCH FIRST 10 ROWS ONLY;
ran VECTOR INDEX HNSW SCAN and returned exactly the same rows as FETCH APPROX: the same recall, every time. If a
query must be exact (a test that compares results, a deduplication job), write FETCH EXACT. Its plan is a full
table scan.
A different distance metric skips the index
The index was created with DISTANCE COSINE. The same query with VECTOR_DISTANCE(emb, :q, EUCLIDEAN) and
FETCH APPROX did a full table scan: no error, no warning, just the plan. Use the metric the index was built with,
or leave the metric out of VECTOR_DISTANCE (in the lab, that query used the cosine index).
Changes and restarts
DML is handled for you. An insert, an update and a delete each showed up in the very next approximate search,
the insert even before commit in the same session. V$VECTOR_INDEX.NUM_VECTORS stayed at 100,000 throughout: the
graph itself wasn’t rebuilt, the changes were applied around it.
A restart empties the pool, and the index is reloaded after the database opens. A query run right after the open, before the reload finished, did a full table scan and returned the correct rows. A few seconds later the same query used the index again. For 100,000 vectors that window is short; for a large index it’s a window of full scans.
When the index doesn’t fit after a restart
The pool is set at startup. Lower VECTOR_MEMORY_SIZE in the spfile (a memory rebalance, a copied parameter file,
a smaller VM) to less than the index needs and restart. In the lab, with the pool at 64M and an 81 MiB index:
DBA_INDEXES.STATUS VALID
V$VECTOR_INDEX.NUM_VECTORS (60 s after open) 0
FETCH APPROX FIRST 10 plan TABLE ACCESS FULL DOCS
results correct (the exact answer)
alert log, about the vector index nothing
Every vector query still worked. Each one was now a full scan. Nothing reported it.
Raising the CDB pool online to 256M didn’t reload the index: 30 seconds later NUM_VECTORS was still 0. Then the
step that looks like the fix:
EXEC DBMS_VECTOR.REBUILD_INDEX(idx_name => 'DOCS_HNSW', idx_owner => 'APP')
-- ORA-51962: The vector memory area is out of space for the current container.
and DOCS_HNSW was no longer in DBA_INDEXES. That’s the documented behaviour: with idx_rebuild_mode left NULL,
REBUILD_INDEX drops the
index and recreates it. The recreate failed, so the index was simply gone. A plain CREATE VECTOR INDEX failed the
same way, with the PDB’s VECTOR_MEMORY_SIZE showing 208M, until the PDB value was set again with
ALTER SYSTEM SET vector_memory_size = 200M. Then it built and queries used it.
To catch it, compare what the pool holds with what the table has:
SELECT i.owner, i.index_name, i.num_vectors, i.used_bytes
FROM v$vector_index i
WHERE i.index_organization = 'INMEMORY NEIGHBOR GRAPH';
-- NUM_VECTORS = 0 on a table with rows: the index is not in memory, and every query is a full scan
Prove it
The lab runs Oracle AI Database 26ai Free (23.26.3) from the public gvenzl/oracle-free image, with 100,000
synthetic vectors in two tables (clustered and uniform random) and 20 query vectors each:
drill what happens result
default VECTOR_MEMORY_SIZE = 0: CREATE HNSW ORA-51962
raise it online, CDB / PDB ORA-51950 / ORA-51955
CREATE IVF, FETCH APPROX built, uses the centroid partitions
sizing spfile 256M + restart buffer cache shrinks
PDB limit 64M (formula) / 90M / 128M ORA-51962 / ORA-51962 / built
finished index 80-90 MiB of the pool (80.9)
raise the CDB pool online / lower it below startup works / ORA-51953
second vector index on the same column ORA-01408
accuracy clustered, default target 90 >= 99% of the exact top 10
uniform, default target 90 < 90%
FETCH FIRST (no APPROX) HNSW scan, same recall as APPROX
TARGET ACCURACY 100 more than the default
FETCH EXACT; EUCLIDEAN query on a COSINE index full table scan
restart insert / update / delete seen at once; NUM_VECTORS unchanged
restart reloaded, index used again
restart with a 64M pool VALID, NUM_VECTORS 0, full scans
raise the pool online; REBUILD_INDEX not reloaded; ORA-51962, index gone
autogrow VECTOR_MEMORY_SIZE = 1: PDB limit / PDB SGA_TARGET 0 ORA-51999 / ORA-51995
PDB SGA_TARGET 512M; restart built, pool grows; reloaded
Don’t take my word for it — run it. The vector-index lab asserts every row: each error code, that the pool can’t be raised from 0 and can after a restart, the limits that fail and the one that works, the pool the finished index uses, the recall on each dataset, the plan of each query, what DML and restarts do, the silent full scans with a pool that’s too small, and the index gone after a failed rebuild. If any of it doesn’t reproduce, the run fails. Proven on every CI push.
What the lab doesn’t cover: IVF tuning, vector quantization, RAC, Exadata, and Autonomous Database, where the pool is managed for you. The vectors are synthetic, so the recall numbers compare queries on the same index; they aren’t a benchmark of any embedding model.
What teams get wrong
- Assuming the feature is ready because the data type is.
VECTORcolumns andVECTOR_DISTANCEwork on the image as shipped; the HNSW index doesn’t. Plan the restart with the change, not after the error. - Sizing with the formula. It came out below what the finished index used, and the build needs more than the
finished index. Use
DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISORand leave headroom. - Forgetting the pool is taken from the SGA. With a fixed
SGA_TARGET, the rest of the SGA gets less; in the lab the buffer cache shrank every time. - Reading
FETCH FIRSTas exact. Here it used the approximate index. WriteFETCH EXACTwhen the answer must be exact, andFETCH APPROXwhen you mean approximate, so the query says which one it is. - Treating target accuracy as a guarantee. It’s a target. Measure recall on your own data against
FETCH EXACT. - Checking only
DBA_INDEXES.STATUS. An HNSW index that isn’t in memory is stillVALID. WatchV$VECTOR_INDEX.NUM_VECTORS. - Running
DBMS_VECTOR.REBUILD_INDEXto “reload” it. By default it drops the index first. If the pool still can’t hold it, you end with no index.
Frequently asked questions
Why does CREATE VECTOR INDEX fail with ORA-51962 on Oracle 26ai?
An HNSW index (ORGANIZATION INMEMORY NEIGHBOR GRAPH) lives in the vector pool, and VECTOR_MEMORY_SIZE defaults to 0. Set VECTOR_MEMORY_SIZE in the CDB spfile and restart, or set it to 1 for automatic growth and give the PDB an SGA_TARGET. An IVF index (ORGANIZATION NEIGHBOR PARTITIONS) doesn't need the vector pool and builds without either.
Can VECTOR_MEMORY_SIZE be changed without a restart?
Not from 0. In the companion lab on Oracle Database Free 26ai, ALTER SYSTEM failed with ORA-51950 in the CDB and ORA-51955 in the PDB while the pool was 0. After a restart with a non-zero value, the CDB pool could be raised online but not lowered below its startup size (ORA-51953), and the PDB limit could be changed online.
How much memory does an Oracle HNSW vector index need?
More than the documented rough formula of 1.3 x vectors x dimensions x bytes per dimension. For 100,000 vectors of 128 FLOAT32 dimensions the formula gives 63.5 MiB; the finished index used 80.9 MiB, and the build failed with a 90M PDB limit. DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR said 96.7 MiB, which matched what the build needed. Use the advisor and leave headroom.
Does FETCH FIRST without APPROX do an exact vector search in Oracle 26ai?
Not necessarily. The documentation says EXACT is optional and that on Autonomous Database Serverless an approximate search is attempted when it's omitted. On Oracle Database Free 26ai in the companion lab, FETCH FIRST without APPROX or EXACT used the HNSW index and returned the same rows as FETCH APPROX. Write FETCH EXACT when the result must be exact.
Why is my HNSW vector index VALID but not used after a restart?
Because it didn't fit in the vector pool when the instance started. The index stays VALID in DBA_INDEXES, V$VECTOR_INDEX.NUM_VECTORS is 0, and approximate queries run as full table scans with correct results and nothing in the alert log. Raising the pool online didn't reload it in the lab, and DBMS_VECTOR.REBUILD_INDEX dropped it and then failed to recreate it. Size the pool so the index fits at startup, then recreate the index if it's gone.
Vector search on 26ai is a database feature with a memory pool behind it, and that pool is off by default and set at
startup. Put it in the spfile before the first HNSW index, size it with the advisor, ask for FETCH EXACT or
FETCH APPROX explicitly, and watch NUM_VECTORS after every restart. Prove each one with the
vector-index lab, where an index that’s VALID
and a query that returns the right rows are still a full scan.
Have a question or some feedback?
I write here in a personal capacity and enjoy comparing notes with other Oracle folks. Say hello.
Get in touch