Are you excited about the new AI Vector Search in Oracle 23ai! Have you been able to do a vector search in the new database without calling external APIs to do vector embeddings? Unless you are skilled in Python and Linux, this could take a while to get to the point where you can load a Large Language Model into the database to do native vector embedding. My skillset is really focused on Oracle SQL and PL/SQL, so the challenges to get to the point where I could work with AI Vector searches natively in the database were a bit daunting.
Most of the instructions for installing OML4PY used a fresh container or a linux machine in the OCI cloud, but I really didn’t want to work through that process either. After I create the correct ONNX models locally, I should be able to put them in object storage in my OCI cloud and directly import them to my autonomous database without further ado.
I have found that there are no ready for loading to Oracle LLM models available anywhere on the internet, so my only alternative was to use OML4PY to create my own. If you are like me and your wheelhouse is focused on Oracle SQL and PL/SQL, you may want to skip to the end of this article and download some of the augmented models that I generated and made available for easy use.
- Getting Started: Just 2 Downloads
- Import the VirtualBox Appliance
- Set Up VirtualBox: Guest Additions, Host Only Networking
- Share OML4PY Install Files From Host Machine
- Check Installed Python Version
- Roadblock: Must Fix Linux Package Installer
- Install Linux Packages
- Upgrade Python Version to 3.12
- Adding Python Packages
- Install OML4PY Client
- Verify OML4PY Installation
- Exporting Augmented ONNX Models
- Loading the Model To The Database
- Using the Model In The Database
- Download Augmented Models
I was ready to write up this quick start guide for PL/SQL developers as I read the news from Jeff Smith that the new version of Oracle 23ai has just been released for VirtualBox. The timing was perfect, and it gave me a chance to see if any of the issues I had faced with installing the OML4PY client on the previously released 23ai virtual box were resolved. The bad news is that the issues were still there, it is still a bit difficult to get everything going. The good news is that I had finally figured out what the problem was with this installation and my solution still works!
My entire goal is to get the OML4PY running on this virtualbox appliance and export some models, I am not interested in learning the intricacies of Python programming and I don’t mind that my Unix skills are a bit out of date when applied to the modern Linux situation.
The VirtualBox appliance comes preconfigured with a working Oracle 23ai database, but what it doesn’t come with is preinstalled support for OML4PY or any preloaded models in the database. If you want to explore vector searches and creating document embeddings natively in the database, you will need large language models in an ONNX format that have been augmented for use in the Oracle database.
The list of preconfigured LLMs for OML4PY are all publicly available, and you can find each model on the internet…unfortunately the available ONNX formatted models are not ready to load to the database and are of no help to an Oracle developer exploring Vector Searches and document Embeddings.
Oracle has made available a Python client that can export a set preconfigured models in the correct format, OML4PY. Looking at the details for installation, it turns out that Python 3.12 needs to be installed with many optional components. The default installation of Python on the Oracle VirtualBox appliance is 3.6, so it needs to be upgraded before OML4PY can be installed. The installation of Python 3.12 also requires many additional linux packages to be installed as well, and this turns out to be more difficult than it sounds.
Getting Started: Just 2 Downloads
If you already have VirtualBox installed, there are only two elements to download: the Oracle 23ai VirtualBox appliance and the OML4PY client installation files.
Go to https://www.oracle.com/database/free/get-started/ and download the Oracle_Database_23ai_Free_Developer.ova file to import into VirtualBox. As of August 2024, this download has the 23.5 release of the Database preinstalled on Oracle Linux 8.
Next, go to https://www.oracle.com/database/technologies/oml4py-downloads.html and download the linux 64-bit OML4Py 2.0 Client for Database 23ai. You will need to log into your free Oracle account to access the download. This will download the oml4py-client-linux-x86_64-2.0.zip file.
Import the VirtualBox Appliance
Open Oracle VM VirtualBox manager and import the ova file to a new machine (File>Import Appliance). Select the downloaded ova file, the virtual machine base directory, and configure options for the machine name, processor count and memory. If you have the resources I recommend doubling the defaults for processors and memory available to the virtual machine: 4 cpus and 8192 gb ram.
After the machine imports, you should adjust a few settings before starting up. Remote Display always shows up as an invalid setting for my machines, so I just disable it. Add a second network adapter for Host Only networking so you can access the machine from the host with SQL Developer.
Add a shared folder from the host machine where you can share files with the Linux appliance and set up directories for the database to access. Select automount and set the mountpoint for the external folder to /home/oracle/ext-data so its easy to get to from the default logged in user. Note: since you want to write files to this folder from the virtual machine, do not make it read only.
Set Up VirtualBox: Guest Additions, Host Only Networking
Now start the virtual machine and set up a couple of things to get it working correctly. The first thing to do is upgrade Guest Additions from the Devices menu so that external files are enabled and you can copy/paste between the host and the virtual machine. You will see a message from this install that the automatic installation has failed and you may want to reboot the virtual machine.
Automatic update of Guest Additions has failed: files were installed, but kernel modules were not reloaded automatically. Please consider rebooting the guest.
Go ahead and restart the machine so that this installation is completed.
After the machine reboots, go to Settings and increase the resolution from the default of 800 x 600 to match the host machine better. Also go into Settings>Network and configure the second network adapter with a fixed IP address so that you can access the database from the host machine.
Share OML4PY Install Files From Host Machine
I share a folder called ExternalData with all of my virtual machines. Create a folder here called /ml-downloads with a subfolder called oml4py. Unzip the OML4PY folder here. This will create a /client folder with the install files for OML4PY that the linux machine will be able to access.
Also create a folder to share as a directory for staging model files to be loaded into the database. Create it as ora-db-directories/shared/ml-models. Later we will set it up as a database directory and use it for staging exported models.
Check Installed Python Version
Open a terminal on the virtual machine and check the Python version. Use ctrl-d to exit the Python environment.
[oracle@localhost ~]$ python3
Python 3.6.8 (default, May 24 2024, 06:39:46)
[GCC 8.5.0 20210514 (Red Hat 8.5.0-21.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> OML4PY requires Python 3.12, so this has to be updated. Before that will work there are also Linux packages that need to be installed. The command to do this looks simple, but it doesn’t work. Every call to rpm or yum for installing packages times out after a while.
Roadblock: Must Fix Linux Package Installer
After digging around, I discovered that the yum server is looking for the phoenix yum server in the OCI cloud, and this won’t be visible to the virtual machine.
Switch your terminal login to root (the password is ‘oracle’) and go to /etc/dnf/vars to fix the ociregion file that is pointing to the OCI yum server, this will make the linux machine go to the default public Oracle yum server for installing packages.
[oracle@localhost ~]$ su
Password:
[root@localhost oracle]# cd /etc/dnf/vars
[root@localhost vars]# ls
ocidomain ociregion
[root@localhost vars]# gedit ociregionSome warnings come up from gedit, these can be ignored. In the editor you will see that the file content is a single entry:
-us-phoenix-1Just delete this, save the file and exit the file editor.
Now the yum installer will be able to find the linux packages easily.
Install Linux Packages
Install the necessary Linux packages to support the Python 3.12 and OML4PY installations. This command will download the packages and ask if you want to install them, reply y and wait for the install to complete.
[root@localhost vars]# cd /
[root@localhost /]# yum install perl-Env libffi-devel openssl openssl-devel tk-devel xz-devel zlib-devel bzip2-devel readline-devel libuuid-devel ncurses-devel
...checking yum server and resolving dependencies
Total download size: 11 M
Installed size: 25 M
Is this ok [y/N]: y
...downloading and installing packages
Installed:
bzip2-devel-1.0.6-26.el8.x86_64 expat-devel-2.2.5-13.0.1.el8_10.x86_64 fontconfig-devel-2.13.1-4.el8.x86_64 freetype-devel-2.9.1-9.el8.x86_64 keyutils-libs-devel-1.5.10-9.0.1.el8.x86_64
krb5-devel-1.18.2-28.0.1.el8_10.x86_64 libX11-devel-1.6.8-8.el8.x86_64 libXau-devel-1.0.9-3.el8.x86_64 libXft-devel-2.3.3-1.el8.x86_64 libXrender-devel-0.9.10-7.el8.x86_64
libcom_err-devel-1.46.2-2.el8.x86_64 libffi-devel-3.1-24.el8.x86_64 libkadm5-1.18.2-28.0.1.el8_10.x86_64 libpng-devel-2:1.6.34-5.el8.x86_64 libselinux-devel-2.9-8.el8.x86_64
libsepol-devel-2.9-3.el8.x86_64 libuuid-devel-2.32.1-46.0.1.el8.x86_64 libverto-devel-0.3.2-2.el8.x86_64 libxcb-devel-1.13.1-1.el8.x86_64 ncurses-c++-libs-6.1-10.20180224.el8.x86_64
ncurses-devel-6.1-10.20180224.el8.x86_64 openssl-devel-1:1.1.1k-12.el8_9.x86_64 pcre2-devel-10.32-3.el8_6.x86_64 pcre2-utf16-10.32-3.el8_6.x86_64 pcre2-utf32-10.32-3.el8_6.x86_64
perl-Env-1.04-395.el8.noarch readline-devel-7.0-10.el8.x86_64 tcl-devel-1:8.6.8-2.el8.x86_64 tk-1:8.6.8-1.el8.x86_64 tk-devel-1:8.6.8-1.el8.x86_64
xorg-x11-proto-devel-2020.1-3.el8.noarch xz-devel-5.2.4-4.el8_6.x86_64 zlib-devel-1.2.11-25.el8.x86_64
Complete!The Python pip upgrade will also need another package, we can install it now.
[root@localhost /]# yum install gcc-c++
...checking yum server and resolving dependencies
Total download size: 14 M
Installed size: 42 M
Is this ok [y/N]: y
...downloading and installing packages
Installed:
gcc-c++-8.5.0-22.0.1.el8_10.x86_64 libstdc++-devel-8.5.0-22.0.1.el8_10.x86_64
Complete!Upgrade Python Version to 3.12
Exit root and create a directory for the Python installation in /home/oracle
[root@localhost /]# exit
exit
[oracle@localhost ~]$ pwd
/home/oracle
[oracle@localhost ~]$ mkdir ./python
[oracle@localhost ~]$ cd ./python
[oracle@localhost python]$ Get the python installation package now.
[oracle@localhost python]$ wget https://www.python.org/ftp/python/3.12.0/Python-3.12.0.tar.xz
--2024-08-11 14:22:03-- https://www.python.org/ftp/python/3.12.0/Python-3.12.0.tar.xz
Resolving www.python.org (www.python.org)... 146.75.32.223, 2a04:4e42:200::223, 2a04:4e42:f000::223, ...
Connecting to www.python.org (www.python.org)|146.75.32.223|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 20575020 (20M) [application/octet-stream]
Saving to: ‘Python-3.12.0.tar.xz’
Python-3.12.0.tar.xz 100%[===================>] 19.62M 69.0MB/s in 0.3s
2024-08-11 14:22:04 (69.0 MB/s) - ‘Python-3.12.0.tar.xz’ saved [20575020/20575020]Extract the package here in the /home/oracle/python directory:
[oracle@localhost python]$ tar xvf Python-3.12.0.tar.xz
....extracting files
[oracle@localhost python]$ ls
Python-3.12.0 Python-3.12.0.tar.xzAll of the files have been unpacked into the Python-3.12.0 directory and can be installed now. Export a PREFIX variable assigned to the path and change to that directory, then configure the Python installation.
[oracle@localhost python]$ pwd
/home/oracle/python
[oracle@localhost python]$ export PREFIX=`pwd`/Python-3.12.0
[oracle@localhost python]$ cd $PREFIX
[oracle@localhost Python-3.12.0]$ pwd
/home/oracle/python/Python-3.12.0
[oracle@localhost Python-3.12.0]$ ./configure --prefix=$PREFIX --enable-shared --enable-optimizations
...checking for lib extensions...
configure: creating Makefile
[oracle@localhost Python-3.12.0]$After this is done, make the Python installation
[oracle@localhost Python-3.12.0]$ make clean; make
...This takes quite a while and you will see lots of messages that may surely be of interest to a Python developer. There appear to be some missing bits, but it doesn’t affect the OML4PY installation. After this completes run altinstall, this installs both versions of Python in the system.
[oracle@localhost Python-3.12.0]$ make altinstall
...
Installing collected packages: pip
WARNING: The script pip3.12 is installed in '/home/oracle/python/Python-3.12.0/bin' which is not on PATH.
Consider adding this directory to PATH or, if you prefer to suppress this warning, use --no-warn-script-location.
Successfully installed pip-23.2.1Next we add this directory to the path.
[oracle@localhost Python-3.12.0]$ echo $PREFIX
/home/oracle/python/Python-3.12.0
[oracle@localhost Python-3.12.0]$ export PYTHONHOME=$PREFIX
[oracle@localhost Python-3.12.0]$ export PATH=$PYTHONHOME/bin:$PATH
[oracle@localhost Python-3.12.0]$ export LD_LIBRARY_PATH=$PYTHONHOME/lib:$LD_LIBRARY_PATH
[oracle@localhost Python-3.12.0]$ echo $PYTHONHOME
/home/oracle/python/Python-3.12.0Create a symbolic link to this alternate Python installation. Then check to see that the correct version of Python is being invoked.
[oracle@localhost Python-3.12.0]$ cd $PYTHONHOME/bin
[oracle@localhost bin]$ pwd
/home/oracle/python/Python-3.12.0/bin
[oracle@localhost bin]$ ln -s python3.12 python3
[oracle@localhost bin]$ cd /home/oracle/python
[oracle@localhost python]$ pwd
/home/oracle/python
[oracle@localhost python]$ python3
Python 3.12.0 (main, Aug 11 2024, 14:42:22) [GCC 8.5.0 20210514 (Red Hat 8.5.0-22.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>>Success! The first major part is completed, we are able to run the correct version of Python.
Adding Python Packages
Now we need to add components to Python with the pip installer.
[oracle@localhost python]$ python3 -m pip install --upgrade pip
...
Successfully installed pip-24.2There are a lot of pip packages to install. Run each command separately to see that it completed successfully. I am not sure why scipy is installed twice here, but the first install fixes the numpy installation so I am ok with it.
pip3.12 install pandas==2.1.1
pip3.12 install scipy==1.11.3
pip3.12 install matplotlib==3.7.2
pip3.12 install oracledb==2.0.1
pip3.12 install threadpoolctl==3.1.0
pip3.12 install joblib==1.2.0
pip3.12 install setuptools
pip3.12 install scipy==1.12.0
pip3.12 install scikit-learn==1.3Those are all of the prerequisites. Now we install the onnx code that will be used to export onnx models. The last one installs well over a gigabyte of packages.
pip3.12 install onnx
pip3.12 install onnxruntime_extensions
pip3.12 install onnxruntime
pip3.12 install transformers==4.38.1
pip3.12 install sentencepiece==0.2.0
pip3.12 install torchNow the basics of upgrading Python and installing the required packages are completed. We can move on to actually installing the OML4PY client.
Install OML4PY Client
Create a directory for the OML4PY files and then copy the unzipped /client folder and files to it. I like windows, so I used the Files application to do this.
[oracle@localhost python]$ cd /home/oracle
[oracle@localhost ~]$ mkdir ./oml4py
...use the files application to move the unzipped install folder
[oracle@localhost ~]$ cd ./oml4py/client
[oracle@localhost client]$ pwd
/home/oracle/oml4py/client
[oracle@localhost client]$ ls
client.pl oml-2.0-cp312-cp312-linux_x86_64.whl OML4PInstallShared.pm oml4py.verThe next step is to run the install script. I am not sure why this error happens since we are in the client directory and the OML4PInstallShared.pm file is right there.
[oracle@localhost client]$ perl -Iclient ./client.pl -i --ask
Can't locate OML4PInstallShared.pm in @INC (you may need to install the OML4PInstallShared module) (@INC contains: client /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5) at ./client.pl line 32.
BEGIN failed--compilation aborted at ./client.pl line 32.Backup a folder and run the installer with the full path to the client.pl script.
[oracle@localhost client]$ cd ..
[oracle@localhost oml4py]$ pwd
/home/oracle/oml4py
[oracle@localhost oml4py]$ perl -Iclient /home/oracle/oml4py/client/client.pl -i --ask
Oracle Machine Learning for Python 2.0 Client.
Copyright (c) 2018, 2024 Oracle and/or its affiliates. All rights reserved.
Checking platform .................. Pass
Checking Python .................... Pass
Checking dependencies .............. /home/oracle/oml4py/check_deps.py:2: DeprecationWarning: pkg_resources is deprecated as an API. See https://setuptools.pypa.io/en/latest/pkg_resources.html
from pkg_resources import WorkingSet, VersionConflict, DistributionNotFound
Pass
Checking OML4P version ............. Pass
Current configuration
Python Version ................... 3.12.0
PYTHONHOME ....................... /home/oracle/python/Python-3.12.0
Existing OML4P module version .... None
Operation ........................ Install/Upgrade
Proceed? [yes]y
Processing ./client/oml-2.0-cp312-cp312-linux_x86_64.whl
Installing collected packages: oml
Successfully installed oml-2.0
DoneVerify OML4PY Installation
Now we can verify that the OML4PY client is installed by checking for the list of preconfigured models.
[oracle@localhost oml4py]$ python3
Python 3.12.0 (main, Aug 11 2024, 14:42:22) [GCC 8.5.0 20210514 (Red Hat 8.5.0-22.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import oml
>>> from oml.utils import EmbeddingModel, EmbeddingModelConfig
>>> EmbeddingModelConfig.show_preconfigured()
['sentence-transformers/all-mpnet-base-v2', 'sentence-transformers/all-MiniLM-L6-v2', 'sentence-transformers/multi-qa-MiniLM-L6-cos-v1', 'ProsusAI/finbert', 'medicalai/ClinicalBERT', 'sentence-transformers/distiluse-base-multilingual-cased-v2', 'sentence-transformers/all-MiniLM-L12-v2', 'BAAI/bge-small-en-v1.5', 'BAAI/bge-base-en-v1.5', 'taylorAI/bge-micro-v2', 'intfloat/e5-small-v2', 'intfloat/e5-base-v2', 'prajjwal1/bert-tiny', 'thenlper/gte-base', 'thenlper/gte-small', 'TaylorAI/gte-tiny', 'infgrad/stella-base-en-v2', 'sentence-transformers/paraphrase-multilingual-mpnet-base-v2', 'intfloat/multilingual-e5-base', 'intfloat/multilingual-e5-small', 'sentence-transformers/stsb-xlm-r-multilingual']
>>> Everything works! Now we can export these preconfigured models.
Exporting Augmented ONNX Models
Its pretty easy to export models at this point. Make an exports directory here in /home/oracle/oml4py to store the exported models and try an export:
[oracle@localhost oml4py]$ mkdir ./exports
[oracle@localhost oml4py]$ cd ./exports
[oracle@localhost exports]$ pwd
/home/oracle/oml4py/exports
[oracle@localhost exports]$ python3
Python 3.12.0 (main, Aug 11 2024, 14:42:22) [GCC 8.5.0 20210514 (Red Hat 8.5.0-22.0.1)] on linux
Type "help", "copyright", "credits" or "license" for more information.
>>> import oml
>>> from oml.utils import EmbeddingModel, EmbeddingModelConfig
>>> em = EmbeddingModel(model_name="sentence-transformers/all-MiniLM-L6-v2")
>>> em.export2file("all_MiniLM_L6_v2",output_dir=".")
/home/oracle/python/Python-3.12.0/lib/python3.12/site-packages/huggingface_hub/file_download.py:1150: FutureWarning: `resume_download` is deprecated and will be removed in version 1.0.0. Downloads always resume when possible. If you want to force a new download, use `force_download=True`.
warnings.warn(
tokenizer_config.json: 100%|.....| 350/350 [00:00<00:00, 3.98MB/s]
vocab.txt: 100%|.................| 232k/232k [00:00<00:00, 10.5MB/s]
special_tokens_map.json: 100%|...| 112/112 [00:00<00:00, 1.45MB/s]
tokenizer.json: 100%|............| 466k/466k [00:00<00:00, 25.5MB/s]
config.json: 100%|...............| 612/612 [00:00<00:00, 7.33MB/s]
model.safetensors: 100%|.........| 90.9M/90.9M [00:00<00:00, 108MB/s]
>>>
[oracle@localhost exports]$ ls
all_MiniLM_L6_v2.onnxPerfect! We have successfully exported an augmented LLM in ONNX format that is ready to load to the database.
I worked through exporting all of the models in the preconfigured models list. They all worked except for three, which seemed to be correctly producing a 1gb onnx file correctly…but the export then reported the process as killed and the final file was never created. Several of the models generated some warnings, but they dont seem to impact loading them into the database and using them for test vectoer embeddings. The results of exporting each model are detailed here.
Copy the exported onnx file to the ext-data directory that we created for staging models into the database. Verify that the file is there.
[oracle@localhost ml-models]$ cd /home/oracle/ext-data/ora-db-directories/shared/ml-models
[oracle@localhost ml-models]$ ls
all_MiniLM_L6_v2.onnxLoading the Model To The Database
Thats the end of our excursion into Linux and Python territory, now we can go back to the database and work with the augmented model.
Login as sys and create a user to test vector searches as well as a directory to load the staged models.
--create.user.dev_vector.sql
create user dev_vector identified by oracle;
grant connect, db_developer_role to dev_vector;
grant create mining model to dev_vector;
alter user dev_vector quota unlimited on users;
--Oracle 23ai VirtualBox Linux Appliance with external data path
create or replace directory
ml_models_dir as '/home/oracle/ext-data/ora-db-directories/shared/ml-models';
grant read on directory ml_models_dir to dev_vector;
grant write on directory ml_models_dir to dev_vector;Now we can login as dev_vector and load the model as a mining model.
--load-models\load-all_miniLM_l6_v2.sql
begin
dbms_vector.load_onnx_model(
'ML_MODELS_DIR',
'all_MiniLM_L6_v2.onnx',
'all_minilm_l6_v2');
end;
/
PL/SQL procedure successfully completed.You can see that the model is loaded in user_mining_models and see the attributes of the model in user_mining_model_attributes.
select model_name, mining_function, algorithm, algorithm_type, model_size
from user_mining_models
where model_name = 'ALL_MINILM_L6_V2'
order by model_name
/
select model_name, attribute_name, attribute_type, data_type, vector_info
from user_mining_model_attributes
where model_name = 'ALL_MINILM_L6_V2'
order by attribute_name
MODEL_NAME MINING_FUNCTION ALGORITHM ALGORITHM_TYPE MODEL_SIZE
------------------ --------------- ---------- --------------- ----------
ALL_MINILM_L6_V2 EMBEDDING ONNX NATIVE 90621438
MODEL_NAME ATTRIBUTE_NAME ATTRIBUTE_TYPE DATA_TYPE VECTOR_INFO
------------------ --------------- --------------- ---------- --------------------
ALL_MINILM_L6_V2 DATA TEXT VARCHAR2
ALL_MINILM_L6_V2 ORA$ONNXTARGET VECTOR VECTOR VECTOR(384,FLOAT32) Using the Model In The Database
Now that we have a mining model loaded to the database, it can be used to create vector embeddings for unstructured data that we can store with the data.
Create a table, load some sample data and generate vector embeddings for each row.
--create-table-recipes.sql
create table if not exists recipes (
id number,
name varchar2(100),
doc varchar2(4000),
embedding vector,
embedding_model varchar2(100)
)
/
declare
procedure insert_recipe(
p_id number,
p_name in varchar2,
p_doc in varchar2)
is
begin
insert into recipes(id, name, doc)
values (p_id, p_name, p_doc);
end insert_recipe;
begin
execute immediate('truncate table recipes drop storage');
insert_recipe(1, 'Oatmeal Cookies',
'Use oatmeal and raisins with flour, oil and egg equivalent. Bake for a special treat');
insert_recipe(2, 'Strawberry Pie',
'Strawberries in a light syrup and a flaky crust are a great after dinner option');
insert_recipe(3, 'Grilled Cheese Sandwiches',
'Cheese, tomato slices and bread for a quick and delicious lunch');
insert_recipe(4, 'Miso Soup',
'Miso with tofu cubes and sliced green onions are the perfect complement to a sushi dinner');
insert_recipe(5, 'Curried Tofu',
'Tofu, vegetables and a light curry sauce served over rice is a nutritious and easy to prepare meal for anytime');
insert_recipe(6, 'Raspberry Tarts',
'Fresh raspberries in a folded pie crust are a great fall snack');
update recipes g
set
embedding = vector_embedding(all_minilm_l6_v2 using g.doc as data),
embedding_model = 'ALL_MINILM_L6_V2';
commit;
end;
/These embeddings are then used in vector searches by creating an embedding for the search string and using it to run a similarity search.
In the documentation examples, the search embedding is always generated first and passed to the query in a bind variable. There may be motivations for this that we can explore later. There is no issue with generating the search embedding inline with the sql statement.
--similarity-search-recipes.sql
column name format a30
column doc format a200
select name, doc
from recipes g
order by
vector_distance(
g.embedding
, vector_embedding(ALL_MINILM_L6_V2 using 'yummy dessert' as data)
, cosine)
fetch first 3 rows only;NAME DOC
Strawberry Pie Strawberries in a light syrup and a flaky crust are a great after dinner option
Raspberry Tarts Fresh raspberries in a folded pie crust are a great fall snack
Grilled Cheese Sandwiches Cheese, tomato slices and bread for a quick and delicious lunch
I don’t think everyone will consider grilled cheese sandwiches to be a yummy dessert… The results can be interesting, it looks like there will be a lot to explore with these augmented vector searches now that we have a head start.
Comparing Different Models
Given the strange results of the first test search, lets compare the results of using different models to search the same data with the same expression and see what we find.
The search has to use a vector search expression that is embedded with the same model that the table embedding vector uses, so we will have to update the embeddings with each search before running a test query to see what a ‘yummy dessert’ is in the eyes of each model.
Without further investigation of the nuances of these vector search queries, I am not sure that the model name can be passed as a bind variable. To be on the safe side I will use dynamic sql for the updates and the search queries.
--compare-model-searches.sql
set serveroutput on;
declare
cursor c_models is
select model_name
from user_mining_models
order by model_name;
procedure update_embeddings(p_model_name in varchar2)
is
l_sql varchar2(1000);
begin
l_sql := q'[
update recipes g
set
embedding = vector_embedding(##model_name## using g.doc as data),
embedding_model = '##model_name##'
]';
l_sql := replace(l_sql, '##model_name##', p_model_name);
execute immediate l_sql;
commit;
end update_embeddings;
procedure run_search(p_search_expression in varchar2, p_model_name in varchar2)
is
l_sql varchar2(1000);
rc sys_refcursor;
type t_rec is record(
ranking number
, name varchar2(100)
, doc varchar2(1000)
);
r t_rec;
begin
update_embeddings(p_model_name);
dbms_output.put_line(lpad('-', 50, '-'));
dbms_output.put_line('Mining Model: ' || p_model_name);
dbms_output.put_line('Search Expression: ' || p_search_expression);
l_sql := q'[
select rownum as ranking, name, doc
from
(
select name, doc
from recipes g
order by
vector_distance(
g.embedding
, vector_embedding(##model_name## using :search_expression as data)
, euclidean)
fetch first 3 rows only
)
]';
l_sql := replace(l_sql, '##model_name##', p_model_name);
open rc for l_sql using p_search_expression;
loop
fetch rc into r;
exit when rc%notfound;
dbms_output.put_line(r.ranking || '-' || r.name);
end loop;
close rc;
exception
when others then
dbms_output.put_line('Vector Distance Error, model ' || p_model_name || chr(10) || sqlerrm);
end run_search;
begin
for r in c_models loop
run_search('yummy dessert', r.model_name);
end loop;
end;
/--------------------------------------------------
Mining Model: ALL_MINILM_L12_V2
Search Expression: yummy dessert
1-Strawberry Pie
2-Raspberry Tarts
3-Oatmeal Cookies
--------------------------------------------------
Mining Model: ALL_MINILM_L6_V2
Search Expression: yummy dessert
1-Strawberry Pie
2-Raspberry Tarts
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: ALL_MPNET_BASE_V2
Search Expression: yummy dessert
1-Strawberry Pie
2-Raspberry Tarts
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: BERT_TINY
Search Expression: yummy dessert
1-Miso Soup
2-Curried Tofu
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: BGE_BASE_EN_V1_5
Search Expression: yummy dessert
1-Raspberry Tarts
2-Strawberry Pie
3-Oatmeal Cookies
--------------------------------------------------
Mining Model: BGE_MICRO_V2
Search Expression: yummy dessert
1-Raspberry Tarts
2-Strawberry Pie
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: BGE_SMALL_EN_V1_5
Search Expression: yummy dessert
1-Strawberry Pie
2-Oatmeal Cookies
3-Raspberry Tarts
--------------------------------------------------
Mining Model: CLINICAL_BERT
Search Expression: yummy dessert
1-Miso Soup
2-Strawberry Pie
3-Raspberry Tarts
--------------------------------------------------
Mining Model: DISTILUSE_BASE_MULTILINGUAL_CASED_V2
Search Expression: yummy dessert
1-Strawberry Pie
2-Raspberry Tarts
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: E5_BASE_V2
Search Expression: yummy dessert
1-Raspberry Tarts
2-Strawberry Pie
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: E5_SMALL_V2
Search Expression: yummy dessert
1-Raspberry Tarts
2-Strawberry Pie
3-Grilled Cheese Sandwiches
--------------------------------------------------
Mining Model: FINBERT
Search Expression: yummy dessert
1-Grilled Cheese Sandwiches
2-Raspberry Tarts
3-Strawberry Pie
--------------------------------------------------
Mining Model: GTE_BASE
Search Expression: yummy dessert
1-Strawberry Pie
2-Raspberry Tarts
3-Oatmeal Cookies
--------------------------------------------------
Mining Model: GTE_SMALL
Search Expression: yummy dessert
1-Strawberry Pie
2-Oatmeal Cookies
3-Raspberry Tarts
--------------------------------------------------
Mining Model: GTE_TINY
Search Expression: yummy dessert
1-Strawberry Pie
2-Raspberry Tarts
3-Oatmeal Cookies
--------------------------------------------------
Mining Model: MULTILINGUAL_E5_SMALL
Search Expression: yummy dessert
1-Grilled Cheese Sandwiches
2-Strawberry Pie
3-Raspberry Tarts
--------------------------------------------------
Mining Model: MULTI_QA_MINILM_L6_COS_V1
Search Expression: yummy dessert
1-Strawberry Pie
2-Grilled Cheese Sandwiches
3-Raspberry Tarts
--------------------------------------------------
Mining Model: STELLA_BASE_EN_V2
Search Expression: yummy dessert
1-Strawberry Pie
2-Oatmeal Cookies
3-Grilled Cheese Sandwiches
It looks like several of the models got the concept of the search and other models would be quite inappropriate as cafe waiters. Understanding why these results are so different will be critical to how we choose a model to use for AI Vector searches.
Download Augmented Models
I set up an open source repository at https://github.com/gaiansentience/practical-oracle-llm to store Oracle database ready ONNX models for easy download. This is intended to allow database developers to investigate AI Vector Searching without going into the Python rabbithole.
All of my notes from exporting each model are also in this repository along with the scripts for loading each model to the database. In the future when its easier (and less expensive) to store large files on github, I will upload augmented models there for easy access.
It turns out that github free accounts are not the best place to store large files, so I uploaded all of the successful exports to Google Drive and shared them for anyone with the links. They are listed in the readme file of the repository, here is the list if you want to get started right away with in database AI Vector embedding and searches in Oracle 23ai.
HuggingFace open source ONNX Models in this listing have been augmented with OML4PY to support direct loading into Oracle 23ai databases.
All models were exported using OML4PY and loaded successfully in Oracle 23ai 23.5
ONNX files are shared on Google Drive in my LLM4ORACLE folder
sentence-transformers/all-MiniLM-L6-v2
all_MiniLM_L6_v2.onnx (88 MB)
sentence-transformers/all-MiniLM-L12-v2
all_MiniLM_L12_v2.onnx (130 MB)
sentence-transformers/all-mpnet-base-v2
all_mpnet_base_v2.onnx (107 MB)
prajjwal1/bert-tiny
bert_tiny.onnx (17 MB)
BAAI/bge-base-en-v1.5
bge_base_en_v1_5.onnx (107 MB)
taylorAI/bge-micro-v2
bge_micro_v2.onnx (34 MB)
BAAI/bge-small-en-v1.5
bge_small_en_v1_5.onnx (130 MB)
medicalai/ClinicalBERT
clinical_bert.onnx (133 MB)
sentence-transformers/distiluse-base-multilingual-cased-v2
distiluse_base_multilingual_cased_v2.onnx (133 MB)
intfloat/e5-base-v2
e5_base_v2.onnx (107 MB)
intfloat/e5-small-v2
e5_small_v2.onnx (130 MB)
ProsusAI/finbert
finbert.onnx (107 MB)
thenlper/gte-base
gte_base.onnx (213 MB)
thenlper/gte-small
gte_small.onnx (65 MB)
TaylorAI/gte-tiny
gte_tiny.onnx (44 MB)
sentence-transformers/multi-qa-MiniLM-L6-cos-v1
multi_qa_MiniLM_L6_cos_v1.onnx (88 MB)
intfloat/multilingual-e5-small
multilingual_e5_small.onnx (120 MB)
infgrad/stella-base-en-v2
stella_base_en_v2.onnx (213 MB)Database native AI Vector searching is a whole new area to explore, it is my hope that making these models easily available will help Oracle developers dive deeper into the specifics without any entry barriers.
–Anthony Harper
Practical Pl/Sql 
Leave a comment