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")'.
Like:
%_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).
Related document:
