Example 4:

In this example we'll insert 304 vectors and index them before searching on them. Once the number of vectors that you are searching grows into the hundreds of thousands, using an index for the search is a lot more efficient.

First, execute the following at the UNIX command line:


awk 'BEGIN { print "BEGIN WORK;";
for (i=1; i<=300; i++) {
printf "insert into ifx_sample_tab4 values (%d, lvector_embed('\''sample text %d'\''), '\''{\"sample text
%d\"}'\'', '\''{\"This metadata will not be used in this example\"}'\'');\n", i, i, i;
}
printf "insert into ifx_sample_tab4 values (301, lvector_embed('\''This text is about the new york cycle club'\''), '\''{\"This text is about the new york cycle club\"}'\'', '\''{\"This metadata will not be used in this example\"}'\'');\n";
printf "insert into ifx_sample_tab4 values (302, lvector_embed('\''This text is about the new york citrus club'\''), '\''{\"This text is about the new york citrus club\"}'\'', '\''{\"This metadata will not be used in this example\"}'\'');\n";
printf "insert into ifx_sample_tab4 values (303, lvector_embed('\''This text is about the los angeles penal code'\''), '\''{\"This text is about the los angeles penal code\"}'\'', '\''{\"This metadata will not be used in this example\"}'\'');\n";
printf "insert into ifx_sample_tab4 values (304, lvector_embed('\''This text can be about anything you want. Let us say... monkeys.'\''), '\''{\"This text can be about anything you want. Let us say... monkeys.\"}'\'', '\''{\"This metadata will not be used in this example\"}'\'');\n";
print "COMMIT WORK;";
}' > /tmp/load_ifx_sample_tab4.sql Now create our traditional table: database examples;
create table ifx_sample_tab4 (
ident bigint,
embedding_data lvector_embedding, text_data lvarchar,
metadata lvarchar, primary key (ident)
);
Exit out of dbaccess. Now insert vectors and their associated text into our traditional table using the script:
dbaccess examples /tmp/load_ifx_sample_tab4.sql Back into dbaccess now.
-- Create our lance table that will be used for vector searches database examples;
EXECUTE PROCEDURE lvector_create_table('sample_tab4', 1536);
-- Synchronize the traditional table with the lance table database examples;
EXECUTE PROCEDURE lvector_bulk_sync('ifx_sample_tab4', 'sample_tab4', 'ident', 'embedding_data');
-- Create an index on the lance table
EXECUTE PROCEDURE lvector_create_index('sample_tab4', 'ivf_hnsw_pq ', 1);
-- Now let's do a symantic search that will make use of the vector
-- index
SELECT text_data
FROM ifx_sample_tab4WHERE ident IN
(SELECT * FROM TABLE(lvector_id_search('sample_tab4', lvector_embed('Tell me about the NYCC'), 2)));

Results:

text_data {"This text is about the new york cycle club"} text_data {"This text is about the new york citrus club"}