SYNC_INDEX

Synchronizes the index to process inserts, updates, and deletes to the base table.

Note: Because CTX_DDL.SYNC_INDEX issues implicit commits, calling CTX_DDL.SYNC_INDEX in a trigger is strongly discouraged. Doing so can result in errors being raised, as both SYNC_INDEX and post-commit $R LOB maintenance try to update the same $R LOB.

Syntax

CTX_DDL.SYNC_INDEX(
    idx_name        IN  VARCHAR2 DEFAULT NULL
    memory          IN VARCHAR2 DEFAULT NULL,
    part_name       IN VARCHAR2 DEFAULT NULL,
    parallel_degree IN NUMBER DEFAULT 1
    maxtime         IN NUMBER DEFAULT NULL,
    locking         IN NUMBER DEFAULT LOCK_WAIT
);

idx_name

Specify the name of the index to synchronize.

Note: When idx_name is null, all CONTEXT and CTXRULE indexes that have pending changes are synchronized. You must be connected as ctxsys to perform this operation. Each index or index partition is synchronized in sequence, one after the other. Because of this, the individual syncs are performed with locking set to NOWAIT and maxtime set to 0. Any values that you specify for locking or maxtime on the SYNC_INDEX call are ignored. However, the memory and parallel_degree parameters are passed on to the individual synchronizations.

memory

Specify the runtime memory to use for synchronization. This value overrides the DEFAULT_INDEX_MEMORY system parameter.

The memory parameter specifies the amount of memory Oracle Text uses for the synchronization operation before flushing the index to disk. Specifying a large amount of memory:

Specifying smaller amounts of memory increases disk I/O and index fragmentation, but might be useful when runtime memory is scarce.

part_name

If your index is a local index, then the part_name parameter is not mandatory. You may set part_name to specify the name of the index partition to synchronize, otherwise all index partitions are synchronized.

If your index is a global, nonpartitioned index, then specify NULL, which is the default.

parallel_degree

Specify the degree to run parallel synchronize. A number greater than 1 turns on parallel synchronize. The actual degree of parallelism might be smaller depending on your resources.

maxtime

Indicate a suggested time limit on the operation, in minutes. SYNC_INDEX will process as many documents in the queue as possible within the time limit. The maxtime value of NULL is equivalent to CTX_DDL.MAXTIME_UNLIMITED. This parameter is ignored when SYNC_INDEX is invoked without an index name, in which case maxtime value of 0 is used instead. The locking parameter is ignored for automatic syncs (that is, SYNC ON COMMIT or SYNC EVERY).

The time limit specified is treated as approximate. The actual time taken may be somewhat less than or greater than what you specify. The “time clock” for maxtime does not start until the SYNC lock is acquired.

locking

Configure how SYNC_INDEX deals with the situation where another sync is already running on the same index or index partition. When locking is ignored because SYNC_INDEX is invoked without an index name, then locking value of LOCK_NOWAIT is used instead. The locking parameter is ignored for automatic syncs (that is, SYNC ON COMMIT or SYNC EVERY).

The options for locking are:

Locking Parameter Description
CTX_DDL.LOCK_WAIT If another sync is running, wait until the running sync is complete, then begin sync. (In the event of not being able to get a lock, it will wait forever and ignore the maxtime setting.)
CTX_DDL.LOCK_NOWAIT If another sync is running, immediately returns without error.
CTX_DDL.LOCK_NOWAIT_ERROR If another sync is running, error “DRG-51313: timeout while waiting for DML or optimize lock” is raised.

Example

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

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

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

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

Notes

Related Topics