Problem solve Get help with specific problems with your technologies, process and projects.

Rebuilding Oracle text indexes when record updates are frequent

Is there a recommended method for rebuilding Oracle text indexes when the frequency of updates is high? I have a very active DB, and want to avoid rebuilding every two minutes, but do I have any other choice?
If you are updating records with a high frequency, and those updates change the columns that are indexed, then it is probably not a good idea to be rebuilding indexes at all! Let's suppose you have a column with a value of 'garage' and you update it to 'canoe'. The index entry in the leaf block will move to a different leaf block to maintain sorted order. Now another update on another row updates the value 'witch' to 'garden'. If you have rebuilt the index, then there is no room for 'garden'. If you have not rebuilt the index, then it is highly likely that 'garden' will go into the leaf block entry that was vacated when 'garage' was updated and moved. So I would suggest not rebuilding the indexes on tables that get updated frequently unless you have a very good reason to do so.

Dig Deeper on Oracle database design and architecture

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.