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

Avoiding overhead of insert/delete with indexes

Well my Question is related to Databases index created on the tables. Suppose we have created an index on the table. Now while performing Insert/Delete on the table, we will have extra overhead, i.e. data will be deleted/inserted in both the index and the table. How can we avoid this overhead?

The only real way to avoid the overhead is to drop the index when performing major data changes, then rebuild it when the changes are complete. Indices can help a great deal with data retrieval, but they are almost pure overhead during data changes (INSERT/UPDATE/DELETE operations).

There are some niche products that claim to offer the benefits of indices with very little of the overhead associated with them. Fox Software developed the "Rushmore" indexing methodology to deal with just this issue in their FoxPro database (now marketed by Microsoft), and InterSystems offers Cach?hich seems to be logically very similar to me. If you need these features and can work within the limitations they impose, they might be an answer for you.

For More Information

Dig Deeper on Oracle and SQL

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.