Adding an index will slow down INSERT operations and new entries have to be added to the index also. It will slow down UPDATE operations only if columns in the index are updated. It will slow down DELETE operations since the index needs to be deleted also.
It can greatly speed up queries that have predicates on the columns of the new index. If the query was table scanning, this would result in great performance improvement. It can also eliminate the need for sorting which is a big performance improvement.
In that case you wld first chk the existing query in explain plan wht exactly its doing table scan, or any partial index scan. Partial index scan from existing then we can add up the new one with them or modify the existing according to new one. If table scan then depending on the impact of performance and how much % of improvement it gives you can create the new index. Your suggestion on this pls.
I cannot give you specific quantities on how adding an index will impact the performance of the database. It is one thing to have an index, it is another thing to use it correctly. You as the DBA will have to make that determination based on the data and the queries against the table. As an example, we have a table that had an index on col2 and col2, but the developer wrote the query with the predicate only on col2 (even though it is dependent on col1). Well the access plan showed that the query was using the index, but the application which used this query numerous time was taking 23 to 48 hours to complete. It was leaf scanning which is just as bad as table scanning. When the developer fixed his query to include col1 then the application finished in under 10 minutes.
As you suggest in ur example regarding c2 column was used in the predicates by the developler in that case too the optimizer was using the index which was impacting the db query performance, coz it was not using the root page of index column c1. Is it possible even to go for table scan if only c2 column is used in predicates by the developer ?
In this case it does the non matching index scan which don't use the root leaf and intermediate level of index, which means its indirectly scan all the leaf pages for c2 column.