What are secondary index and its advantages?
If none of the primary key is present in your select query where clause then we can think of secondary index otherwise the system searches through the entire table (full table scan) and the cost of such hit would be very high and would degrade the performance drastically.
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. Secondary indexes should contain columns that you use frequently in a selection, and that are as highly selective as possible to improve performance..
Keep the indexes as small as you can. The more fields the more inefficient they become. Try to keep it max with 4 fields.
Please note that whenever a table created a primary index automatically be created with the entire primary keys in it.
We can have maximum of 9 secondary index for a particular table.
How to use secondary index:
If there is any secondary index and you are using the particular fields in your where clause then the query should automatically take according to the secondary index.
In another instance, though the secondary index existed for the custom table, the Oracle Cost Based Optimizer (CBO) chose to ignore it and did a full table scan instead. This could be because the CBO found the cost of full table scan lower than the cost of using the secondary index. This was most likely caused by oracle profile settings which make full table scan more favorable. In such cases, in order to optimize performance we need to force the system to use the existing Secondary Index. This can be done by using Database Hints. Hints force the CBO to use the index defined in the query.
SELECT <fields> INTO <work area>
FROM <database table>
WHERE <logical expression> AND <logical expression>
%_HINTS <database name> ‘INDEX(“<database table>” “Index name”)’.
%_HINTS ORACLE ‘INDEX(“MARC” “MARC~Z01”)’.
What to Keep in Mind for Secondary Indexes:
Disadvantages of secondary index:
Every time you insert, update, modify or delete row it has to be updated. That means extra time at those moments. For highly maintained tables, this can be a real pain.
Lot of Secondary indexes will in turn introduce load on the select query to identify the most appropriate index which may slow down the performance.
Take additional DB space and time per update operation.
Might make matters more complex for the CBO (There are instances when the SAP Cost Based Optimizer (CBO) over-rides the secondary index and continues to do a sequential read on the table. At such times, coding an ABAP hint that would force the CBO to use the secondary index can be used).