Vector Search in Oracle 26ai - A Hands On Example - With FreeSQL Embed

Oracle FreeSQL is Oracle's free, browser based SQL playground that lets you write and run real Oracle Database SQL and PL/SQL directly in your web browser, no installation or setup required. It is basically for learning and practicing Oracle SQL on a real Oracle Database, offering instant access without requiring downloads or configuration. It allows you to write, run, and share SQL scripts directly online.

You can access it with below links,

https://www.oracle.com/database/technologies/oracle-free-sql/

or directly at the playground at 

https://FreeSQL.com

Oracle 26ai brings first class support for AI Vector Datatypes, letting you store embeddings and run similarity search directly inside SQL. 

In this tutorial, we will see,

1. Create a simple table with text along with vector embeddings 

2. Insert sample documents 

3. Run vector similarity search using VECTOR_DISTANCE 

4. Show how readers can experiment using a live FreeSQL worksheet

1. Create Table for Text with Vector Embeddings

 CREATE TABLE documents ( id NUMBER GENERATED BY DEFAULT AS IDENTITY   PRIMARY KEY, 

 title VARCHAR2(100), 

 content VARCHAR2(4000), 

 embedding VECTOR(3) ); 

2. Insert Sample Text along with Embeddings ,

 For simplicity, we'll manually insert tiny vectors 

INSERT INTO documents (title, content, embedding) 

VALUES ( 'SQL Tutorial','Learn the basics of SQL queries, joins, and filtering.', VECTOR('[0.12, 0.33, 0.14]')), 

('PL/SQL Stored Procedures','How to build and execute stored procedures in Oracle.', VECTOR('[0.10, 0.30, 0.11]')), 

('Machine Learning with Python','A guide to data science, python, and ML models.', VECTOR('[0.89, 0.76, 0.81]')), 

('Machine Learning with R','A guide to data science With R .', VECTOR('[0.88, 0.72, 0.80]')), 

('Machine Learning with JAVA','Data science with JAVA.', VECTOR('[0.78, 0.66, 0.58]')), 

('Machine Learning with MATLAB','Engineering Data science with MATLAB.', VECTOR('[0.91, 0.44, 0.35]')) ; 

commit;

3. Perform Vector Similarity Search 

 Suppose the user query embedding is:

SELECT 

title, 

VECTOR_DISTANCE(embedding,VECTOR('[0.11, 0.31, 0.12]'), COSINE ) AS similarity_score 

FROM documents ORDER BY similarity_score ASC 

FETCH FIRST 3 ROWS WITH TIES; 

You can use the Live FreeSQL Playground below to easily try out and understand how FreeSQL works,

Click on "Open in FreeSQL" button in below "live FreeSQL worksheet"

Login with your Oracle SSO account.

Just copy paste the above code and see the results !

 

That's it !

Hope you like it !

Thanks for reading !