Practical notes: Vector‑native RAG on Oracle: embeddings, HNSW/IVF, and hybrid

Use this as an operator-facing rebuild of the ideas in “Vector‑native RAG on Oracle: embeddings, HNSW/IVF, and hybrid search under database governance”: clear stages, ordered code slots, and recovery notes that survive a handoff. The Overview stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Key Takeaways

For the Key Takeaways stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

Prerequisites

For the Prerequisites stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

What “vector‑native” means in Oracle

For the What vector native means stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap. For the What vector native means stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

From document to chunk to embedding

When working through the From document to chunk stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

DBMS_HYBRID_VECTOR: keyword + semantic in one call

When working through the DBMSHYBRIDVECTOR keyword semantic in stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Choosing an index: HNSW or IVF

When working through the Choosing an index HNSW stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface. When working through the Choosing an index HNSW stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Writing hybrid retrieval you can defend

The Writing hybrid retrieval you stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move.

SELECT id, source, content
FROM   documents
WHERE  source = 'kb'
AND    published >= DATE '2025-01-01'
ORDER  BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH  FIRST 5 ROWS ONLY;

SELECT id, source, content
FROM   documents
ORDER  BY embedding <=> :qvec
FETCH  FIRST 3 ROWS ONLY;

A tiny end‑to‑end demo

The A tiny end to stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move.

CREATE TABLE documents (
  id         NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  source     VARCHAR2(64),
  published  DATE,
  content    VARCHAR2(4000), -- use CLOB for larger text in production
  embedding  VECTOR(3, FLOAT32)
);

INSERT INTO documents (source, published, content, embedding) VALUES
  ('kb', DATE '2025-01-15', 'How to reset your password',
   TO_VECTOR('[0.10, 0.05, 0.90]', 3, FLOAT32));

INSERT INTO documents (source, published, content, embedding) VALUES
  ('kb', DATE '2025-02-10', 'How to export monthly invoices',
   TO_VECTOR('[0.80, 0.10, 0.10]', 3, FLOAT32));

INSERT INTO documents (source, published, content, embedding) VALUES
  ('runbook', DATE '2025-03-05', 'Rotate API keys every 90 days',
   TO_VECTOR('[0.15, 0.85, 0.10]', 3, FLOAT32));
COMMIT;

CREATE VECTOR INDEX docs_hnsw_idx
ON documents (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE;

SELECT id, source, content
FROM   documents
ORDER  BY VECTOR_DISTANCE(
           embedding,
           TO_VECTOR('[0.12, 0.04, 0.92]', 3, FLOAT32),
           COSINE
         )
FETCH  FIRST 3 ROWS ONLY;

-- Suppose :qvec is a VECTOR(3, FLOAT32) bind variable
SELECT id, source, content
FROM   documents
ORDER  BY VECTOR_DISTANCE(embedding, :qvec, COSINE)
FETCH  FIRST 3 ROWS ONLY;

SELECT id, source, content
FROM   documents
ORDER  BY embedding <=> :qvec
FETCH  FIRST 3 ROWS ONLY;

SELECT id, source, content
FROM   documents
WHERE  source = 'kb'
ORDER  BY VECTOR_DISTANCE(
           embedding,
           TO_VECTOR('[0.12, 0.04, 0.92]', 3, FLOAT32),
           COSINE
         )
FETCH  FIRST 2 ROWS ONLY;

DROP INDEX docs_hnsw_idx;

CREATE VECTOR INDEX docs_ivf_idx
ON documents (embedding)
ORGANIZATION NEIGHBOR PARTITIONS
DISTANCE COSINE;

Orchestrating the answer: in your app or with Select AI

The Orchestrating the answer in stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Separate chunking policy from retrieval policy. Changing one should not force a rewrite of the other when quality metrics move. The Orchestrating the answer in stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

-- In a session with an AI Profile that scopes access
SELECT AI SHOWSQL 'List the top 3 KB articles about password resets from 2025.'
USING 'profile = <your_ai_profile>';</your_ai_profile>

Operating in production shape

For the Operating in production shape stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

BEGIN
  DBMS_STATS.GATHER_TABLE_STATS(USER, 'DOCUMENTS');
END;
/

BEGIN
  DBMS_STATS.GATHER_INDEX_STATS(USER, 'DOCS_HNSW_IDX');
END;
/

Version scope and environment notes

For the Version scope and environment stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap.

Out of scope

For the Out of scope stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Record timings and token or query cost next to functional results. Cost visibility early prevents surprise bills when the path moves from demo to shared environments. Cite the passages that actually grounded the answer. Without citations, operators cannot tell hallucination from an indexing gap. For the Out of scope stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Try it next

When working through the Try it next stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Prefer small, testable units over sprawling scripts. When a step fails, the failure should point at a single responsibility rather than a tangled pipeline. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

References

When working through the References stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion. Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Operational checklist

When working through the Operational checklist stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest.

Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph.

Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Pin dependency versions and record the image digest that ran the demo. Reproducibility beats tribal knowledge.

Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Measure recall on a fixed question set before tuning prompts. Prompt churn rarely fixes a weak retrieval surface.

Before promoting the stack, freeze versions, capture a golden transcript for the critical path, and confirm rollback steps. Shared environments need rate limits, tenancy checks, and a clear owner for secret rotation. Prefer boring reliability over clever one-off demos.

Batch note for dbbeb034416b: keep provider keys out of the repo, set a per-session token ceiling, and store transcripts next to the eval fixtures so later model swaps stay comparable.

The hardening note 0 stage works best when treated as a measurable surface. Capture one golden transcript, one failure case, and the rollback note before expanding scope. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Hardening detail 0/899: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

For the hardening note 1 stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Keep configuration outside application code. Environment files, secret stores, and feature flags belong in one place operators can audit without reading the whole graph.

Hardening detail 1/899: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

For the hardening note 0 stage, define the inputs, the owner of the step, and the exit criteria before changing code. Operators should be able to re-run the step from a known checkpoint without guessing hidden state. Document the happy path and the recovery path together. Retries, human gates, and dead-letter handling are part of the product, not later polish.

Hardening detail 0/918: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.

When working through the hardening note 1 stage, write down the contract first: required inputs, success signal, and what happens on partial failure. That checklist keeps later code changes honest. Treat this stage as a contract between inputs and validated outputs. Name the artifacts, define success checks, and refuse silent partial completion.

Hardening detail 1/918: measure wall time, error class, and token spend for this note, then decide whether to keep the change based on a fixed question set rather than anecdote.