The INDEX_VECTOR_MEMORY_ADVISOR function estimates the memory usage of vector indexes for tables that have not been created yet.
Notice
This function is available starting from OceanBase Database V4.4.1.
Syntax
FUNCTION index_vector_memory_advisor (
IN idx_type VARCHAR(65535),
IN num_vectors BIGINT UNSIGNED,
IN dim_count INT UNSIGNED,
IN dim_type VARCHAR(65535) DEFAULT 'FLOAT32',
IN idx_parameters LONGTEXT DEFAULT NULL,
IN max_tablet_vectors BIGINT UNSIGNED DEFAULT 0)
Parameters
| Parameter | Description |
|---|---|
| idx_type | The index type. Valid values: HNSW, HNSW_SQ, HNSW_BQ, IVF_FLAT, and IVF_PQ. Case-insensitive. |
| num_vectors | The number of vectors. |
| dim_count | The dimension of the vector. |
| dim_type | The data type of the vector elements. Valid value: FLOAT32. Case-insensitive. |
| idx_parameters | The index parameters. The string of the vector index parameters used when the table is created, for example, distance=l2, type=hnsw, lib=vsag. For more information, see Vector index memory management. |
| max_tablet_vectors | The maximum number of vectors in a partition of a partitioned table. The default value is 0, which indicates that the maximum number of vectors in a partition is equal to the total number of vectors. Using a partitioned table can reduce the minimum memory requirement when the total data volume is the same. |
The return value is a string that describes the estimated memory usage.
Notice
In OceanBase Database, when you create an HNSW_BQ index, the system automatically selects the parallel or sequential method for creating the index for a partitioned table based on the memory situation. The recommended value returned by the INDEX_VECTOR_MEMORY_ADVISOR function is the maximum memory requirement for creating the index sequentially. If the memory only meets this value, the index creation time may be extended because the index cannot be created in parallel.
To ensure the successful execution of vector index rebuilding, the recommended memory reservation value should be greater than the actual memory occupied by the vector index after the index is built. The recommended memory value is related to the maximum number of vectors in a partition:
- For HNSW and HNSW_SQ indexes, the recommended memory value is returned.
- For HNSW_BQ indexes, the recommended memory value and the actual memory occupied after the index is built are returned.
- For IVF and IVF_PQ indexes, the recommended memory value and the actual memory occupied after the index is built are returned.
Examples
Query the recommended memory value for an HNSW index:
SELECT dbms_vector.index_vector_memory_advisor('HNSW',1000000,768,'FLOAT32','M=10,DISTANCE=L2');
+------------------------------------------------------------------------------------------+
| dbms_vector.index_vector_memory_advisor('HNSW',1000000,768,'FLOAT32','M=10,DISTANCE=L2') |
+------------------------------------------------------------------------------------------+
| Suggested minimum vector memory is 7.2 GB |
+------------------------------------------------------------------------------------------+
1 row in set
Query the recommended memory value and the actual memory occupied after the index is built for an HNSW_BQ index:
SELECT dbms_vector.index_vector_memory_advisor('HNSW_BQ',1000000,768,'FLOAT32','M=10,DISTANCE=L2');
+---------------------------------------------------------------------------------------------------------+
| dbms_vector.index_vector_memory_advisor('HNSW_BQ',1000000,768,'FLOAT32','M=10,DISTANCE=L2') |
+---------------------------------------------------------------------------------------------------------+
| Suggested minimum vector memory is 4.1 GB, memory consumption when providing search service is 347.5 MB |
+---------------------------------------------------------------------------------------------------------+
1 row in set
Query the recommended memory value and the actual memory occupied after the index is built for an IVF index:
SELECT dbms_vector.index_vector_memory_advisor('IVF_FLAT',1000000,768,'FLOAT32','SAMPLE_PER_NLIST=1024,DISTANCE=L2');
+---------------------------------------------------------------------------------------------------------------+
| dbms_vector.index_vector_memory_advisor('IVF_FLAT',1000000,768,'FLOAT32','SAMPLE_PER_NLIST=1024,DISTANCE=L2') |
+---------------------------------------------------------------------------------------------------------------+
| Suggested minimum vector memory is 452.7 MB, memory consumption when providing search service is 384.0 KB |
+---------------------------------------------------------------------------------------------------------------+
1 row in set