Partition Maintenance Operations with GLOBAL Vector Indexes

The table that a Partition Maintenance Operation is performed on must be a partitioned table. It can be partitioned using various methods, including RANGE, LIST, HASH, and COMPOSITE, and can have one or more global IVF and HNSW indexes.

The following Partition Maintenance Operations are supported on global IVF and HNSW indexes:

• ADD [SUB]PARTITION
• DROP [SUB]PARTITION
• MERGE [SUB]PARTITION
• SPLIT [SUB]PARTITION
• EXCHANGE [SUB]PARTITION
• MOVE [SUB]PARTITION
• RENAME [SUB]PARTITION
• COALESCE [SUB]PARTITION

In general, a Partition Maintenance Operation does not maintain its global vector indexes by default, unless the clause UPDATE GLOBAL INDEX or UPDATE INDEXES has been specified in the ALTER TABLE statement. One exception to this rule is when a new partition is added to a table that is partitioned on range or list. In this case, the clause UPDATE GLOBAL INDEX or UPDATE INDEXES is not required because the new partition has no data yet and thus no data needs to be maintained during the Partition Maintenance Operation.

If its status is VALID before a Partition Maintenance Operation, a global IVF or HNSW index is maintained automatically during the processing of the Partition Maintenance Operation when the clause UPDATE GLOBAL INDEXES or UPDATE INDEXES has been specified. After the Partition Maintenance Operation is complete, its status will still be VALID.

Note:

The use of the ONLINE clause in the ALTER TABLE statement is not currently supported. Executing a statement such as ALTER ABLE MERGE PARTITION ONLINE fails with an ORA-14808: table does not support ONLINE MERGE PARTITION due to unsupported vector index.

A global IVF index is composed of two tables:

• One called VECTOR$<base table name>_IVF_IDX$<object info>$IVF_FLAT_CENTROIDS, containing the list of 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 on the centroid ids. Each partition contains the base table vectors closely related (cluster) to the corresponding centroid id for that partition.

This is illustrated by the following diagram:

Description of the illustration global_ivf_index.eps

This structure is used to accelerate searches in the index by identifying first the centroid that is the closest to your query vector, and then using the corresponding centroid id to prune unnecessary partitions.

However, if the base table is partitioned on some relational data and your query is filtering on the base table partition key, then global IVF indexes are not optimal because they are completely independent of the base table partition key. For example, if you search the top-10 houses in California similar to a vectorized picture, the picture itself most likely has no relationship with the state of California. While your query benefits from the fact that your base table is partitioned by state, so you can search only the partition corresponding to California, the query still must look at pictures that may not be in California.

To further accelerate such type of queries, you have the possibility to create a local IVF index. The term local for an index refers to a one-to-one relationship between the base table partitions or subpartitions and the index partitions. For more information about local IVF indexes, see Partition Maintenance Operations with LOCAL IVF Vector Indexes.

• Experiment with Global IVF and HNSW Indexes
  You can start experimenting with GLOBAL IVF and HNSW 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.