Tales From A Lazy Fat DBA

Its all about Databases, their performance, troubleshooting & much more …. ¯\_(ツ)_/¯

Posts Tagged ‘vector’

Loading an ONNX embedding model into Oracle AI Database 26ai

Posted by FatDBA on August 11, 2026

As part of a recent AI and RAG proof of concept, I needed to load the all-MiniLM-L12-v2 embedding model directly into Oracle AI Database 26ai. The process was straightforward, although I encountered one small connection issue that is worth documenting.

ONNX stands for Open Neural Network Exchange. It is an open format for representing machine-learning models independently of the framework in which they were created. For example, a model may be trained using PyTorch and then exported to ONNX for optimized inference in another environment. This makes the model more portable and avoids tying it permanently to one machine-learning framework. In Oracle AI Database 26ai, an ONNX embedding model can be loaded as a database object and called directly from SQL. Text can therefore be converted into vectors without sending it to an external AI service.

Running the embedding model inside the database provides several advantages:

  • Sensitive text remains inside the database.
  • No external REST API or internet connection is required during inference.
  • Network latency and API usage costs are avoided.
  • Embeddings can be generated directly through SQL and PL/SQL.
  • The model can be used consistently for document and search-query embeddings.
  • Deployment becomes easier because a separate embedding service is not required.

For this test, I used all-MiniLM-L12-v2. It is a relatively small and efficient English-language model that produces 384-dimensional vectors, making it a good choice for semantic search, document retrieval and lightweight RAG solutions.

A wquick comparison between difference approaches and why and when ONNX can become a preferred choice. ONNX is particularly useful when data privacy, predictable performance and simplified architecture are more important than continuously switching to the latest hosted embedding model.

ApproachMain advantageMain consideration
In-database ONNXPrivate, fast and no external API dependencyUses database CPU and memory
External embedding APIEasy access to newer hosted modelsNetwork latency, API cost and data-privacy considerations
PyTorch/TensorFlow serviceMaximum flexibility and fine-tuning optionsRequires a separate runtime and serving infrastructure

Below are the steps to download and load the model … Oracle provides an augmented version of all-MiniLM-L12-v2 that includes the required tokenization and post-processing logic. This is important because a raw ONNX model downloaded directly from Hugging Face may contain only the neural network and may not be sufficient for direct in-database text embedding.

[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$ ps -ef|grep pmon
oracle     36866    4602  0 03:37 ?        00:00:21 ora_pmon_myaidb
oracle     92325   92163  0 17:14 pts/0    00:00:00 grep --color=auto pmon
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$ . oraenv
ORACLE_SID = [oracle] ? myaidb
The Oracle base has been set to /u01/app/oracle
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$ !sql
sqlplus / as sysdba

SQL*Plus: Release 23.26.1.0.0 - Production on Tue Aug 11 17:14:11 2026
Version 23.26.1.0.0

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


Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0

SQL>
SQL>
SQL> exit
Disconnected from Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$ sudo mkdir -p /u01/app/oracle/onnx_models
[sudo] password for oracle:
oracle is not in the sudoers file.  This incident will be reported.
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$ cd /u01/app/oracle/onnx_models
-bash: cd: /u01/app/oracle/onnx_models: No such file or directory
[oracle@fatdba1 ~]$  mkdir -p /u01/app/oracle/onnx_models
[oracle@fatdba1 ~]$
[oracle@fatdba1 ~]$ cd /u01/app/oracle/onnx_models
[oracle@fatdba1 onnx_models]$ wget https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/TtH6hL2y25EypZ0-rrczRZ1aXp7v1ONbRBfCiT-BDBN8WLKQ3lgyW6RxCfIFLdA6/n/adwc4pm/b/OML-ai-models/o/all_MiniLM_L12_v2_augmented.zip
--2026-08-11 17:16:56--  https://adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com/p/TtH6hL2y25EypZ0-rrczRZ1aXp7v1ONbRBfCiT-BDBN8WLKQ3lgyW6RxCfIFLdA6/n/adwc4pm/b/OML-ai-models/o/all_MiniLM_L12_v2_augmented.zip
Resolving adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com (adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com)... 134.70.24.1, 134.70.32.1, 134.70.28.1
Connecting to adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com (adwc4pm.objectstorage.us-ashburn-1.oci.customer-oci.com)|134.70.24.1|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 122537890 (117M) [application/x-zip-compressed]
Saving to: ‘all_MiniLM_L12_v2_augmented.zip’

all_MiniLM_L12_v2_augmented.zip                 100%[====================================================================================================>] 116.86M  7.92MB/s    in 19s

2026-08-11 17:17:17 (6.09 MB/s) - ‘all_MiniLM_L12_v2_augmented.zip’ saved [122537890/122537890]

[oracle@fatdba1 onnx_models]$
[oracle@fatdba1 onnx_models]$ ls -lh /u01/app/oracle/onnx_models
total 117M
-rw-r--r--. 1 oracle oinstall 117M Oct 30  2025 all_MiniLM_L12_v2_augmented.zip
[oracle@fatdba1 onnx_models]$ unzip all_MiniLM_L12_v2_augmented.zip
Archive:  all_MiniLM_L12_v2_augmented.zip
  inflating: all_MiniLM_L12_v2.onnx
  inflating: LICENSE_ATTRIBUTION.txt
  inflating: README-ALL_MINILM_L12_V2-augmented.txt
[oracle@fatdba1 onnx_models]$ ls -ltrh
total 245M
-rw-rw-rw-. 1 oracle oinstall 4.2K Oct 30  2025 README-ALL_MINILM_L12_V2-augmented.txt
-rw-rw-rw-. 1 oracle oinstall  12K Oct 30  2025 LICENSE_ATTRIBUTION.txt
-rw-rw-rw-. 1 oracle oinstall 128M Oct 30  2025 all_MiniLM_L12_v2.onnx
-rw-r--r--. 1 oracle oinstall 117M Oct 30  2025 all_MiniLM_L12_v2_augmented.zip
[oracle@fatdba1 onnx_models]$ sqlplus / as sysdba

SQL*Plus: Release 23.26.1.0.0 - Production on Tue Aug 11 17:20:34 2026
Version 23.26.1.0.0

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


Connected to:
Oracle AI Database 26ai Enterprise Edition Release 23.26.1.0.0 - Production
Version 23.26.1.0.0

SQL> SHOW PDBS;

    CON_ID CON_NAME                       OPEN MODE  RESTRICTED
---------- ------------------------------ ---------- ----------
         2 PDB$SEED                       READ ONLY  NO
         3 MYYAIDB                        READ WRITE NO
SQL> ALTER SESSION SET CONTAINER = MYYAIDB;

Session altered.

SQL> SHOW CON_NAME;

CON_NAME
------------------------------
MYYAIDB
SQL> CREATE USER vector_user IDENTIFIED BY oracle90 DEFAULT TABLESPACE users QUOTA UNLIMITED ON users;

User created.

SQL> GRANT DB_DEVELOPER_ROLE TO vector_user;

Grant succeeded.

SQL> GRANT CREATE MINING MODEL TO vector_user;

Grant succeeded.

SQL> CREATE OR REPLACE DIRECTORY ONNX_DIR AS '/u01/app/oracle/onnx_models';

Directory created.

SQL> GRANT READ, WRITE ON DIRECTORY ONNX_DIR TO vector_user;

Grant succeeded.

SQL> SELECT directory_name, directory_path
FROM dba_directories
WHERE directory_name = 'ONNX_DIR';  2    3

DIRECTORY_NAME
--------------------------------------------------------------------------------
DIRECTORY_PATH
--------------------------------------------------------------------------------
ONNX_DIR
/u01/app/oracle/onnx_models


SQL> 



SQL> CONNECT vector_user@"//127.0.0.1:1521/myyaidb"
Enter password:
Connected.
SQL>
SQL> SHOW USER;
SHOW CON_NAME;USER is "VECTOR_USER"
SQL>

CON_NAME
------------------------------
MYYAIDB
SQL> BEGIN
  DBMS_VECTOR.LOAD_ONNX_MODEL(
    directory  => 'ONNX_DIR',
    file_name  => 'all_MiniLM_L12_v2.onnx',
    model_name => 'ALL_MINILM_L12_V2'
  );
END;
/  2    3    4    5    6    7    8

PL/SQL procedure successfully completed.

SQL> COLUMN model_name FORMAT A30
COLUMN mining_function FORMAT A20
COLUMN algorithm FORMAT A20

SELECT model_name,
       mining_function,
       algorithm
FROM user_mining_models
WHERE model_name = 'ALL_MINILM_L12_V2';SQL> SQL> SQL> SQL>   2    3    4    5

MODEL_NAME                     MINING_FUNCTION      ALGORITHM
------------------------------ -------------------- --------------------
ALL_MINILM_L12_V2              EMBEDDING            ONNX

SQL> SELECT VECTOR_DIMS(
         VECTOR_EMBEDDING(
           ALL_MINILM_L12_V2
           USING 'Oracle AI Vector Search test' AS DATA
         )
       ) AS dimensions
FROM dual;  2    3    4    5    6    7

DIMENSIONS
----------
       384

SQL> SET LONG 100000
SET LINESIZE 200

SELECT VECTOR_EMBEDDING(
         ALL_MINILM_L12_V2
         USING 'Oracle Database backup and recovery' AS DATA
       ) AS embedding
FROM dual;SQL> SQL> SQL>   2    3    4    5

EMBEDDING
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
[-4.87856008E-002,7.1997717E-002,-2.54948232E-002,-5.70283532E-002,-1.06920907E-002,1.0775161E-002,-5.90280592E-002,4.400618E-002,-4.38417606E-002,-1.99456103E-002,5.76283038E-003,7.40298927E-002,1.31
917335E-002,-3.32452208E-002,-8.51406157E-002,-2.12764088E-002,-8.46216164E-004,6.76434338E-002,-5.09454496E-003,7.59954751E-002,-1.47132769E-001,-7.61819351E-003,4.72804867E-002,-3.58171314E-002,4.77
031525E-003,9.91140381E-002,6.09872341E-002,-4.96129356E-002,-6.89210966E-002,-2.29366459E-002,3.0035438E-002,-8.5439207E-003,-1.87823065E-002,-5.27824089E-002,-5.07728159E-002,5.71492985E-002,-1.0317
5268E-001,2.06469093E-003,-4.94726524E-002,5.38232923E-002,-4.20075236E-003,-5.83661646E-002,-6.939473E-002,4.70481254E-003,-3.57628129E-002,-2.06067171E-002,1.55578945E-002,-3.64977755E-002,1.3607616
5E-002,4.83786911E-002,6.55189529E-002,9.09776092E-002,-3.78638739E-003,4.63182144E-002,1.67185273E-002,-2.0538846E-002,1.88092468E-003,1.13089666E-001,-6.65725395E-002,-5.29151671E-002,7.0534341E-002
,3.82151194E-002,-3.87142561E-002,4.89930958E-002,-3.72733884E-002,4.71422225E-002,-9.42653418E-003,8.30410421E-002,7.23783001E-002,-8.00792221E-003,-1.04699969E-001,1.3227962E-002,2.10432312E-003,-5.
2194491E-002,5.44612296E-002,2.64221951E-002,-6.92174537E-003,3.2788564E-003,-7.07466304E-002,-1.09599065E-002,-2.07525976E-002,6.07146174E-002,-7.14476183E-002,-6.52319693E-004,-2.61781225E-003,1.029
82512E-002,2.22747419E-002,7.84466136E-003,-2.85771638E-002,-7.22757652E-002,1.31514639E-001,-6.92670466E-003,1.05359353E-001,2.31848098E-002,2.36764625E-002,1.93412453E-002,-7.73303732E-002,-3.468839
68E-003,1.3238284E-001,2.77728699E-002,6.44600466E-002,2.86891386E-002,3.98902521E-002,-1.04312496E-002,-1.1179284E-001,1.21374004E-001,5.50982319E-002,-2.86319014E-002,-7.31713325E-002,2.7698854E-002
,-7.78927729E-002,7.7220737E-003,4.11929712E-002,1.38421329E-002,2.2667855E-002,-9.06801131E-003,-1.06181979E-001,-1.63826789E-003,-8.82744044E-002,-1.22012058E-003,1.58981606E-002,-7.58664086E-002,9.
57456082E-002,1.25050836E-003,5.26473019E-003,2.16946639E-002,4.42689583E-002,6.8870157E-002,1.41299153E-002,5.51517941E-002,-5.14429659E-002,-5.23285232E-002,5.11708781E-002,-2.63342485E-002,-2.20014

EMBEDDING
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
565E-002,-1.73898265E-002,-3.62575054E-002,-8.39175209E-002,1.63958769E-003,-6.85187057E-002,2.91524939E-002,-1.2798515E-001,-1.83849374E-003,7.93192387E-002,2.51277834E-002,4.95435148E-002,-4.4572427
9E-002,-3.21206041E-002,-6.48666397E-002,2.0290127E-002,6.15623547E-003,2.16576364E-002,-4.66784462E-003,4.63866107E-002,3.30464952E-002,3.85379605E-002,1.23593844E-002,-4.7582306E-002,-7.36421198E-00
2,-7.33523369E-002,-6.89318478E-002,-7.00623095E-002,-3.05234399E-002,7.80285448E-002,-9.35370028E-002,2.69017499E-002,-1.14727495E-002,-2.12617069E-002,4.35556322E-002,-4.36026894E-004,9.77274105E-00
2,-3.76839899E-002,-8.01233053E-002,-7.22195879E-002,3.63463326E-003,-2.48486884E-002,-2.03172676E-002,-2.47620214E-002,2.27805087E-003,-5.56117743E-002,7.34143425E-003,-5.76570295E-002,-4.28294996E-0
03,5.3869877E-002,2.87791248E-002,5.07858656E-002,-5.34493811E-002,8.79583731E-002,7.63130933E-002,4.32174765E-002,1.31380977E-002,-3.26855257E-002,1.11090411E-002,-4.76806909E-002,-4.36781952E-003,1.
01589016E-003,3.21580544E-002,1.30654527E-002,3.04144267E-002,-3.80649902E-002,-4.27651405E-002,4.52963784E-002,-1.58415572E-003,-1.993821E-002,3.90723571E-002,-4.20001186E-002,-1.84409693E-002,9.7515
6799E-002,-4.25614715E-002,-4.88893613E-002,-4.92014624E-002,9.41627845E-003,8.72373432E-002,4.78187315E-002,-2.54044053E-003,-3.99130322E-002,1.34559376E-002,6.15696721E-002,-2.59792339E-002,-4.70647
514E-002,1.11222304E-001,-6.54862896E-002,9.61392298E-002,2.07331398E-034,-3.65259964E-003,-8.21233988E-002,-1.20636057E-002,1.44557646E-002,-3.51657383E-002,-1.3823634E-002,-1.64992362E-002,4.4988017
5E-002,-4.54784371E-002,-2.92942306E-004,2.15027835E-002,-1.31722605E-002,1.60137545E-002,-6.75500855E-002,-4.15628366E-002,-7.30369315E-002,8.64059255E-002,-5.12768142E-002,-3.84957977E-002,4.5663215
2E-002,6.85658455E-002,4.10435982E-002,7.44328089E-003,8.91532004E-002,2.70864032E-002,3.0453993E-002,-3.20769548E-002,1.2025306E-002,-1.22540602E-002,8.77800658E-002,3.16540003E-002,-8.52044672E-003,
-5.95438443E-002,1.48355916E-001,-3.60100642E-002,-1.0032624E-002,2.08637211E-002,2.84589995E-002,-2.41973568E-002,3.15869339E-002,1.12110479E-002,5.38171502E-004,2.05618907E-002,1.07340226E-002,-1.28

EMBEDDING
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
460256E-003,-5.25169186E-002,2.49615274E-002,1.5979778E-002,7.37336874E-002,-2.18623616E-002,8.46073851E-002,-2.29403824E-002,5.97928651E-002,2.6352426E-002,8.22185166E-003,-3.29606682E-002,9.63416323
E-002,-8.68180543E-002,-4.90903854E-002,2.07517482E-002,-2.32356545E-002,-6.64651319E-002,2.38323156E-002,5.20869419E-002,-1.13063902E-002,-1.03914761E-003,1.15302512E-002,5.39318845E-002,-2.76873186E
-002,-7.64334872E-002,1.62822269E-002,-4.78982665E-002,-7.46970624E-002,4.14117053E-002,1.85170472E-002,1.50091248E-002,-2.5248073E-002,5.56167169E-003,5.31971306E-002,4.19711061E-002,-1.05979659E-001
,-2.56049028E-003,-8.31877738E-002,5.11595644E-002,-3.61793442E-003,2.72909719E-002,2.98573133E-002,-1.31529346E-001,5.84768467E-002,-8.44340324E-002,-2.63655409E-002,-1.02462331E-002,-3.13680619E-002
,2.79930402E-002,7.4749887E-002,-6.41344061E-033,-4.88035977E-002,3.4346763E-002,1.05161041E-001,1.83394493E-003,5.63648827E-002,-1.1864084E-001,2.758242E-002,4.09473255E-002,-3.94932777E-002,-5.41642
867E-002,-8.78240447E-003,5.86595088E-002,3.33358464E-003,9.50002074E-002,-4.01680171E-002,7.73140565E-002,1.7790196E-003,-2.23562624E-002,-1.65800732E-002,-6.10178225E-002,3.96786481E-002,-5.95294759
E-002,4.85839993E-002,-9.53975134E-003,5.50697884E-003,3.7492007E-002,5.52483797E-002,2.59457417E-002,3.2463897E-002,-1.12312446E-004,1.87224504E-002,5.46318106E-002,-1.953681E-002,4.85305414E-002,7.7
0374667E-003,-1.2780644E-001,3.89365392E-004,9.75998677E-003,-8.70148391E-002,2.54446249E-002,8.90563652E-002,7.0743598E-002,-4.44219634E-002,-2.12613344E-002,-5.60087189E-002,2.35729497E-002,2.095438
73E-002,1.70082841E-002,-9.77526943E-005,-4.19775173E-002,2.22535385E-003,7.9500908E-003,-3.70596088E-002,2.16165632E-002,2.07185978E-003,-6.042099E-002,1.44844491E-003,6.97642863E-002,3.27955447E-002
,-5.20859994E-002,3.89892906E-002,4.39782776E-002,-1.08199725E-002,-7.10714981E-002]


SQL>

And that’s it. Its simple. Oracle AI Database 26ai makes it surprisingly easy to bring embedding generation directly to the data. Once the augmented ONNX model is staged and loaded, embeddings can be generated through regular SQL without maintaining a separate Python service or calling an external API.

For organizations building private semantic-search or RAG solutions, in-database ONNX provides a practical balance of security, performance and operational simplicity.

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , , , , , , , | Leave a Comment »

fatdba explores Vector Search in Oracle 23ai

Posted by FatDBA on July 23, 2025

So Oracle rolled out 23ai a while back and like every major release, it came packed with some really cool and interesting features. One that definitely caught my eye was Vector Search. I couldn’t resist diving in… and recently I explored it in depth and would like to share a though on this subject.

You see, we’ve been doing LIKE '%tax policy%' since forever. But now, Oracle’s SQL has become more powerful. Not only does it match words … it matches meaning.

So here’s me trying to explain what vector search is, how Oracle does it, why you’d care, and some examples that’ll hopefully make it click.

What’s Vector Search, Anyway?

Alright, imagine this:

You have a table of products. You search for “lightweight laptop for travel”.
Some entries say “ultrabook”, others say “portable notebook”, and none mention “lightweight” or “travel”. Old-school SQL would’ve said: “No Matches Found”

But with vector search, it gets it. Oracle turns all that text into math .. basically, a long list of numbers called a vector … and compares meanings instead of words.

So What’s a Vector?

When we say “vector” in vector search, we’re not talking about geometry class. In the world of AI and databases, a vector is just a long list of numbers … each number representing some aspect or feature of the original input (like a sentence, product description, image, etc.).

Here’s a basic example:
[0.12, -0.45, 0.88, …, 0.03]
This is a vector … maybe a 512 or 1536-dimension one .. depending on the embedding model used (like OpenAI, Oracle’s built-in model, Cohere, etc.).

Each number in this list is abstract, but together they represent the essence or meaning of your data.

Let’s say you have these two phrases:
“Apple is a tech company”
“iPhone maker based in California”

Now, even though they don’t share many words, they mean nearly the same thing. When passed through an embedding model, both phrases are converted into vectors:

Vector A: [0.21, -0.32, 0.76, …, 0.02]
Vector B: [0.22, -0.30, 0.74, …, 0.01]
They look very close … and that’s exactly the point.

What Oracle 23ai Gives You

  • A new VECTOR datatype (yeah!)
  • AI_VECTOR() function to convert text into vectors
  • VECTOR_INDEX to make search blazing fast
  • VECTOR_DISTANCE() to measure similarity
  • It’s all native in SQL ..no need for another vector DB bolted on

Let’s Build Something Step-by-Step

We’ll build a simple product table and do a vector search on it.

Step 1: Create the table

CREATE TABLE products (
  product_id     NUMBER PRIMARY KEY,
  product_name   VARCHAR2(100),
  description    VARCHAR2(1000),
  embedding      VECTOR(1536)
);

1536? Yeah, that’s the number of dimensions from Oracle’s built-in embedding model. Depends on which one you use.

Step 2: Generate vector embeddings

UPDATE products
SET embedding = ai_vector('text_embedding', description);

This’ll take the description, pass it through Oracle’s AI model, and give you a vector. Magic.

Step 3: Create the vector index

CREATE VECTOR INDEX product_vec_idx
ON products (embedding)
WITH (DISTANCE METRIC COSINE);

This speeds up the similarity comparisons … much like an index does for normal WHERE clauses.

Step 4: Semantic Search in SQL

SELECT product_id, product_name, 
       VECTOR_DISTANCE(embedding, ai_vector('text_embedding', 'light laptop for designers')) AS score
FROM products
ORDER BY score
FETCH FIRST 5 ROWS ONLY;

Now we’re searching for meaning, not words.

VECTOR_DISTANCE Breakdown

You can use different math behind the scenes:

VECTOR_DISTANCE(v1, v2 USING COSINE)
VECTOR_DISTANCE(v1, v2 USING EUCLIDEAN)
VECTOR_DISTANCE(v1, v2 USING DOT_PRODUCT)

Cosine is the usual go-to for text. Oracle handles the rest for you.

Use Cases You’ll Actually Care About

1. Semantic Product Search — “Fast shoes for runners” => shows “Nike Vaporfly”, even if it doesn’t say “fast”.

2. Similar Document Retrieval — Find all NDAs that look like this one (even with totally different words).

3. Customer Ticket Suggestion — Auto-suggest resolutions from past tickets. Saves your support team hours.

4. Content Recommendation — “People who read this also read…” kind of stuff. Easy to build now.

5. Risk or Fraud Pattern Matching — Find transactions that feel like fraud ..even if the details don’t match 1:1.

I know it might sound little confusing .. lets do a Onwe more example : Legal Document Matching

CREATE TABLE legal_docs (
  doc_id       NUMBER PRIMARY KEY,
  title        VARCHAR2(255),
  content      CLOB,
  content_vec  VECTOR(1536)
);

Update vectors:

UPDATE legal_docs
SET content_vec = ai_vector('text_embedding', content);

Now find similar docs:

SELECT doc_id, title
FROM legal_docs
ORDER BY VECTOR_DISTANCE(content_vec, ai_vector('text_embedding', 'confidentiality in government contracts'))
FETCH FIRST 10 ROWS ONLY;

That’s it. You’re officially building an AI-powered legal search engine.

Things to Know

  • Creating vectors can be heavy .. batch it.
  • Indexing speeds up similarity search a lot.
  • Combine with normal filters for best results:
SELECT * FROM products
WHERE category = 'laptop'
ORDER BY VECTOR_DISTANCE(embedding, ai_vector('gaming laptop under 1kg'))
FETCH FIRST 5 ROWS ONLY;

Final Thoughts from fatdba

I’m honestly impressed. Oracle took something that felt like ML black magic and put it right in SQL. No external service. No complicated setups. Just regular SQL, but smater.

Hope It Helped!
Prashant Dixit
Database Architect @RENAPS
Reach us at : https://renaps.com/

Posted in Uncategorized | Tagged: , , , , , , , , , , , , , , | Leave a Comment »

How I see vector indexing and search in Oracle 23ai

Posted by FatDBA on November 1, 2024

Hi everyone!

It’s been a while since Oracle 23c (or 23c AI) was released, and there’s already a ton of great content out there on one of its coolest new features: Vector Indexes and Searches. This feature has opened up exciting possibilities for AI-driven applications, from advanced recommendations to similarity searches within databases. Now, it’s finally my turn to dive in and put it to the test!

Vector search in Oracle 23c AI is a feature designed for searching, comparing, and finding similarities between data represented as vectors (numerical arrays). This capability is particularly useful for AI and machine learning applications, where data like images, text, and products are often encoded as vector embeddings. Oracle’s vector search enables the database to efficiently process these embeddings, allowing users to perform similarity-based queries within the database. Oracle’s vector search in 23c AI makes it possible to bring advanced similarity-based search and recommendation capabilities directly into the database, facilitating powerful new applications that leverage the latest in AI and machine learning.

The base or the foundation of this feature is VECTOR data type, a powerful tool for storing and managing vector embeddings directly within the database. This capability allows Oracle to support advanced AI and machine learning applications, such as similarity searches and recommendation engines, by efficiently handling multi-dimensional arrays that represent data features, semantics, or other complex characteristics.

Key Advantages of the VECTOR Data Type

  1. Compact Multi-Dimensional Array Storage:
  • VECTOR allows fixed-length arrays of numbers (often floating-point) to be stored compactly in a single column. A 100-dimensional vector, for instance, might represent an embedding for text, an image, or a user profile, capturing nuanced data in a manageable form.
  1. Efficient Similarity Calculations:
  • With Oracle 23c AI, VECTOR data can be processed for similarity searches directly in the database. Functions like VECTOR_DISTANCE make it easy to calculate similarity scores or distances between vectors, enabling Oracle to quickly identify similar items without needing external tools or complex data transformations.
  1. Tailored for AI and Machine Learning:
  • VECTOR is ideal for applications that require semantic searches, such as finding similar products, media, or documents, and for AI-driven recommendations. This makes it well-suited for Natural Language Processing (NLP), recommendation engines, and image recognition tasks.
  1. Support for Various Distance Metrics:
  • Oracle’s VECTOR data type is compatible with common distance metrics, such as Euclidean and cosine similarity, which measure how close or far two vectors are from each other. This enables efficient and flexible similarity calculations, essential for applications relying on vector comparisons.
  1. Scalability with Vector Indexing:
  • Oracle 23c AI allows for VECTOR data indexing through VECTOR_INDEX, optimizing performance for vector-based queries. This makes real-time similarity searches on large datasets feasible, supporting applications that need quick response times, such as real-time AI insights and personalized recommendations.

The VECTOR data type in Oracle 23c AI is a robust addition for data scientists and developers, enabling high-performance similarity searches, efficient data storage, and a foundation for complex AI and machine learning workloads directly within the Oracle database.

Alright, so now when the stage is set, lets stay tuned as I explore how Vector Indexes and Searches can bring a whole new dimension to Oracle 23c. Here’s a detailed, step-by-step demo to help you showcase vector search and indexes in Oracle Database 23c AI, which will be ideal for your blog post. This example will simulate a product recommendation system using vector embeddings to represent product features.

1. Set Up the Environment

Ensure you’re using Oracle 23c AI, as vector indexes and the VECTOR data type are new features available in this release.

2. Create a Table with Vector Embeddings

In this example, each product is represented by a 10-dimensional vector embedding. This vector could represent a combination of product attributes, like color, category, brand, and other numerical encodings.

CREATE TABLE products (
    product_id NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    product_description VARCHAR2(255),
    product_embedding VECTOR(10) -- Vector with 10 dimensions
);

3. Insert Sample Data

Populate the table with sample products and their vector embeddings. Here’s an example where each vector represents the product’s characteristics:

INSERT INTO products (product_id, product_name, product_description, product_embedding) VALUES 
    (1, 'Laptop A', 'High-performance laptop for professionals', VECTOR(0.5, 0.2, 0.7, 0.1, 0.4, 0.3, 0.8, 0.6, 0.9, 0.3)),
    (2, 'Smartphone B', 'Latest smartphone with advanced features', VECTOR(0.6, 0.1, 0.8, 0.5, 0.2, 0.7, 0.4, 0.3, 0.6, 0.1)),
    (3, 'Tablet C', 'Compact and powerful tablet for multimedia', VECTOR(0.4, 0.9, 0.5, 0.7, 0.3, 0.6, 0.2, 0.8, 0.1, 0.9)),
    (4, 'Laptop D', 'Affordable laptop with reliable performance', VECTOR(0.7, 0.6, 0.4, 0.9, 0.8, 0.2, 0.5, 0.3, 0.1, 0.4)),
    (5, 'Headphones E', 'Noise-canceling headphones with high fidelity', VECTOR(0.3, 0.8, 0.1, 0.9, 0.6, 0.4, 0.7, 0.2, 0.5, 0.6));

4. Create a Vector Index for Efficient Searches

Creating a vector index allows Oracle to search these embeddings efficiently based on similarity, which is ideal for recommendation systems, image retrieval, and text similarity searches.

CREATE INDEX product_vector_idx ON products (product_embedding)
    INDEXTYPE IS VECTOR_INDEX;

5. Perform a Vector Similarity Search

With the vector index in place, you can now perform searches to find products that are similar to a given vector. For example, suppose a user has shown interest in products that correspond to a vector representation of [0.6, 0.2, 0.5, 0.4, 0.3, 0.5, 0.7, 0.8, 0.2, 0.6]. You can use this vector to find products with similar embeddings.

SELECT product_name, product_description, product_embedding
FROM products
ORDER BY VECTOR_DISTANCE(product_embedding, VECTOR(0.6, 0.2, 0.5, 0.4, 0.3, 0.5, 0.7, 0.8, 0.2, 0.6)) ASC
FETCH FIRST 5 ROWS ONLY;

In this query:

  • The VECTOR_DISTANCE function calculates the similarity (distance) between the target vector and each product’s embedding. Sorting by VECTOR_DISTANCE allows you to retrieve the closest matches.
  • This example fetches the top 5 most similar products.

6. Use Case: Personalized Product Recommendation

This setup is ideal for applications such as a product recommendation engine. Suppose you have a user profile represented as a vector, you could use a vector search to find products similar to that profile, offering highly relevant recommendations.

7. Handling Updates to Vector Embeddings

If a product’s features change, you may need to update its embedding. Here’s how you could update the vector for a specific product:

UPDATE products
SET product_embedding = VECTOR(0.7, 0.3, 0.9, 0.5, 0.6, 0.4, 0.8, 0.2, 0.3, 0.5)
WHERE product_id = 2;

8. Advanced Use Case: Nearest Neighbor Search with Filtering

Let’s extend the search by filtering for specific criteria. For example, if you only want to recommend products that contain “Laptop” in the name, you can add a WHERE clause to the query.

SELECT product_name, product_description, product_embedding
FROM products
WHERE product_name LIKE '%Laptop%'
ORDER BY VECTOR_DISTANCE(product_embedding, VECTOR(0.6, 0.2, 0.5, 0.4, 0.3, 0.5, 0.7, 0.8, 0.2, 0.6)) ASC
FETCH FIRST 5 ROWS ONLY;

9. Explain the Vector Distance Metric

For your blog, it’s useful to explain the concept of vector similarity:

  • Vector Distance (Similarity): This metric measures how similar or dissimilar two vectors are. A smaller distance implies a higher similarity. Oracle 23c AI’s VECTOR_DISTANCE function calculates this distance to identify closely related items, making it ideal for applications in recommendations, content search, and clustering.

10. Additional Notes for Optimization and Use Cases

  • Index Maintenance: Like other indexes, a vector index should be maintained for performance. Ensure to monitor the index usage and rebuild if necessary for large datasets.
  • Scalability: Oracle 23c AI’s vector search is optimized for large-scale deployments, making it suitable for extensive recommendation systems, content-based searches, and personalized user experiences.

This detailed walkthrough should provide a comprehensive demo for your blog post, showcasing Oracle 23c AI’s new vector search and indexing features. Let me know if you need any additional details or further customization!

Hope It Helped!
Prashant Dixit

Posted in Uncategorized | Tagged: , , , , , , , , , | Leave a Comment »