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

Do indexes affect performance of updates and inserts?

I have three indexes for Table A. These indexes are helpful when I perform select statements, especially for SQL involving GROUP BY. Will these indexes affect the performance on updates and inserts for Table A?

I have a question about index performance. This is my scenario: Table A consists of 10 columns. Col1 is the primary key. I have three indexes for Table A: IDX1 consists of Col2, IDX2 consists of Col1, Col3, Col4 and Col5 and IDX3 consists of Col4, Col5, Col6 and Col7. These indexes are helpful when I perform select statements, especially for SQL involving GROUP BY. Will these indexes affect the performance on updates and inserts for Table A? Without these indexes, the select statements are causing a lot of problems in performance and are thus resulting in high-cost execution and elapsed time is high.
Table A
---------
Col1 PK
Col2
Col3
Col4 
Col5
Col6
Col7
Col8
Col9
Col10
Thanks and regards.
All indexes cause overhead (for index maintenance) on DML (insert, update, delete). You must assess whether the performance gain on selects, updates and deletes (all of which can include a predicate, or WHERE clause) is worth the overhead. In your case, the table does not appear to be over-indexed, and you gain significant performance advantages by having the indexes, so you should keep them unless DML is unacceptably slow.
This was last published in July 2006

Dig Deeper on Oracle and SQL

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

Join the conversation

2 comments

Send me notifications when other members comment.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

What is the reason if the table have more number of indexes performance decrease?
Cancel
What is the compound trigger? What is the use and where is it using in projects.
Cancel

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close