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.