There's a difference between reading that Oracle 23ai supports native vector search and actually watching a full table scan turn into an index scan on your own Always Free instance. AI tools can explain HNSW indexes to you all day, but they can't hand you a real EXPLAIN PLAN from a database you built yourself, with your own filler data, on your own free-tier hardware.
This post walks through building a small vector search pipeline on a fully free Oracle Autonomous Database instance: table, embeddings, similarity queries, an HNSW index, and a quick benchmark. Here's what the screenshots below show.
Creating the Table
The build starts with a simple table: an identity primary key, a text column, and a VECTOR column typed for 1536-dimension float32 embeddings — the same shape you'd get back from a typical embedding model.
CREATE TABLE docs (
id NUMBER GENERATED ALWAYS AS IDENTITY,
content VARCHAR2(500),
embedding VECTOR(1536, FLOAT32),
PRIMARY KEY (id)
);
Table DOCS created successfully in SQL Developer Web.
Worth calling out explicitly: production embedding models (OpenAI, Cohere, etc.) commonly output 1536-dimension vectors, which is why that number shows up here instead of something smaller and easier to eyeball.
Seeding Data with Random Embeddings
Since there's no live embedding model in this exercise, a small PL/SQL procedure generates pseudo-random 1536-element vectors and inserts rows describing Oracle 23ai's AI Vector Search features — a nice bit of dogfooding, since the demo data is about the feature being demoed.
DECLARE
PROCEDURE add_doc(p_content VARCHAR2) IS
v_str VARCHAR2(32000) := '[';
BEGIN
FOR i IN 1..1536 LOOP
v_str := v_str || TO_CHAR(ROUND(DBMS_RANDOM.VALUE(-1,1), 4));
IF i < 1536 THEN v_str := v_str || ','; END IF;
END LOOP;
v_str := v_str || ']';
INSERT INTO docs (content, embedding) VALUES (p_content, TO_VECTOR(v_str));
END;
BEGIN
add_doc('Oracle 23ai adds native vector search to the converged database.');
add_doc('Autonomous Database patches, tunes and backs itself up automatically.');
add_doc('HNSW indexes trade a small accuracy loss for major speed gains.');
add_doc('Retrieval-augmented generation grounds LLM answers in real documents.');
add_doc('VARCHAR2 columns still store plain text alongside vector data.');
add_doc('Oracle Database 23ai introduces AI Vector Search capabilities natively within the database');
add_doc('It enables AI-powered semantic search using vector embeddings stored alongside enterprise data.');
add_doc('Vector Datatype allows organizations to store and query embeddings directly in Oracle Database.');
add_doc('HNSW and IVF vector indexes improve the performance of similarity searches.');
add_doc('AI Vector Search enables Retrieval-Augmented Generation (RAG) applications using enterprise data.');
add_doc('Oracle 23ai supports Select AI, allowing natural-language interaction with database data using LLMs.');
add_doc('JSON Relational Duality Views provide flexible access to relational data in JSON format.');
add_doc('Property Graph capabilities enable graph-based analysis of relationships within enterprise data.');
add_doc('SQL Firewall provides built-in protection against unauthorized SQL operations.');
add_doc('True Cache improves application performance by providing a read-only in-memory cache for frequently accessed data.');
add_doc('Oracle 23ai enhances JSON support for modern application development.');
add_doc('Schema-level privileges simplify and strengthen database security and access management.');
add_doc('Oracle 23ai provides improved support for JavaScript and modern application development frameworks.');
add_doc('AI Vector Search combined with Oracle Database security allows enterprises to keep sensitive data within the database.');
add_doc('Oracle Database 23ai provides a foundation for building AI-ready, secure, and intelligent enterprise applications.');
COMMIT;
END;
All 20 rows inserted successfully via the anonymous PL/SQL block.
Two things stand out here: TO_VECTOR() doing the string-to-vector conversion, and the fact that even with fully random embeddings, the pipeline mechanics (insert, index, query) work identically to how they would with real semantic embeddings. That's a useful decoupling to notice — the infrastructure doesn't care whether the vectors are meaningful, only the results do.
Scaling Up with a Benchmark Dataset
To get a query plan and timing numbers that actually mean something, the same insert pattern gets wrapped in a bulk-loading procedure and run 2,000 times to generate a benchmark-sized table.
DECLARE
PROCEDURE add_bulk_doc(p_content VARCHAR2) IS
v_str VARCHAR2(32000) := '[';
BEGIN
FOR i IN 1..1536 LOOP
v_str := v_str || TO_CHAR(ROUND(DBMS_RANDOM.VALUE(-1,1), 4));
IF i < 1536 THEN v_str := v_str || ','; END IF;
END LOOP;
v_str := v_str || ']';
INSERT INTO docs (content, embedding) VALUES (p_content, TO_VECTOR(v_str));
END;
BEGIN
FOR i IN 1..2000 LOOP
add_bulk_doc('Filler document number ' || i || ' for timing benchmark purposes.');
END LOOP;
COMMIT;
END;
/
2,000 filler rows loaded in 4.871 seconds on an Always Free ATP instance.
Did You Know? On a fully free ATP instance, loading 2,000 rows of 1536-dimension vectors completed in about 4.5 seconds. That's a genuinely useful number to have, because it's not in any Oracle doc — it's specific to this hardware tier, this row count, and this vector width.
Running a Similarity Search Before Indexing
With ~2,000 rows in place, a nearest-neighbor query against a single reference row (id = 1) shows the core pattern of vector search in SQL: order by distance, take the top N.
SELECT content,
VECTOR_DISTANCE(embedding,
(SELECT embedding FROM docs WHERE id = 1),
COSINE) AS score
FROM docs
WHERE id != 1
ORDER BY score
FETCH FIRST 5 ROWS ONLY;
Top 5 nearest neighbors ranked by cosine distance, no index yet.
At this point there's no vector index yet, so this query is doing a full table scan and computing distance row-by-row — fine at 2,000 rows, but exactly the pattern that gets expensive at scale.
Adding an HNSW Index
Next, an HNSW (Hierarchical Navigable Small World) index gets built in-memory over the same column, targeting 95% recall accuracy instead of exact nearest-neighbor:
CREATE VECTOR INDEX docs_hnsw_idx ON docs (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;
HNSW vector index created in-memory, targeting 95% recall.
Worth Noting The WITH TARGET ACCURACY 95 clause is an explicit trade-off knob: you're telling Oracle it's allowed to be approximate in exchange for speed, and you get to pick how approximate.
Comparing the Execution Plan
Finally, EXPLAIN PLAN on the same query reveals whether the optimizer is actually using the new index:
EXPLAIN PLAN FOR
SELECT content,
VECTOR_DISTANCE(embedding,
(SELECT embedding FROM docs WHERE id = 1),
COSINE) AS score
FROM docs
WHERE id != 1
ORDER BY score
FETCH FIRST 5 ROWS ONLY;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
Explain plan generated and stored via DBMS_XPLAN.
Graphical plan: SELECT STATEMENT feeding from a COLLECTION ITERATOR PICKLER step
This is the payoff of the whole exercise: a plan graph you generated yourself, on your own free-tier data, that you can compare against the pre-index full-scan plan. No AI explanation of "HNSW indexes trade a small accuracy loss for major speed gains" substitutes for actually seeing your own cost numbers change.
The Real Takeaway
None of this — the exact DDL, the elapsed times, the shape of the execution plan on a small dataset — is something an AI model can hand you from memory. It can explain the concepts (HNSW vs IVF, cosine vs Euclidean, why 1536 dimensions is common), but the actual benchmark numbers, the actual plan output, and the actual "did the index get used" answer only come from running it yourself on real infrastructure. That's the gap between reading about Oracle 23ai Vector Search and building it.