In our IFS Applications product, we heavily use Oracle text indexes. we are not updating the indexes real time instead we do it at a separate time interval through Ctx_Ddl.Sync_Index
passing a CLOB document. We synchronize the indexes with a default 30 minute interval. In the maintenance cycle, we optimize an index once a week using Ctx_DdL.Optimize_Index
method using FAST option. Also we perform an ALTER INDEX REBUILD for the text index once a week.
Recently we encountered some errors with this REBUILD operation at few customers so having a thought how we should go forward. When we went through the Oracle documentation, we saw some articles related to this but they are somewhat confusing. https://docs.oracle.com/en/database/oracle/oracle-database/19/ccref/CTX_DDL-package.html#GUID-28E079B1-D5CA-4264-B1C0-A1C5CE174C55
It says: "Using this procedure to optimize the index is recommended over using the ALTER INDEX
It says: "You might rebuild an index when you want to index with a new preference"
We have few questions to clarify.
1. We are not changing any preferences
of the text index once after it was created at the installation time, so do we want to rebuild the text index in a scheduled manner? Is there any other benefit doing so?
2. Do you believe Ctx_Ddl_Optimize_Index
option instead FAST
option would be a good option to have if we skip rebuilding the index using ALTER INDEX
statement? Or is it unnecessary in our situation.
3. Going forward, do you see any other risks if we are to remove ALTER INDEX REBUILD
& continue with only Ctx_Ddl_Optimize_Index
option? May be in aspects such as performance, etc.
Thanks & Best Regards,