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
- Dozens more answers to tough database design questions from Pat Phelan
- The Best Database Design Web Links: tips, tutorials, scripts, and more
- Have an Oracle or SQL tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your database design questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.