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.