This topic describes the syntax and examples for creating, searching, and dropping IVF series indexes in OceanBase AI Database.
Index syntax and description
Create
The IVF series indexes include two types: IVF and IVF_PQ. The cluster centers of an IVF index are built based on the data in the primary table. To ensure 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 after the primary table is created. 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 IVF/IVF_PQ indexes after writing data to the primary table, and rebuild them after writing a large amount of incremental data. For more information about how to create an index, see the examples in the following sections.
- When creating an IVF index, the index name length must not exceed 33 characters; otherwise, an exception may occur due to the index auxiliary table name exceeding the
index_namelimit. Longer index names will be supported in future versions. - When processing large-scale vector data, appropriately increasing the
nlistparameter helps improve the recall rate of IVF indexes. Whennlist≥ 5000, the storage structure for caching IVF coarse clustering centers switches from an array to an HGraph (graph structure), further optimizing 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, ...);
Explanation of param parameters:
Parameter |
Default value |
Value range |
Required |
Description |
Remarks |
|---|---|---|---|---|---|
| distance | l1/l2/inner_product/cosine | Yes | Specifies the vector distance algorithm type. | l1 indicates Manhattan distance, which supports only the IVF_FLAT index on FP32 general dense vectors; l2 indicates Euclidean distance, inner_product indicates inner product distance, and cosine indicates cosine distance. | |
| type | ivf_flat/ivf_pq | Yes | Specifies the IVF index type. | ||
| lib | ob | ob | No | Specifies the type of the vector index database. | |
| nlist | 128 | [1,65536] | No | The number of cluster centers. | |
| sample_per_nlist | 256 | [1,int64_max] | Yes | The number of samples for each cluster center. This parameter is used for post-create indexes. | |
| nbits | 8 | [1,24] | No | Specifies the quantization bit depth.
NoticeThis parameter can only be specified when creating an IVF_PQ index. |
The recommended value is 8, within the suggested range of [8,10]. A higher value indicates higher quantization precision and search accuracy, but it also impacts search performance. |
| m | No default value. This parameter must be specified. | [1,65536] | Yes | Specifies the dimensionality of the quantized vector.
NoticeThis parameter can be specified only when creating an IVF_PQ index. |
A larger value indicates a slower index build but higher search accuracy, while also affecting search performance. |
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);
Search syntax requirements:
- The
APPROXIMATE/APPROXkeyword must be specified for the search to use the vector index instead of a full table scan. - The
ORDER BYandLIMITclauses must be included. ORDER BYsupports only a single vector condition.- The value range for
LIMIT + OFFSETis(0, 16384]. - Omitting the
LIMITclause will result in an error.
Distance function usage rules:
- The
l1_distance()distance function is supported only for IVF indexes and applies only to regular dense vectors with FP32 (32-bit floating-point) precision. It does not support IVF_PQ quantized indexes or other vector types such as sparse vectors. - If
APPROXIMATE/APPROXis specified, and a supported distance function is called that matches the vector index algorithm, the search will use the vector index. - If
APPROXIMATE/APPROXis specified, and the distance function does not match the vector index algorithm, the search will not use the vector index but no error will be reported. - If
APPROXIMATE/APPROXis specified, and the distance function is not supported, the search will not use the vector index and an error will be reported. - If
APPROXIMATE/APPROXis not specified, and a supported distance function is called, the search will not use the vector index but no error will be reported.
Other notes:
WHEREconditions serve as filtering criteria for vector index searches.- Recall is affected by construction parameters and search parameters.
- Index search parameters are specified during index creation and cannot be modified afterward. However, you can set the number of cluster centers used during search by using the session variable
ob_ivf_nprobes. If this session variable is set, its value is used for the search. For more information about how to set this session variable, see ob_ivf_nprobes.
Delete
The syntax for dropping a vector index is as follows:
DROP INDEX index_name ON table_name;
Create, search, and delete examples
Post-creation
IVF example
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);
Delete
DROP INDEX vec_idx1 ON vec_table;
Query the index after dropping it.
SHOW INDEX FROM vec_table;
The return result is as follows:
Empty set
References
- For information about memory estimation and actual usage of IVF series indexes, see Vector index memory management.
- For information about monitoring and maintenance of IVF series indexes, see Monitor and maintain indexes.
- For information about performance optimization of IVF series indexes, see Index performance optimization.