This topic describes how to create, search for, and drop HNSW series indexes in OceanBase AI Database, including syntax and examples.
Index syntax and description
Create
The HNSW series includes three types of indexes: HNSW, HNSW_SQ, and HNSW_BQ. They can be created during table creation or after table creation. Consider the following when creating:
- The
VECTORkeyword is required to create a vector index. - Parameters and descriptions for indexes created afterward are the same as those for creating indexes during table creation.
- If the amount of data is large, we recommend that you write the data and then create the index to achieve the best search performance.
- We recommend that you create an HNSW_SQ index after you write the data and rebuild the index after you write a large amount of incremental data. For more information about how to create an HNSW series index, see the following examples.
- When creating an HNSW index, the index name length must not exceed 25 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. - We recommend that you create an HNSW series index on a heap table to achieve the best search performance.
Create during table creation
Syntax:
CREATE TABLE table_name (
column_name1 data_type1,
column_name2 VECTOR(dim),
...,
VECTOR INDEX index_name (column_name2) WITH (param1=value1, param2=value2, ...)
);
Create after table creation
Syntax:
-- 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.
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; l2 indicates Euclidean distance, inner_product indicates inner product distance, and cosine indicates cosine distance. | |
| type | hnsw / hnsw_sq / hnsw_bq |
Yes | Specifies the index type. | ||
| lib | vsag | vsag | No | Specifies the type of the vector index database. | Currently, only the VSAG vector database is supported. |
| m | 16 | [5,128] | No | The maximum number of neighbors for each node. | A larger value indicates slower index construction but better search performance. |
| ef_construction | 200 | [5,1000] | No | The size of the candidate set during index construction. | A larger value indicates a slower index build but better index quality. ef_construction must be greater than m. |
| ef_search | 64 | [1,1000] | No | The size of the candidate set during search. | A larger value indicates a slower search but a higher recall rate. |
| duplicate_group | false | true/false | No | Specifies whether to enable identical-vector grouping. TRUE enables it and FALSE disables it. |
Improves the recall rate in scenarios with many duplicate vectors, but increases the memory overhead for recording grouping relationships. Only exactly identical vectors can be merged. For an existing index, you can enable or disable duplicate_group through a full rebuild. For the operation method, see Monitor and maintain indexes, section "Switch the vector grouping mode".
NoteThis field is supported starting from V4.6.3 Hotfix1. |
| extra_info_max_size | 0 | [0,16384] | No | Sets the maximum size of each primary key field (in bytes). Storing the table's primary keys in indexes accelerates search performance. | 0: Do not store the primary key information.1: Force the storage of primary key information, ignoring the size limit. In this case, the table's primary key type (see details below) must be a supported type.Greater than 1: Set the maximum size for primary key information (in bytes). In this case, the following conditions must be met:
|
| refine_type | sq8 | sq8/fp32 | No | This parameter can only be specified when creating the HNSW_BQ index. It is used to set the precision for building the quantized vector index. |
This value improves efficiency by reducing memory usage and index construction time during index creation, but it may affect recall. |
| bq_bits_query | 32 | 0/4/32 | No | This parameter can only be specified when creating the HNSW_BQ index. It specifies the search precision for quantized vector indexes, in bits. |
This value improves efficiency by reducing memory usage and index construction time during index creation, but it may affect recall. |
| bq_use_fht | true | true/false | No | This parameter can only be specified when creating the HNSW_BQ index. It specifies whether to use FHT for search. FHT (Fast Hadamard Transform) is an algorithm used to accelerate vector inner product calculations. |
extra_info_max_size supports the following primary key types:
- Overview of numeric types: integer, floating-point, and Bit_value types.
- Overview of date and time types
- Character type overview: VARCHAR type.
Calculation method for primary key information size:
SET @table_name = 'test'; -- Replace with the table name to query
SELECT
CASE
WHEN COUNT(*) <> COUNT(result_value) THEN 'not support'
ELSE COALESCE(SUM(result_value), 'not support')
END AS extra_info_size
FROM (
SELECT
CASE
WHEN vdt.data_type_class IN (1, 2, 3, 4, 6, 8, 9, 14, 27, 28) THEN 8 -- For numeric types. extra_info_size += 8
WHEN oc.data_type = 22 THEN oc.data_length -- For the varchar type, extra_info_size += data_length
ELSE NULL -- Other types are not supported
END AS result_value
FROM
oceanbase.__all_column oc
JOIN
oceanbase.__all_virtual_data_type vdt
ON
oc.data_type = vdt.data_type
WHERE
oc.rowkey_position != 0
AND oc.table_id = (SELECT table_id FROM oceanbase.__all_table WHERE table_name = @table_name)
) AS result_table;
-- The calculation result is 8 bytes.
Online modify index parameters
Starting from V4.6.3, you can use DBMS_VECTOR.ALTER_INDEX_PARAMS to online modify the construction and query parameters of HNSW series vector indexes after they are created. You only need to pass the parameters to be modified; unspecified parameters retain their current values.
When the construction parameters change, asynchronous partition-level rebuilding is triggered; modifying only query parameters does not trigger index rebuilding. For specific operation methods, see Index monitoring and maintenance.
Search
The search in the HNSW index series is an approximate nearest neighbor (ANN) search, and does not guarantee 100% accuracy in the search results. The recall rate is used to measure the accuracy of the search. For example, if 9 of the 10 nearest neighbors are returned stably, the recall rate is 90%.
SELECT ... FROM table_name
ORDER BY distance_function(column_name, vector_expr) [APPROXIMATE|APPROX]
LIMIT num [OFFSET num]
[PARAMETERS(param1=value1, param2=value2, ...)];
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]. A single approximate search returns at most 16384 results; for larger-scale similar recalls, you can use the search iterator to continue fetching data in batches vialast_distanceinPARAMETERS(supported starting from V4.6.2.1). - Omitting the
LIMITclause will cause an error. PARAMETERSis an optional query-level parameter where you can setef_search,refine_k, andlast_distance.
Distance function usage rules:
- The
l1_distance()distance function is supported only by HNSW indexes and applies only to regular dense vectors of FP32 (32-bit floating-point). It does not support quantized indexes like HNSW_SQ or HNSW_BQ, nor other vector types such as sparse vectors. - If you specify the
APPROXIMATE/APPROXkeyword, the search will use the vector index. In this case, the distance function must be supported by the current version and must match the vector index algorithm. - If you specify the
APPROXIMATE/APPROXkeyword, the search will not use the vector index, but no error is returned if the distance function does not match the vector index algorithm. - If you specify the
APPROXIMATE/APPROXkeyword, the search will not use the vector index and an error is returned if the distance function is not supported by the current version. - If you do not specify the
APPROXIMATE/APPROXkeyword, the search will not use the vector index, but no error is returned if the distance function is supported by the current version.
Other notes:
WHEREconditions serve as filter criteria for vector index searches.- Recall is affected by construction parameters and search parameters.
- Index search parameters can be set when creating the index or modified online after creation. For details, see Online modification of index parameters; query-level parameters can be specified per query via
PARAMETERS. - When the
SELECTlist contains a distance function (such asl2_distance()) and the query accesses the vector index path, the system can reuse previously computed distance values from the index, reducing overhead in largeLIMITscenarios.
Search parameters:
Parameters |
Default value |
Value range |
Required |
Note |
Remarks |
|---|---|---|---|---|---|
| ef_search | 64 | [1,1000] | No | The size of the candidate set during search. | A larger value results in slower search but a higher recall rate. It can be set when creating the index or specified during the search by using the PARAMETERS clause. If specified during the search, the specified value is used; otherwise, the value used when the index was created is used. |
| refine_k | 4.0 | [1.0,1000.0] | No |
NoticeThis parameter applies only to the HNSW_BQ index. |
It can be set when creating the index or specified during a search by using the PARAMETERS clause. If specified during the search, the specified value is used; otherwise, the value used when the index was created is used. |
| last_distance | No |
NoticeThis parameter is supported starting from V4.6.2.1. |
This parameter is not set for the first batch of queries. For each subsequent batch, pass the distance value of the last row from the previous batch's results to continue fetching the next batch. It is not recommended to pass 0 for the initial request. When the number of rows returned in a batch is less than LIMIT or no results are returned, it indicates that all data has been fetched. This parameter is valid only for APPROXIMATE/APPROX queries and is not supported for the HNSW_BQ index. |
Delete
The syntax for deleting a vector index is as follows:
DROP INDEX index_name ON table_name;
Examples of creation, search, and deletion
Create during table creation
HNSW example
Create a test table.
CREATE TABLE t1(c1 INT, c0 INT, c2 VECTOR(10), c3 VECTOR(10), PRIMARY KEY(c1), VECTOR INDEX idx1(c2) WITH (distance=l2, type=hnsw, lib=vsag), VECTOR INDEX idx2(c3) WITH (distance=l2, type=hnsw, lib=vsag));
Write test data.
INSERT INTO t1 VALUES(1, 1,'[0.203846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]', '[0.203846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]');
INSERT INTO t1 VALUES(2, 2, '[0.735541,0.670776,0.903237,0.447223,0.232028,0.659316,0.765661,0.226980,0.579658,0.933939]', '[0.213846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]');
INSERT INTO t1 VALUES(3, 3, '[0.327936,0.048756,0.084670,0.389642,0.970982,0.370915,0.181664,0.940780,0.013905,0.628127]', '[0.223846,0.205289,0.880265,0.824340,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833]');
Perform approximate nearest neighbor search.
SELECT * FROM t1 ORDER BY l2_distance(c2, [0.712338,0.603321,0.133444,0.428146,0.876387,0.763293,0.408760,0.765300,0.560072,0.900498]) APPROXIMATE LIMIT 1;
The return result is as follows:
+----+------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
| c1 | c0 | c2 | c3 |
+----+------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
| 3 | 3 | [0.327936,0.048756,0.08467,0.389642,0.970982,0.370915,0.181664,0.94078,0.013905,0.628127] | [0.223846,0.205289,0.880265,0.82434,0.615737,0.496899,0.983632,0.865571,0.248373,0.542833] |
+----+------+-------------------------------------------------------------------------------------------+--------------------------------------------------------------------------------------------+
1 row in set
HNSW_SQ example
CREATE TABLE t2 (c1 INT AUTO_INCREMENT, c2 VECTOR(3), PRIMARY KEY(c1), VECTOR INDEX idx1(c2) WITH (distance=l2, type=hnsw_sq, lib=vsag));
HNSW_BQ example
CREATE TABLE t3 (c1 INT AUTO_INCREMENT, c2 VECTOR(3), PRIMARY KEY(c1), VECTOR INDEX idx3(c2) WITH (distance=l2, type=hnsw_bq, lib=vsag));
For HNSW_BQ, the distance parameter supports l2 and cosine.
What to do next
HNSW example
Create a test table.
CREATE TABLE vec_table_hnsw (id INT, c2 VECTOR(10));
Create an HNSW index.
CREATE VECTOR INDEX vec_idx1 ON vec_table_hnsw(c2) WITH (distance=l2, type=hnsw);
Check the created table.
SHOW CREATE TABLE vec_table_hnsw;
The return result is as follows:
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| vec_table_hnsw | CREATE TABLE `vec_table_hnsw` (
`id` int(11) DEFAULT NULL,
`c2` VECTOR(10) DEFAULT NULL,
VECTOR KEY `vec_idx1` (`c2`) WITH (DISTANCE=L2, TYPE=HNSW, LIB=VSAG, M=16, EF_CONSTRUCTION=200, EF_SEARCH=64) BLOCK_SIZE 16384
) DEFAULT CHARSET = utf8mb4 ROW_FORMAT = DYNAMIC COMPRESSION = 'zstd_1.3.8' REPLICA_NUM = 2 BLOCK_SIZE = 16384 USE_BLOOM_FILTER = FALSE TABLET_SIZE = 134217728 PCTFREE = 0 |
+-----------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set
Check the created index.
SHOW INDEX FROM vec_table_hnsw;
The return result is as follows:
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | Visible | Expression |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
| vec_table | 1 | vec_idx1 | 1 | c2 | A | NULL | NULL | NULL | YES | VECTOR | available | | YES | NULL |
+-----------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+-----------+---------------+---------+------------+
1 row in set
HNSW_SQ example
Create a test table.
CREATE TABLE vec_table_hnsw_sq (c1 INT AUTO_INCREMENT, c2 VECTOR(3), PRIMARY KEY(c1));
Create an HNSW_SQ index.
CREATE VECTOR INDEX vec_idx2 ON vec_table_hnsw_sq(c2) WITH (distance=l2, type=hnsw_sq, lib=vsag, m=16, ef_construction = 200);
HNSW_BQ example
CREATE VECTOR INDEX vec_idx3 ON vec_table_hnsw_bq(c2) WITH (distance=l2, type=hnsw_bq, lib=vsag, m=16, ef_construction = 200);
The distance parameter of the HNSW_BQ index supports l2 and cosine.
Search
An example of a search iterator is as follows:
-- First batch: do not pass last_distance
SELECT pk, l2_distance(vec, '[1,1,2]') AS dist
FROM image_embeddings
ORDER BY l2_distance(vec, '[1,1,2]') APPROXIMATE LIMIT 1000
PARAMETERS(ef_search=200);
-- Assume the last row is dist = 0.35
-- Batch 2: Use the maximum distance from the previous batch and continue to select results where dist > 0.35.
SELECT pk, l2_distance(vec, '[1,1,2]') AS dist
FROM image_embeddings
ORDER BY l2_distance(vec, '[1,1,2]') APPROXIMATE LIMIT 1000
PARAMETERS(ef_search=200, last_distance=0.35);
Delete
DROP INDEX vec_idx1 ON vec_table;
View the deleted index.
SHOW INDEX FROM vec_table;
The return result is as follows:
Empty set
References
- For information about how to estimate and query the actual memory usage of HNSW series indexes, see Vector index memory management
- For information about how to monitor and maintain HNSW series indexes, see Monitor and maintain indexes