Rebuilding Indexes Online

The Inverted File Flat (IVF) index may require a rebuild when significant DML activity, such as inserts, updates, or deletes occurs on the underlying base table. Heavy modifications can cause the index structure to become suboptimal, resulting in reduced search efficiency or accuracy. To ensure optimal accuracy, it is important to perform suitable index reorganization as the data changes over time. Internal testing has demonstrated that queries per second (QPS) increases significantly following index reorganization. The IVF indexes can be rebuilt either manually or automatically.

Manual IVF Index Rebuild

Manual reorganization gives the user control to initiate an index rebuild when needed. This can be particularly useful to maintain index performance and ensure data consistency as your data evolves. The manual IVF index reorganization supports both Global and Local index rebuilding:

Global IVF Index Reorganization
Global IVF index reorganization rebuilds the entire index. You can initiate a global IVF index rebuild using the following syntax:
```
ALTER INDEX <ivf_index_name> REBUILD ONLINE [PARALLEL
      <degree_of_parallelism>];
```
Starting with Oracle AI Database 26ai, Release Update 23.26.1, users can also specify parameters when rebuilding an IVF index. This allows users to select a different set of parameters from those used during the initial index creation, without needing to drop and recreate the index. This flexibility makes it easier to tune index performance or optimize for new data patterns by simply running the index rebuild command with updated parameter values. For more information on paramaters, see Inverted File Flat CREATE INDEX.
```
ALTER INDEX <ivf_index_name> REBUILD 
[WITH TARGET ACCURACY] 
[PARAMETERS (
      NEIGHBOR PARTITIONS <number of partitions>,
      SAMPLES_PER_PARTITION <number of samples>,
      MIN_VECTORS_PER_PARTITION <minimum number of vectors per partition>
            )
]
```
Local IVF Index Reorganization
Local IVF indexing organizes both your data and its search index into matching partitions. Each partition of the data table has a corresponding partition in the index, and related data within each partition is grouped together. This structure improves search performance and simplifies management as data grows or changes. For more information, see Understand Local Inverted File Flat Vector Indexes. Consequently, it allows you to rebuild only the selected partition rather than the entire index, enhancing efficiency and reducing maintenance overhead.
To rebuild a specific partition of the index, use the following syntax:
```
ALTER INDEX <ivf_index_name> REBUILD PARTITION <partition_name> [PARALLEL
      <degree_of_parallelism>] ONLINE;
```

Auto IVF Index Reorganization

Automatic index optimization enhances index performance and minimizes manual maintenance by continuously monitoring index usage and health. When necessary, it automatically rebuilds indexes to ensure optimal efficiency. By default, this feature is enabled for global IVF indexes. A background process periodically scans all IVF indexes at fixed intervals, assessing each for potential optimization. It tracks DML activity, such as inserts, updates, or deletes on corresponding tables. When DML changes surpass a configurable threshold (configured parameters), the system automatically schedules a rebuild for the affected index. This proactive approach ensures that indexes remain efficient and performant without user intervention.

The Automatic Optimization framework is fully configurable, offering several parameters to customize its operation. Users can control how often the background process runs, set the sensitivity to DML changes, and define how rebuild operations are scheduled. These configurable options enable users to fine-tune the automation to match their workload characteristics and performance requirements.

Table 6-2 Configuration Parameters

Parameter	Description
`VECTOR_INDEX_OPTIMIZATION_BACKGROUND`	Enables or disables the vector index background coordinator process that manages and optimizes vector indexes in the background for both global and local IVF indexes. Valid values: `VECTOR_INDEX_OPTIMIZATION_BACKGROUND_DISABLED`: disables the vector index background coordinator process. `VECTOR_INDEX_OPTIMIZATION_BACKGROUND_ENABLED`: enables the vector index background coordinator process for both global and local IVF indexes. `VECTOR_INDEX_OPTIMIZATION_BACKGROUND_ENABLED_GLOBAL`: enables the vector index background coordinator process for global IVF indexes only. Default value: `VECTOR_INDEX_OPTIMIZATION_BACKGROUND_ENABLED_GLOBAL`
`VECTOR_INDEX_OPTIMIZATION_BACKGROUND_INTERVAL`	Specifies the frequency, in seconds, at which the background coordinator process optimizes the index. Valid values: 600 to 2³¹ - 1 Default value: 1800 seconds
`VECTOR_INDEX_OPTIMIZATION_BACKGROUND_MAXPROCS`	Specifies the maximum number of processes scheduled for vector index optimization by the background coordinator process. Valid values: 1 to 2³¹ - 1 Default value: 1
`VECTOR_INDEX_OPTIMIZATION_DML_THRESHOLD`	Specifies the percentage threshold of DML (insert, update, or delete) operations executed on the base table that will trigger index optimization actions. For example, if the threshold is set to 20%, index optimization is initiated each time the number of DML operations equals 20% of the base table’s current row count. This ensures timely optimization in response to significant data changes. Valid values: 1 to 2³¹ - 1 Default value: 20
`VECTOR_INDEX_OPTIMIZATION_MIN_ROWS`	Specifies the minimum row count threshold that a base table must meet before index optimization actions are initiated. Valid values: 1 to 2³¹ - 1 Default value: 100,000
`VECTOR_INDEX_OPTIMIZATION_TASK_BACKOFF_MINS`	Sets a backoff time (in minutes) to be observed after a failed automatic index optimization action. During this backoff period, the vector index optimization background coordinator will not schedule any further optimization tasks for the affected index until the specified time has elapsed since the most recent failure. Valid values: 0 to 2³¹ - 1 Default value: 60 minutes
`VECTOR_INDEX_OPTIMIZATION_TASK_JOB_CLASS`	Allows users to specify a job class for index optimization tasks that are scheduled to run in the background (as part of PL/SQL procedure call as well as auto-optimization background coordinator actions). Valid values: `VECTOR_INDEX_OPTIMIZATION_JOB_CLASS_DEFAULT`: assigns background jobs scheduled with `DBMS_SCHEDULER` a default priority level. These jobs are managed with standard resource allocation and scheduling. `VECTOR_INDEX_OPTIMIZATION_JOB_CLASS_HIGH`: assigns background jobs scheduled with `DBMS_SCHEDULER` a high priority level. These jobs are given preference in resource allocation and scheduling, allowing critical or time-sensitive tasks to run ahead of lower-priority jobs. Default value: `VECTOR_INDEX_OPTIMIZATION_JOB_CLASS_DEFAULT`

Configuring Parmaters using the DBMS_VECTOR_ADMIN Package

The DBMS_VECTOR_ADMIN package provides procedures to configure and retrieving these parameter values. For more information, see DBMS_VECTOR_ADMIN. The following procedures are available:

Table 6-3 DBMS_VECTOR_ADMIN PACKAGE

Subprogram	Description
`SET_PARAMETER`	Sets a value for the specified input parameter.
`GET_PARAMETER`	Gets the current value for the specified input parameter.

Parent topic: Inverted File Flat Vector Indexes Online Rebuild