My Oracle Support Banner

CTX_DDL.OPTIMIZE_INDEX REBUILD or FULL is Not Effective in Reducing the Fragmentation and Size of the Index (Doc ID 2695050.1)

Last updated on MAY 18, 2022

Applies to:

Oracle Text - Version to [Release 12.1 to 12.2]
Information in this document applies to any platform.


CTX_DDL.OPTIMIZE_INDEX REBUILD/FULL is not effective in reducing the fragmentation and size of the Text index's DR$I table.

The report generated by CTX_REPORT.INDEX_STATS after the OPTIMIZE_INDEX REBUILD/FULL shows the estimated row fragmentation did not decrease significantly, and there are millions of garbage docid's in the $I table:

                         FRAGMENTATION STATISTICS

total size of $I data:                            3,886,673,732 (3.62 GB)

$I rows:                                                        6,062,624
estimated $I rows if optimal:                                   1,050,835
estimated row fragmentation:                                         83 %

garbage docids:                                                91,248,968
estimated garbage size:                           1,203,577,220 (1.12 GB)


The log files from the CTX_DDL.OPTIMIZE_INDEX REBUILD/FULL show the OPTIMIZE_INDEX process (or each of the P00x process, if run with PARALLEL > 1) is clearing garbage tokens from only 1 million docid's:



To view full details, sign in with your My Oracle Support account.

Don't have a My Oracle Support account? Click to get started!

In this Document

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.