The INDEX_VECTOR_MEMORY_ESTIMATE procedure is used to analyze the memory usage of vector indexes in existing tables.
Note
This function is supported starting from V4.3.5 BP3.
Notice
The calculation of this procedure depends on statistics. Before calling it, 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 each partition based on the statistics. The calculation process for estimating memory is the same as that of the INDEX_VECTOR_MEMORY_ADVISOR procedure.
Parameters
| Parameter | Description |
|---|---|
| table_name | The name of the table. You can directly specify the table name or the database 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 sequential 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 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