1. Application Developer's Guide
  2. Maintaining Oracle Text Indexes

5 Maintaining Oracle Text Indexes

Learn how to manage indexing errors or failures, re-create or rebuild indexes, drop custom index preferences, manage synchronization and optimization tasks, and automate index maintenance operations.

5.1 Viewing Index Errors

Sometimes an indexing operation might fail or it might not complete successfully. When the system encounters an error during row indexing, it logs the error in an Oracle Text view.

You can view errors on your indexes with CTX_USER_INDEX_ERRORS. View errors on all indexes as CTXSYS with CTX_INDEX_ERRORS.

For example, to view the most recent errors on your indexes, enter the following statement:

SELECT err_timestamp, err_text 
  FROM ctx_user_index_errors 
  ORDER BY err_timestamp DESC;

To clear the view of errors, enter:

DELETE FROM ctx_user_index_errors;

This view is cleared automatically when you create a new index.

See Also:

Oracle Text Reference to learn more about index error views

5.2 Dropping an Index

You must drop an existing index before you can re-create it with the CREATE INDEX statement.

Drop an index by using the DROP INDEX statement in SQL.

If you try to create an index with an invalid PARAMETERS string, then you still need to drop it before you can re-create it.

For example, to drop an index called newsindex, enter the following SQL statement: 

DROP INDEX newsindex;

If Oracle Text cannot determine the state of the index (for example, because of an indexing malfunction), you cannot drop the index. Instead use:

DROP INDEX newsindex FORCE;

See Also:

Oracle Text Reference to learn more about the DROP INDEX statement

5.3 Resuming a Failed Index

You can sometimes resume a failed index by using the ALTER INDEX statement. You typically resume a failed index after you have investigated and corrected the index failure. You cannot resume all index failures.

Index optimization commits at regular intervals. Therefore, if an optimization operation fails, then all optimization work up to the commit point was already saved.

See Also:

Oracle Text Reference to learn more about the ALTER INDEX statement syntax

The following statement resumes the indexing operation on newsindex with 10 megabytes of memory: 

ALTER INDEX newsindex REBUILD PARAMETERS('resume memory 10M');

5.4 Re-creating an Index

This section describes the procedures for re-creating an index. During the re-creation process, you can query the index normally.

5.4.1 Re-creating a Global Index

Oracle Text provides RECREATE_INDEX_ONLINE to re-create a CONTEXT index with new preferences, while preserving inserts, updates, and deletes on the base table. You can use RECREATE_INDEX_ONLINE in a single-step procedure to re-create a CONTEXT index online for global indexes. Because the new index is created alongside the existing index, this operation requires storage that is roughly equal to the size of the existing index. Also, because the RECREATE_INDEX_ONLINE operation is performed online, you can perform inserts, updates, and deletes on the base table during the operation. All insert, update, and delete operations that occur during the re-creation process are logged into an online pending queue.

  • After the re-creation operation is complete, new information may not be immediately reflected. As with creating an index online, you should synchronize the index after the re-creation operation is complete to bring it fully up-to-date.

  • Synchronizations issued against the index during the re-creation operation are processed against the existing data. Synchronizations are blocked when queries return errors.

  • Optimize commands issued against the index during the re-creation operation return immediately without error and without processing.

  • During RECREATE_INDEX_ONLINE, you can query the index normally most of the time. Queries return results based on the existing index and policy until after the final swap. Also, if you issue insert, update, and delete operations and synchronize them, then you will be able to see the new rows when you query the existing index.

Note:

Transactional queries are not supported with RECREATE_INDEX_ONLINE.

Re-creating a Global Index with Time Limit for Synch

You can control index re-creation to set a time limit for SYNC_INDEX during nonbusiness hours and incrementally re-create the index. Use the CREATE_SHADOW_INDEX procedure with POPULATE_PENDING and maxtime.

Re-creating a Global Index with Scheduled Swap

With CTX_DDL.EXCHANGE_SHADOW_INDEX, you can perform index re-creation during nonbusiness hours when query failures and DML blocking can be tolerated.

See Also:

5.4.2 Re-creating a Local Partitioned Index

If the index is locally partitioned, you cannot re-create the index in one step. You must first create a shadow policy, and then run the RECREATE_INDEX_ONLINE procedure for every partition. You can specify SWAP or NOSWAP, which indicates whether re-creating the index for the partition swaps the index partition data and index partition metadata.

You can also use this procedure to update the metadata (for example, the storage preference) of each partition when you specify NOPOPULATE in the parameter string. This keyword is useful for incremental building of a shadow index through time-limited synchronization. If you specify NOPOPULATE, then NOSWAP is silently enforced.

  • When all partitions use NOSWAP, the storage requirement is approximately equal to the size of the existing index. During re-creation of the index partition, because no swapping is performed, queries on the partition are processed normally. Queries spanning multiple partitions return consistent results across partitions until the swapping stage is reached.

  • When the partitions are rebuilt with SWAP, the storage requirement for the operation is equal to the size of the existing index partition. Because index partition data and metadata are swapped after re-creation, queries spanning multiple partitions do not return consistent results from partition to partition, but they will always be correct with respect to each index partition.

  • If you specify SWAP, then insert, update, and delete operations and synchronization on the partition are blocked during the swap process.

Re-creating a Local Index with All-at-Once Swap

You can re-create a local partitioned index online to create or change preferences. The swapping of the index and partition metadata occurs at the end of the process. Queries spanning multiple partitions return consistent results across partitions when the re-creation is in process, except at the end when EXCHANGE_SHADOW_INDEX is running.

Scheduling Local Index Re-creation with All-at-Once Swap

With RECREATE_INDEX_ONLINE of the CTX.DDL package, you can incrementally re-create a local partitioned index, where partitions are all swapped at the end.

Re-creating a Local Index with Per-Partition Swap

Instead of swapping all partitions at once, you can re-create the index online with new preferences, and each partition is swapped as it is completed. Queries across all partitions may return inconsistent results during this process. This procedure uses CREATE_SHADOW_INDEX with RECREATE_INDEX_ONLINE.

See Also:

Oracle Text Reference for complete information about RECREATE_INDEX_ONLINE

5.5 Rebuilding an Index

You can rebuild a valid index by using ALTER INDEX. Rebuilding an index does not allow most index settings to be changed. You might rebuild an index when you want to index with a new preference. Generally, there is no advantage in rebuilding an index over dropping it and re-creating it with the CREATE INDEX statement.

See Also:

"Re-creating an Index" for information about changing index settings

The following statement rebuilds the index and replaces the lexer preference with my_lexer:

ALTER INDEX newsindex REBUILD PARAMETERS('replace lexer my_lexer');

5.6 Dropping a Preference

You might drop a custom index preference when you no longer need it for indexing.

You drop index preferences with the CTX_DDL.DROP_PREFERENCE procedure.

Dropping a preference does not affect the index that is created from the preference.

See Also:

Oracle Text Reference to learn more about the syntax for the CTX_DDL.DROP_PREFERENCE procedure

The following code drops the my_lexer preference: 

begin
ctx_ddl.drop_preference('my_lexer');
end;

5.7 Managing DML Operations for a CONTEXT Index

DML operations refer to when documents are inserted, updated, or deleted from the base table.

This section describes how you can view, synchronize, and optimize the Oracle Text CONTEXT index for DML operations. This section contains the following topics:

5.7.1 Viewing Pending DML Operations

When you insert or update documents in the base table, their rowids are held in a DML queue until you synchronize the index. You can view this queue in the CTX_USER_PENDING view.

For example, to view pending DML operations on your indexes, enter the following statement:

SELECT pnd_index_name, pnd_rowid, to_char(
        pnd_timestamp, 'dd-mon-yyyy hh24:mi:ss'
        ) timestamp FROM ctx_user_pending;

This statement gives output in the following form:

PND_INDEX_NAME                 PND_ROWID          TIMESTAMP
------------------------------ ------------------ --------------------
MYINDEX                        AAADXnAABAAAS3SAAC 06-oct-1999 15:56:50

See Also:

Oracle Text Reference to learn more about the CTX_USER_PENDING view

5.7.2 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;

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).

Note:

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 (10K by default). Therefore, you do not have to run merge optimization explicitly or schedule an auto optimize job.

See Also:

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

5.7.3 Optimizing the Index

The CONTEXT index is an inverted index where each word contains the list of documents that contain that word. For example, after a single initial indexing operation, the word DOG might have the following entry: 

DOG DOC1 DOC3 DOC5

Frequent index synchronization ultimately causes fragmentation of your CONTEXT index. Index fragmentation can adversely affect query response time. Therefore, to reduce fragmentation and index size and to ensure optimal query performance, allow time to optimize your CONTEXT index.

To schedule an auto optimize job, you must explicitly set STAGE_ITAB_MAX_ROWS to 0 to disable the automatic merging that now happens with SYNC_INDEX.

To optimize an index, Oracle recommends that you use CTX_DDL.OPTIMIZE_INDEX. To understand index optimization, you must understand the structure of the index and what happens when it is synchronized. This section contains the following topics:

See Also:

Oracle Text Reference for the CTX_DDL.OPTIMIZE_INDEX statement syntax and examples

5.7.3.1 Index Fragmentation

When you add new documents to the base table, the index is synchronized by adding new rows. For example, if you add the DOC 7 document with the word dog and synchronize the index, you now have: 

DOG DOC1 DOC3 DOC5
DOG DOC7

Subsequent inserts, updates, or deletes also create new rows, as follows:

DOG DOC1 DOC3 DOC5
DOG DOC7
DOG DOC9
DOG DOC11

Index fragmentation occurs when you add new documents and synchronize the index. In particular, background inserts, updates, or deletes, which synchronize the index frequently, generally produce more fragmentation than batch mode synchronization.

When you perform batch processing less frequently, you reduce fragmentation because you produce longer document lists with a reduced number of rows in the index.

You can reduce index fragmentation by optimizing the index in either FULL or FAST mode with CTX_DDL.OPTIMIZE_INDEX.

5.7.3.2 Document Invalidation and Garbage Collection

When you remove documents from the base table, Oracle Text marks the document as removed but does not immediately alter the index.

Because the old information takes up space and can cause extra overhead at query time, you must remove the old information from the index by optimizing it in FULL mode. This process is called garbage collection. Optimizing in FULL mode for garbage collection is necessary when you perform frequent updates or deletes to the base table.

5.7.3.3 Single Token Optimization

In addition to optimizing the entire index, you can optimize single tokens. You can use token mode to optimize index tokens that are frequently searched, without spending time on optimizing tokens that are rarely referenced.

For example, you can specify that only the token DOG be optimized in the index, if you know that this token is updated and queried frequently.

An optimized token can improve query response time for the token.

To optimize an index in token mode, use CTX_DDL.OPTIMIZE_INDEX.

5.7.3.4 Viewing Index Fragmentation and Garbage Data

With the CTX_REPORT.INDEX_STATS procedure, you can create a statistical report on your index. The report includes information on optimal row fragmentation, a list of most fragmented tokens, and the amount of garbage data in your index. Although this report might take a long time to run for large indexes, it can help you decide whether to optimize your index.

See Also:

Oracle Text Reference to learn more about using the CTX_REPORT.INDEX_STATS procedure

5.8 Using Automatic Maintenance for an Index

Instead of manually managing synchronization tasks for your indexes, you can automate CTX_DDL.SYNC_INDEX operations using automatic maintenance.

5.8.1 About Automatic Maintenance

Index maintenance is the process of updating index data structures (in-memory and on-disk) as a result of performing DML operations.

Overview

Indexes with automatic maintenance are synchronized in the background without any user intervention.

Automatic maintenance is the default method for synchronizing Oracle Text CONTEXT and search indexes (Oracle Text, JSON, and XML search indexes) that are created in Oracle Database 23ai and later releases.

Both the automatic maintenance and synchronization (SYNC) methods involve processing pending updates, inserts, and deletes to the base table. However, the automatic maintenance and SYNC specifications are orthogonal. Automatic maintenance uses an asynchronous maintenance framework to perform SYNC operations in the background, and provides the following capabilities:

  • Eliminates time-based or manual SYNC operations:

    In an automatic maintenance mode, IRnn background processes automatically perform index maintenance operations in an optimal manner. This feature internally determines an optimal synchronization interval (based on the DML arrival) and automatically schedules background SYNC operations, as required. You cannot override the automatically determined intervals.

    For detailed information about this background mechanism process, see Asynchronous Maintenance Framework.

  • Reduces the frequency of background jobs:

    Background processes maintain indexes rather than the database scheduler. The background mechanism breaks each CTX_DDL.SYNC_INDEX operation into separate events (sync stages) and launches each event only when needed.

  • Provides the default maintenance configuration:

    These indexes do not require you to configure a SYNC type or set any synchronization interval. By default, indexes are configured with a combination of automatic maintenance and SYNC (MANUAL). No other SYNC settings are compatible with these indexes.

    Note that the SYNC (MANUAL) behavior is different in this mode. Unlike the regular SYNC (MANUAL) type (where you must manually synchronize an index), here CTX_DDL.SYNC_INDEX is automatically called in the background.

Why and When to Use Automatic Maintenance?

Oracle recommends that you use automatic maintenance in cases where sync requirements for indexes are not clear or you want to synchronize a large number of indexes in an optimal manner.

In addition to reducing the administrative tasks of managing your indexes, the benefit of using this framework is that it automatically determines when a background SYNC operation needs to be performed, by tracking the DML queue. It also provides more control over the frequency of different background jobs running at any given time, instead of creating independent jobs for each index or index partition per pluggable database (PDB). As a result, automatic maintenance helps in reducing the workload on database resources, eliminates scheduling conflicts, and enhances query performance.

With SYNC (EVERY), which also enables automatic background synchronization, you must manually specify sync interval using interval-string. Although SYNC (EVERY) allows you to explicitly control the synchronization interval, automatic maintenance provides an efficient usage of database resources especially when supporting multiple PDBs. In addition, SYNC (EVERY) may result in excessive launching of background sync jobs, based on the user's estimate of how frequently new index data may arrive.

What is Manual Maintenance?

Manual maintenance is a non-automatic maintenance mode that provides the pre-release 23ai synchronization behavior.

In a manual maintenance mode, you can specify SYNC types, such as SYNC MANUAL, SYNC EVERY interval-string, or SYNC ON COMMIT. The MAINTENANCE MANUAL index parameter sets your indexes to manual maintenance.

After upgrading to a new release, existing indexes continue to use the previously specified method of synchronization. For example, after upgrading to Oracle Database 23ai, existing indexes are set to manual maintenance with the previously specified SYNC settings. If you did not specify any SYNC setting before the upgrade, then the index uses the default SYNC type. That is, SYNC MANUAL for Oracle Text CONTEXT indexes and SYNC ON COMMIT for JSON and XML search indexes. If required, you can manually enable automatic maintenance for such indexes.

How to Configure the MAINTENANCE Parameter?

The MAINTENANCE parameter controls the maintenance type (mode) for your index. You can set the MAINTENANCE parameter globally and not per partition. This means that the maintenance type specified for an index applies to all index partitions.

The supported maintenance types are:

  • MAINTENANCE AUTO (default): Sets your indexes to automatic maintenance.

    By default, you do not need to configure automatic maintenance while creating an index. This example creates a JSON search index that has the default behavior (no PARAMETERS clause): 
    CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document) 
    FOR JSON;
    This example creates a JSON search index by explicitly specifying MAINTENANCE AUTO using the PARAMETERS clause:
    CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
    FOR JSON PARAMETERS ('MAINTENANCE AUTO');

  • MAINTENANCE MANUAL: Sets your indexes to manual maintenance.

    This example disables automatic maintenance on a new JSON search index by specifying MAINTENANCE MANUAL using the PARAMETERS clause:
    CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
    FOR JSON PARAMETERS('MAINTENANCE MANUAL');

For detailed information about configuring these parameters, see Enabling and Disabling Automatic Maintenance.

You can switch between the automatic and manual maintenance modes using ALTER INDEX. This command alters only the synchronization options, and thus you do not need to rebuild the index. When set to manual maintenance, if you do not explicitly specify any SYNC type, then the index uses the default SYNC type.

Requirements and Restrictions

  • Database compatibility for the asynchronous maintenance framework is Oracle Database 21.0.0.0 and later.

  • The combination of automatic maintenance with the following parameters is not supported:

    • FAST_QUERY

    • ASYNCHRONOUS_UPDATE

    • TRANSACTIONAL

    • SYNC (ON COMMIT) and SYNC (EVERY)

    Running any of the preceding combinations results in an error, prompting you to use a compatible mode for your index.

  • The MAINTENANCE AUTO indexes do not support shadow indexes.

5.8.2 Asynchronous Maintenance Framework

In an automatic maintenance mode, IRnn background processes perform index maintenance operations, which provides a better scalability of background jobs and enhances query performance.

List of Maintenance Events

Each SYNC operation consists of separate events (stages) that can concurrently run in the background.

Event Description

SYNC-Mapping

(Sync-M)

Reads the $B catalog table to find the next DocID, and allocates DocID to each ROWID. Next, it reads the contents of the $C commit journal, sorts it on ROWID, and decides which DocIDs must be removed or added. Next, it performs deletes and inserts on the $K mapping table. Then, it adds the removed DocIDs to the $N garbage collection table. Finally, it deletes all the rows read from $C.

During a failure, these events are retried and also broadcasted to other Oracle Real Application Clusters (Oracle RAC) nodes.

SYNC-Mapping Timeout

(Sync-MT)

Specifies the Sync-M timeout events, generated on a commit. These are processed only by timeout actions and not by interrupt actions.

During a timeout, the Sync-MT events are converted into the Sync-M events.

SYNC-Ranges

(Sync-R)

Assigns DocID ranges generated by Sync-M as READY for postings stage. It reads all available NEW ranges to determine the minimum of the first DocID and the maximum on the last DocID. Next, it deletes all NEW ranges and inserts a number of equally-sized READY ranges. The size of the range is determined based on the average size of each document for the index or index partition.

During a failure, these events are retried and also broadcasted to other RAC nodes.

SYNC-Scheduler

(Sync-S)

Schedules Sync-P based on the number of READY ranges generated by Sync-R. Depending on the value, it schedules either a serial or concurrent Sync-P event.

SYNC-Postings

(Sync-P)

Generates postings lists that contain new index data. Sync-P starts by getting a READY range. During scheduling, it decides the number of workers for running the event.

On Oracle RAC systems, concurrent events are also broadcasted to other nodes for running as the SYNC-Postings Concurrent (Sync-PC) events.

During a failure, the Sync-S events are scheduled with increased iteration. On Oracle RAC systems, the failed Sync-PC and SYNC-Postings Serial (Sync-PS) events are broadcasted as Sync-PS events.

SYNC-Postings Serial

(Sync-PS)

Runs Sync-P serially. The postings are generated in SGA batches.

SYNC-Postings Concurrent

(Sync-PC)

Runs Sync-P concurrently. Can schedule multiple ranges to run concurrently and independently without contention.

SYNC-Writer

(Sync-W)

Writes SGA batches of postings lists to disk. Sync-W events can run concurrently with itself. However, these events cannot run with SYNC-Cleanup batches (Sync-C).

Sync-W events are never broadcasted to other RAC nodes because they process SGA batches, which are local to the node that generated them.

SYNC-Cleanup batches

(Sync-C)

Cleans up WRITE ranges in $B that do not have any SGA batches associated with them due to a failure. These events are retried in the subsequent run of Sync-P.

SYNC-Inspect

(Sync-I)

Inspects index and index partitions (checks the $C and $B tables) to find if any events are missing.

During a failure, the Sync-I events are not retried and are also not broadcasted to other RAC nodes.

MONITOR

Schedules the Sync-I events for each index or index partition and the EClean events for each pluggable database (PDB). The Scheduler schedules the actual Monitor event, which is further processed by the Monitor worker.

EVENT Stats

(EStat)

Terminal event that is processed by Writer workers (Sync-W events). It writes event stats for all completed events in a PDB.

EVENT Stats Clean up

(EClean)

Cleans up persisted event stats (older than the PDB-specific threshold) from the dictionary.

OPTIMIZE-Scheduler Timeout

(Opti-ST)

Prevents dealing with large postings lists gaps that are crated after the order completion of the Sync-W events.

OPTIMIZE-Scheduler

(Opti-S)

Determines the maximum DocID for which there are no gaps in postings lists due to the out-of-order processing of the WRITE ranges by the Sync-W events.

The Opti-S event aggregates $G token counts that are stored in $B by the Sync-W events, and it schedules the Opti-M event when the aggregate count reaches a user-specified threshold.

OPTIMIZE-Merge

(Opti-M)

Terminal event that does not perform the actual MERGE operation. Instead, it schedules the DBMS_SCHEDULER Optimize Merge operation.

Background Mechanism Process

The main Scheduler background process checks for the workload from all indexes at predefined intervals (that is, every 3 seconds by default). It then assigns the workload to a Worker background process, which reads events one at a time and processes them based on the event type. The index is synchronized immediately after the Worker process runs CTX_DDL.SYNC_INDEX. Apart from the Scheduler and Worker processes, the Monitor background process helps in recovering lost events.

A CTX_DDL.SYNC_INDEX call performs the following steps in an order:

  1. Resets all waiting events for an index or index partition:

    When an event fails, it adds the event to the Waiting Queue with progressively increasing delay. Even after the issue is corrected, the event continues to wait for the current delay to elapse. To track such delays, it schedules retry events at an incremented level. This means that the Scheduler process first moves a retry event into an Event Queue. From the Event Queue, the Scheduler moves it into the Waiting Queue, then to the Ready Queue, and finally allocates the event to the Worker process.

    In addition to incremental levels, each event has a retry iteration. On long retries (that is, when a retry event is not the same as the original event), it increments the iteration instead of the level and initializes the level to the iteration.

    CTX_DDL.SYNC_INDEX can force immediate re-execution of the event, which moves all relevant Waiting Queue events to the Event Queue and also resets the level and iteration. If the event fails again, then it restarts from the starting incremental level or iteration.

  2. Performs Sync-M in the foreground:

    CTX_DDL.SYNC_INDEX waits for the background maintenance to finish. However, instead of waiting for all events to finish, it calls Sync-M in the foreground and gets the maximum DocID that is allocated. It uses this DocID to ignore all future events.

  3. Schedules other stages of SYNC in the background:

    CTX_DDL.SYNC_INDEX posts the Scheduler process so that it can immediately start processing pending events.

  4. Waits for the completion of background processing:

    The waiting is controlled by the locking parameter when it is set to CTX_DDL.LOCK_WAIT. For all other values, CTX_DDL.SYNC_INDEX returns after completing Sync-M.

    The values of the memory, parallel_degree, maxtime, and direct_path parameters are ignored.

    If some background events are delayed or cannot complete, then CTX_DDL.SYNC_INDEX returns ORA-30608 and logs an error message in the catalog views.

Differences in SYNC Behavior Between Automatic Maintenance and Manual Maintenance

Compare the differences in the synchronization behavior between automatic maintenance and manual maintenance, and how different events are processed during a CTX_DDL.SYNC_INDEX operation:

Behavior Automatic Maintenance Manual Maintenance

Background mechanism

In an automatic maintenance mode, background processes maintain indexes. The background mechanism breaks each SYNC operation into separate events that are run concurrently with each other, as needed.

For example, Sync-S launches Sync-P to pick up new index data only when Sync-R generates READY ranges.

In a manual maintenance mode, the DBMS_SCHEDULER background jobs maintain indexes. The background mechanism implements all sync events (Sync-M, Sync-R, and Sync-P) together as a single SYNC operation.

SYNC types

These indexes are preconfigured with a combination of automatic maintenance and SYNC (MANUAL).

Unlike the regular SYNC (MANUAL) type (where you must manually call CTX_DDL.SYNC_INDEX), here CTX_DDL.SYNC_INDEX is automatically called in the background at optimal intervals.

The other SYNC types, such as SYNC ON COMMIT and SYNC EVERY are not supported with automatic maintenance.

Running SYNC (MANUAL), SYNC (ON COMMIT), or SYNC (EVERY) launches a SYNC operation in the foreground for each index or index partition.

Catalog views

  • CTX_BACKGROUND_EVENTS

  • CTX_USER_BACKGROUND_EVENTS

  • V$TEXT_WAITING_EVENTS

  • CTX_AUTOSYNC_JOBS

  • CTX_AUTOSYNC_STATUS

  • CTX_USER_AUTOSYNC_JOBS

  • CTX_USER_AUTOSYNC_STATUS

Related Topics

5.8.3 Enabling and Disabling Automatic Maintenance

Automatic maintenance is enabled by default for new Oracle Text CONTEXT and search indexes. Learn how to explicitly specify automatic maintenance while creating an index, or disable it to override the default behavior and enable SYNC instead.
  1. To explicitly specify automatic maintenance for a new index, use the MAINTENANCE AUTO keyword in the PARAMETERS clause of the CREATE INDEX or CREATE SEARCH INDEX statement.
    • For an Oracle Text index: 
      CREATE INDEX CTX_IDX ON CTX_TAB(DOC)
    INDEXTYPE IS CTXSYS.CONTEXT 
    PARAMETERS('MAINTENANCE AUTO');
    • For an Oracle Text search index: 
      CREATE SEARCH INDEX CTX_IDX ON CTX_TAB(DOC)
    PARAMETERS('MAINTENANCE AUTO');
    • For a JSON search index:
      CREATE SEARCH INDEX JSON_IDX ON CTX_TAB(JSON_DOC) FOR JSON
    PARAMETERS('MAINTENANCE AUTO');
    • For an XML search index:
      CREATE SEARCH INDEX XML_IDX ON CTX_TAB(XML_DOC) FOR XML
    PARAMETERS('MAINTENANCE AUTO');
  2. To disable automatic maintenance for a new index, use the MAINTENANCE MANUAL keyword in the CREATE INDEX or CREATE SEARCH INDEX clause.

    This will set your index to manual maintenance. If you do not specify any SYNC type, then the index will use the default SYNC settings. For example, SYNC MANUAL for Oracle Text CONTEXT indexes and SYNC ON COMMIT for JSON and XML search indexes.

    • For an Oracle Text index: 
      CREATE INDEX CTX_IND ON CTX_TAB(DOC) 
INDEXTYPE IS CTXSYS.CONTEXT 
PARAMETERS('MAINTENANCE MANUAL');
    • For an Oracle Text search index: 
      CREATE SEARCH INDEX CTX_IDX ON CTX_TAB(DOC)
    PARAMETERS('MAINTENANCE MANUAL');
    • For a JSON search index:
      CREATE SEARCH INDEX JSON_IDX ON CTX_TAB(JSON_DOC) FOR JSON
    PARAMETERS('MAINTENANCE MANUAL');
    • For an XML search index:
      CREATE SEARCH INDEX XML_IDX ON CTX_TAB(XML_DOC) FOR XML
    PARAMETERS('MAINTENANCE MANUAL');
  3. To override the default SYNC settings for the index set to manual maintenance, specify the required SYNC type:

    • MANUAL: To manually synchronize the index on demand.

      For example: 
      ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);

    • ON COMMIT: To synchronize the index immediately after a commit.

      For example: 
      ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS('REPLACE METADATA SYNC(ON COMMIT) MAINTENANCE MANUAL');

    • EVERY "interval-string": To synchronize the index at a regular interval.

      For example, starting every 20 minutes:
      ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS('REPLACE METADATA SYNC(EVERY "freq=minutely;interval=20") MAINTENANCE MANUAL');

Related Topics

5.8.4 Switching between Automatic and Manual Maintenance

You can use ALTER INDEX to switch between MAINTENANCE AUTO and MAINTENANCE MANUAL, without rebuilding the index. While changing modes, you must specify compatible Maintenance type and SYNC type combinations.

Guidelines for Switching between Modes

  • MAINTENANCE AUTO is supported with SYNC (MANUAL). By default, all indexes with automatic maintenance are specified with this combination.

  • A combination of MAINTENANCE AUTO and SYNC ON COMMIT or SYNC (EVERY) is not supported. If you want to specify MAINTENANCE AUTO for indexes that also use SYNC (ON COMMIT) or SYNC (EVERY), then you must first set such indexes to SYNC (MANUAL).

  • Static dictionary view CTX_USER_INDEXES contains information about existing Oracle Text CONTEXT and search indexes for the current user. For example, this query lists the SYNC and Maintenance types for an Oracle Text index set to MAINTENANCE AUTO: 

    SQL> SELECT IDX_NAME, IDX_SYNC_TYPE, IDX_MAINTENANCE_TYPE FROM CTX_USER_INDEXES;

IDX_NAME                IDX_SYNC_TYPE                IDX_MAINTENANCE_TYPE
----------------------- ---------------------------- ----------------------
DOCIDX                  MANUAL                       AUTO

Switching Indexes to Automatic Maintenance

This table provides ALTER INDEX examples with various SYNC types, while altering your index from MAINTENANCE MANUAL to MAINTENANCE AUTO:

Setting Maintenance and SYNC Type Example

SYNC (MANUAL)

to

MAINTENANCE AUTO

IDX_SYNC_TYPE: MANUAL

IDX_MAINTENANCE_TYPE: AUTO

 
ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE AUTO’);

SYNC ON COMMIT

to

MAINTENANCE AUTO

IDX_SYNC_TYPE: ON COMMIT

IDX_MAINTENANCE_TYPE: AUTO

 
ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA SYNC (MANUAL) MAINTENANCE AUTO’);

SYNC (EVERY)

to

MAINTENANCE AUTO

IDX_SYNC_TYPE: EVERY

IDX_MAINTENANCE_TYPE: AUTO

 
ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA SYNC (MANUAL) MAINTENANCE AUTO’);

Switching Indexes to Manual Maintenance

This table provides ALTER INDEX examples with various SYNC types, while altering your index from MAINTENANCE AUTO to MAINTENANCE MANUAL:

Setting Maintenance and SYNC Type Example

MAINTENANCE AUTO

to

SYNC (MANUAL)

IDX_SYNC_TYPE: MANUAL

IDX_MAINTENANCE_TYPE: MANUAL

 
ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);
The default SYNC type for a CONTEXT index is MANUAL.

MAINTENANCE AUTO

to

SYNC ON COMMIT

IDX_SYNC_TYPE: ON COMMIT

IDX_MAINTENANCE_TYPE: MANUAL

 
ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS('REPLACE METADATA SYNC(ON COMMIT) MAINTENANCE MANUAL');

MAINTENANCE AUTO

to

SYNC (EVERY)

IDX_SYNC_TYPE: EVERY

IDX_MAINTENANCE_TYPE: MANUAL

 
ALTER INDEX CTX_IDX REBUILD
    PARAMETERS(‘REPLACE METADATA MAINTENANCE MANUAL);
ALTER INDEX CTX_IDX REBUILD 
    PARAMETERS(‘REPLACE METADATA SYNC(EVERY "freq=minutely;interval=20")’);

Related Topics

5.8.5 Monitoring Maintenance Events and Errors

The SYS and CTXSYS users can query catalog views to monitor the status of all background maintenance events for indexes with automatic maintenance.

In an automatic maintenance mode, indexes are asynchronously maintained without any user intervention. Oracle recommends that you periodically examine the CTX and dynamic performance views to know the status of all background maintenance events that are complete or delayed.

Querying Data and Status about Maintenance Events

Use the following views to monitor events at an index or index partition level:

  • CTX_BACKGROUND_EVENTS:

    This Oracle Text view displays historical information about the execution of events for the SYS or CTXSYS user.

  • CTX_USER_BACKGROUND_EVENTS:

    This Oracle Text view displays historical information about the execution of events for the current user, based on the index owner.

  • V$TEXT_WAITING_EVENTS:

    This dynamic performance view displays historical information about events that are delayed or cannot complete due to errors or contentions.

For example, you can query the index object number and name, index owner number and name, base table (or table partition) object number and name, type of event (such as SYNC-Postings, SYNC-Mapping, SYNC-Ranges, and so on), status of the event (such as successful, running, failed, and so on), status of the retry iterations (such as retry delays, waiting time, and so on), elapsed time since the event started waiting, logged error messages, and so on.

Handling Errors

When the system encounters an indexing error (such as an index failure, event delay, or event retry), it logs the error in a catalog view. The error is not directly reported to the user. You must periodically query views to examine such errors and take corrective actions, as follows:

  • Some errors are transient and do not reproduce on a retry. Such error types do not require user intervention.

  • Some failed events may automatically succeed after a retry. If a retry event does not succeed, then try restarting the event from another event.

    For example, when SYNC-Postings (Sync-P) fails after a retry, you can restart SYNC-Scheduler (Sync-S) so that the system can schedule a serial or concurrent operation. A successful completion of Sync-S clears the queue for Sync-P, and Sync-P immediately runs at a starting level without overloading the system.

  • If an event does not succeed even after periodic retries, then contact your database administrator.

  • To limit the load on the system due to periodic retries, the delays between successive retries may progressively increase.

    To track such delays, all retry events are scheduled at an incremented level. This means that the Scheduler process first moves a retry event into an Event Queue. From the Event Queue, the Scheduler process moves it into the Waiting Queue, then to the Ready Queue, and finally allocates the event to the Worker process.

    In addition to incremental levels, each event has a retry iteration. On long retries (that is, when a retry event is not the same as the original event), the iteration is incremented instead of the level and the level is initialized to the iteration.

  • On Oracle Real Application Clusters (Oracle RAC) systems, there may be errors that occur only on some nodes but not on others. In such cases, an event may successfully complete only when the event is sent to the other nodes.

Related Topics