The INDEX_VECTOR_MEMORY_ESTIMATE procedure is used to analyze the memory usage of vector indexes in an existing table.
Note
This function is supported starting from V4.3.5 BP3.
Notice
The calculation of this procedure depends on statistics. Before calling it, you must execute the ANALYZE TABLE statement to update the statistics.
Syntax
FUNCTION index_vector_memory_estimate (
IN table_name VARCHAR(65535),
IN column_name VARCHAR(65535),
IN idx_type VARCHAR(65535),
IN idx_parameters LONGTEXT DEFAULT NULL)
The INDEX_VECTOR_MEMORY_ESTIMATE procedure reads the dimension and data type information of the vector column based on table_name and column_name, and obtains the total number of vectors and the maximum number of vectors in a partition based on the statistics. The calculation process of the memory estimation is consistent with that of INDEX_VECTOR_MEMORY_ADVISOR.
Parameters
| Parameter | Description |
|---|---|
| table_name | The name of the table. You can directly specify the table name or specify the database name and table name. |
| column_name | The name of the vector column. |
| idx_type | The index type. Valid values: HNSW, HNSW_SQ, HNSW_BQ, IVF_FLAT, IVF_SQ8, and IVF_PQ. The value is case-insensitive. |
| idx_parameters | The index parameters. The string specifies the vector index parameters used when the table was created. Example: distance=l2, type=hnsw, lib=vsag. For more information, see Create a vector index. |
The return value is a string that describes the memory estimation result.
Notice
In OceanBase Database, when you create an HNSW_BQ index, the system automatically selects the parallel or serial method for partitioned tables based on the memory situation. The recommended value returned by the INDEX_VECTOR_MEMORY_ESTIMATE function is the maximum memory requirement when creating the index in serial mode. If the memory only meets this value, the index creation time may be extended because the index cannot be created in parallel.
Examples
Create a table named
t1and insert data.CREATE TABLE t1 (id int auto_increment, c1 vector(3)); INSERT INTO t1(c1) SELECT array(1/UNIFORM(1,1000,RANDOM(1)), 1/UNIFORM(1,1000,RANDOM(2)) , 1/UNIFORM(1,1000,RANDOM(3))) FROM table(generator(10000));Update the statistics of the
t1table.ANALYZE TABLE t1;Query the memory estimation result of the vector index of the
t1table.-- Query the memory estimation result of the HNSW index. SELECT dbms_vector.index_vector_memory_estimate('t1','c1','HNSW','M=10,TYPE=HNSW'); +-----------------------------------------------------------------------------+ | dbms_vector.index_vector_memory_estimate('t1','c1','HNSW','M=10,TYPE=HNSW') | +-----------------------------------------------------------------------------+ | Suggested minimum vector memory is 11.5 MB | +-----------------------------------------------------------------------------+ 1 row in set -- Query the memory estimation result of the IVF index. SELECT dbms_vector.index_vector_memory_estimate('t1','c1','IVF_FLAT','SAMPLE_PER_NLIST=100'); +---------------------------------------------------------------------------------------------------------+ | dbms_vector.index_vector_memory_estimate('t1','c1','IVF_FLAT','SAMPLE_PER_NLIST=100') | +---------------------------------------------------------------------------------------------------------+ | Suggested minimum vector memory is 151.5 KB, memory consumption when providing search service is 1.5 KB | +---------------------------------------------------------------------------------------------------------+ 1 row in set