Using Oracle Vector Search to find Similar Wines – Part 3

Posted on Updated on

In this post we will move onto exploring similarity search for the wine of interest. This is an Irish produced wine called Lusca Wine. In the previous posts we did all the hard work of setting up our original data set and adding the AI model to the database. Check out Part-1 and Part-2 for these details. This post will focus on creating a stored function to generate the vector, creating a trigger on our data set table to automatically update the vector data when a new record is added or a update, and then exploring similar wines using vector search.

1-Create a Function to Generate the Vector

To simplify the generation of a Vector we can create a function to perform the necessary steps. This means we have a simple function call to use going forward.

create or replace function wine_desc_vector(p_desc varchar2)
	return VECTOR
IS
    v_vector VECTOR;
BEGIN
	SELECT vector_embedding(all_minilm_l12_v2 using p_desc as data) INTO v_vector;
	
	RETURN v_vector;
END wine_desc_vector;
/

We can test this using the following:

set serveroutput on;
DECLARE
    v_return VECTOR;
BEGIN
    v_return := wine_desc_vector('A lovely fruity red from the south of france.');
    dbms_output.put_line(to_char(v_return));
END;
/
set serveroutput off;

This will print out the vector data. I haven’t included it here as the output is very long.

2-Cerate a Table Trigger to Automatically the Vector

We can now use this function to add or update the vector data whenever a record is inserted into the table or a record is updated.

CREATE OR REPLACE TRIGGER trig_winereviews_vec
BEFORE INSERT OR UPDATE ON winereviews_vec
REFERENCING NEW as NEW
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        :NEW.wine_vec := wine_desc_vector(:NEW.description);        
    ELSIF UPDATING THEN
        :NEW.wine_vec := wine_desc_vector(:NEW.description);     
    END IF;
END;
/

3-Insert our Lusca Wine Data

For our test/sample Wine I’m using the Lusca Cabernet Merlot. I’m going to use the description on their website and create a record in the table.

insert into winereviews_vec (id, country, title, winery, description)
values(999999, 'Ireland', 'Lusca Cabernet Merlot', 'Lusca Vineyard', 'With a smattering of Cabernet Franc, Shiraz, and Dunkelfelder. Deep ruby red in colour. On the nose there are harmonious aromas of blackcurrants, green herbs, green peppers, mint, black pepper and vanilla. Ripe, dark fruit flavours of blackcurrants, plums, damsons and redcurrants');
select country, title, winery, description, wine_vec from winereviews_vec where id = 999999;

When the above is run, we will see the Vector data being displayed.

4-Use Vector Search to Explore Similar Wines

We can now perform Similarity Search.

SELECT vector_distance(wine_vec,
                       (SELECT wine_vec FROM winereviews_vec WHERE id = 999999)) AS distance,
       title,
       variety,
       winery,
       country
FROM   winereviews_vec
WHERE  id <> 999999
ORDER  BY 1
FETCH  APPROXIMATE FIRST 5 ROWS ONLY;

This will display the 5 closest or most similar wines.

We can expand our search for Wines from France and Italy.

SELECT vector_distance(wine_vec,
                       (SELECT wine_vec FROM winereviews_vec WHERE id = 999999)) AS distance,
       title,
       variety,
       winery,
       country
FROM   winereviews_vec
WHERE  id <> 999999
AND    country in ('France', 'Italy')
ORDER  BY 1
FETCH  APPROXIMATE FIRST 5 ROWS ONLY;

Although the results gives us the most similar other wines based on the description data. But out accurate are the results. This is where some empirical testing is needed. Yes we would have to buy these wines and to do a full tasting. I’ll leave that enjoyable experience to yourself to explore.

This entry was posted in Machine Learning and tagged , , , .