This topic describes how to create, search for, and use in-memory sparse vector indexes in OceanBase AI Database. These indexes are also known as memory sparse indexes. Two index types are supported: SINDI and SINDI_SQ.
Notice
This feature is experimental and is not recommended for production environments.
Overview
Memory sparse indexes are efficient index types provided by OceanBase AI Database for sparse vector data (vectors where most elements are zero). These indexes must be fully loaded into memory and support DML operations and real-time searches. To improve query performance for sparse vectors, OceanBase AI Database integrates the Sparse Vector Index (SINDI) from the VSAG algorithm library. This index outperforms disk-based sparse vector indexes and is suitable for use when sufficient memory resources are available.
Supported features
In-memory sparse vector indexes support the following features:
Module |
Feature |
Introduction |
|---|---|---|
| DDL | Create a sparse vector index during table creation | You can create a sparse vector index on a SPARSEVECTOR column when creating a table. Up to 500,000 dimensions are supported. |
| DDL | Create a sparse vector index after table creation | Supports creating a sparse vector index on a SPARSEVECTOR column of an existing table. Up to 500,000 dimensions are supported. |
| DML | Insert, update, delete | The syntax is identical to that of regular vector indexes for DML operations. |
| Search | Vector search | SQL functions can be used for search. |
| Search | Query parameters | Supports setting query-level parameters by using the parameters clause during search. |
| DBMS_VECTOR | REBUILD_INDEX |
Performs a full rebuild of the index. |
| Monitoring | Index monitoring | Supports viewing basic index information and real-time status through views. For more information, see Monitor and maintain indexes. |
Query index memory estimation and actual usage
You can use the DBMS_VECTOR system package to estimate the memory required for an index. The syntax is the same as that for dense indexes. The following section describes the special requirements for sparse vector indexes:
- The
IDX_TYPEparameter must be set toSINDIorSINDI_SQ(case-insensitive). - The memory estimate for a sparse vector depends on its average length, which is the average number of non-zero dimensions per vector.
Syntax and description
In-memory sparse vector indexes can be created when creating a table or after the table is created. Consider the following points when creating an index:
- Maximum dimensionality: A column used to create a sparse vector index can have up to 500,000 dimensions.
- Column type: An index can only be created on a column of type
SPARSEVECTOR. - Keyword: The
VECTORkeyword is required to create an index. - Index type: The
sindiandsindi_sqtypes are supported.sindiindicates a full-precision in-memory sparse index, andsindi_sqindicates a scalar-quantized sparse index. For SINDI_SQ, you must build the index on a dataset of a certain scale to minimize precision loss. The logic is similar to that of HNSW_SQ. - Distance algorithm: Only
inner_product(inner product) is supported. - Parameter consistency: Parameters and descriptions for creating an index after the table is created are the same as those for creating it when creating the table.
- It is recommended to create an SINDI_SQ index after writing data and to rebuild the index after writing a significant amount of incremental data:
- If creating the index when creating the table, create SINDI first, and then rebuild it to SINDI_SQ after the data exceeds 10,000 entries.
- If creating the index after the table is created, create SINDI if the data in a single partition does not exceed 10,000 entries; otherwise, you can directly create SINDI_SQ.
Create when creating a table
You can use the CREATE TABLE statement to create a sparse vector index.
Syntax
CREATE TABLE table_name (
column_name1 data_type1,
column_name2 SPARSEVECTOR,
...,
VECTOR INDEX index_name (column_name2) WITH (param1=value1, param2=value2, ...)
);
Parameters
Parameter |
Default value |
Value range |
Required |
Description |
Remarks |
|---|---|---|---|---|---|
distance |
inner_product |
Yes | Specifies the vector distance algorithm type. | Both SINDI and SINDI_SQ indexes support only the inner product (inner_product) as the distance algorithm. |
|
type |
sindi / sindi_sq |
Yes | Specifies the index algorithm type. | sindi indicates a full-precision in-memory sparse index, and sindi_sq indicates a sparse index with scalar quantization. |
|
lib |
vsag |
vsag |
No | Specifies the type of the vector index database. | Currently, only the VSAG vector database is supported. |
prune |
false |
true/false |
No | Whether to prune vectors. | The refine and drop_ratio_build parameters need to be set only when prune is true. When prune is false, full-precision search is available, and an error is reported if refine is set to true or drop_ratio_build is set to a non-0 value. |
refine |
false |
true/false |
No | Whether to perform reordering. | When set to true, the original sparse vectors of the search results are fetched to perform high-precision distance calculation and reordering, which means that an extra copy of the original vector data must be stored. This can be set only when prune=true. |
drop_ratio_build |
0 |
[0, 0.9] |
No | The proportion of sparse vector data to be pruned. | When a sparse vector is inserted, the query_length * drop_ratio_build smallest values are truncated based on their magnitudes. If refine is true, the original vector data is retained; otherwise, only the truncated data is retained. This can be set only when prune=true. |
drop_ratio_search |
0 |
[0, 0.9] |
No | The proportion of sparse vector values to be pruned during search. | A larger value results in more pruning, lower accuracy, and higher performance. You can also set this parameter by using the parameters clause during search, and query parameters take precedence. |
refine_k |
4.0 |
[1.0, 1000.0] |
No | Represents the proportion of results that participate in reordering. | Searches limit_k * refine_k results and obtains the original vectors for reordering. This is meaningful only when refine=true. You can also set it by using the parameters clause during search, and query parameters take precedence over the clause setting. |
Add an index after table creation
You can create a sparse vector index on an existing SPARSEVECTOR column of a table.
Syntax
CREATE VECTOR INDEX index_name ON table_name(column_name) WITH (param1=value1, param2=value2, ...);
Parameters
The parameters are the same as those for creating an index when you create a table. For more information, see the preceding section.
Examples: Create, update, and drop
Create an index when creating a table
Create a test table named sparse_t1 and create a sparse vector index:
CREATE TABLE sparse_t1 (
c1 INT PRIMARY KEY,
c2 SPARSEVECTOR,
VECTOR INDEX sparse_idx1(c2)
WITH (lib=vsag, type=sindi, distance=inner_product)
);
Create a test table named sparse_t1_sq and create an SINDI_SQ index:
CREATE TABLE sparse_t1_sq (
c1 INT PRIMARY KEY,
c2 SPARSEVECTOR,
VECTOR INDEX sparse_idx1_sq(c2)
WITH (lib=vsag, type=sindi_sq, distance=inner_product)
);
Insert sparse vector data into the test table:
INSERT INTO sparse_t1 VALUES(1, '{1:0.1, 2:0.2, 3:0.3}');
INSERT INTO sparse_t1 VALUES(2, '{3:0.3, 2:0.2, 4:0.4}');
INSERT INTO sparse_t1 VALUES(3, '{3:0.3, 4:0.4, 5:0.5}');
Search the test table:
SELECT * FROM sparse_t1;
The result is as follows:
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | {1:0.1,2:0.2,3:0.3} |
| 2 | {2:0.2,3:0.3,4:0.4} |
| 3 | {3:0.3,4:0.4,5:0.5} |
+----+---------------------+
3 rows in set
Add an index after table creation
Create a test table and then create a sparse vector index:
CREATE TABLE sparse_t2 (
c1 INT PRIMARY KEY,
c2 SPARSEVECTOR
);
CREATE VECTOR INDEX sparse_idx2 ON sparse_t2(c2)
WITH (lib=vsag, type=sindi, distance=inner_product,
prune=true, refine=true, drop_ratio_build=0.1,
drop_ratio_search=0.5, refine_k=2.0);
Insert sparse vector data into the test table:
INSERT INTO sparse_t2 VALUES(1, '{1:0.1, 2:0.2, 3:0.3}');
Search the test table:
SELECT * FROM sparse_t2;
The return result is as follows:
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 1 | {1:0.1,2:0.2,3:0.3} |
+----+---------------------+
1 row in set
Update
When updating sparse vector data, the index is automatically maintained:
UPDATE sparse_t1 SET c2 = '{1:0.1}' WHERE c1 = 1;
Delete
The deletion operation is the same as that for a regular vector index; you can delete the data directly:
DELETE FROM sparse_t1 WHERE c1 = 1;
Search
The search syntax for a sparse vector index is similar to that for a dense vector index. You can use the APPROXIMATE/APPROX keyword to perform an approximate nearest neighbor search.
Syntax
SELECT ... FROM table_name
ORDER BY inner_product(column_name, query_vector) [APPROXIMATE|APPROX]
LIMIT n [PARAMETERS(param1=value1, param2=value2)];
Where:
column_name: TheSPARSEVECTORcolumn specified when creating the sparse vector index.query_vector: The query vector, which can be a string in sparse vector format, such as'{1:2.4, 3:1.5}'.n: The number of result rows to return.PARAMETERS: Optional query-level parameters used to setdrop_ratio_searchandrefine_k.
Search syntax requirements
- The
APPROXIMATE/APPROXkeyword must be specified for the vector index to be used 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 cause an error.
Rules for using distance functions
- When
APPROXIMATE/APPROXis specified and the called distance function is supported and matches the vector index algorithm, the search uses the vector index. - When
APPROXIMATE/APPROXis specified and the distance function does not match the vector index algorithm, the search does not use the vector index, but no error is returned. - When
APPROXIMATE/APPROXis specified and the distance function is not supported, the search does not use the vector index and an error is returned. - When
APPROXIMATE/APPROXis not specified and a supported distance function is called, the search does not use the vector index, but no error is returned.
Additional considerations
- The
WHEREcondition serves as the filter for the vector index search. - Recall is affected by construction parameters and search parameters.
- Index search parameters can be set when creating the index. If adjustments are needed after the index is created, you can modify table-level attributes using REBUILD_INDEX, or adjust them during queries by specifying the query-level parameter
ef_search.
Search-specific requirements
For detailed requirements, see Overview of dense indexes. Here are the special requirements for sparse vector indexes:
- Query parameter precedence: Query-level parameters set in
PARAMETERS> query parameters set during index construction > default values. drop_ratio_search: Value range[0, 0.9], default value0. This specifies the proportion of sparse vector values to prune. A larger value means more pruning, lower accuracy, and higher performance. Prunes outquery_length * drop_ratio_searchsmallest values based on their size. Since pruning all values is meaningless, at least one value is retained.refine_k: Value range[1.0, 1000.0], default value4.0. Indicates the proportion of results involved in rearrangement. Queries the results of thelimit_k * refine_kitems and obtains the original vectors for rearrangement. This is only valid whenrefine=true.
Usage examples
Regular query
CREATE TABLE t1 (
c1 INT PRIMARY KEY,
c2 SPARSEVECTOR,
VECTOR INDEX idx1(c2)
WITH (lib=vsag, type=sindi, distance=inner_product)
);
INSERT INTO t1 VALUES(1, '{1:0.1, 2:0.2, 3:0.3}');
INSERT INTO t1 VALUES(2, '{3:0.3, 2:0.2, 4:0.4}');
INSERT INTO t1 VALUES(3, '{3:0.3, 4:0.4, 5:0.5}');
INSERT INTO t1 VALUES(4, '{5:0.5, 4:0.4, 6:0.6}');
INSERT INTO t1 VALUES(5, '{5:0.5, 6:0.6, 7:0.7}');
SELECT * FROM t1
ORDER BY negative_inner_product(c2, '{3:0.3, 4:0.4}')
APPROXIMATE LIMIT 4;
The result is as follows:
+----+---------------------+
| c1 | c2 |
+----+---------------------+
| 2 | {2:0.2,3:0.3,4:0.4} |
| 3 | {3:0.3,4:0.4,5:0.5} |
| 4 | {4:0.4,5:0.5,6:0.6} |
| 1 | {1:0.1,2:0.2,3:0.3} |
+----+---------------------+
Using query parameters
SELECT *, negative_inner_product(c2, '{3:0.3, 4:0.4}')
AS score FROM t1
ORDER BY score APPROXIMATE LIMIT 4
PARAMETERS(drop_ratio_search=0.5);
The result is as follows:
+----+---------------------+---------------------+
| c1 | c2 | score |
+----+---------------------+---------------------+
| 4 | {4:0.4,5:0.5,6:0.6} | -0.1600000113248825 |
| 3 | {3:0.3,4:0.4,5:0.5} | -0.2500000149011612 |
| 2 | {2:0.2,3:0.3,4:0.4} | -0.2500000149011612 |
+----+---------------------+---------------------+
3 rows in set
Index monitoring and maintenance
In-memory sparse vector indexes provide several monitoring views and support index maintenance using the DBMS_VECTOR system package, including incremental refresh and full rebuild. The usage is consistent with dense indexes.
References
- For more information about the sparse vector data type, see Vector data types overview.
- For more information about vector distance functions, see Use SQL functions.
- For more information about monitoring and maintaining dense vector indexes, see Monitor and maintain indexes.
- For more information about memory estimation and actual usage of vector indexes, see Vector index memory management.