Hi,

While creating new index in table which already has 2 to 4 indexes
which has huge data in it, what all things need to be consider or
what impact it will have in the performance.

regds
Paul

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.

Andy

thks andy,

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.

regds
Paul

Just for completeness:
The index might also speed up UPDATE and DELETEs if the WHERE condition can make use of the index.

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.

Andy

andy,

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.


Regds
Paul

There is no real way to tell DB2 which access plan to use. You basically get stuck with whatever it comes up with.

Andy