Semantic Search in Oracle AI Database - Part III

This is the third post in our series on Oracle AI Vector Search and builds on the examples and data used in the two previous posts. The first was getting started with AI Vector search, the second was using HNSW vector index. In those posts we explored how AI Vector search allows you to find the similar content based on semantic representation or meaning rather than the unstructured data raw values
 
In the first post, we performed exhaustive similarity searches, comparing every item in the dataset to find the closest matches. In contrast, the second post used an In-Memory Neighbor Graph (HNSW) vector index to perform approximate similarity searches, which we found to be much faster than an exhaustive similarity search. Although very fast, an HNSW vector index is an in-memory only index and can require a lot of memory for large datasets. In practice, too much memory for some very large datasets.
 
In this post, we will explore how to use an Inverted File Flat (IVF) partition-based index, which is a type of Neighbor Partition vector index. This is a storage-based index as opposed to the HNSW vector index, which is an in-memory index. The IVF vector index also uses a different search technique than an HNSW index. It is designed to enhance search efficiency by narrowing the search area through the use of neighbor partitions or clusters.
 
The dataset size determines the number of partitions in the index. The center of each partition, or the centroid, represents the average vector for each partition. It is also called the center of gravity of the partition. By default, the number of partitions and their corresponding centroids is set to the square root of the dataset’s size. However, this can be adjusted by specifying the NEIGHBOR PARTITIONS parameter during index creation. The centroids conceptually partition the vector space.
 
For a query vector, the search algorithm identifies the number of nearest centroids, where the default number is the square root of the total number of centroids. This can be adjusted by setting the NEIGHBOR PARTITION PROBES parameter. The higher the number of nearest centroids, the more accurate the search, but that accuracy comes at a cost. The more centroids selected, the more work that needs to be performed. Once the number of partitions is determined, they are thoroughly scanned for the top nearest vectors to the vector specified by the query.
 
More information about how IVF indexes work are available in the docs.
 
To create IVF indexes on CASE_EMBEDDINGS vector column in the table VECTOR_SEARCH_DEMO ,the following statement can be used.

 

demo@ADB26AI> create vector index ivf_demo
  2  on VECTOR_SEARCH_DEMO( CASE_EMBEDDINGS )
  3  organization neighbor partitions
  4* distance cosine ; 

Vector INDEX created.

 

  

This statement will create a global partitioned IVF vector index and will result in the creation of two internal tables:
 
demo@ADB26AI> select table_name
  2  from user_tables
  3* where table_name like 'VECTOR$IVF_DEMO%';
 
TABLE_NAME
_________________________________________________________________
VECTOR$IVF_DEMO$4040231_4231331_0$IVF_FLAT_CENTROIDS
VECTOR$IVF_DEMO$4040231_4231331_0$IVF_FLAT_CENTROID_PARTITIONS
 
These tables are of the format:
 

• VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROIDS, containing the list of identified centroid vectors and associated ids.

• VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROID_PARTITIONS, which is list-partitioned on the centroid ids. Each partition contains the base table vectors closely related (cluster) to the corresponding centroid id for that partition.

 
IVF indexes do need some memory, either from the vector pool if it has been defined or the shared pool, to speed up index creation and during DML operations on the base table. Let’s query the Vector Pool and see how much memory our new index uses.

Details of the pool can be displayed by querying the view V$VECTOR_MEMORY_POOL:
 
demo@ADB26AI> select * from v$vector_memory_pool;
 
POOL            ALLOC_BYTES    USED_BYTES POPULATE_STATUS       CON_ID
____________ ______________ _____________ __________________ _________
1MB POOL           70479593      76546048 OUT OF MEMORY             37
64KB POOL          17619898       1310720 DONE                      37
 
This is far less than our HNSW vector index used in the Using HNSW Vector Indexes post since the index itself is not stored in memory. In fact, if we look at the memory usage of the index as we did in the previous post using the view V$VECTOR_INDEX, we see the following:
 
demo@ADB26AI> select index_name, index_organization, allocated_bytes
  2    , used_bytes, num_vectors, index_dimensions
  3  from v$vector_index
  4* where index_name ='IVF_DEMO';
 
INDEX_NAME    INDEX_ORGANIZATION        ALLOCATED_BYTES    USED_BYTES    NUM_VECTORS    INDEX_DIMENSIONS
_____________ ______________________ __________________ _____________ ______________ ___________________
IVF_DEMO      NEIGHBOR PARTITIONS               2097152         80768        1427029                 384
 
Now we can run the same query that was used in the Using HNSW Vector Indexes post with the APPROX keyword and a target accuracy that matches the index we created:
 
demo@ADB26AI> select case_primary_type , case_description , vd
  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 t
  7     order by vd
  8     fetch approx first 10 rows only with target accuracy 90 )
  9  group by case_primary_type , case_description ,vd
 10  order by vd
 11* fetch first 15 rows only;
 
CASE_PRIMARY_TYPE    CASE_DESCRIPTION                  VD
____________________ _________________________________ ______________________
OTHER OFFENSE        OTHER CRIME INVOLVING PROPERTY    0.38452393962513975
 
Note that this is an approximate similarity search because we added the APPROX keyword and will use the IVF index we just created. Below is the execution plan:
 
demo@ADB26AI> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
---------------------------------------
SQL_ID  2p9222n5fnzg7, child number 1
-------------------------------------
select case_primary_type , case_description , vd    from (    select
case_primary_type    , case_description    ,
vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using
'LARCENY' as data ) , cosine ) vd    from vector_search_demo t    order
by vd    fetch approx first 10 rows only with target accuracy 90 )
group by case_primary_type , case_description ,vd order by vd fetch
first 15 rows only
 
Plan hash value: 1724078458
 
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                  | Name                                                           | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |                                                                |      1 |        |      1 |00:00:00.32 |    4977 |      3 |
|   1 |  RESULT CACHE                              | d2jdkwfk05fmrfw35q2m0zwf4c                                     |      1 |        |      1 |00:00:00.32 |    4977 |      3 |
|*  2 |   COUNT STOPKEY                            |                                                                |      1 |        |      1 |00:00:00.32 |    4977 |      3 |
|   3 |    VIEW                                    |                                                                |      1 |     10 |      1 |00:00:00.32 |    4977 |      3 |
|*  4 |     SORT GROUP BY STOPKEY                  |                                                                |      1 |     10 |      1 |00:00:00.32 |    4977 |      3 |
|   5 |      VIEW                                  |                                                                |      1 |     10 |     10 |00:00:00.32 |    4977 |      3 |
|   6 |       VIEW                                 |                                                                |      1 |     10 |     10 |00:00:00.32 |    4977 |      3 |
|   7 |        NESTED LOOPS                        |                                                                |      1 |     10 |     10 |00:00:00.32 |    4977 |      3 |
|   8 |         VIEW                               | VW_IVPSR_F17F1C9B                                              |      1 |     10 |     10 |00:00:00.32 |    4962 |      3 |
|*  9 |          COUNT STOPKEY                     |                                                                |      1 |        |     10 |00:00:00.32 |    4962 |      3 |
|  10 |           VIEW                             | VW_IVPSJ_578B79F1                                              |      1 |    169K|     10 |00:00:00.32 |    4962 |      3 |
|* 11 |            SORT ORDER BY STOPKEY           |                                                                |      1 |    169K|     10 |00:00:00.32 |    4962 |      3 |
|* 12 |             HASH JOIN                      |                                                                |      1 |    169K|   1603 |00:00:00.32 |    4962 |      3 |
|  13 |              PART JOIN FILTER CREATE       | :BF0000                                                        |      1 |      7 |      7 |00:00:00.22 |      22 |      0 |
|  14 |               VIEW                         | VW_IVCR_B5B87E67                                               |      1 |      7 |      7 |00:00:00.22 |      22 |      0 |
|* 15 |                COUNT STOPKEY               |                                                                |      1 |        |      7 |00:00:00.22 |      22 |      0 |
|  16 |                 VIEW                       | VW_IVCN_9A1D2119                                               |      1 |     59 |      7 |00:00:00.22 |      22 |      0 |
|* 17 |                  SORT ORDER BY STOPKEY     |                                                                |      1 |     59 |      7 |00:00:00.22 |      22 |      0 |
|  18 |                   TABLE ACCESS STORAGE FULL| VECTOR$IVF_DEMO$4040231_4271572_0$IVF_FLAT_CENTROIDS           |      1 |     59 |     59 |00:00:00.01 |      22 |      0 |
|  19 |              PARTITION LIST JOIN-FILTER    |                                                                |      1 |   1427K|   1603 |00:00:00.10 |    4940 |      3 |
|  20 |               TABLE ACCESS STORAGE FULL    | VECTOR$IVF_DEMO$4040231_4271572_0$IVF_FLAT_CENTROID_PARTITIONS |      7 |   1427K|   1603 |00:00:00.10 |    4940 |      3 |
|  21 |         TABLE ACCESS BY USER ROWID         | VECTOR_SEARCH_DEMO                                             |     10 |      1 |     10 |00:00:00.01 |      15 |      0 |
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
 
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   9 - filter(ROWNUM<=10)
  11 - filter(ROWNUM<=10)
  12 - access("VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID")
  15 - filter(ROWNUM<=7)
  17 - filter(ROWNUM<=7)
 
You may notice that we do not see a VECTOR INDEX IVF SCAN access path. In the Using HNSW Vector Indexes post, we saw a VECTOR INDEX HNSW SCAN access path. With an IVF index, we see TABLE ACCESS FULL scans of the two internal tables that were created as part of the IVF index creation. See the description of the internal tables in the IVF Vector Index Creation section above.

Semantic Search in Oracle AI Database - Part II

In the previous blog post we explored how AI vector search allows you to find similar content based on semantic representation or meaning rather than the actual (raw) values. In that examples we performed exhaustive similarity searches, comparing each item in the dataset to find the closest matches, while this approach helps guarantee accuracy, it doesn’t scale well. (think of searching thorough millions or billions of vectors one by one!)
 
This is where the vector index comes in, instead of checking every possible matches, an approximate similarity search uses a class of algorithms referred to as Approximate nearest neighbour. Using vector indexes helps to reduce the number of distance calculations, making searches faster and more efficient in accuracy
 
The example we used from the previous blog post performed exhaustive similarity searches, An exhaustive search to find the closest match for a given query vector are accurate, but can be slow , since the vector distance computation are needed for all vectors in that column.
 
demo@ADB26AI> select case_primary_type , case_description , vd
  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 t
  7      order by vd
  8      fetch exact first 500 rows only)
  9  group by case_primary_type , case_description ,vd
 10  order by vd
 11  fetch first 15 rows only;
 
CASE_PRIMARY_TYPE         CASE_DESCRIPTION                                      VD
------------------------- --------------------------------------------- ----------
OTHER OFFENSE             OTHER CRIME INVOLVING PROPERTY                3.845E-001
 
And the execution plan is the following
 
demo@ADB26AI> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------
SQL_ID  53rn6381kurk1, child number 0
-------------------------------------
select case_primary_type , case_description , vd     from (     select
case_primary_type     , case_description     ,
vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using
'LARCENY' as data ) , cosine ) vd     from vector_search_demo t
order by vd     fetch exact first 500 rows only) group by
case_primary_type , case_description ,vd order by vd fetch first 15
rows only
 
Plan hash value: 1825924980
 
---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                    |      1 |        |      1 |00:00:00.78 |   27455 |  27453 |
|   1 |  RESULT CACHE                     | 6msm9kz3v36qg9v5yj4|      1 |        |      1 |00:00:00.78 |   27455 |  27453 |
|*  2 |   COUNT STOPKEY                   |                    |      1 |        |      1 |00:00:00.78 |   27455 |  27453 |
|   3 |    VIEW                           |                    |      1 |    500 |      1 |00:00:00.78 |   27455 |  27453 |
|*  4 |     SORT GROUP BY STOPKEY         |                    |      1 |    500 |      1 |00:00:00.78 |   27455 |  27453 |
|   5 |      VIEW                         |                    |      1 |    500 |    500 |00:00:00.78 |   27455 |  27453 |
|*  6 |       COUNT STOPKEY               |                    |      1 |        |    500 |00:00:00.78 |   27455 |  27453 |
|   7 |        VIEW                       |                    |      1 |   1427K|    500 |00:00:00.78 |   27455 |  27453 |
|*  8 |         SORT ORDER BY STOPKEY     |                    |      1 |   1427K|    500 |00:00:00.78 |   27455 |  27453 |
|   9 |          TABLE ACCESS STORAGE FULL| VECTOR_SEARCH_DEMO |      1 |   1427K|   1427K|00:00:00.43 |   27455 |  27453 |
---------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   6 - filter(ROWNUM<=500)
   8 - filter(ROWNUM<=500)
 
 
Since we have no indexes defined, we see a full table scan access to the target table (id : 9  - TABLE ACCESS STORAGE FULL)
 
There are currently three types of vector indexes available in AI Vector search, and one among them is in-memory neighbour graph vector index also called as in-memory Hierarchical Navigable small world (HNSW). It is a type of in-memory Neighbour graph vector index , where the vertices represent vectors and the edges between the vertices represent similarity. This is an in-memory only index, this type of index is highly efficient for both accuracy and speed. 

 When HNSW indexes are used, we must need to enable a new memory area in the database called the vector pool , the vector pool is a memory allocated from System global area (SGA) to store HNSW type vector indexes and their associated metadata.
 
There is a new database init parameter called vector_memory_size that specifies the size of the vector pool and enabled HNSW vector index creation.
 
demo@ADB26AI> show parameter vector_memory_size
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- -----------
vector_memory_size                   big integer 83383634
 
Details of the vector pool can be found by querying the V$VECTOR_MEMORY_POOL
 
demo@ADB26AI> select *
  2  from v$vector_memory_pool;
 
POOL                       ALLOC_BYTES USED_BYTES POPULATE_STATUS                CON_ID
-------------------------- ----------- ---------- -------------------------- ----------
1MB POOL                      58368543   62914560 OUT OF MEMORY                      37
64KB POOL                     22930499    2359296 DONE                               37
 
The 64KB pool is mainly for index metadata, where as the 1MB pool is mainly for the vector contents.
 
How do we know how big to make the Vector pool for the HNSW index creation ? this is where we have DBMS_VECTOR package with a method called INDEX_VECTOR_MEMORY_ADVISOR that can help to evaluate the number of indexes that can fit for a simulated vector memory size.
 
demo@ADB26AI> variable r clob
demo@ADB26AI> begin
  2     dbms_vector.index_vector_memory_advisor(
  3             table_owner => user
  4             , table_name => 'VECTOR_SEARCH_DEMO'
  5             , column_name => 'CASE_EMBEDDINGS'
  6             , index_type => 'HNSW'
  7             , response_json => :r );
  8  end;
  9  /
Using default accuracy: 90%
Suggested vector memory pool size: 3 GB
 
This suggests that we will need approximately 3GB of memory for this index in our vector memory pool.  Since this demo is from ADBS instance, vector memory pool is limited to 70% of the SGA size (that is upto 9GB of memory can be used by vector memory pool )
 
demo@ADB26AI> show parameter sga_target
 
NAME           TYPE        VALUE
-------------- ----------- --------
sga_target     big integer 13600M
 
To create a vector index on the CASE_EMBEDDINGS column of the VECTOR_SEARCH_DEMO table, we use this following DDL
 
demo@ADB26AI> create vector index HNSW_demo
  2  on VECTOR_SEARCH_DEMO( CASE_EMBEDDINGS )
  3  organization inmemory neighbor graph
  4  distance cosine
  5  with target accuracy 90;
 
Index created.
 
This statement will create a vector index of HNSW type with a target accuracy of 90%
 
We can verify the memory usage of vector index with the view v$vector_index
 
demo@ADB26AI> select index_name, index_organization, allocated_bytes
  2    , used_bytes, num_vectors, index_dimensions
  3  from v$vector_index
  4  where owner = user;
 
INDEX_NAME      INDEX_ORGANIZATION        ALLOCATED_BYTES USED_BYTES NUM_VECTORS INDEX_DIMENSIONS
--------------- ------------------------- --------------- ---------- ----------- ----------------
HNSW_DEMO       INMEMORY NEIGHBOR GRAPH        2619080704 2613867800     1427029              384
 
After this index is created we can query the v$vector_memory_pool to see the memory utilization from vector pool.
 
demo@ADB26AI> select *
  2  from v$vector_memory_pool;
 
POOL         ALLOC_BYTES USED_BYTES POPULATE_STATUS     CON_ID
------------ ----------- ---------- --------------- ----------
1MB POOL      2945554763 2658140160 DONE                    37
64KB POOL      447608812   26738688 DONE                    37
 
Note that we are a bit off in actual used space from the estimate we got from the INDEX_VECTOR_MEMORY_ADVISOR procedure in the previous section (that is, 3GB versus 2658 MB).
 
Since HNSW indexes are created in-memory, when a database instance is restarted the index is no longer in-memory and must be re-built. By default, a reload mechanism is triggered at instance restart. This is controlled by the initialization parameter VECTOR_INDEX_NEIGHBOR_GRAPH_RELOAD and defaults to RESTART. To facilitate faster reloads after an instance restart a full checkpoint on-disk structure is also maintained.
 
demo@ORA26AI> show parameter vector_index
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ---------
vector_index_neighbor_graph_reload   string      RESTART
 
 
Now we can run the same query that was used in the exact similarity search section previously, but with the APPROX keyword to enable an approximate similarity search and a target accuracy that matches the index we created:
 
demo@ADB26AI> select case_primary_type , case_description , vd
  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 t
  7      order by vd
  8      fetch approx first 500 rows only with target accuracy 90 )
  9  group by case_primary_type , case_description ,vd
 10  order by vd
 11  fetch first 15 rows only;
 
CASE_PRIMARY_TYPE         CASE_DESCRIPTION                                      VD
------------------------- --------------------------------------------- ----------
OTHER OFFENSE             OTHER CRIME INVOLVING PROPERTY                3.845E-001
OTHER OFFENSE             OTHER CRIME AGAINST PERSON                    3.949E-001
 
Note that since we added the APPROX keyword, we can use the HNSW index we created previously. We can verify this by displaying the execution plan:
 
demo@ADB26AI> select * from table( dbms_xplan.display_cursor(format=>'allstats last'));
 
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
SQL_ID  amk960c8v78xa, child number 0
-------------------------------------
select case_primary_type , case_description , vd     from (     select
case_primary_type     , case_description     ,
vector_distance(case_embeddings , vector_embedding( MY_DEMO_MODEL using
'LARCENY' as data ) , cosine ) vd     from vector_search_demo t
order by vd     fetch approx first 500 rows only with target accuracy
90 ) group by case_primary_type , case_description ,vd order by vd
fetch first 15 rows only
 
Plan hash value: 3088503952
 
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                    |      1 |        |      2 |00:00:00.01 |    1260 |      7 |
|   1 |  RESULT CACHE                       | 6xt3798pj66dtdndjw |      1 |        |      2 |00:00:00.01 |    1260 |      7 |
|*  2 |   COUNT STOPKEY                     |                    |      1 |        |      2 |00:00:00.01 |    1260 |      7 |
|   3 |    VIEW                             |                    |      1 |    500 |      2 |00:00:00.01 |    1260 |      7 |
|*  4 |     SORT GROUP BY STOPKEY           |                    |      1 |    500 |      2 |00:00:00.01 |    1260 |      7 |
|   5 |      VIEW                           |                    |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|*  6 |       COUNT STOPKEY                 |                    |      1 |        |    500 |00:00:00.01 |    1260 |      7 |
|   7 |        VIEW                         |                    |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|*  8 |         SORT ORDER BY STOPKEY       |                    |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|   9 |          TABLE ACCESS BY INDEX ROWID| VECTOR_SEARCH_DEMO |      1 |    500 |    500 |00:00:00.01 |    1260 |      7 |
|  10 |           VECTOR INDEX HNSW SCAN    | HNSW_DEMO          |      1 |    500 |    500 |00:00:00.01 |       2 |      0 |
-----------------------------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - filter(ROWNUM<=15)
   4 - filter(ROWNUM<=15)
   6 - filter(ROWNUM<=500)
   8 - filter(ROWNUM<=500)
 
 
You can see the use of the vector index that was created in the query execution plan at id 10. Note that Oracle AI Vector Search is fully integrated into Oracle Database 23ai, so SQL execution plans will show the use of vector indexes. Also note that the time and the number of buffers accessed is much less than compared to the previous exhaustive search execution
 
It is important however, to recognize that using a vector index search is approximate as opposed to exhaustive. This means that the results can be different than with an exhaustive search. And depending on the data it can be quite different. 
 
 

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.