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 JULY 20, 2024

Applies to:

Symptoms

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:

Cause

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