Everyone is talking about AI. AI this, vector that, vector indexes eating your storage, running out of memory. I deployed 26ai on my lab cluster and went through the internals so you don’t have to guess what’s actually going on under the hood. Brace yourself for this DDL and DML ride.
Here’s the short version of what I found:
- Vector search is a memory problem, not a GPU problem. HNSW indexes live entirely in the SGA
- The Vector Memory Pool can’t be increased dynamically (ORA-51950), which matters for capacity planning
- HNSW now supports DML. In earlier 23ai release updates, INSERT/UPDATE/DELETE were blocked on HNSW-indexed tables
- BINARY vectors are 32x smaller than FLOAT32 and Jaccard distance works
- Most of the interesting features gate on COMPATIBLE 23.6.0, not just 23.0.0
- You can write custom distance functions in JavaScript
Lab environment
All testing ran on bench-01, one node of an 8-node Oracle 26ai cluster running 23.26.1.0.0 Enterprise Edition with COMPATIBLE=23.6.0.
SGA is set to ~92GB. I carved 2GB out for the Vector Memory Pool, which reduced the buffer cache from ~81GB to ~79GB. Vector memory comes directly out of SGA. It’s not additive. Every gigabyte you give to vectors is a gigabyte your buffer cache loses.
The Vector Memory Pool is a new region inside the SGA, allocated from space that would otherwise be buffer cache. HNSW indexes live entirely in this pool. IVF indexes store their centroid vectors in the pool but keep the actual vector data on disk.
[ARCHITECTURE DIAGRAM: SGA layout showing Buffer Cache (~79GB), Vector Memory Pool (2GB) split into 1MB Pool and 64KB Pool, with HNSW indexes fully in-memory and IVF centroids cached but data on disk]
The test dataset is 1000 rows with 1536-dimension FLOAT32 vectors generated using DBMS_RANDOM.NORMAL. 1536 dimensions matches OpenAI’s ada-002 embedding model, so the sizing is realistic even if the data is synthetic.
CREATE TABLE vec_test_1536 (
id NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
doc_text VARCHAR2(200),
embedding VECTOR(1536, FLOAT32)
);
1000 rows isn’t production scale, but it’s enough to verify the mechanics and compare index types.
Vector Memory Pool
The pool is controlled by the vector_memory_size initialization parameter. It defaults to 0 (disabled). HNSW indexes live entirely in this pool, so you can’t create one until you allocate memory for it.
Setting it dynamically should work according to the documentation. It doesn’t.
ALTER SYSTEM SET vector_memory_size = 2G SCOPE=BOTH;
ORA-51950: The Oracle Database Vector Memory size cannot be increased.
SCOPE=SPFILE and a restart is the only way I got it to work. After the restart:
SELECT POOL, ROUND(ALLOC_BYTES/1024/1024) AS ALLOC_MB,
ROUND(USED_BYTES/1024/1024) AS USED_MB, POPULATE_STATUS
FROM V$VECTOR_MEMORY_POOL WHERE CON_ID = 3;
POOL ALLOC_MB USED_MB POPULATE_STATUS
-------- -------- ------- ---------------
1MB POOL 1843 ... DONE
64KB POOL 245 ... DONE
The pool splits into two segments per PDB. POPULATE_STATUS=DONE confirms the memory is available.
The inability to increase this dynamically is a production constraint. If you undersize the pool and need more memory for a new HNSW index, you’re restarting the instance. There is an elastic mode (vector_memory_size=1 with sga_target > 0) that grows the pool automatically when HNSW indexes are created and shrinks it when they’re dropped, but the resized values aren’t persisted to SPFILE. A restart resets the pool back to 1.
For sizing, the documented formula for HNSW memory is:
Memory = 1.3 x NumVectors x NumDimensions x DimensionTypeSize
For 1 million rows at 1536 dimensions with FLOAT32 (4 bytes): 1.3 x 1,000,000 x 1536 x 4 = ~8GB. In my lab with 1000 vectors, the formula predicts 7.6MB. Actual usage was 6.2MB, about 78% of predicted. The 1.3x multiplier appears conservative, but I’d rather overallocate than restart in production. Oracle also provides a memory advisor procedure (DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR) that gives recommendations based on your dimensions and row count.
HNSW indexes
HNSW (Hierarchical Navigable Small World) is the in-memory vector index.
CREATE VECTOR INDEX vec_hnsw_idx ON vec_test_1536(embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;
Created in about 1 second for 1000 rows. The graph statistics view shows what was built:
SELECT INDEX_NAME, NUM_VECTORS, NUM_NEIGHBORS, EF_CONSTRUCTION,
ALLOCATED_BYTES, USED_BYTES
FROM V$VECTOR_GRAPH_INDEX WHERE CON_ID = 3;
INDEX_NAME NUM_VECTORS NUM_NEIGHBORS EF_CONSTRUCTION ALLOCATED_BYTES USED_BYTES
-------------- ----------- ------------- --------------- --------------- ----------
VEC_HNSW_IDX 1000 32 300 6422528 6233800
NUM_NEIGHBORS=32 is the default M parameter, controlling how many connections each node maintains in the graph. EF_CONSTRUCTION=300 controls build-time search breadth. Both are tuneable at index creation. Higher values improve recall but cost more memory.
Approximate search uses the FETCH APPROXIMATE syntax:
SELECT id, doc_text,
ROUND(VECTOR_DISTANCE(embedding,
(SELECT embedding FROM vec_test_1536 WHERE id = 5), COSINE), 6) AS distance
FROM vec_test_1536
ORDER BY VECTOR_DISTANCE(embedding,
(SELECT embedding FROM vec_test_1536 WHERE id = 5), COSINE)
FETCH APPROXIMATE FIRST 5 ROWS ONLY;
Returns id=5 with distance ~0 (exact match), then the four nearest neighbours. The APPROXIMATE keyword triggers use of the vector index. Without it, Oracle does a full scan.
DML support is new in 26ai. In earlier 23ai release updates (23.4, 23.5), tables with HNSW indexes were effectively read-only. INSERT, UPDATE, and DELETE were all blocked. In 26ai, all three work with transactional consistency:
UPDATE vec_test_1536 SET doc_text = 'Updated via DML' WHERE id = 1;
DELETE FROM vec_test_1536 WHERE id = (SELECT MAX(id) FROM vec_test_1536);
Both completed without error. DML uses a private journal (in-memory, per-transaction) and a shared journal (on-disk, committed with SCN tracking). The graph refreshes incrementally or via full repopulation. DBMS_VECTOR.REBUILD_INDEX gives explicit control over refresh strategy.
HNSW also supports persistent checkpoints that save the graph to disk, so restarts load from the checkpoint rather than rebuilding from scratch. The default vector_index_neighbor_graph_reload=RESTART enables this. I confirmed the checkpoint table exists in DBA_INDEXES, but V$VECTOR_GRAPH_INDEX_CHKPT returned no rows. The checkpoint may be time-triggered or require an explicit flush.
IVF indexes
IVF (Inverted File) is the disk-based alternative. It uses less memory than HNSW because only the centroids live in the Vector Memory Pool. The actual vector data stays on disk.
CREATE VECTOR INDEX vec_ivf_idx ON vec_test_1536(embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE
WITH TARGET ACCURACY 90;
Only one vector index per column is allowed (ORA-01408), so I dropped the HNSW index first. Memory comparison on the same 1000-row dataset:
| Index | ALLOCATED_BYTES | USED_BYTES |
|---|---|---|
| HNSW | 6,422,528 | 6,233,800 |
| IVF | 2,097,152 | 381,872 |
IVF uses a third of the allocated memory and a sixteenth of the used memory. The centroids are cached in the Vector Memory Pool, but the actual vector data stays on disk. When the pool is exhausted, IVF falls back silently to the large pool, then to no caching at all. No error, just slower queries. That silent regression is worth monitoring.
IVF also supports automatic reorganization when query quality degrades after heavy DML.
BINARY vectors and Jaccard distance
BINARY vectors store each dimension as a single bit instead of a 4-byte float. 32x smaller than FLOAT32, up to 40x faster distance computation. Providers like Cohere and Jina AI support binary embedding output.
The dimension encoding is non-obvious. VECTOR(64, BINARY) means 64 bits, stored as 8 INT8 byte values. You input 8 numbers (0-255), not 64.
CREATE TABLE vec_binary_test (
id NUMBER PRIMARY KEY,
bvec VECTOR(64, BINARY)
);
INSERT INTO vec_binary_test VALUES
(1, TO_VECTOR('[255,0,255,0,255,0,255,0]', 64, BINARY));
INSERT INTO vec_binary_test VALUES
(2, TO_VECTOR('[0,255,0,255,0,255,0,255]', 64, BINARY));
SELECT a.id AS id_a, b.id AS id_b,
ROUND(VECTOR_DISTANCE(a.bvec, b.bvec, JACCARD), 4) AS jaccard_dist
FROM vec_binary_test a, vec_binary_test b WHERE a.id < b.id;
Jaccard distance = 1.0 for completely opposite bit patterns. Dimensions must be a multiple of 8 (ORA-51813 otherwise), maximum 65,528. Requires COMPATIBLE 23.6.0.
Sparse vectors
Sparse vectors store only non-zero values. Useful for models like SPLADE and BM25 that produce high-dimensional output where most dimensions are zero.
The input format is unusual: '[TotalDimCount, [IndexArray], [ValueArray]]'.
CREATE TABLE vec_sparse_real (
id NUMBER PRIMARY KEY,
svec VECTOR(100, FLOAT32, SPARSE)
);
INSERT INTO vec_sparse_real VALUES (1, '[100,[5,20,75],[0.5,0.8,0.3]]');
That stores 3 non-zero values at indices 5, 20, and 75 out of 100 total dimensions. VECTOR_DIMENSION_COUNT returns 100 (the logical count, not stored elements). You can view the full dense representation with FROM_VECTOR(svec RETURNING CLOB FORMAT DENSE), which shows all 100 elements with zeros in the unstored positions. Requires COMPATIBLE 23.6.0.
Custom distance functions
If the built-in metrics (COSINE, EUCLIDEAN, DOT, JACCARD) aren’t enough, you can write your own in JavaScript via Oracle’s MLE engine.
CREATE OR REPLACE FUNCTION my_l2_sq("a" VECTOR, "b" VECTOR)
RETURN BINARY_DOUBLE
DETERMINISTIC PARALLEL_ENABLE
AS MLE LANGUAGE JAVASCRIPT PURE
{{
let len = a.length;
let sum = 0;
for(let i = 0; i < len; i++) {
const tmp = a[i] - b[i];
sum += tmp * tmp;
}
return sum;
}};
/
SELECT my_l2_sq(
TO_VECTOR('[1,2,3]', 3, FLOAT32),
TO_VECTOR('[4,5,6]', 3, FLOAT32)
) FROM DUAL;
-- Returns 27 (correct: 9+9+9)
The function must take exactly two VECTOR arguments and return BINARY_DOUBLE. DETERMINISTIC and PURE are both required. PURE is specifically needed for use with HNSW indexes. Custom distance works with HNSW only, not IVF. If you modify the function after index creation, the index becomes UNUSABLE. Requires COMPATIBLE 23.6.0.
What needs COMPATIBLE 23.6.0
This matters for anyone upgrading from 19c. A fresh 26ai install sets COMPATIBLE=23.6.0 by default, but upgrades retain the source database’s value. You have to change it manually.
| Needs 23.6.0 | Works at 23.0.0 |
|---|---|
| HNSW DML support | Basic VECTOR data type |
| Sparse vectors (SPARSE keyword) | HNSW indexes (read-only) |
| BINARY vector format | IVF indexes |
| Jaccard distance | COSINE, EUCLIDEAN, DOT distances |
| Custom distance functions (MLE) | FLOAT32, FLOAT64, INT8 types |
| HNSW checkpoints | Approximate search syntax |
| Hybrid Vector Indexes | |
| Vector arithmetic |
If you want the full vector feature set, bumping COMPATIBLE to 23.6.0 is not optional.
Gaps in my testing
Hybrid Vector Indexes combine Oracle Text full-text search with vector similarity search in a single index. Creation requires an in-database ONNX embedding model. I didn’t have one loaded on bench-01. ONNX model support is limited to x86-64 Linux and Linux ARM. Not Windows.
HNSW checkpoint contents. The checkpoint table exists but V$VECTOR_GRAPH_INDEX_CHKPT returned no rows after index creation. Whether checkpoints are time-triggered, need an explicit flush via DBMS_VECTOR.ENABLE_CHECKPOINT, or require a minimum DML threshold is unclear.
RAC distribution. I tested on a single instance. Distributed HNSW on RAC spans the Vector Memory Pool across instances using DISTRIBUTE AUTO, but different instances may return different approximate results. That tradeoff needs its own testing.