Now, let’s take a look at some examples of creating vector indexes. First up, we’ll look at the HNSW vector index.
Here, we’re using the CREATE VECTOR INDEX syntax, giving it a name on which table and on which column that contains the vector type. And then you’ll notice an organization clause, where we describe everything else, INMEMORY. And then we have NEIGHBOR GRAPH. So NEIGHBOR is the maximum connections per vector, and that can be from 1 to 2,048.
Create vector index hnsw_idx on some_table(vector_col1)
organization
inmemory neighbor graph
distance cosine
with target accuracy 90
parameters (type hnsw, neighbors 40, efconstruction 500);
The EFCONSTRUCTION component is the number of closest vector candidates considered during each step of HNSW index creation, and that can be 1 to 65,535. A higher value does improve accuracy, but it can increase the time it takes in order to create the index. And the TARGET ACCURACY component is the desired accuracy percentage, from 0 to 100. A value of 90 means the algorithm aims for 90% accuracy while balancing speed. And if you leave that off, by the way, the default is going to be about 80% accuracy.
Now, creating an IVF vector index.
Create vector index ivf_idx on some_table(vector_col1)
organization
neighbor partitions
distance cosine
with target accuracy 90
parameters (type ivf, neighbor partitions 10);
In this case, the syntax starts out the same– CREATE VECTOR INDEX. Give it a name on the table and on which column with the vector type. And then the organization clause has a NEIGHBOR PARTITIONS component. This is the number of centroid partitions. Increasing NEIGHBOR PARTITION allows the algorithm to search more partitions, which leads to higher accuracy. The SAMPLES_PER_PARTITION is the training sample size. And the TARGET ACCURACY is the desired accuracy percentage, again, from 0 to 100. A value of 95 means the algorithm will aim for 95% accuracy while balancing speed.
So let’s compare the two. The In-Memory Neighbor Graph index constructs a multi-layered hierarchical graph connecting vectors based on their proximity and employs a navigational search algorithm. It’s generally high-accurate, especially when the entire index fits in memory. It does excel in performance when the index fits in memory and can be affected if the index size exceeds available memory. Now, DML operations are allowed on the index table after index creation. And memory usage is high because the entire index is stored in memory. RAC is also not supported.
With the Inverted File Flat Neighbor Partition index, it divides the vector space into partitions or clusters based on centroids. It limits searches to a subset of relevant partitions. It sacrifices some accuracy for speed, and accuracy can degrade over time due to DML operations. It’s designed for speed and can handle larger data sets and less dependent on memory availability. DML operations are supported, which makes it suitable for dynamic data sets. However, frequent DML operations can reduce the accuracy. Memory use is lower. It relies on partitioning to reduce the search space. And RAC is supported and benefits from setting up a vector pool on each instance.
Now, a word on using vector indexes. You have to use either the APPROX or APPROXIMATE keyword. Distance functions must match the index. If the distance metric used in the query differs from the one specified during index creation, then the system is going to perform an exact match instead of using the vector index, even though you included the APPROX or APPROXIMATE keyword. All vectors also must have the same dimensions.
Now, we do have the ability to monitor index accuracy. There is a package called “dbms_vector,” with a subprogram called “index_accuracy_query.” You’ll notice that we’re passing in several different parameters. And it will print out using the “dbms_output.put_line,” using the populated report variable.
It’s worth mentioning some important limitations. You cannot create vector indexes on external tables, global temporary tables, materialized views, non-vector columns, or function-based expressions. HNSW specific limitations– after a database restart, HNSW indexes because those in-memory structures have to be rebuilt. You can either do this manually, or you can enable the parameter called “VECTOR_INDEX_NEIGHBOR_GRAPH_RELOAD,” which will allow it to do an automatic reload. And you can only use one index type per vector column.
The best practices include– you do need to size the vector pool appropriately. Use HNSW when the data fits in memory. Use IVF for larger data sets. You should match distance metrics with the embedding model. And monitor index accuracy. Regular maintenance should be done for IVF indexes. And consider rebuilding indexes when accuracy drops.
Recent Comments