Index in SAP ABAP

Overview of Indexes in SAP ABAP

1. Overview of Index Kabilarasan R Presented by

2. INDEX Purpose: Index makes the database table search faster. A database index is a data structure that improves the speed of data retrieval operations on a database table Indexes are used to quickly locate data without having to search every row in a database table How it Works ? This index table contains a field which contains the pointer to the actual record in the table. Indexes can be created using one or more columns of a database table

3. Types of Index

4. Primary Index Primary index contains all the key fields of the table and a pointer to the non-key fields. It is automatically created at the time of creation of table. Primary index is a unique index

5. Example MANDT CARRID COUNT_NUM P 001 AA 001 7 001 BA 001 9 001 BA 002 12 001 BA 003 5 001 BA 004 2 001 LH 001 10 001 LH 002 4 001 LH 003 8 001 LH 004 11 001 LH 005 1 001 LH 006 13 001 LH 007 6 001 UA 001 3 001 UA 008 14 MANDT CARRID COUNT_NUM AIRPORT 001 LH 005 ACA 001 BA 004 ACE 001 UA 001 BER 001 LH 002 LCY 001 BA 003 LHR 001 LH 007 BER 001 AA 001 DEN 001 LH 003 FRA 001 BA 001 LCY 001 LH 001 LGW 001 LH 004 LHR 001 BA 002 MUD 001 LH 006 RTM 001 UA 008 HAM Primary Index for SCOUNTER Table: SCOUNTER It demonstrates how the primary index table will look like. The pointer refers to position where the same record in the sorted primary table is available in the actual database table which you can see with the highlighted Red line. Key Fields

6. Secondary Index We can also add our own index to the table known as secondary index . We create secondary index for the non-key fields. The secondary indexes are only to be created when these fields are used extensively in search .

7. MANDT CARRID COUNT_NUM AIRPORT 001 LH 005 ACA 001 BA 004 ACE 001 UA 001 BER 001 LH 002 LCY 001 BA 003 LHR 001 LH 007 BER 001 AA 001 DEN 001 LH 003 FRA 001 BA 001 LCY 001 LH 001 LGW 001 LH 004 LHR 001 BA 002 MUD 001 LH 006 RTM 001 UA 008 HAM From this table (SCOUNTER) consider a scenario where I wanted to fetch the record from the database table based on the airport code ( AIRPORT ) which is actually a non-key field . Sorting of the primary keys will not be of any use as the airport code will still be scattered . A secondary index for the field airport code needs to be created to support the faster access. Table: SCOUNTER

8. Example MANDT CARRID COUNT_NUM AIRPORT 001 LH 005 ACA 001 BA 004 ACE 001 UA 001 BER 001 LH 002 LCY 001 BA 003 LHR 001 LH 007 BER 001 AA 001 DEN 001 LH 003 FRA 001 BA 001 LCY 001 LH 001 LGW 001 LH 004 LHR 001 BA 002 MUD 001 LH 006 RTM 001 UA 008 HAM Secondary Index on AIRPORT Table: SCOUNTER Now when I select the records using the AIRPORT (airport code), then the system will identify the list of records for the airport code in the index table. And fetch the corresponding records using the pointer which you can see through the highlighted colour red for the airport search LCY . AIRPORT P ACA 1 ACE 2 BER 3 BER 6 DEN 7 FRA 8 HAM 14 LCY 4 LCY 9 LGW 10 LHR 6 LHR 11 MUD 12 RTM 13 SELECT * FROM SCOUNTER WHERE AIRPORT = ‘LCY’

9. Options Unique Index: If the fields chosen in an index are very unique i.e., for each combination of fields only one entry exists , then we chose unique index. Non-Unique Index: If the above is not true, we choose this option. We have 3 radio buttons under this called Index on all database systems, For selected database systems , and No database index . We can chose any one of them depending on our requirement. For client Dependent tables, we always include the field “MANDT” under all the indexes or else, Optimizer might not use the Index.

10. Tables that allow Indexing Not all the tables allow you to create an Index. They are allowed only for transparent tables . All business data and application data are stored in transparent tables. Ex: VBPA (Sales Document: Partner), VLCVEHICLE (VELO: Vehicle), MARA (General Material Data), etc..

11. Secondary indexes are not allowed for the below tables but gives a message saying ‘Index maintenance only possible for transparent tables’. Cluster tables: Ex: BSEG (Accounting Document Segment), KONV Conditions (Transaction Data) ), etc… Pooled tables: Ex: Configuration tables, etc..