Partition Maintenance Operations with LOCAL IVF Vector Indexes

There are certain considerations to keep in mind regarding the use of Partition Maintenance Operations with local IVF vector indexes.

These are the Partition Maintenance Operation possibilities and restrictions for local IVF indexes:

• ALTER TABLE TRUNCATE [sub]partition <partition_name>

  ALTER TABLE DROP [sub]partition <partition_name>

  These operations are supported with all partition schemes: RANGE, HASH, LIST. However, all corresponding IVF index partitions are marked as UNUSABLE after the operation.

• ALTER TABLE ADD [sub]partition <partition_name>

  If the base table is partitioned by RANGE or LIST, then the operation is supported. However, all corresponding IVF indexes are marked as UNUSABLE after the operation. If the table is partitioned by HASH, then the operation fails if there are any local IVF indexes on the base table.

• All other PMOP operations on the base table are not supported, such as ALTER TABLE SPLIT/MERGE/MOVE/EXCHANGE/COALESCE.

  These ALTER TABLE statements will fail if there are any local IVF indexes on the table being altered.

• ALTER INDEX on local IVF indexes is not supported.

Note:

Partition Maintenance Operations are not supported with local HNSW indexes.

Local IVF index creation and DML operations on base tables with IVF indexes can be accelerated if Vector Pool is enabled. Vector Pool is a new memory area stored in the SGA. For more information related to Vector Pool, see Size the Vector Pool.

This is illustrated by the following graphic, where the base table has three partitions. The created local IVF index is constituted by two internal tables:

• One called VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROIDS, which is list-partitioned by base table partition ids, and is thus equi-partitioned with the base table. Each partition contains the list of corresponding identified centroid vectors and associated ids.

• The second called VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROID_PARTITIONS, which is list-partitioned by base table partition id and list-subpartitioned by centroid id. This table is also equi-partitioned with the base table, and each subpartition contains the base table vectors closely related (cluster) to the corresponding centroid id for that subpartition.

Description of the illustration local_ivf_index_usecase1.eps

If, for example, you search the top-10 houses in California similar to a vectorized picture, your query benefits from partition pruning on the base table and Centroids table (California) as they are both partitioned by state. Once the closest centroid is identified in that partition, the query simply needs to scan the corresponding centroid cluster subpartition in the Centroid Partitions table without having to scan other centroid subpartitions.

Another possibility is for the base table to be composite partitioned. Here is a graphical representation corresponding to that case. The Centroids table is list-partitioned according to base table subpartitions. Each partition in the Centroids table contains all centroid vectors found in the corresponding base table subpartition. The Centroid Partitions table is list-partitioned by base table subpartition id, and is further subpartitioned by centroid id:

Description of the illustration local_ivf_index_usecase2.eps

Note:

You can create local IVF indexes only on a partitioned base table.

Local IVF indexes inherit all system catalog tables and views used by regular local indexes. A flag (idx_spare2) in the vecsys.vector$index table indicates if an index is a local or global vector index.

Using local IVF indexes brings additional advantages:

• Simplified Partition Maintenance Operations:

  For example, dropping a table partition just involves dropping the corresponding index partition.

• Flexible Indexing schemes:

  For example, marking certain index partitions UNUSABLE to avoid indexing certain table partitions through partial indexing.

Note:

If you want your user queries to use the full potential of local IVF indexes by taking advantage of the benefit of partition pruning, user queries must satisfy the following conditions:

• The base table is [sub]partitioned by a single column.

• Conditions are a form of [sub]partitition_key CMP constant, where CMP can be:

  =, >, >=, <, <=, IN

• The partition_key condition is ANDed with other non-partition conditions.

• Experiment with Local IVF Indexes
  You can start experimenting with LOCAL IVF indexes using the included code. These are not complete scenarios but rather a series of SQL commands to help you get started on your own testing.