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 Oracle Database 23ai AI Vector Search, and the second was Using HNSW Vector Indexes. In those posts, we explored how AI Vector Search allows you to find similar content based on the semantic representation or meaning rather than the unstructured data’s raw values (words or pixels).
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. However, there is an alternative. As I described in the Using HNSW Vector Indexes post, Oracle AI Vector Search supports three types of vector indexes.
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 detailed information about how the IVF vector index works is available in the AI Vector Search User’s Guide.
A significant advantage of an IVF vector index is that it is not constrained by the amount of memory available in the vector pool like an HNSW vector index is. Although an IVF index won’t be as fast as an equivalent HNSW index, it can be used for very large data sets and still provide excellent performance when compared to an exhaustive similarity search.
Another characteristic of IVF vector indexes is that they support global and local indexes on partitioned tables. By default, IVF vector indexes are globally partitioned and can be used on non-partitioned or partitioned tables. However, for very large datasets that are partitioned, the ability to create a locally partitioned IVF vector index means that query performance can be further enhanced with partition pruning, avoiding a costly scan of the entire index.
IVF Vector Index Creation
To create an IVF vector index on the vector column VECTOR_DESC in the SEARCH_DATA table used in my previous posts, the following statement can be used:
CREATE VECTOR INDEX search_data_ivf_idx ON search_data (vector_desc) ORGANIZATION NEIGHBOR PARTITIONS DISTANCE COSINE;
This statement will create a global partitioned IVF vector index and will result in the creation of two internal tables:
VECTOR$SEARCH_DATA_IVF_IDX$88760_97299_0$IVF_FLAT_CENTROIDS VECTOR$SEARCH_DATA_IVF_IDX$88760_97299_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.
Vector Pool
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:
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------------------------- ----------- ---------- -------------------------- ---------- 1MB POOL 3758096384 4194304 DONE 3 64KB POOL 1593835520 65536 DONE 3 IM POOL METADATA 16777216 16777216 DONE 3
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:
ALLOCATED OWNER INDEX_NAME INDEX_ORGANIZATION BYTES USED_BYTES NUM_VECTORS ---------- --------------------------- ------------------------- ---------- ---------- ----------- VECTOR SEARCH_DATA_IVF_IDX NEIGHBOR PARTITIONS 0 0 469380
Note that there is no memory being used for the actual index since an IVF index is not an in-memory index.
Using IVF Vector Indexes
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:
SELECT description, TO_NUMBER(vector_distance) AS vector_distance
FROM (
SELECT id, description,
VECTOR_DISTANCE(vector_desc, VECTOR_EMBEDDING(minilm_l12_v2 USING 'ROBBERY' AS data),
COSINE) AS vector_distance
FROM search_data
ORDER BY 3
FETCH APPROX FIRST 50000 ROWS ONLY )
GROUP BY description, vector_distance
ORDER BY 2
FETCH FIRST 10 ROWS ONLY ;
DESCRIPTION VECTOR_DISTANCE
---------------------------------------- ---------------
RETAIL THEFT .299974287
ATTEMPT THEFT .336217165
PREDATORY .450500665
OTHER CRIME INVOLVING PROPERTY .462044049
AGGRAVATED FINANCIAL IDENTITY THEFT .462046878
EXTORTION .466129633
FORGERY .481875205
ATTEMPT FINANCIAL IDENTITY THEFT .485770402
THEFT/RECOVERY: AUTOMOBILE .494581937
COMPOUNDING A CRIME .501327872
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:
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | Id | Operation | Name | Starts | Cost (%CPU)| A-Rows | A-Time | Buffers | Reads | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 55072 (100)| 10 |00:00:00.50 | 37161 | 37042 | | 1 | COUNT STOPKEY | | 1 | | 10 |00:00:00.50 | 37161 | 37042 | | 2 | VIEW | | 1 | 55072 (1)| 10 |00:00:00.50 | 37161 | 37042 | | 3 | SORT ORDER BY STOPKEY | | 1 | 55072 (1)| 10 |00:00:00.50 | 37161 | 37042 | | 4 | HASH GROUP BY | | 1 | 55072 (1)| 43 |00:00:00.50 | 37161 | 37042 | | 5 | VIEW | | 1 | 55012 (1)| 30553 |00:00:00.49 | 37161 | 37042 | | 6 | VIEW | | 1 | 55012 (1)| 30553 |00:00:00.49 | 37161 | 37042 | | 7 | NESTED LOOPS | | 1 | 55012 (1)| 30553 |00:00:00.48 | 37161 | 37042 | | 8 | VIEW | VW_IVPSR_F17F1C9B | 1 | 4702 (3)| 30553 |00:00:00.15 | 7785 | 7758 | | 9 | COUNT STOPKEY | | 1 | | 30553 |00:00:00.14 | 7785 | 7758 | | 10 | VIEW | VW_IVPSJ_578B79F1 | 1 | 4702 (3)| 30553 |00:00:00.14 | 7785 | 7758 | | 11 | SORT ORDER BY STOPKEY | | 1 | 4702 (3)| 30553 |00:00:00.13 | 7785 | 7758 | | 12 | HASH JOIN | | 1 | 3876 (3)| 30553 |00:00:00.09 | 7779 | 7758 | | 13 | PART JOIN FILTER CREATE | :BF0000 | 1 | 12 (9)| 13 |00:00:00.01 | 46 | 38 | | 14 | VIEW | VW_IVCR_B5B87E67 | 1 | 12 (9)| 13 |00:00:00.01 | 46 | 38 | | 15 | COUNT STOPKEY | | 1 | | 13 |00:00:00.01 | 46 | 38 | | 16 | VIEW | VW_IVCN_9A1D2119 | 1 | 12 (9)| 13 |00:00:00.01 | 46 | 38 | | 17 | SORT ORDER BY STOPKEY | | 1 | 12 (9)| 13 |00:00:00.01 | 46 | 38 | | 18 | TABLE ACCESS FULL | VECTOR$SEARCH_DATA_IVF_IDX$77067_79644_0$IVF_FLAT_CENTROIDS | 1 | 11 (0)| 113 |00:00:00.01 | 37 | 36 | | 19 | PARTITION LIST JOIN-FILTER| | 1 | 297 (3)| 30553 |00:00:00.07 | 7733 | 7720 | | 20 | TABLE ACCESS FULL | VECTOR$SEARCH_DATA_IVF_IDX$77067_79644_0$IVF_FLAT_CENTROID_PARTITIONS | 13 | 297 (3)| 30553 |00:00:00.06 | 7733 | 7720 | | 21 | TABLE ACCESS BY USER ROWID | SEARCH_DATA | 30553 | 1 (0)| 30553 |00:00:00.32 | 29376 | 29284 | ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
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.
Also note that the execution time is much less than compared to the exhaustive search execution that we ran at the beginning of the previous Using HNSW Vector Indexes post, 1.50 seconds for the exhaustive search versus 0.50 seconds for an approximate search using an IVF index.
In that previous post on Using HNSW Vector Indexes, we briefly mentioned that the results may differ from those of an exhaustive search versus an approximate search. In fact, an exhaustive search returned a total of 59 distinct descriptions, and an approximate search using an HNSW index returned 291 distinct descriptions. I ran the same query with the IVF index, and only 43 distinct descriptions were returned. Again, as I mentioned in the previous post, this isn’t a bad thing, but we do need to recognize that the data plays a role in which search method and/or indexing strategy we might ultimately decide to use.