A database index is a data structure that significantly improves the speed of data retrieval operations on a database table and has an important trade off to make i.e. the updates made to the database table are costlier (in terms of number of operations / updates and storage space).
In relational databases, index is a reduced set of a table. Any intended updates to the main table result in an eventual updates on all the indexes as well. Thus making an index a very key pointer during performance evaluation / tuning activities.
The below diagram helps us visualize the entities that get created on the database. Now every new CRUD (Create, Read, Update, and Delete) activity on the database table results in updating the reduced set of tables (secondary indices) as well. Now if we have too many index tables then it drastically affects the performance of the system.
Fig. 1: Secondary Index in relational databases
SAP provides lots of secondary indexes to most of the tables which they feel are necessary, but need not be true all the time. Typically there are scenarios, where these might prove to be an overhead. Also creating a Secondary Index on a standard SAP table comes under modification activity.
Fig. 2: Pop-up when adding a custom index to standard SAP Tables
Also, during an upgrade these are dropped and need to be recreated / built in the later system. This involves effort and cost. If these go unnoticed then a potential performance issue will knock the door.
SAP has added a new feature called the “Extension Index” which is a part of the New Enhancement Framework and is tightly integrated with the current DDIC objects. There is an additional option when you create a secondary index. Since this is a part of the Enhancement Framework, these will not get dropped during an upgrade and also there is no need to rebuild them after the upgrade.
Fig. 3: Create Secondary Index
All the further steps for creating the “Extension Index” are same as that of a regular “Secondary Index”.
Bluesky