OPTIMIZE_INDEX

Optimizes the index. You can optimize on fast or full mode. This is the same as optimizing with ALTER INDEX.

Syntax

CTX_DDL.OPTIMIZE_INDEX( 
  idx_name  in  varchar2, 
  optlevel  in  varchar2, 
  maxtime   in  number    default null 
); 

idx_name

Specify the name of the index.

optlevel

Specify optimization level as a string, either FAST or FULL.

You can also specify this parameter as one of the symbols: CTX_DDL.OPTLEVEL_FAST or CTX_DDL.OPTLEVEL_FAST.

When you optimize in fast mode, Oracle works on the entire index, compacting fragmented rows. However, in fast mode, old data is not removed.

When you optimize in full mode, you can optimize the whole index or a portion. This method compacts rows and removes old data (garbage collection.)

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.

When you specify 0 for maxtime, Oracle performs minimal optimization.

Example

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;

Related Topics

ALTER INDEX in Chapter 2.