/*===============================================================
ORACLE 23AI COMPLETE AI + SQL + MACHINE LEARNING MASTER SCRIPT
Author : Vinayak Vishweshwara Dabgar
www.dabgarvinayakv.com
Oracle Apps DBA - Consultant | DBA - Developer | Database Architect | Performance Tuning Expert
PART 11
VECTOR SEARCH AND RAG (Retrieval Augmented Generation)
=================================================================
Oracle 23ai introduces vector search capabilities which allow
AI applications to store embeddings and perform similarity search.
Embeddings are numeric representations of text, images,
or documents generated by AI models.
Example embedding
[0.12, 0.98, 0.44, 0.66]
Documents with similar meaning will have embeddings
close to each other in vector space.
Vector search allows AI systems to retrieve relevant
documents from databases before generating responses.
This architecture is called RAG:
Retrieval Augmented Generation
Steps in RAG
1 User asks question
2 Question converted to embedding
3 Database performs vector similarity search
4 Relevant documents retrieved
5 AI model generates response using retrieved context
===============================================================*/
/*===============================================================
SECTION 1
DOCUMENT KNOWLEDGE BASE
===============================================================*/
DROP TABLE ai_knowledge_base PURGE;
CREATE TABLE ai_knowledge_base
(
doc_id NUMBER PRIMARY KEY,
title VARCHAR2(200),
content VARCHAR2(2000),
embedding VECTOR(4)
);
/*===============================================================
INSERT SAMPLE DOCUMENTS
===============================================================*/
INSERT INTO ai_knowledge_base VALUES
(
1,
'Oracle Database Basics',
'Oracle database is a relational database system used for enterprise applications.',
VECTOR '[0.10,0.21,0.33,0.47]'
);
INSERT INTO ai_knowledge_base VALUES
(
2,
'Machine Learning Introduction',
'Machine learning allows computers to learn patterns from data.',
VECTOR '[0.88,0.75,0.66,0.70]'
);
INSERT INTO ai_knowledge_base VALUES
(
3,
'Artificial Intelligence Overview',
'Artificial intelligence enables machines to perform intelligent tasks.',
VECTOR '[0.92,0.83,0.71,0.69]'
);
INSERT INTO ai_knowledge_base VALUES
(
4,
'SQL Performance Tuning',
'SQL tuning improves database query performance.',
VECTOR '[0.11,0.19,0.30,0.45]'
);
COMMIT;
/*===============================================================
SECTION 2
VIEW KNOWLEDGE BASE
===============================================================*/
SELECT * FROM ai_knowledge_base;
/*===============================================================
SECTION 3
VECTOR SIMILARITY SEARCH
===============================================================
Find documents most similar to a query vector.
Lower distance = higher similarity.
===============================================================*/
SELECT
doc_id,
title,
VECTOR_DISTANCE(
embedding,
VECTOR '[0.90,0.80,0.70,0.68]',
COSINE
) AS similarity_score
FROM ai_knowledge_base
ORDER BY similarity_score;
/*===============================================================
SECTION 4
TOP-K DOCUMENT RETRIEVAL
===============================================================*/
SELECT
doc_id,
title,
content
FROM ai_knowledge_base
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.90,0.80,0.70,0.68]',
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
SECTION 5
CREATE VECTOR INDEX
===============================================================
Vector indexes accelerate similarity search
for large datasets.
===============================================================*/
CREATE VECTOR INDEX ai_vector_index
ON ai_knowledge_base(embedding);
/*===============================================================
SECTION 6
AI QUESTION ANSWERING EXAMPLE
===============================================================
User Question
"What is machine learning?"
System converts question to embedding.
Database retrieves similar documents.
===============================================================*/
SELECT
doc_id,
title,
content
FROM ai_knowledge_base
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.87,0.73,0.65,0.70]',
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
SECTION 7
DOCUMENT RECOMMENDATION SYSTEM
===============================================================
Example
User reads article about SQL.
System recommends similar articles.
===============================================================*/
SELECT
doc_id,
title
FROM ai_knowledge_base
WHERE doc_id <> 4
ORDER BY
VECTOR_DISTANCE(
embedding,
(
SELECT embedding
FROM ai_knowledge_base
WHERE doc_id=4
),
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
SECTION 8
SEMANTIC SEARCH
===============================================================
Traditional search
keyword matching
Semantic search
meaning-based similarity using vectors.
===============================================================*/
SELECT
doc_id,
title
FROM ai_knowledge_base
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.12,0.20,0.34,0.48]',
COSINE
)
FETCH FIRST 3 ROWS ONLY;
/*===============================================================
SECTION 9
REAL WORLD APPLICATIONS
===============================================================
Vector search powers many modern AI systems.
Applications include
Enterprise knowledge search
Customer support AI
Document retrieval
Recommendation engines
Fraud pattern similarity detection
===============================================================*/
/*===============================================================
SECTION 10
RAG ARCHITECTURE SUMMARY
===============================================================
RAG = Retrieval Augmented Generation
Components
Vector embeddings
Vector similarity search
Knowledge database
AI generation model
Oracle 23ai provides native support for:
Vector storage
Vector indexing
Vector similarity queries
===============================================================*/
/*===============================================================
END OF PART 11
===============================================================
Disclaimer and Limitation of Liability
The author provides all scripts, queries, and related materials βas isβ, without any express or implied
warranties, including but not limited to warranties of accuracy, completeness, reliability,
merchantability, or fitness for a particular purpose.
By using, executing, or implementing any part of this material, the user acknowledges and agrees
that they do so at their own risk. The author shall not be held liable for any direct, indirect,
incidental, consequential, special, or exemplary damages, including but not limited to loss of data,
loss of profits, system failures, security breaches, or any other damages or losses arising from the
use or misuse of these materials.
Users are strongly advised to thoroughly review, validate, and test all scripts, queries, and
configurations in a controlled, non-production (test) environment prior to deploying them in any
live or production systems.
It is the sole responsibility of the user to ensure that the use of these materials complies with
all applicable local, national, and international laws, regulations, and organizational policies.
By proceeding to use these materials, the user agrees to indemnify and hold harmless the author
from any claims, damages, liabilities, or expenses arising from their use.
===============================================================*/