IVF series indexes

This topic describes the syntax and examples for creating, searching, and dropping IVF series indexes in OceanBase Database.

Syntax and description

Create

IVF series indexes include IVF and IVF_PQ indexes. The construction of the cluster centers of an IVF index depends on the data in the primary table. To ensure the normal use of the IVF index, you must create the primary table and import data before you create the IVF index. You can create an IVF index only by using the post-create method. When you create an IVF index, note the following:

• If the data volume is large, we recommend that you write data to the primary table and then create the index to achieve the best search performance.
• We recommend that you create an IVF or IVF_PQ index after you write data to the primary table, and rebuild the index after you write a large amount of incremental data. For more information about how to create an index, see the examples in the following sections.
• The length of the index name must be less than or equal to 33 characters. Otherwise, an exception may be returned because the name of the auxiliary table of the index exceeds the index_name limit. In a later version, the length of the index name will be supported.
• When you process large-scale vector data, you can increase the nlist parameter to improve the recall rate of the IVF index. When nlist is greater than or equal to 5000, the storage structure of the cluster centers of the IVF index will be changed from an array to a graph structure to further optimize the index performance and space utilization.

Syntax for post-create indexes:

-- You can specify the parallelism to improve the index construction performance. The maximum value of the parallelism is twice the number of CPU cores.
CREATE [/*+ paralell $value*/] VECTOR INDEX index_name ON table_name(column_name2) WITH (param1=value1, param2=value2, ...);

The following table describes the param parameters.

Search

IVF series indexes support approximate nearest neighbor (ANN) search. The search results are not guaranteed to be 100% accurate. The recall rate is used to measure the search accuracy. For example, if 9 out of 10 nearest neighbors can be returned, the recall rate is 90%.

SELECT ... FROM table_name 
ORDER BY distance_function(column_name, vector_expr) [APPROXIMATE|APPROX] 
LIMIT num (OFFSET num);

Syntax requirements:

• You must specify the APPROXIMATE or APPROX keyword. Otherwise, the vector index will not be used for the search, and the full table scan will be performed.
• You must specify the ORDER BY and LIMIT clauses.
• The ORDER BY clause can specify only one vector condition.
• The value of LIMIT + OFFSET must be in the range of (0, 16384].
• If you do not specify the LIMIT clause, an error will be returned.

Rules for using distance functions:

• If you specify the APPROXIMATE or APPROX keyword, the search will use the vector index only if the distance function is supported in the current version and is compatible with the vector index algorithm.
• If you specify the APPROXIMATE or APPROX keyword, the search will not use the vector index if the distance function is not compatible with the vector index algorithm. No error will be returned.
• If you specify the APPROXIMATE or APPROX keyword, the search will not use the vector index if the distance function is not supported in the current version. An error will be returned.
• If you do not specify the APPROXIMATE or APPROX keyword, the search will not use the vector index even if the distance function is supported in the current version. No error will be returned.

Other considerations:

• The WHERE clause specifies the filter conditions for the vector index search.
• The recall rate is affected by the construction parameters and search parameters.
• The index search parameters are specified when you create the index and cannot be modified later. However, you can set the number of cluster centers used for the search by using the session variable ob_ivf_nprobes. If you set this session variable, it will be used for the search. For more information about how to set this session variable, see ob_ivf_nprobes.

Drop

Syntax for dropping a vector index:

DROP INDEX index_name ON table_name;

Examples

Post-create

Create a test table.

CREATE TABLE vec_table_ivf (c1 INT, c2 VECTOR(3), PRIMARY KEY(c1));

Create an IVF index.

CREATE VECTOR INDEX vec_idx3 ON vec_table_ivf(c2) WITH (distance=l2, type=ivf_flat);

Drop

DROP INDEX vec_idx1 ON vec_table;

Query the dropped index.

SHOW INDEX FROM vec_table;

The return result is as follows:

Empty set

References

• For information about how to query the estimated memory usage and actual memory usage of IVF series indexes, see Index memory management.
• For information about how to monitor and maintain IVF series indexes, see Index monitoring and maintenance.
• For information about how to optimize the performance of IVF series indexes, see Index performance optimization.