Semantic Search in Oracle AI Database - Part I

 

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 perform semantic search, each description needs to be converted into a vector.

 

Next, create a new table that contains all source data along with an embedding column.

 

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.