Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Navinth Bakmeedeniya.

Asked: August 05, 2020 - 2:36 am UTC

Last updated: December 08, 2020 - 5:11 am UTC

Version: Oracle 19.3.0.0.0

Viewed 1000+ times

You Asked

Dear Team,

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 statement."

https://docs.oracle.com/en/database/oracle/oracle-database/19/ccapp/maintaining-oracle-text-indexes.html#GUID-10365262-8B48-40AA-B1F3-DE3268EA9B39

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 with REBUILD 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 with FAST option? May be in aspects such as performance, etc.

Thanks & Best Regards,
Navinth

and Connor said...

Bottom line for me on (any) index rebuild is:

Do I see a tangible benefit after rebuilding?

That benefit might be

- a permanent space saving (because if its not permanent, then why bother)
- a measurable difference in query performance

If you are not seeing that, then it seems a waste of effort. We have an AskTOM index that facilitates search - we never rebuild it.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

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

Connor McDonald
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
Connor McDonald
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
Connor McDonald
December 08, 2020 - 5:11 am UTC

glad we could help
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database