Explain about secondary index for queries.

An index in SAP is like an index for a book. If you want to look up something from the book you refer the book's index page. Right?
In the same way in SAP, when you fire a query it makes it easy for the system to search a particular record in the db table if the field that you have in the SELECT statement is an index.

So in that sense, there is no difference between the query based on a primary index or a secondary index.
They are fired exactly the same way.

eg: SELECT MATNR FROM MARA.
Here matnr could be a primary or a secondary index.

Primary Index

The Primary Index is the one that is created on declared Primary Keys of the table.

It is not always possible to stick on to primary index for querying a particular table for your report.

For example, at our company, we are using Special Stock Indicators "E" and "O" for the stocks.  Almost 95% of the Finished & Semifinished products move within SAP with tag "E".

If I want to develop Z report to process material documents with "E" stock, I have to query mseg table on Sale Order Number (MAT_KDAUF) and Item Number (MAT_KDPOS).

But Primary Index of mseg does not include sale order number and item.  Then to improve performance of my query I have to create another index on mseg with MAT_KDAUF and MAT_KDPOS fields.

Then my query will be something like this,

select * into table i_mseg from mseg where matnr = p_matl and mat_kdauf = p_so and mat_pos = p_item.

But there are many things to be taken care while creating secondary indexes.

1. More number of indexes on single table will result in database swelling and also reduce the performance of the database.  Indexes are nothing but a copy index key columns of the main table and associated record pointer sorted on index keys.  Hence as we create more more sets, database will keep on searching the suitable index for your query.

2. An index with more number of fields, is literally an useless index.  Because as you keep adding fields to your index, it is just like you are searching your entrie table for the filter criteria.

3. Essentially, maximum three fields per index, and maximum 6 indexes per table will produce the desired results.

4. If the table size is small and growth rate is less, more number of fields or indexes can be added.

5. Very important point is, create index with a field set only if you have the requirement of certain querying criteria in many programs or reports.  In the above example, whatever material doc related report I develop I'll use sale order number and item number in the query.  Hence the requirement is generic.  Don't create an index for one time use.

6. Maintain the field sequence in where clause when you are trying to point to an index of the table.  It will improve the performance considerably.

Get help for your ABAP problems
Do you have a ABAP Question?

ABAP Books
ABAP Certification, BAPI, Java, Web Programming, Smart Forms, Sapscripts Reference Books

More ABAP Tips

• ABAP Programming Tips and Tricks
• ABAP Functions Examples - ABAP Questions - BAPI Programming Tips
• BDC Programming Tips - Sapscripts Tips - Smartforms Tips