I have a partitioned Oracle TEXT context index that has never been optimized. After running the fragmentation report on a specific partition, the size of the $I table is showing *462MB*, 33% fragmentation and 9,779,790 rows.
I ran an optimize against this partition with OPTLEVEL_FULL and parallel 4. After the optimize completed, I ran a fragmentation report again and at this point, the $I table now shows to be even bigger at *466MB*, 0% fragmentation and 6,518,541 rows.
This was an older partition that did not have any data added to it during the time of optimization, so I have a few questions:
* Is the $I expected to grow after an optimization?
* Is it supported to run a ALTER TABLE...SHRINK operation on this table after running an optimization?
* Does the parallel affect how big the table grows due to the optimization?
it is mentioned in support note "CTX_DDL.OPTIMIZE_INDEX in Rebuild Mode Using Lots of Tablespace Space [ID 799674.1]". It is a tradeoff between storage and performance. But in your case it is just 1% larger, but you will see there is some space left in the blocks.
Herald ten Dam
Thanks so much for your reply! I see the note relates to optimize REBUILD, but it appears to be the same scenario for optimize FULL.
Any idea if it is supported to perform a SHRINK on the $I table?
There are similar issues with FULL optimize. We're reading lots of small chunks and replacing them with larger chunks. It's likely that the space freed up by the small chunks can't be used by the large chunks we're writing back, so the space gets left empty. Optimize rebuild was originally designed, in part, so avoid this issue - and usually you will find that it reduces space usage, although that's not always the case as can be seen by the note referenced by Herald.
As far as I know the comment "REBUILD is not supported when the $I table is partitioned" is at best misleading. You can use optimize rebuild on a partition of a LOCAL index. I think this actually refers to a non-supported configuration used by a couple of customers where the $I table was separately partitioned on the TOKEN_TYPE column.
Is it supported to perform a shrink on the $I table? I'm not really sure. In general we don't recommend any operations directly on the index tables, but I can't really see how it could cause any harm.
On the other hand, it may not provide much or any benefit either if the free space chunks are too small to be reused.
You would need to ask Oracle Support for a definitive answer as to whether it is supported.
the documentation states there is no restriction: "Restriction on Shrinking Index Segments: You cannot specify this clause for for a bitmap join index or for a function-based index.", see http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_1010.htm#i2208791.
There is a restriction using shrink on tables using text indexes: "Segment shrink is not supported for tables with function-based indexes, domain indexes, or bitmap join indexes."
So I think it can be done, but is not advised. Normally a rebuild is sufficiënt and let Oracle do the rest. After an optimize Oracle will reuse the space if it can. So there will be some waste but not that much. If you are concerned with this waste, you can try to choose a bigger block size, 16k or even 32k. Those sizes will waste less space.
Herald ten Dam
Thanks so much for the confirmation.
I would like to consider using the REBUILD option, but it is curious that the docs mention that REBUILD only operates serially and ignores any parallel settings and that REBUILD can produce significant redo (if enabled), yet it mentions that it performs faster than optimize FULL. I would assume the "faster" would be in comparing both REBUILD and FULL when they operate in serial mode. Since FULL can operate in parallel mode, my guess is it has the potential to perform faster than REBUILD (haven't verified)?
That being said, I'd be curious if using RECREATE_INDEX_ONLINE would offer any advantages over optimize with REBUILD and avoid any potential space issues?
Yes, rebuild is usually I/O limited so wouldn't benefit much from running in parallel, and the parallel running would probably introduce its own fragmentation. I would still expect it to be faster than FULL optimize in parallel, but couldn't guarantee it.
RECREATE_INDEX_ONLINE has to do a lot more work than OPTIMIZE REBUILD (it has to lexically analyze all the text) and won't produce such an optimal final index. It may use less space, but only because it's "less optimal" through having smaller $I rows.