Clarification on Secondary Indexes limitations on database tables

[VijayaKrishnaG]

Couple of frequently asked questions in SCN forum,

1. How many secondary indexes can be created on a database table in SAP?

2. How many fields can be included in a secondary index (SAP)?

By seeing many threads over the above couple of questions in SCN forum marked as 'Answered' (correctly) with different answers, I have decided to test the limitations on the Secondary Indexes. The different answers are like 9, 10 (1 Primary and 9 Secondary), 15, 16 (15 Secondary, 1 Primary), No such limit.

So, to check, I have created Secondary indexes on table SFLIGHT.

1. How many Secondary Indexes can be created on a database table in SAP?

Ans. I have created 18 secondary indexes, but the system has not objected at 9 or 10 or 15 or even 16.

So, I believe that, there is no such limit for number of Secondary indexes to create on database table in SAP. But it is not at all recommended to create more than 5 Secondary indexes on a database table.

2. How many fields can a Secondary Index can contain?

When I am testing this I have created Secondary Index for EKKO table and for  an Index I have assigned all the table fields (134). Then the system says that 'max 16 fields can be assigned' with an error message.

So, for a Secondary index we can assign maximum of 16 fields in a database table. But it is recommended to create a secondary index with not exceeding 4 fields.

> These are the points to be remembered before creating an Index.

a. Create Secondary Indexes for the tables that you mainly read. Because every time we update a database table, it would update indexes also. Let's say there is a database table where we create (or update) 100s of entries in a single day. Avoid using Indexes in such cases.    

b. We should take care that an index shouldn't have more than 4 fields and also the number of indexes should not exceed 5 for a database table. Or else, it would result in choosing a wrong one for particular selection by an optimizer.

c. Place the most selective fields at the beginning of an Index.

d. Avoid creating an Index for a field that is not always filled i.e., if it's value is initial (null) for most entries in a table.

> These are the points to be remembered while coding in ABAP programs for effective use of Indexes i.e., to avoid the full table scan.

a. In the select statement, always put the condition fields in the same order as you mentioned in the INDEX. Sequence is very important here.

b. If possible, try to use positive conditions such as EQ and LIKE instead of NOT and IN which are negative conditions.

c. Optimizer might stop working if you use OR condition. Try to use IN operator instead of that.

d. The IS NULL operator can cause a problem for the Index as some of the database systems do not store null values in the Index structure.

Thanks and Regards,

Vijay Krishna G