Optimizer Plans for IVF Vector Indexes
Inverted File Flat (IVF) is a form of Neighbor Partition Vector index. It is a partition-based index that achieves search efficiency by narrowing the search area through the use of neighbor partitions or clusters.
When using IVF vector indexes, you can see two possible plans for your similarity searches:
-
Pre-filtering evaluates the filters first, before using the IVF vector index. Once the optimizer chooses which centroid partitions to scan, it evaluates the filters for all the rows in those partitions. Then it computes the distance to the query vector for the rows that passed the filters.
-
Post-filtering evaluates the filters after using the IVF vector index. Once the optimizer chooses which centroid partitions to scan, it computes the distance to the query vector for all the rows in those partitions. Once the optimizer finds the closest rows, it then evaluates the filter for those rows and returns only the ones that pass the filters.
Note: Depending on the version you are using, you can find variations of these plans. However, the idea stays the same, so the following plans are just examples for illustration purposes.
Pre-filter plan
-
Plan line ids 7 to 12: This part happens first. The optimizer chooses the centroid
idsthat are closest to the query vector. -
Plan line ids 13 to 14: With these lines, centroid
idsare joined with the centroid partitions table to get the rows only from the passing centroid partitions identified from the centroididsin the first step. -
Plan line id 4: All the rows from 5-14 are joined to the rows from 15 with a
NESTED LOOPS JOIN. That is, the rows are reduced to only the rows that pass the filter. -
Plan line id 3: This step computes the vector distance and sorts on this value, only keeping the top-K rows.
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 8112 | | 4260 (3)| 00:00:01 | | |
|* 1 | COUNT STOPKEY | | | | | | | | |
| 2 | VIEW | | 66 | 130K| | 4260 (3)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 66 | 127K| 144K| 4260 (3)| 00:00:01 | | |
| 4 | NESTED LOOPS | | 66 | 127K| | 4217 (3)| 00:00:01 | | |
| 5 | VIEW | VW_IVENJ_47D4581B | 88 | 880 | | 89 (4)| 00:00:01 | | |
|* 6 | HASH JOIN | | 88 | 138K| | 89 (4)| 00:00:01 | | |
| 7 | PART JOIN FILTER CREATE | :BF0000 | 3 | 39 | | 4 (25)| 00:00:01 | | |
| 8 | VIEW | VW_IVCR_B5B87E67 | 3 | 39 | | 4 (25)| 00:00:01 | | |
|* 9 | COUNT STOPKEY | | | | | | | | |
| 10 | VIEW | VW_IVCN_9A1D2119 | 16 | 208 | | 4 (25)| 00:00:01 | | |
|* 11 | SORT ORDER BY STOPKEY | | 16 | 144 | | 4 (25)| 00:00:01 | | |
| 12 | TABLE ACCESS FULL | VECTOR$IDX_IVF_MY_DATA$75132_75138_0$IVF_FLAT_CENTROIDS | 16 | 144 | | 3 (0)| 00:00:01 | | |
| 13 | PARTITION LIST JOIN-FILTER| | 469 | 733K| | 85 (3)| 00:00:01 |:BF0000|:BF0000|
| 14 | TABLE ACCESS FULL | VECTOR$IDX_IVF_MY_DATA$75132_75138_0$IVF_FLAT_CENTROID_PARTITIONS | 469 | 733K| | 85 (3)| 00:00:01 |:BF0000|:BF0000|
|* 15 | TABLE ACCESS FULL | MY_DATA | 1 | 1965 | | 47 (3)| 00:00:01 | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(ROWNUM<=4)
3 - filter(ROWNUM<=4)
6 - access("VW_IVCR_B5B87E67"."CENTROID_ID"="VTIX_CNPART"."CENTROID_ID")
9 - filter(ROWNUM<=3)
11 - filter(ROWNUM<=3)
15 - filter("MY_DATA".ROWID="VW_IVENJ_47D4581B"."BASE_TABLE_ROWID" AND "MY_DATA"."CATEGORY"<>'Generic')Note: IVF indexes use auxiliary partition tables and associated indexes to store index information. Mainly VECTOR$<index name>$<ids>$IVF_FLAT_CENTROIDS and VECTOR$<index name>$<ids>$IVF_FLAT_CENTROIDS_PARTITIONS.
Post-filter Plan
-
Plan line ids 11 to 15: Here the optimizer chooses the closest centroids (similar to lines 5 through 9 of the pre-filter plan).
-
Plan line id 10: This creates a special filter with information about which centroid
idswere chosen. -
Plan line ids 16 and 17: These lines use the special filter from plan line 10 to scan only the corresponding centroid partitions.
-
Plan line id 9: This
HASH JOINmakes sure that only the rows with the closest centroididsare returned. -
Plan line id 8: This computes the distance for those rows with the closest centroid
idsand finds the top-K rows. -
Plan line id 4: This
NESTED LOOPSapplies the base table filter to the previously identified top-K rows.
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |
|* 1 | COUNT STOPKEY | |
| 2 | VIEW | |
|* 3 | SORT ORDER BY STOPKEY | |
| 4 | NESTED LOOPS | |
| 5 | VIEW | VW_IVPSR_11E7D7DE |
|* 6 | COUNT STOPKEY | |
| 7 | VIEW | VW_IVPSJ_578B79F1 |
|* 8 | SORT ORDER BY STOPKEY | |
|* 9 | HASH JOIN | |
| 10 | PART JOIN FILTER CREATE | :BF0000 |
| 11 | VIEW | VW_IVCR_B5B87E67 |
|* 12 | COUNT STOPKEY | |
| 13 | VIEW | VW_IVCN_9A1D2119 |
|* 14 | SORT ORDER BY STOPKEY | |
| 15 | TABLE ACCESS FULL | VECTOR$DOCS_IVF_IDX4$81357_83292_0$IVF_FLAT_CENTROIDS |
| 16 | PARTITION LIST JOIN-FILTER| |
| 17 | TABLE ACCESS FULL | VECTOR$DOCS_IVF_IDX4$81357_83292_0$IVF_FLAT_CENTROID_PARTITIONS |
|* 18 | TABLE ACCESS BY USER ROWID | DOC_CHUNKS |
---------------------------------------------------------------------------------------------------------------IVF Indexes in the Optimizer Plan
If the IVF index is used by the optimizer, the plan contains the names of the centroids and centroid partitions tables accessed as well as corresponding indexes.
The value displayed in the Options column for tables accessed via IVF indexes depends upon whether the table scan is for a regular table or Exadata table.
Centroids and Centroid Partition Table Options
| Operation | Options | Object_name |
|---|---|---|
| TABLE ACCESS | FULL | VECTOR$<vector-index-name>$<id>$IVF_FLAT_CENTROIDSVECTOR$DOCS_IVF_IDX2$<id>$IVF_FLAT_CENTROID_PARTITIONS |
| TABLE ACCESS | STORAGE FULL | VECTOR$<vector-index-name>$<id>$IVF_FLAT_CENTROIDSVECTOR$DOCS_IVF_IDX2$<id>$IVF_FLAT_CENTROID_PARTITIONS |
| TABLE ACCESS | INMEMORY FULL | VECTOR$<vector-index-name>$<id>$IVF_FLAT_CENTROIDSVECTOR$DOCS_IVF_IDX2$<id>$IVF_FLAT_CENTROID_PARTITIONS |
-
Terminable Iteration for IVF Index
Use this feature if you want to ensure that the desired number of rows are returned from a search.