Local Hierarchical Navigable Small World Indexes

A local HNSW index is an index created for each partition or sub partition of a partitioned table. Instead of building a single, global HNSW (Hierarchical Navigable Small World) graph across all vectors in a large, partitioned table, Oracle enables the creation of individual HNSW graphs for each (sub)partition. This local partitioned indexing approach improves scalability, performance, and manageability essential for enterprise workloads involving high-dimensional similarity search applications, such as semantic search, recommendation systems, and AI-driven analytics.

In addition, partition pruning and parallel execution further optimize query efficiency.

Using partition pruning Oracle Database restricts query processing to only those partitions that satisfy the query’s filtering predicates. The optimizer analyzes FROM and WHERE clauses in SQL statements to eliminate unneeded partitions thereby minimizing unnecessary data access and computation.

For example, if a houses table is partitioned by state column using list or hash partitioning, and a query specifies WHERE state = CA, then the Oracle Database performs partition pruning. It searches only the CA partition and its local HNSW index to process the query:

SELECT id
FROM houses

WHERE state = 'CA'
ORDER BY vector_distance(data_vector, :query_vector)
FETCH APPROX FIRST 10 ROWS ONLY;

--With a local HNSW index, only the CA partition and its local HNSW graphs are searched. 

For more information on partition pruning, see Partition Pruning.

Parallel execution further amplifies efficiency by allowing multiple partitions and their corresponding local HNSW indexes to be searched simultaneously across multiple CPU threads.

Usage Notes

• The base table must be partitioned. Local HNSW indexes can only be created on partitioned tables.
• The base table must be loaded with vector data before creating local HNSW indexes. After creating local HNSW indexes, any DML operations (INSERT, UPDATE, DELETE) on the indexed base table results in errors. Therefore, it is essential to load the table with vector data before creating the local HNSW indexes.
• Adequate sizing of the SGA “Vector Memory Pool” is needed for in-memory index operations.
• Including column clause (covering columns) is not supported.
• Local HNSW on Sparse vector columns is not supported.
• Online creation of local HNSW vector indexes is not supported.
• Parameters, such as Target Accuracy, Distance metric, HNSW graph parameters, and table space clause are globally applied to all HNSW index partitions.
• DML operations (insert, update, delete) are not integrated for local HNSW indexes. This means if you insert, update, or delete rows in the underlying partitioned table, the corresponding partitioned local HNSW index will not reflect those changes.
• In case of HNSW RAC Duplication in an Oracle RAC environment, partitioned local HNSW indexes are built by constructing an independent HNSW graph for each partition of the table, with index creation and loading distributed and coordinated across RAC nodes. This can be achieved in two modes: centralized mode, where one node builds the HNSW graph for a partition and writes a checkpoint that other nodes then load to ensure consistency; or decentralized mode, where each node independently builds its assigned partition graphs in parallel without relying on shared checkpoints.

Syntax

CREATE VECTOR INDEX VIDX_HNSW ON HOUSES(VEC) ORGANIZATION INMEMORY NEIGHBOR GRAPH [WITH TARGET ACCURACY 95] [DISTANCE EUCLIDEAN] [PARAMETERS (type HNSW, neighbors 32, efConstruction 500)] LOCAL;

Example

CREATE VECTOR INDEX VIDX_HNSW ON HOUSES(VEC)
  ORGANIZATION INMEMORY NEIGHBOR GRAPH
  [WITH TARGET ACCURACY 95]
  [DISTANCE EUCLIDEAN]
  [PARAMETERS (type HNSW, neighbors 32, efConstruction 500)]
  LOCAL;