/*===============================================================
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 4
VECTOR DATA TYPE AND AI EMBEDDINGS
=================================================================
Oracle 23ai introduces a new VECTOR data type.
VECTOR columns allow databases to store high-dimensional
numeric arrays called embeddings.
Embeddings are used in AI systems for:
β’ semantic search
β’ similarity matching
β’ recommendation engines
β’ AI chat systems
β’ document search
β’ RAG architectures
Example
Embedding vector
[0.12, 0.98, 0.44, 0.66]
Each number represents a dimension in vector space.
Two vectors that are similar will be close
in mathematical distance.
===============================================================*/
/*===============================================================
SECTION 1
CREATE VECTOR TABLE
===============================================================*/
DROP TABLE document_embeddings PURGE;
CREATE TABLE document_embeddings
(
doc_id NUMBER PRIMARY KEY,
doc_text VARCHAR2(1000),
/* VECTOR(4) means vector dimension is 4 */
embedding VECTOR(4)
);
/*===============================================================
INSERT SAMPLE VECTOR DATA
===============================================================*/
INSERT INTO document_embeddings VALUES
(
1,
'Oracle database tutorial',
VECTOR '[0.12,0.22,0.45,0.90]'
);
INSERT INTO document_embeddings VALUES
(
2,
'Machine learning introduction',
VECTOR '[0.10,0.20,0.40,0.85]'
);
INSERT INTO document_embeddings VALUES
(
3,
'Artificial intelligence concepts',
VECTOR '[0.95,0.80,0.75,0.70]'
);
INSERT INTO document_embeddings VALUES
(
4,
'Database SQL performance tuning',
VECTOR '[0.11,0.24,0.46,0.88]'
);
COMMIT;
/*===============================================================
VIEW VECTOR DATA
===============================================================*/
SELECT * FROM document_embeddings;
/*===============================================================
SECTION 2
VECTOR_DISTANCE FUNCTION
===============================================================
VECTOR_DISTANCE measures similarity between vectors.
Supported distance metrics
COSINE
EUCLIDEAN
DOT_PRODUCT
Lower distance means vectors are more similar.
===============================================================*/
SELECT
doc_id,
doc_text,
VECTOR_DISTANCE(
embedding,
VECTOR '[0.10,0.20,0.40,0.85]',
COSINE
) AS similarity_score
FROM document_embeddings
ORDER BY similarity_score;
/*===============================================================
SECTION 3
SEMANTIC SEARCH
===============================================================
Find documents similar to a query vector.
Example
User asks
"machine learning basics"
Embedding generated by AI model:
[0.10,0.20,0.40,0.85]
Database returns nearest vectors.
===============================================================*/
SELECT
doc_id,
doc_text
FROM document_embeddings
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.10,0.20,0.40,0.85]',
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
SECTION 4
TOP-N SIMILARITY SEARCH
===============================================================*/
SELECT
doc_id,
doc_text,
VECTOR_DISTANCE(
embedding,
VECTOR '[0.12,0.22,0.45,0.90]',
COSINE
) AS similarity
FROM document_embeddings
ORDER BY similarity
FETCH FIRST 3 ROWS ONLY;
/*===============================================================
SECTION 5
VECTOR INDEX
===============================================================
Vector indexes accelerate similarity search.
Without index
Full table scan
With vector index
Approximate nearest neighbor search
===============================================================*/
CREATE VECTOR INDEX doc_vector_index
ON document_embeddings(embedding);
/*===============================================================
SECTION 6
RECOMMENDATION ENGINE EXAMPLE
===============================================================
Example use case
User reads article
"Machine learning introduction"
System recommends similar articles.
===============================================================*/
SELECT
doc_id,
doc_text
FROM document_embeddings
WHERE doc_id <> 2
ORDER BY
VECTOR_DISTANCE(
embedding,
(
SELECT embedding
FROM document_embeddings
WHERE doc_id = 2
),
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
SECTION 7
AI KNOWLEDGE SEARCH
===============================================================
Example scenario
AI chatbot asks database:
"Find documents related to AI"
Embedding generated by AI model
Database performs similarity search.
===============================================================*/
SELECT
doc_id,
doc_text
FROM document_embeddings
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.90,0.85,0.70,0.75]',
COSINE
)
FETCH FIRST 2 ROWS ONLY;
/*===============================================================
SECTION 8
EUCLIDEAN DISTANCE EXAMPLE
===============================================================*/
SELECT
doc_id,
doc_text,
VECTOR_DISTANCE(
embedding,
VECTOR '[0.10,0.20,0.40,0.85]',
EUCLIDEAN
) AS distance
FROM document_embeddings
ORDER BY distance;
/*===============================================================
SECTION 9
DOT PRODUCT SIMILARITY
===============================================================*/
SELECT
doc_id,
doc_text,
VECTOR_DISTANCE(
embedding,
VECTOR '[0.10,0.20,0.40,0.85]',
DOT_PRODUCT
) AS similarity
FROM document_embeddings
ORDER BY similarity DESC;
/*===============================================================
SECTION 10
VECTOR USE CASES
===============================================================
Oracle VECTOR features enable:
β’ AI document search
β’ ChatGPT knowledge retrieval
β’ semantic search engines
β’ recommendation systems
β’ fraud detection similarity analysis
β’ AI powered enterprise search
VECTOR + SQL = powerful AI databases.
===============================================================*/
/*===============================================================
END OF PART 4
===============================================================
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.
===============================================================*/