The INDEX_VECTOR_MEMORY_ESTIMATE function is used to analyze the memory usage of vector indexes in existing tables.
Notice
This function is available starting from OceanBase Database V4.4.1.
Notice
This function relies on statistics. Before calling this function, 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 function reads the dimension and data type information of the vector column based on the table_name and column_name parameters. It also retrieves the total number of vectors and the maximum number of vectors in a partition based on the statistics. The memory estimation process is the same as that of the INDEX_VECTOR_MEMORY_ADVISOR function.
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 type of the index. Valid values: HNSW, HNSW_SQ, HNSW_BQ, IVF_FLAT, and IVF_PQ. The value is case-insensitive. |
| idx_parameters | The index parameters. The value is the vector index parameters used when the table was created, for example, distance=l2, type=hnsw, lib=vsag. For more information about the parameters, see Vector index memory management. |
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 sequential mode based on the memory situation to create the index for a partitioned table. The suggested value returned by the INDEX_VECTOR_MEMORY_ESTIMATE function is the maximum memory requirement when the index is created sequentially. 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 into the table.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 in 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