ADD_AUTO_OPTIMIZE

Adds an index or partition to the list of indexes subject to auto optimization. For partitioned indexes, the name of the partition must be specified, or else an error occurs. For global indexes, STAGE_ITAB must be enabled, or else an error occurs.

Note: In Oracle Database Release 21c, the procedures ADD_AUTO_OPTIMIZE and REMOVE_AUTO_OPTIMIZE, and the views CTX_AUTO_OPTIMIZE_INDEXES, CTX_USER_AUTO_OPTIMIZE_INDEXES and CTX_AUTO_OPTIMIZE_STATUS are deprecated.

The AUTO_OPTIMIZE feature improves the manageability of indexes that use the STAGE_ITAB feature. The STAGE_ITAB feature introduces a staging $G table to collect postings from newly synced documents.

The AUTO_OPTIMIZE feature has the following goals:

Enables you to register indexes and partitions to a background AUTO_OPTIMIZE process.
Automatically moves rows from the $G table to $I at appropriate times.
Movement of rows from $G to $I is done in a way to maximize query performance.

This procedure starts the background process if it has not already been started. The progress of the auto optimization is tracked by CTX logging.

The changes made by this procedure take effect immediately.

Note: The init.ora parameter JOB_QUEUE_PROCESSES must be set to one or higher. See Oracle Database Reference for more information about JOB_QUEUE_PROCESSES.

Syntax

CTX_DDL.ADD_AUTO_OPTIMIZE(

    idx_name       IN VARCHAR2,
    part_name      IN VARCHAR2 default NULL,
    optlevel       IN VARCHAR2 default CTX_DDL.OPTLEVEL_MERGE
);

idx_name

Specify the name of the index to add.

part_name

Specify the name of the partition to add.

optlevel

Specifies the optlevel of the CTX_DDL.OPTIMIZE_INDEX procedure. The only valid value for this parameter is merge.

Notes

The recommended sequence of steps for using auto optimization is:

Create the required indexes.
Add these indexes to the auto optimization list by using the CTX_DDL.ADD_AUTO_OPTIMIZE procedure.

The synchronize index operation automatically begins executing an auto optimization job (unless it is already running). This job continues until it runs out of work. Future synchronize index operations will automatically start executing the auto optimization job, if it is not already running.

“REMOVE_AUTO_OPTIMIZE” Oracle Text Application Developer’s Guide for information about using STAGE_ITAB with CONTEXT indexes SYNC_INDEX

ADD_AUTO_OPTIMIZE

Syntax

Notes

Related Topics