AI and Oracle for free? really?
In this blog post I will describe a use-case with a free AI model provided by Oracle that can be used in the 26ai free edition.
The use-case is: I have a table with additional infos about bands (country, genre, homepage, facebook, youtube, …). And I have a table with events (concerts, festivals) and the bands playing there. If the bandname matches the bandname in the bands table, then I can see the additional info. And sure, I do not have additional infos for all bands. But maybe there are just bands with different spellings.
Theory
The classical way
Let’s see what Oracle already offered to find similar content
lower
Compare the data case-insensitive. If it is written “Meier” or “meier”, that is the same for us
Questions about your own setup? A dbi services consultant can look at your case.
Talk to an expert (opens in a new tab)
select name from persons where lower(name) = 'meier';
like
We will use placeholders for parts we are not interested in. If the band is written “the young gods” or “young gods”, it’s probably the same
select * from bands where band like '%young gods'
regexp_like
more powerful than like, using regular expressions. The following is more precise. At the beginning must be 0 or 1 time “the “.
select * from bands where regexp_like(band, '(^the\s+)?young gods');
Soundex
Start with the 1st char, then calculate an unique code for similar words. “Meier” and “Mayer” both maps to soundex M600. The disadvantage is that only the first few characters are evaluated.
select name from persons where soundex(name) like soundex('meier');
The new AI Vector Search
Return data with similar content – with AI vector search. For example, we have images with animals and want to search all images with a horse.
We have to create an algorithm to create vectors of the image. This is the so-called “model”. And it is not easy to create such a model. Imagine a horse, a donkey, and a mule. What are the differences between them? In comparison, a duck is easy to distinguish. So our model must provide a significantly different vector for the duck than for the other animals. “We”, or at least most of us will use existing models…

And now, how we can retrieve images of horses? Calculate the vector of a horse image, then return the data having the smallest vector distance to our calculated vector.
Usually vectors gets quite big. There are more attributes for animals, e.g. color, size, skin/fur, …
And a vector is calculated for each of these features
The all-MiniLM-L 12-v2 model
Oracle offers the all-MiniLM-L 12-v2 model for download. The file is in the ONNX format and can be loaded in the 26ai database. Download the file to /u01/models in this example.
create user if not exists vector identified by vector quota unlimited on users;
grant create session, db_developer_role, create mining model to vector;
create or replace directory model_dir as '/u01/models';
grant read, write on directory model_dir to vector;
connect vector/vector@//localhost:1521/orclpdb.erfinder
begin
dbms_vector.drop_onnx_model (model_name => 'ALL_MINILM_L12_V2', force => true);
dbms_vector.load_onnx_model (
directory => 'model_dir',
file_name => 'all_MiniLM_L12_v2.onnx',
model_name => 'ALL_MINILM_L12_V2');
end;
/
select model_name, algorithm, mining_function from user_mining_models;
select vector_embedding(all_minilm_l12_v2 using 'Bob Dylan' as data) AS my_vector;
-- To grant it for other users:
grant select on mining model vector.ALL_MINILM_L12_V2 to myuser;
[-3.28829587E-002,7.12544471E-002,-4.57076691E-002,3.34124677E-002,-1.0640244E-002,4.99891154E-002,1.04386434E-001,-6.72798455E-002,-7.81936664E-003,-8.73053167E-003,-2.36021634E-002,2.01056106E-003,-2.46723387E-002,-5.60818836E-002,-3.7508145E-002,1.28634855E-001,5.22060208E-002,-8.21595732E-003,1.95498653E-002,-5.5897072E-002,2.00462937E-002,1.13573E-001,-1.04404532E-003,4.60094661E-002,-2.34374423E-002,3.59080993E-002,-1.83110982E-002,4.1176632E-002,-2.09390521E-002,-4.84892018E-002,-6.01600558E-002,1.95941841E-003,-9.55247506E-002,-4.36511822E-002,2.03858018E-002,5.24709793E-003,-8.09903815E-002,2.58681905E-002,8.45895056E-003,-5.15323766E-002,2.27247402E-002,-3.28555144E-002,-2.27001943E-002,-5.32310866E-002,-9.1472546E-005,-1.03046363E-002,-1.69764727E-003,-1.54329734E-002,4.56825159E-002,7.77621344E-002,3.46121155E-002,-7.57156834E-002,-3.00063612E-003,-4.53864262E-002,-3.84752005E-002,-6.36761934E-002,-2.88341157E-002,5.90838827E-002,-8.12250748E-003,-4.64118458E-002,-3.17675695E-002,-4.25537676E-002,-2.16702688E-002,2.84322235E-003,8.61015916E-003,-2.14451943E-002,8.40565786E-002,-5.86571507E-002,-9.2464231E-002,1.56575125E-002,-3.73052713E-003,8.10047705E-003,1.00164764E-001,-6.44701393E-003,4.39257845E-002,-1.54028821E-003,5.98016754E-002,1.09822132E-001,-2.7124064E-002,-4.46104561E-004,3.20078549E-003,2.79765911E-002,5.03437296E-002,-8.46643522E-002,-9.36391726E-002,-6.73487186E-002,1.84180625E-002,9.63722449E-003,1.24550518E-002,-8.65510926E-002,-7.59489909E-002,1.26308557E-002,3.50978156E-003,-6.28657341E-002,-1.58169977E-002,7.58793652E-002,3.8126424E-002,-5.30581661E-002,-6.27498478E-002,2.64155805E-001,-8.89125839E-003,4.76834886E-002,-3.44932079E-002,3.28368917E-002,4.07917947E-002,-2.3367377E-002,-4.72930893E-002,4.23461609E-002,3.70606966E-003,-4.84246351E-002,3.95867899E-002,-6.20418042E-003,-5.71911037E-002,2.7675597E-002,1.08781736E-002,-8.90007392E-002,2.75215507E-002,-1.67274829E-002,-1.55357672E-002,9.31895003E-002,-5.24692051E-002,3.33056785E-003,-6.93096919E-003,-1.27648907E-002,-1.31879762E-001,-5.87911345E-002,-3.01394444E-002,-1.30496789E-002,1.229118E-002,-1.03035718E-002,3.59751955E-002,-6.99997321E-002,1.10877313E-001,-2.81976648E-002,-6.04905449E-002,3.60108092E-002,-1.06216678E-002,-4.39494215E-002,7.80562013E-002,-1.6695112E-001,1.50056444E-002,7.97785446E-002,2.97659002E-002,4.84109372E-002,-2.56663887E-003,-6.71984777E-002,3.34760435E-002,-2.94934344E-002,-9.33799371E-002,-2.78656222E-002,-4.63584922E-002,-1.08145699E-002,7.80638028E-003,-2.63266789E-004,-8.88938643E-003,3.12825069E-002,6.60058409E-002,2.45609395E-002,2.61974223E-002,4.66002934E-002,5.83518781E-002,1.80750992E-003,-2.74173599E-002,-3.43227871E-002,-2.48813797E-002,5.47519652E-003,-5.82680777E-002,-7.43228644E-002,-1.88004076E-002,-1.98282674E-003,2.54240092E-002,-2.25611627E-002,-6.18117265E-002,7.44435415E-002,9.6341148E-003,1.5786672E-002,-1.04088732E-003,3.61631215E-002,-3.38860713E-002,-4.13281173E-002,-4.50802706E-002,1.95613988E-002,-1.99177042E-002,5.17900707E-003,4.90007438E-002,5.03397081E-003,-6.19004597E-004,-1.13240853E-002,-1.02219107E-002,8.01912174E-002,7.19214156E-002,4.65811603E-002,3.64658721E-002,-4.94964374E-003,2.24289093E-002,-6.02491722E-002,7.99450616E-004,-7.01370239E-002,-7.43662044E-002,-5.69670312E-002,4.62124683E-002,-4.12194617E-002,8.31926242E-003,-9.16962977E-003,5.90781793E-002,-8.93979073E-002,2.46489439E-002,2.81920489E-002,3.73301096E-002,-2.34126672E-002,-2.66622845E-002,-4.46192324E-002,-1.0867619E-002,-3.94763537E-002,-6.28683269E-002,-9.89502668E-002,2.84670247E-003,-4.54771668E-002,-5.41057028E-002,1.49319759E-002,-8.64058435E-002,-2.36107074E-002,-8.96388739E-002,7.36331425E-033,-1.30065847E-002,9.65155736E-002,8.05562809E-002,7.79263452E-002,1.354397E-002,-1.92665216E-002,1.22084673E-002,-4.27422523E-002,5.58627024E-002,4.50925622E-003,-2.63720471E-002,1.77200157E-002,3.12984958E-002,-5.7092566E-002,4.52824384E-002,2.39689071E-002,-6.85803741E-002,8.76181796E-002,7.43384063E-002,3.8558092E-002,3.15629202E-003,3.26407105E-002,4.78902496E-002,1.39964083E-002,-8.43583196E-002,-4.61399555E-002,-9.19615477E-003,7.43216872E-002,5.51957591E-003,-2.65764855E-002,3.59744169E-002,2.61799637E-002,-1.89742539E-002,-4.89917509E-002,-9.24255606E-003,2.38266308E-002,5.41158468E-002,5.97609766E-002,5.02838008E-002,-3.57549414E-002,6.40272647E-002,5.00370339E-002,-3.69527489E-002,4.64274175E-002,2.49757692E-002,-6.76745037E-003,8.21680669E-003,5.42990454E-002,-3.16958688E-002,1.39125353E-002,-6.24617971E-002,2.69391462E-002,1.52521878E-002,1.6519472E-002,-1.17503544E-002,-4.99484092E-002,4.65028994E-002,2.52046362E-002,2.21878756E-002,-1.3119689E-003,-6.08432367E-002,3.77141424E-002,4.42493074E-002,-3.14246006E-002,6.79688305E-002,-8.38463474E-003,-4.85547185E-002,4.65472452E-002,-2.1941036E-002,-4.92079766E-004,-5.7777293E-002,2.28965795E-003,-8.90555829E-002,2.1388296E-002,-2.90373433E-002,6.60333186E-002,2.44755242E-002,-1.03102364E-001,-5.32066338E-002,3.33813578E-002,-5.51838949E-002,2.94785537E-002,-6.78635314E-002,3.5423249E-002,-2.19605342E-002,-4.11156788E-002,7.0969604E-002,-5.22063114E-002,3.73266009E-003,1.00158148E-001,4.24587019E-002,-7.08252415E-002,-2.02396E-003,9.50025488E-003,2.54774839E-002,2.5477032E-035,1.43452147E-002,5.16638942E-002,-1.19648194E-002,-2.49146926E-003,1.24402598E-001,5.28191328E-002,1.02975033E-001,-4.78652567E-002,-5.45930974E-002,1.24237491E-002,1.61334034E-002,-7.59155899E-002,3.52150798E-002,4.61573005E-002,8.28202143E-002,5.92956669E-004,-8.56052712E-002,-1.63979232E-002,8.12467374E-003,2.49185823E-002,7.56010786E-002,8.44534859E-002,8.50686207E-002,-3.74653749E-002,5.28270602E-002,2.15788018E-002,3.67684476E-002,8.69528204E-003,-3.50426733E-002,5.644219E-002,2.85430346E-002,6.9013454E-002,-8.73260349E-002,8.75519589E-002,-1.54081145E-002,-6.64589033E-002,1.22624952E-002,-5.2193813E-002,5.41517045E-003,4.24271524E-002,-2.26328755E-003,1.00552432E-001,1.41395396E-002,2.75035575E-003,3.79429497E-002,-3.93360481E-002,1.01929337E-001,1.10946614E-002,8.39847848E-002,2.47093942E-002,-4.82656285E-002,-3.38978358E-002,8.23562592E-002,9.43520106E-003,1.99154788E-003,-1.32039923E-003,-1.74975079E-002,5.25957383E-002,-9.51754451E-002,-3.40001434E-002,1.5815651E-002,5.44813403E-004,-7.75640979E-002,-1.0712678E-001]
As you can see, a vector of this model is much bigger than the input “Bob Dylan”!
In 26ai we have a new datatype VECTOR
alter table bands add (band_vector vector);
update bands
set BAND_VECTOR = vector_embedding(all_minilm_l12_v2 using BAND as data);
That’s the table with the 9000 bands. In the table bands_unknown (11’000 bands) I will add the most probable band and the vector_dist field.
DECLARE
CURSOR c_bands_unknown IS SELECT alias FROM bands_unknown;
v_band
bands.band%TYPE;
v_vector_dist NUMBER;
BEGIN
FOR r IN c_bands_unknown LOOP
SELECT band, vector_distance(b.band_vector
, vector_embedding(all_minilm_l12_v2 USING r.alias AS data)) INTO v_band, v_vector_dist
FROM vector.bands b ORDER BY 2 FETCH FIRST 1 ROW ONLY;
UPDATE bands_unknown
SET band = v_band, vector_dist = v_vector_dist WHERE alias = r.alias;
END LOOP;
END;
/
Afterwards, we can sort the result by vector distance. And the result?
Practically all results with a vector distance < 0.01 are correct, except e.g.:
- DC/AC -> AC/DC
- AC/DC is a well-known Australian hardrock band
- DC/AC is a small swiss AC/DC cover band
- Kids -> Children
- Children is a hardcore-punk band from Berlin
- Kids is a punkrock band from Zurich
Up to a vector distance of 0.07 the results are quite good, afterwards, the number of false mappings significantly increases.

For me, AI is a great tool for finding duplicates here. However, relying solely on AI isn’t the solution. Some human oversight is still needed. For example, the model isn’t yet able to recognize that, when working with band-names, linguistic synonyms shouldn’t be merged.