Database table indexes
Table indexes are required for speed selection of data records from a database table.
Type of indexes in SAP ABAP database table
There are three types of indexes: Primary index, Secondary index and Extension index.
Every database table has a primary index. The primary index is created automatically by the system when the table is activated. The primary index consists of the key fields of the table and is always unique.
Secondary indexes are created on table’s non-key fields. Creation of secondary index might be required if ABAP programs select statements frequently access the table using its non-key fields i.e. create secondary index on fields that are frequently used in the WHERE clause of SELECT statements. Also, to benefit from secondary index, it is required that the field order in WHERE clause of SELECT statement should match the index field order.
Extension index is an enhancement technique for extension of the standard index.
Note that it is not advisable to create secondary indexes on standard SAP tables as secondary indexes on SAP tables gets deleted after a system upgrade and hence needs to be re-created manually. Extension indexes are automatically copied over during system upgrades and hence remains intact.
Every time a record is inserted, updated, or deleted from database table; indexes are adjusted by the system. This is an overhead on the system and hence it advised to create secondary indexes only if it is a necessity. Also, tables whose contents are frequently changed must not have too many indexes.
Steps to create secondary index
Steps to create secondary index and extension index is same.
- Go to tcode SE11. Enter table name and choose Display
- Click Indexes button on the application tool bar
- In the indices for table dialog box, choose Create -> Create Index.
- Enter 3 character index name and choose continue.
- Enter 3 character index name and choose continue.
- Enter short description and choose Table Fields button. A list of all the fields in the table appears. Select the required fields and click Copy. Arrange the order of fields as required.
- Save, check and activate the index.
Difference between Unique and Non-unique index in ABAP Database table
If Unique index is chosen, the fields under field name column are identified as unique i.e the system will not permit duplicate records for the mentioned fields. In the above case, the name of two employees can be same, hence we have chosen non-unique index.