Synchronizing the Index

When you synchronize the index, you process all pending updates and inserts to the base table. You can do this in PL/SQL with the CTX_DDL.SYNC_INDEX procedure. You can also control the duration and locking behavior for index synchronization with the CTX_DDL.SYNC_INDEX procedure.

Synchronizing the Index with SYNC_INDEX

The following example synchronizes the index with 2 megabytes of memory:

begin
ctx_ddl.sync_index('myindex', '2M');
end;

Starting with Oracle Database 12c Release 2 (12.2.0.1), you automatically merge rows from STAGE_ITAB back to the $I table by using SYNC_INDEX. This merging of rows happens when the number of rows in STAGE_ITAB ($G) exceeds the STAGE_ITAB_MAX_ROWS parameter (1 million by default). Therefore, you do not have to run merge optimization explicitly or schedule an auto optimize job.

Maxtime Parameter for SYNC_INDEX

The SYNC_INDEX procedure includes a maxtime parameter that, like OPTIMIZE_INDEX, indicates a suggested time limit in minutes for the operation. The SYNC_INDEX procedure processes as many documents in the queue as possible within the given time limit.

NULL maxtime is equivalent to CTX_DDL.MAXTIME_UNLIMITED.
The time limit is approximate. The actual time may be less than, or greater than, what you specify.
The ALTER INDEX... sync command has no changes because it is deprecated.
The maxtime parameter is ignored when SYNC_INDEX is invoked without an index name.
The maxtime parameter cannot be communicated for automatic synchronizations (for example, sync on commit or sync every).

Locking Parameter for SYNC_INDEX

The locking parameter of SYNC_INDEX enables you to configure how the synchronization works when another synchronization is already running on the index.

The locking parameter is ignored when SYNC_INDEX is invoked without an index name.
The locking parameter cannot be communicated for automatic synchronizations (that is, sync on commit or sync every).
When the locking mode is LOCK_WAIT, the mode waits forever and ignores the maxtime setting if it cannot get a lock.

The options are as follows:

Option	Description
`CTX_DDL.LOCK_WAIT`	If another `SYNC_INDEX` is running, wait until the running synchronization is complete, and then begin the new synchronization.
`CTX_DDL.LOCK_NOWAIT`	If another `SYNC_INDEX` is running, immediately return without error.
`CTX_DDL.LOCK_NOWAIT_ERROR`	If another `SYNC_INDEX` is running, immediately generate an error (DRG-51313: timeout while waiting for inserts, updates, or deletes or optimize lock).

See Also: Oracle Text Reference to learn more about the CTX_DDL.SYNC_INDEX statement syntax