Navinth Bakmeedeniya, August 05, 2020 - 3:36 am UTC
Thanks for your quick answer Mc.
We are a product company & we were using this rebuild now for sometime with thousands of different Text Indexes in large number of customers. Before going for a framework change, what we need to get confirm is, generally from Oracle perspective, do you see any other risks if we are to remove the index rebuild & continue only with optimize as a scheduled maintenance task?
Why we are asking this is, since we have large number of indexes & we are releasing our service packs in regular cycles, we would like to know the consequences before hand as this could affect the application experience of our customers in a significant level (if something goes bad).
Thanks & Best Regards,
Navinth
August 20, 2020 - 2:52 am UTC
I spoke to the Text PM.
Firstly, depends what you mean by a 'rebuild'. A regular optimize in rebuild mode is a good idea - optimize rebuild creates a new copy of the main index table, and does a more thorough job than optimize full
A full index rebuild is rarely needed. For most users, it's not necessary at all. However in an application with a very high volume of DML - where, for example, more than 50% of records are replaced over a set period - then it can sometimes be faster to run a full index rebuild than to run an index optimize in full or rebuild modes.
Rebuild online
Navinth Bakmeedeniya, December 04, 2020 - 4:49 pm UTC
Mc. Similar to ATER INDEX REBUILD ONLINE , do we have a mode of running CTX_DDL.Optimize_Index with REBUILD option so that DML would not be blocked during the rebuild period? I don't find this information in your documentation or any other reliable resource available.
Or contrary to ALTER INDEX REBUILD, is this CTX_DDL.Optimize_Index not blocking DML? I guess which is not the case here.
Thanks & Best Regards,
Navinth
December 07, 2020 - 3:24 am UTC
It is non blocking but take note of this from the docs:
"There is a very small window of time when a query might fail in CTX_DDL.OPTIMIZE_INDEX REBUILD mode when the $I table is being swapped with the optimized shadow $I table."
You also might want to check out the RECREATE_INDEX_ONLINE routine.
Navinth Bakmeedeniya, December 07, 2020 - 12:40 pm UTC
Thanks for the quick response Mc.
Navinth
December 08, 2020 - 5:11 am UTC
glad we could help