Oracle AI Vector Search – A Practical Deep Dive | TechOrbit Blog

Oracle Database · AI Vector Search · 23ai

Getting Started with AI Vector Search in Oracle 23ai

A hands-on walkthrough of vector embeddings, similarity queries, and distance metrics — using a real product catalogue dataset.

#Oracle23ai #VectorSearch #SemanticSearch #AI #SQL

AI-powered search has fundamentally changed how applications surface relevant data. Traditional keyword-based queries still work well for structured lookups, but they completely miss context — a search for "comfortable running footwear" would never match a product described as "ultra-cushioned trail shoe". Oracle Database 23ai solves this with its native AI Vector Search capability.

In this post we'll walk through every concept from scratch and finish with runnable SQL against a real-world-style product catalogue table.

Core Concepts

Vector

An ordered list of numbers (coordinates) that encodes the attributes of an object — text, image, audio, or anything else — in a multi-dimensional space. Example: a two-dimension vector for a red sports shoe might be [0.82, 0.14].

Vectorization / Embedding

The process of converting raw data into a vector using a trained machine-learning model (an embedding model). Similar items end up close together in the resulting vector space.

Vector Database

A database that stores, indexes, and queries vector embeddings alongside traditional data — enabling semantic similarity search at scale.

Semantic Search

Search based on meaning rather than exact keyword matching. Uses NLP and vector distances so a query for "budget laptop" can match listings described as "affordable notebook computer".

Oracle AI Vector Search

A first-class feature of Oracle Database 23ai that adds a native VECTOR data type, vector indexes, and SQL extensions (like VECTOR_DISTANCE) so you can run semantic search inside the same database that holds all your business data.

Key Features at a Glance

VECTOR Data Type

Store vectors in INT8, FLOAT32, or FLOAT64 with any dimension count right in a table column.

Vector Indexes

Purpose-built indexes (IVF, HNSW) that organise high-dimensional data for fast approximate nearest-neighbour queries.

Flexible Embedding

Import ONNX models into Oracle or generate embeddings externally and load them — your choice.

SQL Extensions

Use VECTOR_DISTANCE and FETCH APPROX FIRST inside ordinary SELECT statements.

Single Platform

Combine vector similarity with JSON, graph, spatial, and relational predicates in one query.

Exadata Optimised

Exadata System Software 24ai offloads index creation and vector search to smart storage cells.

Hands-On Demo: Product Catalogue Search

We'll simulate a simplified product catalogue where each item is already embedded into a two-dimensional vector representing two conceptual axes — let's call them price sensitivity and performance tier. In a real system these would be 768- or 1536-dimension embeddings from a language model, but 2D keeps the maths visible.

Step 1 — Connect to Oracle 23ai

SQL*Plus shell

-- Connect as DBA
C:\Windows\System32>sqlplus / as sysdba

SQL*Plus: Release 23.0.0.0.0 - Production on Thu Aug 21 09:14:22 2026
Version 23.6.0.26.07

Copyright (c) 1982, 2026, Oracle.  All rights reserved.

Connected to:
Oracle Database 23ai Free Release 23.0.0.0.0
Version 23.6.0.26.07

SQL> SELECT name, open_mode FROM v$database;

NAME        OPEN_MODE
----------- ----------
ORCL23AI    READ WRITE

Step 2 — Create the Product Catalogue Table

SQL — DDL

-- product_id   : surrogate key
-- sku          : human-readable identifier
-- category     : product family
-- description  : short label
-- v            : 2-D vector embedding (price_sensitivity, performance_tier)

SQL> CREATE TABLE product_catalogue (
  2    product_id   NUMBER(10),
  3    sku          VARCHAR2(20),
  4    category     VARCHAR2(30),
  5    description  VARCHAR2(60),
  6    v            VECTOR
  7 );

Table created.

Step 3 — Insert Sample Data

We have three product families — Footwear, Laptops, and Audio — nine items total.

SQL — DML

-- Footwear products
SQL> INSERT INTO product_catalogue VALUES (1,  'FW-001', 'Footwear', 'Trail Running Shoe',     '[1,-4]');
SQL> INSERT INTO product_catalogue VALUES (2,  'FW-002', 'Footwear', 'Casual Canvas Sneaker',  '[-5,2]');
SQL> INSERT INTO product_catalogue VALUES (3,  'FW-003', 'Footwear', 'Premium Leather Boot',   '[-4,-3]');

-- Laptop products
SQL> INSERT INTO product_catalogue VALUES (4,  'LP-101', 'Laptop',   'Budget Chromebook',      '[3,5]');
SQL> INSERT INTO product_catalogue VALUES (5,  'LP-102', 'Laptop',   'Mid-range Ultrabook',    '[5,-2]');
SQL> INSERT INTO product_catalogue VALUES (6,  'LP-103', 'Laptop',   'Pro Gaming Laptop',      '[-2,5]');

-- Audio products
SQL> INSERT INTO product_catalogue VALUES (7,  'AU-201', 'Audio',    'TWS Earbuds Budget',     '[2,-3]');
SQL> INSERT INTO product_catalogue VALUES (8,  'AU-202', 'Audio',    'Over-Ear Studio Headset','[4,4]');
SQL> INSERT INTO product_catalogue VALUES (9,  'AU-203', 'Audio',    'Portable Bluetooth Speaker','[-3,-5]');

SQL> COMMIT;
Commit complete.

Step 4 — View All Rows

SQL — SELECT *

SQL> SET lines 300 pages 3000
SQL> COL v           FOR a28
SQL> COL description FOR a28
SQL> COL category    FOR a10

SQL> SELECT * FROM product_catalogue;

PRODUCT_ID  SKU     CATEGORY    DESCRIPTION                  V
----------  ------  ----------  ---------------------------  ---------------------------
         1  FW-001  Footwear    Trail Running Shoe           [1.0E+000,-4.0E+000]
         2  FW-002  Footwear    Casual Canvas Sneaker        [-5.0E+000,2.0E+000]
         3  FW-003  Footwear    Premium Leather Boot         [-4.0E+000,-3.0E+000]
         4  LP-101  Laptop      Budget Chromebook            [3.0E+000,5.0E+000]
         5  LP-102  Laptop      Mid-range Ultrabook          [5.0E+000,-2.0E+000]
         6  LP-103  Laptop      Pro Gaming Laptop            [-2.0E+000,5.0E+000]
         7  AU-201  Audio       TWS Earbuds Budget           [2.0E+000,-3.0E+000]
         8  AU-202  Audio       Over-Ear Studio Headset      [4.0E+000,4.0E+000]
         9  AU-203  Audio       Portable Bluetooth Speaker   [-3.0E+000,-5.0E+000]

9 rows selected.

Supported Distance Metrics

Oracle 23ai's VECTOR_DISTANCE function supports the following metrics. Euclidean is the straight-line distance and the most intuitive starting point.

euclidean euclidean_squared cosine dot manhattan hamming

💡

Tip: Use cosine distance when you care about the direction of vectors (topic similarity), and euclidean when magnitude matters too (e.g., price + performance scoring).

Similarity Queries

Query 1 — Find the 3 nearest products to vector (2, -1)

Imagine a user implicitly sitting at coordinate (2, -1) — moderately price-sensitive, mid performance. Which products match best?

SQL — Query 1

SQL> SELECT product_id, sku, category, description
  2   FROM  product_catalogue
  3   ORDER BY VECTOR_DISTANCE(VECTOR('[2,-1]'), v, euclidean)
  4   FETCH FIRST 3 ROWS ONLY;

PRODUCT_ID  SKU     CATEGORY   DESCRIPTION
----------  ------  ---------  ---------------------------
         1  FW-001  Footwear   Trail Running Shoe
         7  AU-201  Audio      TWS Earbuds Budget
         5  LP-102  Laptop     Mid-range Ultrabook

Rank	SKU	Category	Description	Result
1	FW-001	Footwear	Trail Running Shoe	✅ Nearest
2	AU-201	Audio	TWS Earbuds Budget	✅ 2nd
3	LP-102	Laptop	Mid-range Ultrabook	✅ 3rd

Query 2 — Find the 3 nearest products to vector (-3, 4)

A user profile at (-3, 4) represents a high-performance, less price-sensitive buyer. Let's see what Oracle returns.

SQL — Query 2

SQL> SELECT product_id, sku, category, description
  2   FROM  product_catalogue
  3   ORDER BY VECTOR_DISTANCE(VECTOR('[-3,4]'), v, euclidean)
  4   FETCH FIRST 3 ROWS ONLY;

PRODUCT_ID  SKU     CATEGORY   DESCRIPTION
----------  ------  ---------  ---------------------------
         6  LP-103  Laptop     Pro Gaming Laptop
         2  FW-002  Footwear   Casual Canvas Sneaker
         4  LP-101  Laptop     Budget Chromebook

Rank	SKU	Category	Description	Result
1	LP-103	Laptop	Pro Gaming Laptop	✅ Nearest
2	FW-002	Footwear	Casual Canvas Sneaker	✅ 2nd
3	LP-101	Laptop	Budget Chromebook	✅ 3rd

Bonus — Combine Vector Search with a Relational Filter

One of Oracle's biggest advantages: you can mix semantic similarity with standard SQL predicates. Here we restrict results to the Audio category while still ranking by distance.

SQL — Hybrid (vector + relational)

-- Nearest Audio product to vector (3, 3)
SQL> SELECT product_id, sku, description
  2   FROM  product_catalogue
  3   WHERE category = 'Audio'
  4   ORDER BY VECTOR_DISTANCE(VECTOR('[3,3]'), v, cosine)
  5   FETCH FIRST 1 ROW ONLY;

PRODUCT_ID  SKU     DESCRIPTION
----------  ------  ---------------------------
         8  AU-202  Over-Ear Studio Headset

🎯

Because everything lives in one Oracle database, this hybrid query avoids costly data-pipeline round-trips that would be required if you kept vectors in a separate specialised store.

Why Keep Vectors Inside Oracle?

No data movement — your vectors and your business data coexist; no ETL pipelines to a separate vector store.
Unified security — Oracle's row-level security, VPD, and encryption apply automatically to vector columns.
Freshness — you search on live data, not a stale export.
SQL familiarity — every Oracle developer can write semantic search queries on day one.
Polyglot data — mix vectors with JSON, spatial, graph, and text columns in a single SELECT.

Wrapping Up

Oracle AI Vector Search turns Oracle Database 23ai into a fully-featured semantic search engine without adding any external infrastructure. In this post we:

Introduced the core concepts — vectors, embeddings, vector indexes, and semantic search.
Created a VECTOR column inside an ordinary Oracle table.
Inserted two-dimensional embeddings representing a product catalogue.
Ran VECTOR_DISTANCE queries with both euclidean and cosine metrics.
Combined vector similarity with a relational WHERE clause — something unique to a multi-model database like Oracle.

In a follow-up post, we'll look at HNSW vector indexes, approximate nearest-neighbour search with FETCH APPROX FIRST, and importing an ONNX embedding model directly into the database.

Thanks for reading! If you found this useful, leave a comment below or click Follow to catch the next instalment.