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.