8 Replies Latest reply: May 8, 2012 8:14 AM by user109389 RSS

    $I grows larger after OPTIMIZE_INDEX

    user109389
      Oracle 11.2.0.2

      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?

      Thanks.
        • 1. Re: $I grows larger after OPTIMIZE_INDEX
          Herald ten Dam
          Hi,

          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
          http://htendam.wordpress.com
          • 2. Re: $I grows larger after OPTIMIZE_INDEX
            user109389
            Herald,
            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?
            • 3. Re: $I grows larger after OPTIMIZE_INDEX
              Roger Ford-Oracle
              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.
              • 4. Re: $I grows larger after OPTIMIZE_INDEX
                Herald ten Dam
                Hi,

                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
                http://htendam.wordpress.com
                • 5. Re: $I grows larger after OPTIMIZE_INDEX
                  user109389
                  Roger,
                  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?

                  Thanks again!
                  • 6. Re: $I grows larger after OPTIMIZE_INDEX
                    user109389
                    I think I'll avoid performing any shrink operation on the underlying text tables, but the larger block sizes are definitely something worth considering.

                    Thanks.
                    • 7. Re: $I grows larger after OPTIMIZE_INDEX
                      Roger Ford-Oracle
                      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.
                      • 8. Re: $I grows larger after OPTIMIZE_INDEX
                        user109389
                        I may have to give REBUILD a go and measure it's performance compared to FULL.

                        Thanks much to Herald & Roger. Sorry, I wish I could mark Herald's answer correct too.