Using semantic search in Oracle AI Database 26ai
In this post I will demonstrate the use of multilingual semantic search in Oracle AI Database 26ai.
Table of Contents
We will perform a semantic search on a text column in an Oracle AI 26ai database. To be able to create the vector data (using the new data type VECTOR of Oracle 26ai) for the semantic search we will load a small language model into the database. No network connection to a LLM is needed while querying the data. Since the language model (Multilingual-E5-base) is trained on multiple languages we can perform language independent searches. One very nice feature is the complete integration of the search into standard SQL. This enables developers to use this new feature without installing additional software and tools. Only Oracle AI Database 26ai is needed. The installation of such a database is described in this post.
1. Download and load the language model
In the first step we will download the language model (multilingual_e5_base) and place it to a location accessible from the VM. Then we load it into the Oracle database. Run the following statements as the user oracle:
# copy and unpack the language model
mkdir /u01/models; cd /u01/models
cp /sw/oracle/ai/multilingual_e5_base_augmented.zip .
unzip -q multilingual_e5_base_augmented.zip
Sample Output (click to expand):
[oracle@lin1 ~]$ # copy and unpack the language model
[oracle@lin1 ~]$ mkdir /u01/models; cd /u01/models
[oracle@lin1 models]$ cp /sw/oracle/ai/multilingual_e5_base_augmented.zip .
[oracle@lin1 models]$ unzip -q multilingual_e5_base_augmented.zip
[oracle@lin1 models]$
Now we load the language model into the database. Run these statements from an SQLcl session:
-- connect as system and create user1 for this test
conn -rp -sv -sa lin1_pdb1_system system/changeme@//lin1.fritz.box/pdb1.fritz.box
create user user1 identified by changeme;
grant dba to user1 container=current;
conn -rp -sv -sa lin1_pdb1_user1 user1/changeme@//lin1.fritz.box/pdb1.fritz.box
-- load the language model
create or replace directory model_dir as '/u01/models';
exec dbms_vector.load_onnx_model(directory=>'model_dir', file_name=>'multilingual-e5-base.onnx', model_name => 'MULTILINGUAL_E5_BASE');
Sample Output (click to expand):
SQL> -- connect as system and create user1 for this test
SQL> conn -rp -sv -sa lin1_pdb1_system system/changeme@//lin1.fritz.box/pdb1.fritz.box
Name: lin1_pdb1_system
Connect String: //lin1.fritz.box/pdb1.fritz.box
User: system
Password: ******
Connected.
SQL> create user user1 identified by changeme;
User USER1 created.
Elapsed: 00:00:00.038
SQL> grant dba to user1 container=current;
Grant succeeded.
Elapsed: 00:00:00.008
SQL> conn -rp -sv -sa lin1_pdb1_user1 user1/changeme@//lin1.fritz.box/pdb1.fritz.box
Name: lin1_pdb1_user1
Connect String: //lin1.fritz.box/pdb1.fritz.box
User: user1
Password: ******
Connected.
SQL>
SQL> -- load the language model
SQL> create or replace directory model_dir as '/u01/models';
Directory MODEL_DIR created.
Elapsed: 00:00:00.007
SQL> exec dbms_vector.load_onnx_model(directory=>'model_dir', file_name=>'multilingual-e5-base.onnx', model_name => 'MULTILINGUAL_E5_BASE');
PL/SQL procedure successfully completed.
Elapsed: 00:00:55.927
SQL>2. Create the table and vector data
Now we will create the sample table, create some test data and create vector data for each row (column “description”). Run this in SQLcl as user1:
-- create the test table that stores IT tickets with error descriptions and solutions
CREATE TABLE it_tickets (
id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
priority VARCHAR2(10),
description VARCHAR2(500),
solution VARCHAR2(1000),
embedding VECTOR -- this column will store the VECTOR data
);
-- insert some test data
INSERT INTO it_tickets (priority, description, solution) VALUES
('HIGH', 'My laptop won''t connect to the office Wi-Fi after the latest Windows update', 'Roll back KB5021234, then run network reset'),
('HIGH', 'Wi-Fi authentication keeps failing on company network since patch Tuesday', 'Reinstall wireless adapter driver, forget and re-add SSID'),
('MEDIUM', 'Internet drops every 10 minutes on corporate network', 'Replace defective Ethernet cable, update NIC driver'),
('CRITICAL', 'Database server not reachable after firewall change', 'Revert iptables rule, restart network service'),
('LOW', 'Can''t print to the shared HP LaserJet', 'Clear print spooler, re-add printer by IP');
COMMIT;
-- create vector data and store them in column "embedding"
UPDATE it_tickets SET embedding = vector_embedding(multilingual_e5_base USING description AS data);
COMMIT;
Sample Output (click to expand):
SQL> -- create the test table that stores IT tickets with error descriptions and solutions
SQL> CREATE TABLE it_tickets (
2 id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
3 priority VARCHAR2(10),
4 description VARCHAR2(500),
5 solution VARCHAR2(1000),
6 embedding VECTOR -- this column will store the VECTOR data
7* );
Table IT_TICKETS created.
Elapsed: 00:00:00.018
SQL>
SQL> -- insert some test data
SQL> INSERT INTO it_tickets (priority, description, solution) VALUES
2 ('HIGH',
3 'My laptop won''t connect to the office Wi-Fi after the latest Windows update',
4 'Roll back KB5021234, then run network reset'),
5 ('HIGH',
6 'Wi-Fi authentication keeps failing on company network since patch Tuesday',
7 'Reinstall wireless adapter driver, forget and re-add SSID'),
8 ('MEDIUM',
9 'Internet drops every 10 minutes on corporate network',
10 'Replace defective Ethernet cable, update NIC driver'),
11 ('CRITICAL',
12 'Database server not reachable after firewall change',
13 'Revert iptables rule, restart network service'),
14 ('LOW',
15 'Can''t print to the shared HP LaserJet',
16* 'Clear print spooler, re-add printer by IP');
5 rows inserted.
Elapsed: 00:00:00.021
SQL>
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.002
SQL>
SQL> -- create vector data and store them in column "embedding"
SQL> UPDATE it_tickets SET embedding = vector_embedding(multilingual_e5_base USING description AS data);
5 rows updated.
Elapsed: 00:00:04.388
SQL> COMMIT;
Commit complete.
Elapsed: 00:00:00.002
SQL>3. (optional) Create a HNSW Vector index
This step is optional and can be used to improve performance of the semantic searches. It is called an In-Memory Hierarchical Navigable Small World (HNSW) Index which runs completely in memory. If we want to use this type of index we need to enable the Vector Pool which is part of the Oracle SGA. This requires a container database restart. Run this as user system on the CDB:
-- to avoid: ORA-51962: The vector memory area is out of space for the current container.
-- we need to set the vector memory size:
conn -rp -sv -sa lin1_orcl_system system/changeme@//lin1.fritz.box/orcl.fritz.box
alter system set vector_memory_size=128m scope=spfile;
Then we restart the CDB and all PDBs as the oracle os user:
srvctl stop database -d orcl
srvctl start database -d orcl
Now we are ready to create one or more HNSW indexes.
-- connect as user1 to pdb1 with SQLcl
conn -n lin1_pdb1_user1
-- create the HNSW index
CREATE VECTOR INDEX it_tickets_hnsw_idx ON it_tickets (embedding)
ORGANIZATION INMEMORY NEIGHBOR GRAPH
DISTANCE COSINE
WITH TARGET ACCURACY 95;
-- show infos about the index
SELECT index_name, index_organization, num_vectors, distance_type, index_dimensions, default_accuracy
FROM v$vector_index
WHERE index_name = 'IT_TICKETS_HNSW_IDX';
Sample Output (click to expand):
SQL> -- connect as user1 to pdb1 with SQLcl
SQL> conn -n lin1_pdb1_user1
Connected.
SQL>
SQL> -- create the HNSW index
SQL> CREATE VECTOR INDEX it_tickets_hnsw_idx ON it_tickets (embedding)
2 ORGANIZATION INMEMORY NEIGHBOR GRAPH
3 DISTANCE COSINE
4* WITH TARGET ACCURACY 95;
Vector INDEX created.
Elapsed: 00:00:01.511
SQL>
SQL> -- show infos about the index
SQL> SELECT index_name, index_organization, num_vectors, distance_type, index_dimensions, default_accuracy
2 FROM v$vector_index
3* WHERE index_name = 'IT_TICKETS_HNSW_IDX';
INDEX_NAME INDEX_ORGANIZATION NUM_VECTORS DISTANCE_TYPE INDEX_DIMENSIONS DEFAULT_ACCURACY
______________________ __________________________ ______________ ________________ ___________________ ___________________
IT_TICKETS_HNSW_IDX INMEMORY NEIGHBOR GRAPH 5 COSINE 768 95
1 row selected.
Elapsed: 00:00:00.012
SQL>4. Perform the multilingual semantic search
Now it is time to actually query the data and show the top 3 rows ordered by similarity. We want to show all IT tickets that have a similar meaning than “Wireless connection fails after Tuesday’s patches”:
-- run as user1
SELECT t.id, t.priority, t.description, t.solution,
round(VECTOR_DISTANCE(
t.embedding,
vector_embedding(multilingual_e5_base USING 'Wireless connection fails after Tuesday’s patches' AS data),
COSINE)
,3) AS similarity_distance
FROM it_tickets t
ORDER BY similarity_distance
FETCH FIRST 3 ROWS ONLY;
Query Output (click):
ID PRIORITY DESCRIPTION SOLUTION SIMILARITY_DISTANCE
_____ ___________ ______________________________________________________________________________ ____________________________________________________________ ______________________
2 HIGH Wi-Fi authentication keeps failing on company network since patch Tuesday Reinstall wireless adapter driver, forget and re-add SSID 0,071
4 CRITICAL Database server not reachable after firewall change Revert iptables rule, restart network service 0,137
1 HIGH My laptop won't connect to the office Wi-Fi after the latest Windows update Roll back KB5021234, then run network reset 0,146
3 rows selected.
And since the search is language independent we can also search in other languages. For example in German or in Chinese:
-- run as user1
SELECT t.id, t.priority, t.description, t.solution,
round(VECTOR_DISTANCE(
t.embedding,
vector_embedding(multilingual_e5_base USING 'Die drahtlose Verbindung funktioniert nach dem Patchen am Dienstag nicht mehr.' AS data),
COSINE)
,3) AS similarity_distance
FROM it_tickets t
ORDER BY similarity_distance
FETCH FIRST 3 ROWS ONLY;
SELECT t.id, t.priority, t.description, t.solution,
round(VECTOR_DISTANCE(
t.embedding,
vector_embedding(multilingual_e5_base USING '週二的補丁更新後，無線連接出現故障。' AS data),
COSINE)
,3) AS similarity_distance
FROM it_tickets t
ORDER BY similarity_distance
FETCH FIRST 3 ROWS ONLY;
This will give us the same ordered output which is pretty amazing
SQL> -- run as user1
SQL> SELECT t.id, t.priority, t.description, t.solution,
2 round(VECTOR_DISTANCE(
3 t.embedding,
4 vector_embedding(multilingual_e5_base USING 'Die drahtlose Verbindung funktioniert nach dem Patchen am Dienstag nicht mehr.' AS dat
a),
5 COSINE)
6 ,3) AS similarity_distance
7 FROM it_tickets t
8 ORDER BY similarity_distance
9* FETCH FIRST 3 ROWS ONLY;
ID PRIORITY DESCRIPTION SOLUTION SIMILARITY_DISTANCE
_____ ___________ ______________________________________________________________________________ ____________________________________________________________ ______________________
2 HIGH Wi-Fi authentication keeps failing on company network since patch Tuesday Reinstall wireless adapter driver, forget and re-add SSID 0,146
4 CRITICAL Database server not reachable after firewall change Revert iptables rule, restart network service 0,15
1 HIGH My laptop won't connect to the office Wi-Fi after the latest Windows update Roll back KB5021234, then run network reset 0,166
3 rows selected.
Elapsed: 00:00:00.078
SQL>
SQL> SELECT t.id, t.priority, t.description, t.solution,
2 round(VECTOR_DISTANCE(
3 t.embedding,
4 vector_embedding(multilingual_e5_base USING '週二的補丁更新後，無線連接出現故障。' AS data),
5 COSINE)
6 ,3) AS similarity_distance
7 FROM it_tickets t
8 ORDER BY similarity_distance
9* FETCH FIRST 3 ROWS ONLY;
ID PRIORITY DESCRIPTION SOLUTION SIMILARITY_DISTANCE
_____ ___________ ______________________________________________________________________________ ____________________________________________________________ ______________________
2 HIGH Wi-Fi authentication keeps failing on company network since patch Tuesday Reinstall wireless adapter driver, forget and re-add SSID 0,154
4 CRITICAL Database server not reachable after firewall change Revert iptables rule, restart network service 0,164
1 HIGH My laptop won't connect to the office Wi-Fi after the latest Windows update Roll back KB5021234, then run network reset 0,166
3 rows selected.
Elapsed: 00:00:00.073
SQL>
Leave a Reply