OPTIMIZE_INDEX

Use this procedure to optimize the index. You optimize your index after you synchronize it. Optimizing the index removes old data and minimizes index fragmentation. Optimizing the index can improve query response time.

You can optimize in fast, full, or token mode. In token mode, you specify a specific token to be optimized. You can use token 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 for that token.

Note: Optimizing an index can result in better response time only if you insert, delete, or update documents in your base table after your initial indexing operation.

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

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

); 

idx_name

Specify the name of the index.

optlevel

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

Value	Description
FAST or CTX_DDL.OPTLEVEL_FAST	This method compacts fragmented rows. However, old data is not removed.
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.
TOKEN	This method lets you specify a specific token to be optimized. Oracle does a FULL optimization on the token you specify with token. Use this method to optimize those tokens that are searched frequently. Token optimization is not supported for CTXRULE indexes.

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

Specify the name of the index partition to optimize.

Examples

The following two examples optimize the index for 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;

Related Topics

ALTER INDEX in Chapter 1, "SQL Statements and Operators".