Enhance User Experience with AI Vector Search in APEX

With Oracle Database 23ai, AI Vector Search is now built in, making it easier than ever to bring powerful AI capabilities to your data, enabling seamless vector embedding and semantic search directly within the database. We can leverage this to build smarter APEX apps with intelligent search functionality. In this blog, I'll explore the vector search capabilities in APEX using an ONNX model. I'll walk through the steps to quickly create a simple app powered by AI Vector Search.

What is the ONNX model?

ONNX (Open Neural Network Exchange) is an open standard for machine learning models, enabling easy transfer between frameworks without needing to re-implement them. Oracle Database 23ai includes a built-in ONNX runtime, allowing you to import trained models and perform in-database vector embedding. This simplifies deployment and brings deep learning capabilities directly into the database.

The model can be downloaded here, and there's plenty of documentation available to help you. Once downloaded, you'll need to import the model into Oracle Database 23ai. There are several ways to do this: load the model as a BLOB into a custom table, upload it to a database directory, or place it in an OCI bucket.

In my case, using an OCI bucket was the simplest and fastest option. Below, I'll quickly show you how to import the model from a bucket and create it in the database.

The first step is to grant the necessary privileges to the schema where you plan to import and create the ONNX model. These privileges must be granted by an admin. In my example, the working schema is WKSP_WSVECTORSEARCH. With the proper permissions in place, you'll be ready to move on to the next steps.

GRANT EXECUTE ON DBMS_CLOUD TO WKSP_WSVECTORSEARCH;
GRANT CREATE MINING MODEL TO WKSP_WSVECTORSEARCH;

The next step is to create a credential that allows the database to connect to your OCI account.

You'll need to specify a credential name (which you'll reference later), along with your OCI username and password (TOKEN) for authentication. To verify that your credential was created successfully or to view existing credentials, you can query the ALL_CREDENTIALS table.

The steps are illustrated below.

And finally, the last step is to create the model in your schema.

The example script below shows how to generate the model. You'll need to provide a model name (used in later steps), the credential you created earlier, and the URI to the file returned as a BLOB from your OCI bucket. Once the procedure is completed successfully, you can view model details by querying the USER_MINING_MODEL_ATTRIBUTES table.

The ONNX model ONNX_MODEL has been successfully created and is now ready to use in your APEX application.

Vector Providers

In your workspace, you can define a Vector Provider by navigating to
Workspace Utilities -> Vector Providers. Vector Providers in APEX handle the conversion of text into embeddings using either an external AI service, a local ONNX model in the database, or a custom PL/SQL function. The APEX_AI public API includes a function to fetch embeddings via a configured Vector Provider.

These providers are defined at the workspace level, making them accessible across all apps in that workspace.

When you select "Generative AI Service" as the type, you can choose from OCI Generative AI, OpenAI, or Cohere as the provider. In the next step, you'll be prompted to add the required credentials to enable the connection.

 
In my example, I'm using a local ONNX model. As shown below, I need to provide a Name and a Static ID (which will be used later) and select the ONNX model I created earlier. Once these details are filled in, I can go ahead and create the Vector Provider.

Tables and Embeddings

For my test, I created two tables with a VECTOR column at the end to store embeddings. One table holds movie information, such as title, description, actors, and category, which closely resembles the data in the APEX sample app that I also recommend checking out. The second table stores documents, including a title and a short summary sentence.

I used AI to generate sample data for both tables. Once the data was imported, I ran a script to build embeddings for each row, as shown in the examples below.

Both scripts are self-explanatory. To build embeddings, I concatenated the relevant columns and used the Vector Provider I created earlier. The resulting VECTOR is then updated for each row. In the end, we have a sample dataset with embeddings generated by the selected provider.

Search Configuration and Search Page

To test the results of my VECTOR search, I created a simple APEX application. Before building a basic "Search Page" using the wizard, I first set up a Search Configuration for my VECTOR_MOVIES table.

The setup is straightforward and only takes a few steps, as shown below.

Start by defining a search name and selecting the search type as Oracle Vector Search.

In the next step, select the Vector Provider and the table that contains the VECTOR-type column. 

If the provider or the table hasn't been created beforehand, this step will be locked. Make sure both are set up before proceeding.

Map the existing columns as needed based on your requirements.

The final step is to define the vector attributes, search type, and distance metric. In my example, I used Exact search with the Cosine distance metric. For more details on the different distance metrics, refer to the Oracle documentation and training resources.

Once everything is configured, you can easily create a simple search page using the APEX wizard. A great feature here is that you can select the Search Configuration you previously created—for example, the one for the movies table.

For the documents table, I created a separate Search Configuration and Page based on its available columns. The main difference was selecting the Approximate search type with 90% accuracy.

Keep in mind: to use this search type, the table must have an index on the VECTOR column. Once the index was created, I was able to proceed with setting up the Search Configuration. 

CREATE VECTOR INDEX doc_vec_idx ON documents (embedding) 
ORGANIZATION INMEMORY NEIGHBOR GRAPH 
DISTANCE COSINE WITH TARGET ACCURACY 90 
PARAMETERS (type HNSW, neighbors 40, efconstruction 500);

With both examples set up, we're now ready to explore the results.

Results

As you can see in the screenshot below, I searched for "Recommend a good romance", and the AI Vector Search returned relevant results as expected. I also tested other prompts, like searching by movie titles or actor names stored in the database, and the results matched impressively well.

Another example comes from my documents table, where I set up a separate search configuration. I tried prompts like "best foods for energy and focus" and "tips for improving public speaking," and the results returned relevant documents as expected.

Conclusion

In conclusion, I recommend exploring AI Vector Search to enhance search capabilities in your applications. It helps retrieve more relevant data from your database by understanding the context behind the query, not just keywords.

I encourage you to experiment with different examples and adapt them to your specific business needs, as search behaviour can vary depending on the type of data and use case. In my opinion, integrating vector search will significantly improve the user experience and accuracy of search in your future applications.

For more information, check out our Oracle APEX Services, and if you liked this blog, check out our other APEX blogs here.

Contact us today, and one of our expert developers will be in touch.