Semantic Search in Oracle AI Database - Part I

Oracle announced the general availability of Oracle AI database 23ai in the year 2024.  Oracle AI Database adds more than 300+ new features available in 23ai, so there is a lot to learn ,one of the most exciting new features of Oracle AI database is AI Vector search.
 
The heart of the AI vector search is the ability to do similarity search. A similarity search works with the semantic representations of your data rather than the value (words or pixels) and finds similar objects quickly. For example, find other images or documents that look like this one.  It uses vectors, or more precisely vector embeddings, to search for semantically similar objects based on their proximity to each other. In other words, vector embeddings are a way of representing almost any kind of data, like text, images, videos, and even music, as points in a multidimensional space where the locations of those points and proximity to other data are semantically meaningful.
 
Traditionally, relational searches have been based on attribute values or keywords. With AI Vector Search, structured and, more importantly, unstructured data can be searched for similarities based on the semantic meaning of the data. This opens up a whole new world of possibilities, which is why there is so much excitement around this new feature. Use cases like chatbots, natural language processing, data analysis, image processing, and geographic analysis can leverage AI Vector Search in new and exciting ways.
 
Oracle AI vector search includes a collection of new features to enable semantic or similarity search within the AI database and these features include
 

• A new vector datatype for storing vector embedding
  
• Importing pre-trained embedding models into oracle AI database
  
• Creating embedding in the database locally or loading the externally generated embeddings
  
• Vector indexes for faster similarity searches
  
• New SQL operators and functions to easily combine similarity searches with relational search on business data
  
• Support for Retrieval augmented generation (RAG) to augment LLM responses with enterprise specific contents.
  

 
Rather than repeat a description of these features, the rest of this post and the future posts we will walk through example of these features along with a description of how these features are used to enable and use similarity search.
 
For the following examples in this post we are going to use the crime incident dataset available on the Chicago data portal. This dataset combines both the category fields along with a text description that we will use to highlight how to do similarity search and how they can be combined to perform relational search with similarity search.
 
Traditional text search works well when the search term exists exactly in the source data. For example, if a user searches for THEFT, a LIKE '%THEFT%' query can return records containing that word.
 
However, real-world data is rarely that consistent. The same concept can be represented using different words. A crime related to larceny might be described as retail theft, property crime, embezzlement, or attempt theft. A normal keyword search does not understand those relationships.
 
Oracle Database vector search solves this problem by converting text into embeddings. An embedding is a numeric representation of the meaning of text. Similar meanings produce vectors that are close to each other, even if they do not use the same words.
 
In this example, I load a large crime-case dataset from OCI Object Storage, generate vector embeddings for every case description, and use semantic search to find records related to the word LARCENY.
 
The source file is a CSV file stored in OCI Object Storage. Instead of downloading the file locally and loading it manually, Autonomous Database can read the file directly from Object Storage using the ORACLE_BIGDATA external-table driver.
 
The following statement creates a relational table named CASE_DETAILS from the CSV file.
 
demo@ADB26AI> create table case_details
  2  as
  3  select *
  4  from external(
  5      ( case_id number
  6      , cast_number varchar2(30)
  7      , case_date date
  8      , case_block varchar2(80)
  9      , case_primary_type varchar2(80)
 10      , case_description varchar2(2000)
 11      , case_loc_description varchar2(100)
 12      , case_domestic varchar2(20)
 13      , case_beat varchar2(60)
 14     )
 15      type oracle_bigdata
 16      access parameters(
 17          com.oracle.bigdata.fileformat = csv
 18          com.oracle.bigdata.credential.name = OCI$RESOURCE_PRINCIPAL
 19          com.oracle.bigdata.csv.skip.header = 1
 20          com.oracle.bigdata.trimspaces = ldrtrim
 21          com.oracle.bigdata.removequotes = true
 22          com.oracle.bigdata.ignoreblanklines = true
 23          com.oracle.bigdata.dateformat = 'mm/dd/yyyy hh12:mi:ss am'
 24             com.oracle.bigdata.conversionerrors = reject_record
 25          )
 26  location ('https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/MY_DEMO_BUCKET/o/DEMO07/Sample_data_for_Vector_Search.csv')
 27  );
 
Table created.
 
The external-table definition includes the structure of the CSV file: case identifier, date, category, description, location, and related attributes.
 
A few access parameters are important here:

• fileformat = csv tells the database that the source is a CSV file.

• skip.header = 1 skips the first row containing column names.

• trimspaces and removequotes clean common CSV formatting issues.

• dateformat tells Oracle how to convert the source date value.

• conversionerrors = reject_record prevents one malformed row from stopping the entire load.

• OCI$RESOURCE_PRINCIPAL uses the database resource principal to access Object Storage, avoiding hard-coded Object Storage credentials in the SQL script.

 
After the table is created, verify that the data was loaded successfully.
 
demo@ADB26AI> select count(*) from case_details;
 
            COUNT(*)
--------------------
           1,427,029
 
The CASE_DESCRIPTION column contains short textual descriptions of the crime. To enable similarity search, we will need to create vector embeddings for the column(s) we would like to search on, which, in our examples, is the CASE_DESCRIPTION column. Vector embeddings are stored in a column of data type VECTOR. A vector embedding is a mathematical vector representation of data points or, more simply, an array of numbers. Vector embeddings are generated using Machine Learning models. This is the first place we see AI come into play.
 
How do you decide which embedding model to use? After all, there are open-source embedding models and proprietary embedding models that you might have to pay for, or you could create and train your own embedding models. To add to the confusion, each embedding model has been trained on specific data. The type of embedding model you use will depend on the type of data that you plan to embed and how well that model performs for the searches you or your application need to perform.
 
Once you decide on one or more embedding models to try, you can choose to create vector embeddings outside the database or inside the database by importing the models directly into Oracle AI Database if they are compatible with the Open Neural Network Exchange (ONNX) standard. Since Oracle AI Database implements an ONNX runtime directly within the database, these imported models can be used to generate vector embeddings in Oracle AI Database.
 
In the following examples, we are going to be operating on text-based data, so we will use a sentence-transformer based model, For our example, we will take the easy path and use the pre-built all-MiniLM-L12-v2 model that was detailed in the blog post about using a pre-built vector embedding model above. It is a compact yet powerful and was built using the sentence-transformers library and will work for our data requirements, Its unzipped into the OCI object storage and loaded into the database.
 
demo@ADB26ai> variable uri varchar2(180)
demo@ADB26ai> exec :uri := 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/idcglquusbz6/b/VECTOR_DEMO/o/';
 
PL/SQL procedure successfully completed.
 
demo@ADB26ai>
demo@ADB26ai> ---
demo@ADB26ai> --- Verify the above object store URI works fine
demo@ADB26ai> ---
demo@ADB26ai>
demo@ADB26ai>
demo@ADB26ai> col object_name for a50
demo@ADB26ai> select object_name, bytes
  2      from dbms_cloud.list_objects(
  3              credential_name =>'OCI$RESOURCE_PRINCIPAL'
  4              , location_uri => :uri);
 
OBJECT_NAME                                             BYTES
-------------------------------------------------- ----------
all_MiniLM_L12_v2.onnx                              133322334
json-relational-duality-developers-guide.pdf          1368027
oracle-ai-database-26ai-new-features-guide.pdf        1102685
 
demo@ADB26ai> ---
demo@ADB26ai> --- Load the ONNX model from object storage to databse
demo@ADB26ai> ---
demo@ADB26ai>
demo@ADB26ai> begin
  2      dbms_vector.load_onnx_model_cloud(
  3              model_name =>'MY_DEMO_MODEL'
  4              , credential => 'OCI$RESOURCE_PRINCIPAL'
  5              , uri => :uri||'all_MiniLM_L12_v2.onnx'
  6      );
  7  end;
  8  /
 
PL/SQL procedure successfully completed.
 
Running the following SQL shows that the model name MY_DEMO_MODEL has been loaded into the database:
 
demo@ADB26ai> ---
demo@ADB26ai> --- Verify the Model got loaded
demo@ADB26ai> ---
demo@ADB26ai>
demo@ADB26ai> col MODEL_NAME for a15
demo@ADB26ai> SELECT MODEL_NAME, MINING_FUNCTION, ALGORITHM, ALGORITHM_TYPE, MODEL_SIZE
  2  FROM USER_MINING_MODELS
  3  WHERE MODEL_NAME = 'MY_DEMO_MODEL';
 
MODEL_NAME      MINING_FUNCTION                ALGORITHM                      ALGORITHM_ MODEL_SIZE
--------------- ------------------------------ ------------------------------ ---------- ----------
MY_DEMO_MODEL   EMBEDDING                      ONNX                           NATIVE      133322334
 
demo@ADB26ai>
 
We can also display the model’s characteristics:
 
demo@ADB26ai> col VECTOR_INFO for a25
demo@ADB26ai> SELECT MODEL_NAME, ATTRIBUTE_NAME, ATTRIBUTE_TYPE, DATA_TYPE, VECTOR_INFO
  2  FROM USER_MINING_MODEL_ATTRIBUTES
  3  WHERE MODEL_NAME = 'MY_DEMO_MODEL';
 
MODEL_NAME      ATTRIBUTE_NAME       ATTRIBUTE_TY DATA_TYPE       VECTOR_INFO
--------------- -------------------- ------------ --------------- -------------------------
MY_DEMO_MODEL   ORA$ONNXTARGET       VECTOR       VECTOR          VECTOR(384,FLOAT32)
MY_DEMO_MODEL   DATA                 TEXT         VARCHAR2
 
 
Notice the VECTOR_INFO column. This shows the dimension format for the model. Dimension formats will be explained further in the section Creating Vector Embeddings.
We can test what we have done so far by running a simple query to embed the word ‘hello’:
 
 
demo@ADB26ai>
demo@ADB26AI> col embedding for a80 trunc
demo@ADB26AI> select to_vector(vector_embedding(MY_DEMO_MODEL using 'Hello' as data)) as embedding;
 
EMBEDDING
--------------------------------------------------------------------------------
[-7.49069899E-002,-1.44330589E-002,4.86498661E-002,-2.71380767E-002,-4.30882126E
 
Now we are ready to create vector embeddings for the CASE_DESCRIPTION column, First, we need to add a column to our table to store the vector embeddings. The column will be of data type VECTOR. You can optionally specify the number of dimensions and their format
 
demo@ADB26AI> create table vector_search_demo
  2  as
  3  select a.*,
  4     vector_embedding(MY_DEMO_MODEL using case_description as data ) case_embeddings
  5* from case_details a;
 
Table VECTOR_SEARCH_DEMO created.
 
The VECTOR_EMBEDDING function sends the value of CASE_DESCRIPTION to MY_DEMO_MODEL and returns a vector representation of that text.
 
For example, descriptions such as:
RETAIL THEFT
ATTEMPT THEFT
EMBEZZLEMENT
OTHER CRIME INVOLVING PROPERTY
 
will be stored as vectors. The vectors are not manually interpreted; they are used by the database to calculate semantic similarity.
 
The resulting table contains:
·         The original case information
·         The text in CASE_DESCRIPTION
·         The generated vector in CASE_EMBEDDINGS
 
For a dataset containing more than one million rows, embedding generation is a substantial operation. In a production system, this would normally be managed through a batch pipeline or incremental process that only generates embeddings for new or changed records.
 
Now search for the word LARCENY using a normal SQL predicate.
 
demo@ADB26AI> select * from vector_search_demo where upper(case_description) like '%LARCENY%';
 
no rows selected
 
There are no records that contain the literal word LARCENY. This does not mean the dataset contains no larceny-related records. It only means the source system classified or described those records differently. This is a common limitation of keyword search. The query only knows about matching characters, not matching meaning.
 
To perform semantic search, Oracle first generates an embedding for the search term LARCENY.
It then compares that query embedding to every stored value in CASE_EMBEDDINGS.
 
demo@ADB26AI> select case_id,case_description,vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd
  2  from vector_search_demo
  3  order by vd
  4* fetch exact first 10 rows only ;
 
The VECTOR_DISTANCE function calculates the distance between two vectors:

1. The stored embedding for a case description.
2. The embedding generated for the search term LARCENY. 

This example uses cosine distance. A lower distance means the records are semantically closer to the search phrase.
 
The top results are:
 
 
    CASE_ID CASE_DESCRIPTION                  VD
___________ _________________________________ ______________________
   12707163 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   13069849 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   13111350 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12706126 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12699911 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12702212 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12702566 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12705023 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12705511 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
   12706198 OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
 
10 rows selected.
 
Although none of these descriptions uses the word LARCENY, the embedding model identifies other crime involving property as closely related to the meaning of larceny.
 
The previous query returns individual case records. To understand the different types of results, retrieve distinct combinations of CASE_PRIMARY_TYPE and CASE_DESCRIPTION.
 
 
demo@ADB26AI> select case_primary_type , case_description
  2  from (
  3  select case_primary_type
  4     , case_description
  5     , vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using 'LARCENY' as data ) , cosine ) vd
  6  from vector_search_demo
  7  order by vd
  8  fetch exact first 50000 rows only
  9     )
 10  group by case_primary_type , case_description ,vd
 11  order by vd
 12* fetch first 15 rows only;
 
CASE_PRIMARY_TYPE         CASE_DESCRIPTION
_________________________ _________________________________
OTHER OFFENSE             OTHER CRIME INVOLVING PROPERTY
OTHER OFFENSE             OTHER CRIME AGAINST PERSON
THEFT                     ATTEMPT THEFT
INTIMIDATION              EXTORTION
PUBLIC PEACE VIOLATION    RECKLESS CONDUCT
DECEPTIVE PRACTICE        EMBEZZLEMENT
CRIMINAL DAMAGE           CRIMINAL DEFACEMENT
SEX OFFENSE               CRIMINAL SEXUAL ABUSE
PROSTITUTION              OTHER PROSTITUTION OFFENSE
DECEPTIVE PRACTICE        FORGERY
HOMICIDE                  RECKLESS HOMICIDE
OTHER OFFENSE             COMPOUNDING A CRIME
SEX OFFENSE               ATTEMPT CRIMINAL SEXUAL ABUSE
THEFT                     RETAIL THEFT
 
14 rows selected.
 
The important results are:
 
ATTEMPT THEFT
RETAIL THEFT
EMBEZZLEMENT
OTHER CRIME INVOLVING PROPERTY
 
These descriptions are not exact matches for LARCENY, but they are conceptually related. This is the key benefit of semantic search.
 
A normal keyword search answers the question: Which records contain the word LARCENY?
The result in this dataset is:
 
demo@ADB26AI> select * from vector_search_demo where upper(case_description) like '%LARCENY%';
 
no rows selected
 
A vector search answers a different question: Which records are most similar in meaning to LARCENY? That search returns records related to theft, property crimes, fraud, and similar offense descriptions.
 
This is useful when:

• The source data has inconsistent terminology.
• Different users describe the same concept differently.
• The user does not know the exact wording used in the database.
• The data includes business descriptions, support tickets, legal cases, product documentation, or customer comments.
• A search application needs to understand intent rather than only keywords.

 
This example creates embeddings using only CASE_DESCRIPTION. That is sufficient for a basic demonstration, but a production implementation may generate embeddings from several business fields together.
 
For example:
case_primary_type || ' - ' || case_description
 
This can give the model more context by including both the broad crime category and the specific description.
 
The search can also be combined with traditional SQL filters. For example, a user could perform semantic search for LARCENY while limiting results to a date range, neighborhood, location type, or specific crime category.
 
demo@ADB26AI> select case_id,
  2         case_date,
  3         case_primary_type,
  4         case_description,
  5         vector_distance(
  6           case_embeddings,
  7           vector_embedding(
  8             MY_DEMO_MODEL using 'LARCENY' as data
  9           ),
 10           cosine
 11         ) as distance
 12  from vector_search_demo
 13  where case_date >= date '2024-01-01'
 14    and case_primary_type = 'THEFT'
 15  order by distance
 16* fetch first 5 rows only;
 
    CASE_ID CASE_DATE      CASE_PRIMARY_TYPE    CASE_DESCRIPTION    DISTANCE
___________ ______________ ____________________ ___________________ ______________________
   14090434 22-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593
   14083242 14-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593
   14086550 17-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593
   14086324 17-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593
   14088859 20-JAN-2026    THEFT                ATTEMPT THEFT       0.39880493859729593
 
demo@ADB26AI>
 
This hybrid approach combines the strength of relational filtering with semantic ranking.
 
Oracle AI Database vector search makes it possible to search based on meaning instead of exact words.
 
In this example:

• A CSV file was loaded from OCI Object Storage.
• More than 1.4 million case records were stored in Autonomous Database.
• Embeddings were generated for every case description.
• A keyword search for LARCENY returned no rows.
• A vector search found semantically related records such as attempt theft, retail theft, embezzlement, and other crime involving property.

 
This is a useful pattern for any application where users search with natural language but the underlying data uses different terminology.