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 |