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.

When using partition pruning, Oracle AI 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. When using local HNSW vector indexes, you may see different optimizer plans for similarity searches. For more information about optimizer plans, see Optimizer Plans for Local HNSW Vector Indexes.

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 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 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.
Using parallel execution (PARALLEL n) is supported during local HNSW vector index creation, enabling local HNSW graphs to be built in parallel.
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.
Partition maintenance operations (PMOPs) are 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.

The availability of local HNSW indexes during similarity searches in an Oracle RAC environment depends on whether the necessary partition-local HNSW graphs are loaded on the RAC nodes executing the query. The optimizer uses the partition-local HNSW index only if all required partition-local graphs are present on the RAC nodes handling the query. If these graphs are not available in the RAC node, the optimizer selects a non-index access path and generates a non-vector index plan for queries executed on that node.
A local HNSW index contains one HNSW graph for each table partition or sub partition. During reload, each partition-local graph is loaded into the Vector Memory Pool. If a checkpoint is available for a graph, the graph is loaded from the checkpoint; otherwise, the graph is recreated. Oracle AI Database cleans up memory associated with partially loaded graphs so the Vector Memory Pool can be used by other vector indexes or future reload attempts.

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
  PARALLEL 4;