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

Applies to:

Oracle Text - Version 12.1.0.2 to 19.19.0.0.0 [Release 12.1 to 19c]
Information in this document applies to any platform.

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

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
Symptoms
Cause
Solution
References


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