Next-Gen Data Search: Integrating AI Vector Search into Search Configurations

Oracle Database 23ai introduces a game-changing feature: Oracle AI Vector Search. This new capability enables you to store vector embeddings alongside your business data, opening the door to more advanced and semantic queries. By leveraging embedding models, you can transform unstructured data into vector embeddings that enhance your ability to extract meaningful insights and connections from your data. To learn more about Vector Embeddings and Embedding models, check out this link. With APEX 24.2, we have now included AI Vector Search Support in Search Configurations.

Let’s explore how this innovative feature works and its seamless integration with Oracle APEX. We will also learn more about Vector Providers, PL/SQL APIs, etc., which we will leverage to create a Search Configuration.

In this blog post, we will explore how to use Oracle AI Vector Search natively with Oracle APEX

We will understand how to:

1. Add a Vector Column to your Table
2. Create a Vector Provider
3. Retrieving the Vector Embeddings from a Vector Provider
4. Create a Search Configuration with a Search Type of Oracle Vector Search
5. Create an Oracle AI Vector Search Page

1. Add a Vector Column to the Database Table

What is AI Vector Search?  
AI Vector search uses advanced embedding models to represent data as mathematical vectors. Unlike traditional text searches, vector search allows for semantic queries, identifying similarities across various data types. Whether it’s face recognition, finding related images, or conducting advanced text searches, vector embeddings make it possible to retrieve highly relevant results.

VECTOR Data Type

The VECTOR data type is introduced with the release of Oracle Database 23ai, providing the foundation to store vector embeddings alongside business data in the database. Using embedding models, you can transform unstructured data into vector embeddings that can then be used for semantic queries on business data.

See the following basic example of using the VECTOR data type in a table definition:

CREATE TABLE docs (doc_id INT, doc_text CLOB, doc_vector VECTOR);

2. What is a Vector Provider

A Vector Provider is the key to converting text into embeddings. This transformation can be performed through various means, including:

• Local Database ONNX Models: Run directly within the database for efficient processing, with the added benefit of keeping data within the database for enhanced privacy.
• Generative AI Service: AI services that generate embeddings.
• User-Defined Custom PL/SQL Functions: Custom PL/SQL function tailored to your specific needs.

Vector Providers are workspace-level resources, making them accessible across all applications within the same workspace. They ensure consistency and reusability while maintaining seamless integration. 

Exporting and Importing Vector Providers

Oracle APEX simplifies managing Vector Providers across different environments. When you export an application, any associated Vector Providers are included in the export file. Upon importing the application into another workspace, Oracle APEX checks whether a Vector Provider with the same static ID already exists. If it does, the application uses the existing provider. Otherwise, the Vector Provider is automatically created in the target workspace, ensuring a smooth and efficient migration process.

Create a Vector Provider

1. Navigate to Workspace Utilities via App Builder on the Oracle APEX Workspace home page. Then, click on Vector Providers, which is introduced with Oracle APEX 24.2.
2. Click Create to start and then enter a unique name and Static ID for the Vector Provider. The Static ID must be unique and will be referenced when using the service with the APEX_AI package, APEX_AI.GET_VECTOR_EMBEDDINGS.
3. Select a Provider Type:  
   • Database ONNX Model:
     • Specify ONNX Model Owner (choose from schemas assigned to your workspace).
     • Select the ONNX Model Name (the model to generate embeddings).
   • Generative AI Service: Select AI Provider. Options include OCI, OpenAI, or Cohere. APEX will automatically adjust the interface upon selection.
   • Custom PL/SQL: Enter the Custom Function Name (a PL/SQL function that converts input into embeddings and returns a VECTOR data type).
4. Click Create to save.

Note: If you are using the Database ONNX Model, please make sure you have already imported those ONNX models directly into the Schema(s) assigned to the workspace. To learn more about how to load the ONNX Model, please take a look at this Blogpost.

3. Retrieving the Vector Embeddings from a Vector Provider

Oracle AI Vector Search uses vector embeddings – mathematical representations that capture the meaning behind data like text, images, and audio. Unlike keyword searches, which match exact words, vector search focuses on context and meaning, delivering more accurate results.

It maps data into a multidimensional space, where similar concepts are closer together, reflecting their semantic relationships. This approach makes searches more innovative and more relevant across different types of content.

Before setting up Search Configurations in APEX, it is recommended that the data be converted into vector embeddings and stored in a Vector Data Type column. Starting with APEX 24.2, a new PL/SQL API, APEX_AI.GET_VECTOR_EMBEDDINGS is available with multiple signatures to simplify this process. You can also use this API to Insert or update vector data.

Example:

Example 1:

SELECT

APEX_AI.GET_VECTOR_EMBEDDINGS(

P_VALUE => 'What is Oracle APEX',

P_SERVICE_STATIC_ID => 'onnx_model'

) AS VECTOR_EMBEDDING;

Example 2:

UPDATE EBA_PROJECT_MILESTONES

SET

VECTOR_COLUMN = APEX_AI.GET_VECTOR_EMBEDDINGS(

P_VALUE =>  NAME 

|| ' '

|| DESCRIPTION,

P_SERVICE_STATIC_ID => 'onnx_model'

);

In the above examples, p_service_static_id refers to the Vector Provider Static ID we created in the previous section.

4. Create a Search Configuration with a Search Type of Oracle Vector Search

An Oracle AI Vector Search configuration requires Oracle Database 23ai or later. Before you can create an Oracle AI Vector Search configuration, you’ll need to complete all the steps mentioned above.

Here is how to create an Oracle Vector Search configuration:  

1. Navigate to the Workspace home page, click App Builder, and select your application. Open Shared Components and click Search Configurations under Navigation and Search.  
2. Click Create. Name your configuration (e.g., Search Movies – Vector), choose Oracle Vector Search as the type, and click Next.  
3. Set up the source by selecting a Vector Provider to generate embeddings (e.g., ONNX Model). Pick the source type (Table or SQL Query) and choose the table owner and table name containing the vector column (e.g., POPULAR_MOVIES). Click Next.  
4. Map the columns. To create a Search Type as Oracle Vector Search, we need to map the Vector Attributes along with Column Mapping.
5. Primary Key Column: Select the key column (e.g., ID).  
   • Vector Column: Select the vector data column (e.g., OVERVIEW_V).  
   • Title Column: Choose the title column (e.g., TITLE).  
   • Description Column: Pick the description column (e.g., OVERVIEW).  
   • Icon Source: Set how icons appear, such as using initials.  
6. Click Create Search Configuration to save it.

Customizing Vector Attributes

After creating the Vector Search Configuration, you can further refine it by customizing the vector attributes to suit your specific requirements.  

For instance, under Vector Attributes, you can:  

• Set the Search Type to either Exact or Approximate, depending on the precision needed.
• Choose the Distance Metric that best fits your use case, such as Cosine, Dot, Euclidean, Euclidean Squared, Hamming, or Manhattan.  

Additionally, under Icon and Display, you can configure:  

• Maximum Vector Distance to define the allowable similarity threshold.  
• Maximum Rows to Return to control the number of results displayed.  

These adjustments help tailor the search behavior and results presentation to align perfectly with your application’s needs.

5. Creating an Oracle Vector Search Page

The Create Page Wizard helps you set up a search feature effortlessly. It creates a search field and allows you to select search configurations during setup. You can also add more search configurations later in Page Designer if needed.  

A Search Page includes a search field and a Search Results region. In Page Designer, the Search Results region uses Search Sources that map directly to the search configurations defined in Shared Components. This ensures the search functionality is well-integrated and easy to manage.

Check out this link to learn more about how to Create a Search Page in Oracle APEX.
 

Enhanced Search Flexibility with APEX_SEARCH.SEARCH API for Oracle Vector Search

In addition to the exciting features of Oracle Vector Search within Search Configurations, the APEX_SEARCH.SEARCH PL/SQL API now supports Oracle AI Vector Search in Search Configurations. Developers can use multiple Search Configuration Static IDs for combined searches across various configurations and technologies. This enables the developer to use Search Configuration anywhere, like in the Cards region. With business data at your fingertips, you can effortlessly perform vector searches while integrating various sources.

Here’s an example of how to use it:  

SELECT *  
    FROM TABLE( apex_search.search(  
                  p_search_static_ids => apex_t_varchar2(‘vector_search_movies_db’, ‘character_search’),  
                  p_search_expression => :P4_SEARCH,  
                  p_apply_order_bys   => ‘N’));  

This flexibility enhances the versatility of search functionalities in your APEX applications.

Oracle APEX 24.2 introduces Oracle AI Vector Search in Search Configurations, enabling advanced semantic search capabilities. By integrating with the Oracle AI Vector Search feature of Oracle Database 23ai, you can create Search Configurations that leverage vector embeddings for more accurate and context-aware results. This feature allows for seamless customization and integration, making it easier to implement powerful search functionalities in APEX applications.