/*===============================================================
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 13
VECTOR INDEX INTERNALS AND PERFORMANCE TUNING
=================================================================
Vector indexes accelerate similarity searches
for high-dimensional embeddings.
Without vector indexes:
Full table scan occurs.
With vector indexes:
Approximate Nearest Neighbor (ANN) search is used.
ANN drastically improves performance for large datasets.
===============================================================*/
/*===============================================================
SECTION 1
CREATE LARGE VECTOR DATASET
===============================================================*/
DROP TABLE ai_documents PURGE;
CREATE TABLE ai_documents
(
doc_id NUMBER PRIMARY KEY,
doc_text VARCHAR2(1000),
embedding VECTOR(8)
);
/* Sample documents */
INSERT INTO ai_documents VALUES
(1,'Oracle AI database tutorial',VECTOR '[0.12,0.11,0.15,0.20,0.33,0.44,0.22,0.18]');
INSERT INTO ai_documents VALUES
(2,'Machine learning fundamentals',VECTOR '[0.88,0.71,0.60,0.65,0.50,0.44,0.39,0.55]');
INSERT INTO ai_documents VALUES
(3,'Deep learning neural networks',VECTOR '[0.91,0.80,0.75,0.70,0.69,0.61,0.59,0.66]');
INSERT INTO ai_documents VALUES
(4,'SQL query optimization',VECTOR '[0.10,0.09,0.12,0.14,0.18,0.22,0.25,0.30]');
COMMIT;
/*===============================================================
SECTION 2
VECTOR INDEX CREATION
===============================================================*/
CREATE VECTOR INDEX ai_documents_vector_idx
ON ai_documents(embedding);
/*===============================================================
SECTION 3
SIMILARITY SEARCH QUERY
===============================================================*/
SELECT
doc_id,
doc_text
FROM ai_documents
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.90,0.75,0.60,0.66,0.55,0.50,0.44,0.52]',
COSINE
)
FETCH FIRST 3 ROWS ONLY;
/*===============================================================
SECTION 4
CHECK EXECUTION PLAN
===============================================================*/
EXPLAIN PLAN FOR
SELECT
doc_id
FROM ai_documents
ORDER BY
VECTOR_DISTANCE(
embedding,
VECTOR '[0.90,0.75,0.60,0.66,0.55,0.50,0.44,0.52]',
COSINE
);
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
/*===============================================================
VECTOR PERFORMANCE TUNING TIPS
===============================================================
Use VECTOR INDEX
Keep embedding dimensions consistent
Use COSINE distance for semantic search
Avoid full table scans
Store embeddings generated by AI models
===============================================================*/
/*===============================================================
END OF PART 13
===============================================================
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.
===============================================================*/