On the Eighth Day of 23ai, Viscosity Gave To Me…

Unlock the Power of Oracle 23ai Vector Search

Oracle is equipping developers with cutting-edge tools to explore the fascinating world of AI, and Oracle 23ai is no exception. Packed with features like support for large language models (LLMs), modern data types, and vector databases, it’s revolutionizing workflows—starting with search!

Let’s dive into how Oracle 23ai’s vector database can transform your search experience, allowing you to seamlessly search structured and unstructured data based on semantics or meaning.

Users rely on exact matches in traditional keyword searches (e.g., Oracle APEX grids). But what if you could enable semantic search, where users explore data based on intent or context? It’s like speaking naturally to your database—enabling more human-centric and meaningful interactions with your data.

How to Get Started

Setting up vector database search in Oracle 23ai is straightforward. Here’s an overview:

1. Install Oracle 23ai
   Start with the free version in the cloud or on-premise.
2. Create a Database User
   Connect as System and set up a dedicated user to manage your models and data.
   
   

   GRANT DB_DEVELOPER_ROLE TO DEMO_USER identified by password123;

    

   GRANT create mining model TO AI_DEMO;

3. Load an Embedding Model
   Use a pre-built ONNX file for the embedding model provided by Oracle or upload your custom model.
   
   

   CREATE OR REPLACE DIRECTORY DM_DUMP as 'ONNX directory path>';

   GRANT WRITE ON DIRECTORY dm_dump TO DEMO_USER;

   GRANT READ ON DIRECTORY dm_dump TO DEMO_USER

    

   exec DBMS_VECTOR.DROP_ONNX_MODEL(model_name => 'DOC_MODEL', force => true);

    

   EXECUTE DBMS_VECTOR.LOAD_ONNX_MODEL(

   'DM_DUMP',

   'embedding_model.onyx',

   'doc_model');

4. Vectorize Your Data
   For this example, we’ll vectorize a job listings table with fields like job title, required skills, location, and description.
   
   

      CREATE TABLE JOB_LISTINGS_VECTOR(

          JOB_LISTINGS_VECTOR_ID     NUMBER GENERATED BY DEFAULT AS IDENTITY,

          JOB_TITLE                   VARCHAR2(512),

          REQUIRED_SKILLS             VARCHAR2(512),

          LOCATION                   VARCHAR2(512),

          DESCRIPTION                 VARCHAR2(2000),

          VEC                         VECTOR,

          CONSTRAINT JOB_LISTINGS_VECTOR_PK PRIMARY KEY(JOB_LISTINGS_VECTOR_ID)

      );

     

      INSERT INTO JOB_LISTINGS_VECTOR        (JOB_TITLE,REQUIRED_SKILLS,LOCATION,DESCRIPTION,VEC)

      SELECT

          JOB_TITLE,

          REQUIRED_SKILLS,

          LOCATION,

          DESCRIPTION,

          TO_VECTOR(VECTOR_EMBEDDING(DOC_MODEL USING JOB_TITLE AS DATA, REQUIRED_SKILLS AS DATA1, LOCATION AS DATA2, DESCRIPTION AS DATA3))

      FROM JOB_LISTINGS_DUMP;

5. Perform a Similarity Search
   Use the vector database to compare query vectors and retrieve the top matches based on proximity, rather than exact distance.

Let’s say a user is looking for jobs related to “Leadership and Management.”

Traditional keyword-based search limits results to exact matches or predefined synonyms. However, vector search unlocks a new level of intelligence by understanding the semantic meaning of the query.

Instead of rigid word matching, the vector database retrieves jobs conceptually related to leadership and management—even if those exact terms don’t appear in the data.

SELECT job_listings_vector_id,job_title,required_skills,location,description

FROM job_listings_vector

order by vector_distance(vec, TO_VECTOR(VECTOR_EMBEDDING(doc_model USING 'Leadership and Management' as data)), COSINE)

Semantic Search in Action

Imagine integrating this into an Oracle APEX component: users can search for jobs by meaning—like “roles requiring AI skills”—instead of exact keywords. The result? A smoother, more intuitive search experience.

For this example, we’ve paired Smart Filters with the Cards Region in Oracle APEX to showcase job listings. The magic lies in the SQL function that powers the data source, retrieving results based on vector distance.

Query = “I want to work with machine learning and artificial intelligence.”

Search = “I'm interested in marketing and content creation roles.”

With Oracle 23ai, semantic search powered by vector databases brings a revolutionary shift to how we interact with data. Moving beyond exact keyword matches, it enables context-driven, intuitive search experiences that cater to the user’s intent.

By integrating these advanced capabilities into your applications—like in Oracle APEX—you can unlock smarter, faster, and more human-centric data retrieval. Whether you're navigating job listings, exploring content, or analyzing complex datasets, Oracle 23ai is your gateway to a future where AI and data work seamlessly together.

Start your journey with Oracle 23ai today, and transform the way your users discover and interact with information!

Happy Holidays! 🎄