Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, Navinth Bakmeedeniya.

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

Answered by: Connor McDonald - Last updated: August 20, 2020 - 2:52 am UTC

Category: SQL - Version: Oracle 19.3.0.0.0

Viewed 100+ 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 we 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.

and you rated our response

  (1 rating)

Reviews

August 05, 2020 - 3:36 am UTC

Reviewer: Navinth Bakmeedeniya from Sri Lanka

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

Followup  

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.




More to Explore

Administration

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