How to Create an HNSW Vector Index in Oracle

The HNSW index is the fastest kind of vector index in Oracle AI Database 26ai. It answers a nearest-neighbor search over 50,000 vectors in about a millisecond, and it builds faster than an IVF index. The price is memory: the whole index lives in a part of the SGA reserved for vectors.

This guide shows how to size and set the vector memory pool, create an HNSW index, tune EFSEARCH from measurements, check memory use, store smaller INT8 vectors, and choose between HNSW, IVF, and no index at all.

Code for This Guide

The examples are files 13 to 18 in the examples/ch07 folder of the Oracle AI code repository on GitHub, each with its output.

These examples come from AI Applications with Oracle Database 26ai and APEX 26.1, a book of 237 tested examples of AI in Oracle Database and APEX.

They search TICKET_ARCHIVE, 50,000 archived tickets embedded with a 384-dimension model, and reuse the MEASURE_SEARCH procedure and the IVF index from how to create an IVF vector index in Oracle. The test questions come from EVAL_QUESTIONS, built in how to choose an embedding model.

How an HNSW Index Works

An HNSW (Hierarchical Navigable Small World) index, an in-memory neighbor graph in Oracle's terms, links every vector to a few of its nearest neighbors, in several layers. The top layers have few vectors and long links; the bottom layer has all of them and short links. A search enters at the top, moves to the neighbor nearest the question, and descends layer by layer, like finding an address by country, city, and street. It compares the question with a few hundred vectors instead of 50,000.

The graph lives in the vector memory pool, a part of the SGA set by the parameter VECTOR_MEMORY_SIZE. Without the pool, creating an HNSW index fails with ORA-51962: The vector memory area is out of space for the current container.

Size the Vector Memory Pool

DBMS_VECTOR.INDEX_VECTOR_MEMORY_ADVISOR estimates how much memory an HNSW index of a column needs.

Example:

declare
  l_response clob;
begin
  dbms_vector.index_vector_memory_advisor(
    table_owner   => user,
    table_name    => 'TICKET_ARCHIVE',
    column_name   => 'EMBEDDING',
    index_type    => 'HNSW',
    response_json => l_response);
  dbms_output.put_line(l_response);
end;
/

Output:

Using default accuracy: 90%
Suggested vector memory pool size: 113 MB
{"required_memory":117673528}

PL/SQL procedure successfully completed.

About 113 MB for 50,000 vectors of 384 dimensions. An administrator then sets the pool in the container database and restarts it.

Example (run as SYS in the container database):

alter system set vector_memory_size = 256M scope = spfile;
shutdown immediate
startup

The pool comes from the SGA, so on a small server every megabyte for vectors is a megabyte less for the buffer cache. Size it from the advisor, with room for growth.

Create the HNSW Index

A column can have one vector index per metric, so the IVF index of the same column goes first.

Example:

-- the IVF index of the same column goes first: one vector index per column and metric
drop index ticket_archive_ivf;

set timing on
create vector index ticket_archive_hnsw on ticket_archive (embedding)
  organization inmemory neighbor graph
  distance cosine
  with target accuracy 95;
set timing off

select index_name, index_type, index_subtype, status
from   user_indexes
where  index_name = 'TICKET_ARCHIVE_HNSW';

Output:

Index TICKET_ARCHIVE_IVF dropped.

Vector INDEX created.

Elapsed: 00:00:31.047

INDEX_NAME             INDEX_TYPE    INDEX_SUBTYPE                   STATUS
______________________ _____________ _______________________________ _________
TICKET_ARCHIVE_HNSW    VECTOR        INMEMORY_NEIGHBOR_GRAPH_HNSW    VALID

Thirty-one seconds, against almost two minutes for the IVF index: building a graph in memory is faster than writing partitions to tables.

Measure and Tune EFSEARCH

EFSEARCH is the number of candidates a search keeps while it walks the graph. Fewer candidates are faster and miss more. This example measures the index with its defaults, then with EFSEARCH at 10, 50, 200, and 1,000.

Example:

exec measure_search
exec measure_search('with target accuracy parameters (efsearch 10)')
exec measure_search('with target accuracy parameters (efsearch 50)')
exec measure_search('with target accuracy parameters (efsearch 200)')
exec measure_search('with target accuracy parameters (efsearch 1000)')

Output:

(index default)
  exact:         57.0 ms per query
  approximate:    1.0 ms per query, 100% of the right results

PL/SQL procedure successfully completed.

with target accuracy parameters (efsearch 10)
  exact:         57.7 ms per query
  approximate:    0.3 ms per query, 79% of the right results

PL/SQL procedure successfully completed.

with target accuracy parameters (efsearch 50)
  exact:         56.0 ms per query
  approximate:    0.4 ms per query, 92% of the right results

PL/SQL procedure successfully completed.

with target accuracy parameters (efsearch 200)
  exact:         55.4 ms per query
  approximate:    0.7 ms per query, 100% of the right results

PL/SQL procedure successfully completed.

with target accuracy parameters (efsearch 1000)
  exact:         57.1 ms per query
  approximate:    1.5 ms per query, 100% of the right results

PL/SQL procedure successfully completed.

The default search took about a millisecond and found every right ticket. With 10 candidates it found 79 percent, with 50 it found 92 percent, and from 200 it found all of them.

The graph is built with some randomness. An earlier build of the same index found only 94 percent with the defaults and needed 1,000 candidates to find everything, because the many near-identical archived tickets compete for the same few links. Measure the accuracy after every build, and set EFSEARCH or the target accuracy from that measurement.

Check Memory Use

Example (run as SYS in the pluggable database):

select pool, round(alloc_bytes / 1024 / 1024) as allocated_mb,
       round(used_bytes / 1024 / 1024) as used_mb
from   v$vector_memory_pool
where  con_id = sys_context('USERENV', 'CON_ID');

Output:

POOL            ALLOCATED_MB    USED_MB
____________ _______________ __________
1MB POOL                 224         95
64KB POOL                 16          1

About 95 MB used of 224 MB allocated, close to the advisor's estimate. Keep in mind:

• The pool must hold every HNSW index of the database. When it is full, new indexes fail to build.
• The graph is in memory, so it is rebuilt when the database starts. DBMS_VECTOR.ENABLE_CHECKPOINT keeps a copy on disk that reloads faster.

Store Smaller Vectors with INT8

An INT8 vector takes a quarter of the space of a FLOAT32 one. Embeddings cannot simply be cast to INT8, though: their numbers lie between about -0.25 and 0.25, and rounding would make them all zero. They must first be scaled to the range -127 to 127, then rounded. This is called quantization.

This example multiplies each embedding by a vector of 384 copies of 400, since the largest number in these embeddings is about 0.25, and stores the result as INT8.

Example:

alter table ticket_archive add (embedding_int8 vector(384, int8));

-- every number times 400, rounded to a whole number from -127 to 127
update ticket_archive
set    embedding_int8 = to_vector(
         embedding * to_vector('[' || rtrim(rpad('400,', 1536, '400,'), ',') || ']'),
         384, int8);
commit;

select substr(from_vector(embedding returning clob), 1, 50) || '...'      as float32,
       substr(from_vector(embedding_int8 returning clob), 1, 30) || '...' as int8
from   ticket_archive
where  archive_id = 1;

Output:

Table TICKET_ARCHIVE altered.

50,000 rows updated.

Commit complete.

FLOAT32                                                  INT8
________________________________________________________ ____________________________________
[-3.569014E-002,6.90753311E-002,-4.000552E-002,1.1...    [-14,28,-16,5,-19,-27,-23,18,2...

RPAD repeats "400," 384 times to build the multiplier as text. Multiplying two vectors multiplies them dimension by dimension, and TO_VECTOR with INT8 rounds each product.

Does it still find the right rows? This example finds the 10 nearest tickets for each of 28 questions by their INT8 embeddings and checks them against the FLOAT32 search.

Example:

-- for the 28 questions: the 10 nearest tickets by FLOAT32 and by INT8 embeddings
with q as (
  select question_id, minilm as v,
         to_vector(minilm
                   * to_vector('[' || rtrim(rpad('400,', 1536, '400,'), ',') || ']'),
                   384, int8) as v8
  from   eval_questions
  where  question_id <= 28),
by_float as (
  select q.question_id, max(f.distance) as tenth
  from   q cross apply (select vector_distance(embedding, q.v, cosine) as distance
                        from   ticket_archive
                        order  by distance
                        fetch  exact first 10 rows only) f
  group  by q.question_id),
by_int8 as (
  select q.question_id, vector_distance(t.embedding, q.v, cosine) as float_distance
  from   q cross apply (select embedding
                        from   ticket_archive
                        order  by vector_distance(embedding_int8, q.v8, cosine)
                        fetch  exact first 10 rows only) t)
select count(*) as int8_results,
       sum(is_right) as right_results,
       round(100 * sum(is_right) / count(*)) as pct
from  (select case when i.float_distance <= f.tenth + 1e-6 then 1 else 0 end as is_right
       from   by_int8 i join by_float f on f.question_id = i.question_id);

Output:

   INT8_RESULTS    RIGHT_RESULTS    PCT
_______________ ________________ ______
            280              273     98

INT8 found 273 of the 280 right tickets, 98 percent, with a quarter of the storage. For most applications that trade is worth it; measure it with your own model and data first. More on vector formats is in how to use the VECTOR data type in Oracle AI Database 26ai.

Choose an Index

• Small tables need no index. An exact search over a few thousand rows takes milliseconds, and an index adds work for nothing.
• IVF suits large tables, needs no extra memory, and builds and maintains like an ordinary index. Start with it when memory is short.
• HNSW is the fastest and builds the fastest, at the price of memory. Plan the pool with the advisor, measure accuracy after each build, and remember that the graph is rebuilt when the database restarts.
• Measure accuracy and speed on your own data and questions, and set targets from the measurements, not from defaults.
• Match the metric and write FETCH APPROX. Without both, no vector index is used.

Conclusion

An HNSW index, CREATE VECTOR INDEX ... ORGANIZATION INMEMORY NEIGHBOR GRAPH, needs a vector memory pool sized with INDEX_VECTOR_MEMORY_ADVISOR and set with VECTOR_MEMORY_SIZE. On 50,000 tickets it found every right result in about a millisecond and built in 31 seconds. Tune EFSEARCH from measurements after each build, watch the pool in V$VECTOR_MEMORY_POOL, and consider scaled INT8 vectors to cut storage to a quarter for about 2 percent of accuracy.