Oracle AI Database 26ai Vector Search for app developers

Oracle AI Database 26ai (AI Vector Search) has a native VECTOR data type and SQL functions for similarity search. You store embeddings beside your rows and run semantic queries in plain SQL, with no separate vector store.

Add a VECTOR column

ALTER TABLE docs ADD (embedding VECTOR(1536, FLOAT32));

The first number is the dimension count and must match your embedding model (1536 here is only an example; check your model's output size). The database rejects a vector of the wrong size with ORA-51803. FLOAT32 is the element format; INT8, FLOAT64 and BINARY also exist.

Populate and query

Generate an embedding for each row with an embedding model and store it in the column. You can call a model from your application, or load an ONNX embedding model into the database with DBMS_VECTOR.LOAD_ONNX_MODEL and generate embeddings in SQL with VECTOR_EMBEDDING. Then rank rows by their distance to the query's embedding with VECTOR_DISTANCE:

SELECT id, body
FROM   docs
ORDER  BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH EXACT FIRST 5 ROWS ONLY;

FETCH EXACT forces an exact search that compares the query vector with every row. FETCH APPROX asks for an approximate search through a vector index. If you write neither, the database may use a vector index when one exists, so write EXACT when you need guaranteed exact results. :query_vec is a bind variable holding the question's embedding, produced by the same model as the stored vectors. Use the distance metric your model recommends. COSINE is the default.

Speed it up with a vector index

On larger tables, create a vector index and ask for an approximate search with FETCH APPROX. An IVF (Inverted File Flat) index works without extra memory configuration:

CREATE VECTOR INDEX docs_ivf_idx ON docs (embedding)
  ORGANIZATION NEIGHBOR PARTITIONS
  DISTANCE COSINE
  WITH TARGET ACCURACY 95;

SELECT id, body
FROM   docs
ORDER  BY VECTOR_DISTANCE(embedding, :query_vec, COSINE)
FETCH APPROX FIRST 5 ROWS ONLY;

Check your understanding