Vector Index Concepts, Approximate vs Exact Search, Recall/Latency Tradeoffs, and Evaluation

Learning outcomes

Exact ServiceHub vector search scans every qualifying embedding and returns mathematically exact top-k neighbors. As the collection grows, latency may become too high. Approximate Nearest Neighbor (ANN) indexes deliberately search a subset/graph/partition of vector space to reduce work, so the question is no longer only “is it faster?” but “what recall did we lose, at what latency/build/update/memory cost?” Oracle 26ai provides Hierarchical Navigable Small World (HNSW) graph indexes and Inverted File Flat (IVF) partition/centroid indexes.

1. Exact search is the ground truth for evaluation

FETCH EXACT FIRST k ROWS ONLY forces exact ranking for eligible vector distance queries. FETCH APPROX FIRST k ROWS ONLY WITH TARGET ACCURACY n requests approximate indexed search when syntax, index, metric and optimizer rules allow it. If approximate rules/index use are not satisfied, Oracle can fall back to exact behavior—so inspect the execution plan.

2. HNSW and IVF trade different resources

HNSW index data and metadata live in the SGA Vector Pool. IVF centroids can use Vector Pool/large-pool caching for build/maintenance but the index's main partition structures are not the same persistent in-memory graph. Free's 2 GB database RAM makes HNSW Vector Pool budgeting especially important.

3. Inspect Vector Pool before choosing HNSW

SELECT name,value,issys_modifiable,ispdb_modifiableFROM v$parameterWHERE name IN (  'vector_memory_size',  'sga_target',  'memory_target')ORDER BY name;SELECT *FROM v$vector_memory_poolORDER BY pool;

VECTOR_MEMORY_SIZE is dynamically configurable. At CDB level it is the current Vector Pool size; at PDB level it is the PDB quota. Current 26ai can auto-grow HNSW memory in some SGA-target configurations, but Free's small total memory means “automatic” is not “free.”

4. Build a deterministic 4D evaluation corpus

BEGIN  EXECUTE IMMEDIATE 'DROP TABLE sh25_index_eval PURGE';EXCEPTION WHEN OTHERS THEN  IF SQLCODE != -942 THEN RAISE; END IF;END;/CREATE TABLE sh25_index_eval (  item_id NUMBER PRIMARY KEY,  tenant_code VARCHAR2(20) NOT NULL,  embedding VECTOR(4,FLOAT32,DENSE) NOT NULL);INSERT INTO sh25_index_eval(item_id,tenant_code,embedding)SELECT  LEVEL,  CASE WHEN MOD(LEVEL,2)=0 THEN 'TENANT_A' ELSE 'TENANT_B' END,  TO_VECTOR(    JSON_ARRAY(      MOD(LEVEL*17,101)/100,      MOD(LEVEL*29,103)/102,      MOD(LEVEL*43,107)/106,      MOD(LEVEL*61,109)/108      RETURNING CLOB    ),    4,    FLOAT32  )FROM dualCONNECT BY LEVEL <= 2000;COMMIT;BEGIN  DBMS_STATS.GATHER_TABLE_STATS(USER,'SH25_INDEX_EVAL');END;/

This is synthetic vector geometry for index evaluation, not a semantic embedding benchmark. A production recall test must use representative query vectors from the actual embedding model/workload.

5. Create an IVF vector index

CREATE VECTOR INDEX sh25_ivf_ixON sh25_index_eval(embedding)ORGANIZATION NEIGHBOR PARTITIONSWITH TARGET ACCURACY 90DISTANCE COSINEPARAMETERS (  TYPE IVF,  NEIGHBOR PARTITIONS 20);

NEIGHBOR PARTITIONS controls target centroid partition count, not query top-k. More/fewer partitions change training/storage/probe tradeoffs. Do not copy 20 to production; derive build/search parameters from dataset size/distribution and evaluation.

6. Capture exact top-k ground truth

BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh25_exact_topk PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/BEGIN EXECUTE IMMEDIATE 'DROP TABLE sh25_approx_topk PURGE';EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF; END;/CREATE TABLE sh25_exact_topk (  item_id NUMBER PRIMARY KEY,  distance NUMBER);CREATE TABLE sh25_approx_topk (  item_id NUMBER PRIMARY KEY,  distance NUMBER);VARIABLE q VECTORBEGIN  :q := VECTOR('[0.70,0.20,0.80,0.40]',4,FLOAT32);END;/INSERT INTO sh25_exact_topkSELECT item_id,       VECTOR_DISTANCE(embedding,:q,COSINE)FROM sh25_index_evalWHERE tenant_code='TENANT_A'ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH EXACT FIRST 20 ROWS ONLY;COMMIT;

7. Capture approximate top-k

INSERT INTO sh25_approx_topkSELECT item_id,       VECTOR_DISTANCE(embedding,:q,COSINE)FROM sh25_index_evalWHERE tenant_code='TENANT_A'ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH APPROX FIRST 20 ROWS ONLYWITH TARGET ACCURACY 90;COMMIT;

The optimizer may choose pre-, in-, or post-filtering for relational predicates depending on index type/statistics/query shape. Post-filtering can return fewer than requested rows if many ANN candidates fail the relational predicate; this is a correctness/UX consideration, not merely a plan detail.

8. Compute recall@20 directly

SELECT  COUNT(*) AS overlapping_ids,  20 AS k,  ROUND(COUNT(*)/20*100,2) AS recall_at_20_pctFROM sh25_exact_topk eJOIN sh25_approx_topk a  ON a.item_id=e.item_id;

Recall@k is the fraction of exact top-k IDs recovered by the approximate result. It is one quality metric; application relevance may additionally need labels/judgments, precision/NDCG/MRR, reranking and business outcome checks.

9. Use Oracle's index accuracy API

SET SERVEROUTPUT ONDECLARE  l_report VARCHAR2(4000);BEGIN  l_report := DBMS_VECTOR.INDEX_ACCURACY_QUERY(    owner_name      => USER,    index_name      => 'SH25_IVF_IX',    qv              => :q,    top_k           => 20,    target_accuracy => 90  );  DBMS_OUTPUT.PUT_LINE(l_report);END;/

The report compares indexed approximate behavior with an exact reference for that query vector. One query is not enough to choose parameters; use a representative query set and tail latency.

10. Verify index use in the runtime plan

SELECT /*+ gather_plan_statistics */       item_idFROM sh25_index_evalWHERE tenant_code='TENANT_A'ORDER BY VECTOR_DISTANCE(embedding,:q,COSINE)FETCH APPROX FIRST 20 ROWS ONLYWITH TARGET ACCURACY 90;SELECT *FROM TABLE(  DBMS_XPLAN.DISPLAY_CURSOR(    NULL,NULL,'ALLSTATS LAST +PREDICATE +NOTE'  ));

Look for vector-index operations rather than assuming APPROX guarantees index use. A metric mismatch between query and index prevents that vector index from satisfying the approximate search and can lead to exact search.

11. HNSW syntax and build controls

CREATE VECTOR INDEX sh25_hnsw_ixON sh25_index_eval(embedding)ORGANIZATION INMEMORY NEIGHBOR GRAPHWITH TARGET ACCURACY 90DISTANCE COSINEPARAMETERS (  TYPE HNSW,  NEIGHBORS 16,  EFCONSTRUCTION 64);

NEIGHBORS (M) controls graph connectivity and EFCONSTRUCTION controls candidate exploration during build. Higher values can improve search quality but increase memory/build cost. The course does not run this second index by default on a 2 GB Free database; it is a design comparison.

12. 23.26.3 feature boundary

The July 2026 RU 23.26.3 release note specifically adds scalar quantization support for distributed HNSW indexes. Distributed HNSW is a RAC/topology-heavy design. Do not tell an operator on 23.26.1/23.26.2 that this distributed-HNSW quantization capability exists there merely because ordinary HNSW indexes do. The local IVF/HNSW syntax in this lesson predates that RU-specific enhancement.

13. Deliberately wrong: compare only latency

An ANN query can be 10× faster and still be unacceptable if it drops the only legally/clinically/business-critical neighbor. Conversely, exact search can be wasteful if recall@k 99% meets the application SLO. Record latency distribution, recall, build duration, memory/storage, DML/index-maintenance cost and re-evaluation after data/model drift.

14. Cleanup

DROP TABLE sh25_exact_topk PURGE;DROP TABLE sh25_approx_topk PURGE;DROP INDEX sh25_ivf_ix;DROP TABLE sh25_index_eval PURGE;-- Drop SH25_HNSW_IX too if you chose to create the optional HNSW example.

15. Production judgment

Start with exact retrieval as a correctness baseline, then introduce ANN only when measured scale/latency requires it. Choose HNSW when memory-resident graph tradeoffs fit the workload; choose IVF when centroid partitioning/disk-oriented behavior fits. Keep the index distance metric aligned with the query/model and evaluate many representative queries.

VECTOR requires COMPATIBLE >= 23.4.0. HNSW uses Vector Pool memory; VECTOR_MEMORY_SIZE is dynamic and PDB-modifiable as a quota. No restart is inherently required for this IVF lab. Current 23.26.3 distributed-HNSW scalar quantization is RAC-specific and not used. Lesson 3 combines vector ranking with text, JSON, structured predicates and row-level security so “more relevant” never means “less authorized.”

Authoritative references

• Vector Index Guidelines — HNSW/IVF metrics/parameters
• HNSW Syntax and Parameters — HNSW DDL
• IVF Syntax and Parameters — IVF DDL
• Index Accuracy Report — DBMS_VECTOR accuracy/recall
• July 2026 RU 23.26.3 Vector Updates — distributed HNSW scalar quantization