Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
Creating an additional index could also have side effects on the performance. This is because an index that was used successfully for selection might not be used any longer by the optimizer if the optimizer estimates (sometimes incorrectly) that the newly created index is more selective.
The indexes on a table should therefore be as disjunct as possible, that is they should contain as few fields in common as possible. If two indexes on a table have a large number of common fields, this could make it more difficult for the optimizer to choose the most selective index.
Secondary indexes are used to enhancement performance when retrieving data. For example, say you have a table AFKO where the key to the table is AUFNR. You can select data from this table using AUFNR, the performance will be great because you are using the key. Say you need to hit this table with GLTRP(finish date), it is not part of the key, so the performance would be poor. If you created a secondard index using this field GLTRP, the performance would be improved. Make sense?
1.If you cannot use the primary index to determine the result set because, for example, none of the fields primary index occur in the WHERE or HAVING clause, the system searches through the entire table (full table scan).
2.For this case, you can create secondary indexes, which can restrict the number of table entries searched to form the result set.
3. You should only create secondary indexes, for database tables from which you mainly read, since indexes have to be updated each time the database table is changed.
4.Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible to improve performance.
Basically index are provided to improver performance. i.e with index select on db tables retrieves data much faster.
If you are writting select statement on the table where in where clause you have non-primary keys then it takes longer to retriev data from DB table.
If you have select in dbtable on non-primary key you can create Secondary index with keys as per your where clause which result in faster DB table access.
But remember not to create too many secondary indexes on same table which could result in slow / degrage performance..
you can check out the link below for more inforamation on indexes
Bluesky
