OPTIMIZE_INDEX

Use this procedure to optimize the index. Optimizing an index removes old data and minimizes index fragmentation, which can improve query response time.

Optimize your index after you synchronize it. Querying and DML may proceed while optimization takes place.

You can optimize in fast, full, rebuild, token, token-type, or merge mode.

Fast mode compacts data but does not remove rows.
Full mode compacts data and removes rows.
Optimize in rebuild mode rebuilds the $I table (the inverted list table) in its entirety. Rebuilding an index is often significantly faster than performing a full optimization, and is more likely to result in smaller indexes, especially if the index is heavily fragmented.

Rebuild optimization creates a more compact copy of the $I table, and then switches the original $I table and the copy. The rebuild operation will therefore require enough space to store the copy as well as the original. (If redo logging is enabled, then additional space is required in the redo log as well.) At the end of the rebuild operation, the original $I table is dropped, and the space can be reused. A temporary “change capture trigger” is used to ensure that updates to the $I table during the optimization are not lost. For this reason, the user calling OPTIMIZE_INDEX in REBUILD mode must have the CREATE TRIGGER privilege.

Optimize in rebuild mode supports partitioning on the $I table via the i_table_clause attribute of the basic_storage preference with the following limitations:
- The i_index_clause must specify using a local btree index if the $I table is partitioned.
- Partitioning schemes on the token_first, token_last, or token_count columns are not allowed.
In token mode, specify a specific token to be optimized (for example, all rows with documents containing the word elections). Use this mode to optimize index tokens that are frequently searched, without spending time on optimizing tokens that are rarely referenced. An optimized token can improve query response time (but only for queries on that token).

Starting with Oracle Database Release 21c, the topN fragmented tokens in the $I table are optimized. The $SN and $ST tables are also optimized.
Token-type optimization is similar to token mode, except that the optimization is performed on field, MDATA, or SDATA sections (for example, sections with an <A> tag). This is useful in keeping critical field or MDATA sections optimal.
Use the merge mode to optimize the $I table for the CONTEXT indexes that are frequently used for DML operations. The merge operation compacts the existing data in the $G table, and then copies that data to the $I table. The compacted rows are then deleted from the $G table.

A common strategy for optimizing indexes is to perform regular token optimizations on frequently referenced terms, and to perform rebuild optimizations less frequently. (Use CTX_REPORT.QUERY_LOG_SUMMARY to find out which queries are made most frequently.) You can perform full, fast, or token-type optimizations instead of token optimizations.

Some users choose to perform frequent time-limited full optimizations along with occasional rebuild optimizations.

Note:

Optimizing an index can result in better response time only if you insert, delete, or update documents in the base table after your initial indexing operation.
When you run index optimization, any work in the session is committed effectively and that work cannot be rolled back.
You cannot run DDL ALTER TABLE .. MODIFY concurrently with an index synchronization or index maintenance operation, such as SYNC_INDEX.

Using this procedure to optimize the index is recommended over using the ALTER INDEX statement.

Optimization of a large index may take a long time. To monitor the progress of a lengthy optimization, log the optimization with CTX_OUTPUT.START_LOG and check the resultant logfile from time to time.

Note that, unlike serial optimize full, CTX_DDL.OPTIMIZE_INDEX() run with optlevel of FULL and parallel_degree > 1 is not resumable. That is, it will not resume from where it left after a time-out or failure.

Note: There is a very small window of time when a query might fail in CTX_DDL.OPTIMIZE_INDEX REBUILD mode when the $I table is being swapped with the optimized shadow $I table.

Syntax

CTX_DDL.OPTIMIZE_INDEX(

idx_name        IN  VARCHAR2,
optlevel        IN  VARCHAR2,
maxtime         IN  NUMBER DEFAULT NULL,
token           IN VARCHAR2 DEFAULT NULL,
part_name       IN VARCHAR2 DEFAULT NULL,
token_type      IN NUMBER DEFAULT NULL,
parallel_degree IN NUMBER DEFAULT 1,
maxtokens       IN NUMBER DEFAULT NULL,
section_type    IN NUMBER DEFAULT NULL

);

idx_name

Specify the name of the index. If you do not specify an index name, then Oracle Text chooses a single index to optimize.

optlevel

Specify optimization level as a string. You can specify one of the following methods for optimization:

optlevel value	Description
`FAST` or `CTX_DDL.OPTLEVEL_FAST`	This method compacts fragmented rows. However, old data is not removed. `FAST` optimization is not supported for `CTXCAT` indexes. `FAST` optimization will not optimize `$S` index table. Fast optimization is not supported for local Oracle Text search indexes.
`FULL` or `CTX_DDL.OPTLEVEL_FULL`	In this mode you can optimize the entire index or a portion of the index. This method compacts rows and removes old data (deleted rows). Optimizing in full mode runs even when there are no deleted rows. Full optimization is not supported for `CTXCAT` indexes.
`REBUILD` or `CTX_DDL.OPTLEVEL_REBUILD`	This `optlevel` rebuilds the `$I` table (the inverted list table) to produce more compact token info rows. Like `FULL` optimize, this mode also deletes information pertaining to deleted rows of the base table. `REBUILD` is not supported for `CTCAT`, `CTXRULE`, and local Oracle Text search indexes.
`TOKEN` or `CTX_DDL.OPTLEVEL_TOKEN`	This method lets you specify a specific token to be optimized. Oracle Text does a full optimization on the token you specify with `token`. If no token type is provided, 0 (zero) will be used as the default. Use this method to optimize those tokens that are searched frequently. Token optimization is not supported for `CTCAT` and `CTXRULE` indexes.
`TOKEN_TYPE` or `CTX_DDL.OPTLEVEL_TOKEN_TYPE`	This optlevel optimizes on demand all tokens in the index matching either the input token type or the input section type. When `optlevel` is `TOKEN_TYPE`, either token_type or section_type must be provided. `TOKEN_TYPE` performs `FULL` optimize on any token of the input token_type or section_type, whichever is provided. Like a `TOKEN` optimize, `TOKEN_TYPE` optimize does not change the `FULL` optimize state, and runs to completion on each invocation. Token_type optimization is not supported for `CTCAT` and `CTXRULE` indexes.
`MERGE` or `CTX_DDL.OPTLEVEL_MERGE`	This optlevel compacts the rows in the staging `$G` table and merges them into the `$I` table. This option is not supported at the token level. Specifying the `TOKEN` attribute with this option results in an error. Merge optimization should be used for `CONTEXT` indexes with the `STAGE_ITAB` index option enabled.

The behavior of CTX_DDL.OPTIMIZE_INDEX with respect to the $S index table is as follows:

optlevel value	Will Optimize $S Index Table Yes/No	Notes
`FAST` or `CTX_DDL.OPTLEVEL_FAST`	No
`FULL` or `CTX_DDL.OPTLEVEL_FULL`	Yes	The optimize process will optimize $I table first. Once $I table optimize is finished, `CTX_DDL.OPTIMIZE_INDEX` will continue on to optimize $S index table. `MAXTIME` will also be honored. Once `CTX_DDL.OPTIMIZE_INDEX` completes optimizing $S rows for a given `SDATA_ID`, it will check `MAXTIME` and exit if total elapsed time (including time taken to optimize $I) exceeds specified `MAXTIME`. The next `CTX_DDL.OPTIMIZE_INDEX` with `optlevel=>'FULL'` will pick up where it left off. $S table optimize will be done in serial.
`REBUILD` or `CTX_DDL.OPTLEVEL_REBUILD`	Yes	$S optimize will start after $I rebuild finishes. $S optimize in this case will be processed the same way as $S optimize in `FULL` mode. $S table is optimized in place, not rebuilt. Note: If for some reason $S optimize exits unusually, then it is recommended that you use `optlevel=>TOKEN_TYPE` to optimize $S to avoid rebuilding the $I table again. $S table optimize will be done in serial.
`TOKEN` or `CTX_DDL.OPTLEVEL_TOKEN`	No
`TOKEN_TYPE` or `CTX_DDL.OPTLEVEL_TOKEN_TYPE`	Yes	You can optimize $S rows for a given `SDATA_ID` by setting `optlevel => TOKEN_TYPE` and the `TOKEN_TYPE` parameter to the target `SDATA_ID`.

The behavior of CTX_DDL.OPTIMIZE_INDEX with respect to the $KG index table is as follows:

optlevel value Will Optimize $KG Index Table Yes/No Notes

optlevel value	Will Optimize $KG Index Table Yes/No	Notes
`FULL` or `CTX_DDL.OPTLEVEL_FULL` `REBUILD` or `CTX_DDL.OPTLEVEL_REBUILD` `TOKEN_TYPE` or `CTX_DDL.OPTLEVEL_TOKEN_TYPE` with section_type set to `CTX_DDL.SECTION_WILDCARD_INDEX`	Yes	The optimize process will not remove deleted entries from the $KG table. To permanently remove deleted entries and reclaim the associated storage, you need to rebuild the index using the `ALTER INDEX REBUILD` command.

FULL or CTX_DDL.OPTLEVEL_FULL

REBUILD or CTX_DDL.OPTLEVEL_REBUILD

TOKEN_TYPE or CTX_DDL.OPTLEVEL_TOKEN_TYPE with section_type set to CTX_DDL.SECTION_WILDCARD_INDEX

Yes

The optimize process will not remove deleted entries from the $KG table. To permanently remove deleted entries and reclaim the associated storage, you need to rebuild the index using the ALTER INDEX REBUILD command.

maxtime

Specify maximum optimization time, in minutes, for FULL optimize.

When you specify the symbol CTX_DDL.MAXTIME_UNLIMITED (or pass in NULL), the entire index is optimized. This is the default.

token

Specify the token to be optimized.

part_name

If your index is a local index, then you must specify the name of the index partition to synchronize otherwise an error is returned.

If your index is a local Hybrid Vector Index (HVI), then specify the name of the index partition.

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

token_type

Specify the token_type to be optimized.

You can find the token_type by using the CTX_REPORT.TOKEN_TYPE method or the CTX_USER_SECTIONS view.

parallel_degree

Specify the parallel degree as a number for parallel optimization. The actual parallel degree depends on your resources.

Because the optlevel values are executed serially, this setting throws the error DRG-10598 for the following values:

TOKEN or CTX_DDL.OPTLEVEL_TOKEN
FAST or CTX_DDL.OPTLEVEL_FAST

maxtokens

Specify the maxtokens to be optimized.

maxtokens attribute can be specified only when optlevel value is set to TOKEN or CTX_DDL.OPTLEVEL_TOKEN and when the token parameter is NULL.

Note:

If the number of fragmented tokens exceeds 50% of total number of tokens in $I and maxtokens is not specified, then “index too fragmented” error is returned.
If maxtokens specified is negative or greater than 50% of total number of tokens in $I, then “invalid value for maxtokens” error is returned.

section_type

Specify the section_type to optimize all sections of a certain type. This parameter can have one of the following values:

section_type value	Description
`CTX_DDL.SECTION_FIELD`	The optimization is run for all field sections in the index.
`CTX_DDL.SECTION_SORT_SDATA`	The optimization is run for all `optimized_for` sort SDATA sections in the index.
`CTX_DDL.SECTION_MDATA`	The optimization is run for all MDATA sections in the index.
`CTX_DDL.SECTION_SEARCH_SDATA`	The optimization is run for all `optimized_for` search SDATA sections in the index.
`CTX_DDL.SECTION_WILDCARD_INDEX`	The optimization is run for the $KG table (that is, the wildcard search index).
`CTX_DDL.SECTION_SEMANTIC_INDEX`	The optimization is run for the $VR table (semantic search).

Note:

You can specify section_type only when the optlevel value is set to TOKEN_TYPE or CTX_DDL.OPTLEVEL_TOKEN_TYPE.
In the absence of sections of the specified type, index optimization for section_type is a no-op (no operations). Similarly, in the absence of the $KG table, index optimization for the CTX_DDL.SECTION_WILDCARD_INDEX section_type value is a no-op.

Examples

The following two examples are equivalent ways of optimizing an index using fast optimization:

begin
  ctx_ddl.optimize_index('myidx','FAST');
end;

begin
  ctx_ddl.optimize_index('myidx',CTX_DDL.OPTLEVEL_FAST);
end;

The following example optimizes the index token Oracle:

begin
  ctx_ddl.optimize_index('myidx','token', TOKEN=>'Oracle');
end;

To optimize all tokens of field section MYSEC in index MYINDEX:

begin
  ctx_ddl.optimize_index('myindex', ctx_ddl.optlevel_token_type,
     token_type=> ctx_report.token_type('myindex','field mysec text'));end;

The following two examples are equivalent ways of optimizing an index using merge optimization:

begin
  ctx_ddl.optimize_index('idx','MERGE');
end;

begin
  ctx_ddl.optimize_index('idx',CTX_DDL.OPTLEVEL_MERGE);
end;

The following example optimizes the top 10 fragmented tokens in $I:

begin
 ctx_ddl.optimize_index('idx','TOKEN',maxtokens=>10);
end;

Notes

You can run CTX_DDL.SYNC_INDEX and CTX_DDL.OPTIMIZE_INDEX at the same time. You can also run CTX_DDL.SYNC_INDEX and CTX_DDL.OPTIMIZE_INDEX with parallelism at the same time. However, you should not:
- Run CTX_DDL.SYNC_INDEX with parallelism at the same time as CTX_DDL.OPTIMIZE_INDEX
- Run CTX_DDL.SYNC_INDEX with parallelism at the same time as CTX_DDL.OPTIMIZE_INDEX with parallelism.
If you should run one of these combinations, no error is generated; however, one operation will wait until the other is done.
You cannot sync or optimize an index that is owned by a different schema. Doing so results in the “DRG-10016: You must be the owner to modify this object” error.

Optimizing Wildcard Index ($KG)

You can optimize wildcard index ($KG) independently of the main token index by specifying SECTION_WILDCARD_INDEX as the section_type parameter when using OPTIMIZE_INDEX procedure. This optimization applies to wildcard indexing and does not optimize the main token table.

Examples

exec ctx_ddl.optimize_index(
      'INDEX_NAME',
      'TOKEN_TYPE',
      section_type => ctx_ddl.section_wildcard_index);

For a local partitioned index:

exec ctx_ddl.optimize_index(
      'INDEX_NAME',
      'TOKEN_TYPE',
      part_name => 'P1',
      section_type => ctx_ddl.section_wildcard_index);

Notes

SECTION_WILDCARD_INDEX is supported with TOKEN_TYPE optimization.
Use CTX_REPORT.WILDCARD_INDEX_FRAGMENTATION to evaluate whether wildcard index optimization is appropriate.
For local partitioned indexes, specify the partition name using part_name.
This operation optimizes wildcard index only and does not optimize the main token index data.

Related Topics