Comprehensive Guide on Secondary Index in SAP ABAP: Applications, How to Build One, Constraints, and Sample Scenarios

Gain insight on SAP ABAP secondary index and answers to some common questions: What is it? What is it used for? How to make one? Constraints? And Applications. This should be a one stop shop for not only the fundamentals, but also the advanced concepts and practices of secondary index.

1. What is a SAP ABAP Index?

Before focusing on the secondary index, it is helpful to learn about the index within SAP ABAP and the necessity of this for the database.

In SAP ABAP, the index is the replicated and organized data entry fields of a database table. To perform an economical operation, the database engine leverages this organized data entry to quickly locate data; it bypasses the need for a laborious operation of thoroughly reading each row in a table. Reading in a verse order of the table reduces the need for a full table scan to retrieve insignificant numbers from the index.

A database index streamlines searches for an item by page number, instead of searching page by page.

In SAP, all transparent tables automatically get a primary index which consists of their primary key fields, upon table activation. The primary index is managed by the database, and comes at no added cost.

The primary index will be of no use when the field you are looking for, or filtering by, is not a primary key of the table. In these cases, the addition of a secondary index is needed.

2. What is Secondary Index in SAP ABAP?

A secondary index in SAP ABAP is a user-defined index on a database table that exists alongside the primary index, which is added to speed up data retrieval for fields other than the primary key. Secondary indexes allow the definition of new structures on the database that tap into expected data extracts or specific data readings. In particular, query-based data readings or extractions.

When you use a SELECT statement in ABAP, which defines a data reading and adds a WHERE clause using a non-key field from the database table, the database optimizer is able to use a secondary index to perform those readings, rather than a full table scan. In these cases, it will significantly cut the number of readings on the database, especially for large tables.

Secondary indexes can be created at the ABAP Data Dictionary level by using Transaction SE11. They are created physically in the base database table after the table is activated.

Comparison: Primary Index and Secondary Index

3. Explain the Use of Secondary Index in SAP ABAP?

It is important to know the use of the secondary index in SAP ABAP. Firstly because secondary indexes are not meant to be added to every table. They are created on a performance basis for specific situations.

Primary Uses of Secondary Index in SAP ABAP

• Enabling SELECT queries on large tables to execute instantaneously — SELECT queries on tables having millions of records and requiring WHERE clause to filter on non-key fields. The secondary indexes in those cases allow the database to narrow down the result set rather than the database diving into the millions of records.
• Enhancing the performance of reports — Reports that are developed in the ABAP using transaction code Z to join multiple tables, or to filter based on the fields of the date, status or category would significantly improve in performance with a secondary index.
• Background Job Optimization — When batch processes that manipulate vast data sets use accurately defined secondary indexes to support their SELECT statement, their turnaround time decreases significantly as they cut down on I/O operations.
• Support of common filter field indices — When specific fields such as document status, posting date, or company code appear multiple times within the WHERE clause of crucial programs, the performance of the entire system can significantly be improved by adding a secondary index to that field.
• Lessening the database load — When the database server reads through an entire table less often, I/O load is negated and performance improves for the whole system.

When are Secondary Indexes most beneficial?

Secondary indexes are most beneficial when:

• A table contains a large number of records (usually 100,000 or more).
• The fields in the WHERE clause of the SELECT statements have high selectivity (the list of outcome records is short).
• The same non-key fields are repeated throughout multiple programs and transactions.

4. Secondary Indexes Creation in SAP ABAP

Each and every ABAP developer should be adept at creating secondary indexes in SAP ABAP. The procedure is straightforward and is accomplished in the ABAP Data Dictionary using Transaction SE11.

Transaction: SE11

A Comprehensive Guide: Creating a Secondary Index in SAP ABAP

1. Launch Transaction SE11 (the ABAP Dictionary).
2. Choose the "Database Table" radio button and specify the name of your database table, for instance, ZSALESDATA. Click Display or Change.
3. In the menu, select Goto → Indexes.
4. In the Indexes dialog, either click Create or simply press the F5 key.
5. You need to type a 3-character index ID. (For customer indexes, prefix it with 'Z'. For example, Z01, Z02, etc.) Hit the green checkmark to carry on to the next step.
6. Give a short description of the index. Example: "Secondary Index on Sales Date and Status".
7. In the Index Fields section, add the fields to consider for the index (including but not limited to fields SALES_DATE, STATUS, REGION).
8. Indicate the Index type:
   • Non-unique index — (most secondary indexes): accept fields with any duplicates
   • Unique index — only allow a selected few fields to be unique
9. Click Save and add a transport request.
10. Finally, click Activate. Indexes are created at the database table and after activation, they can be visible.

5. How to Use a Secondary Index in SAP ABAP

Once a secondary index in SAP ABAP is created, it is usually easy to implement in your code. The only issue is that there are specific guidelines to be followed that will allow the database to leverage the index.

The Golden Rule: Match the WHERE Clause to the Index Fields

If the WHERE clause does not use the leading fields of the index, the optimizer will not use the secondary index. If you skip the leading field in the index and only filter on the second or third field, the optimizer will simply not use the index.

Example: Using Secondary Index in a SELECT Statement

Assuming a secondary index Z01 has been created on the table ZSALESDATA with the fields SALES_DATE and STATUS:

" This SELECT will use the secondary index Z01
" because SALES_DATE is the leading field
SELECT *
  FROM zsalesdata
  INTO TABLE lt_sales
  WHERE sales_date = lv_date
    AND status     = 'OPEN'.

" This SELECT will NOT use the secondary index
" because leading field SALES_DATE is absent
SELECT *
  FROM zsalesdata
  INTO TABLE lt_sales
  WHERE status = 'OPEN'.

Hinting the Database to Use a Specific Index

There are cases in which the database optimizer will choose a different execution plan. You can hint the database in a SELECT statement to use a specific index. This method is database-specific and should be used with care.

" Using a database-specific index hint
SELECT *
  FROM zsalesdata
  INTO TABLE lt_sales
  %_HINTS ORACLE 'INDEX("ZSALESDATA" "ZSALESDATA~Z01")'
  WHERE sales_date = lv_date
    AND status     = 'OPEN'.

6. Indexing in SAP ABAP

In addition to database indexing, SAP ABAP also supports internal table indexing for use with LOOP statements. A concept that differs from secondary indexes of database tables, it holds equal value when it comes to processing in-memory data.

Loop with Index in SAP ABAP

When a LOOP construct is applied to an internal table, ABAP processes the rows in a sequential manner. The system variable SY-TABIX holds the index of the current row being processed. You can leverage this variable to control the process, get to a row, manipulate it, or delete it.

DATA: lt_employees TYPE TABLE OF zemployee,
      ls_employee  TYPE zemployee.

" Loop with index tracked via SY-TABIX
LOOP AT lt_employees INTO ls_employee.
  WRITE: / sy-tabix, ls_employee-emp_name.
ENDLOOP.

Using FROM and TO with Index

You can LOOP over a specific range of rows contained in an internal table using the FROM and TO additions:

" LOOP from rows 5 to 10
LOOP AT lt_employees INTO ls_employee FROM 5 TO 10.
  WRITE: / sy-tabix, ls_employee-emp_name.
ENDLOOP.

Sorted Table Index for Performance

For larger internal tables, a SORTED TABLE with a specified key enables ABAP to employ a binary search mechanism instead of performing a linear search — analogous to the operation of a secondary index at the database level:

DATA: lt_sorted_emp TYPE SORTED TABLE OF zemployee
                    WITH UNIQUE KEY emp_id
                    WITH NON-UNIQUE SORTED KEY sk_dept
                         COMPONENTS department.

" READ using secondary table key
READ TABLE lt_sorted_emp
  WITH TABLE KEY sk_dept
  COMPONENTS department = 'HR'
  INTO ls_employee.

7. How Many Secondary Indexes in SAP ABAP?

A frequently asked question by developers is: how many secondary indexes can you have per table in SAP ABAP?

SAP permits a maximum of 16 secondary indexes for each database table. This limit is consistent across all of the database engines supported by SAP (Oracle, HANA, SQL Server, DB2, etc.).

Important Points on the Limit

• The 16-index limit encompasses all secondary indexes, both active and inactive.
• Concerning SAP HANA, index building is managed clearly differently. HANA utilizes column-based storage and possesses many automatic optimization mechanisms; consequently, the requirement for user-defined secondary indexes is significantly less compared to older database systems, such as Oracle or SQL Server.
• You can check all the defined indexes for a table in SE11 → Goto → Indexes.
• Index IDs A00 to XFF are reserved for SAP's own use. For customer-defined indexes, IDs must begin with a Z, followed by two alphanumeric characters.

8. Downsides of Secondary Index in SAP ABAP

Despite the benefits that secondary indexes provide in terms of read speed, there are notable downsides. Comprehending the downsides of secondary indexes in SAP ABAP can guide you on when to utilize secondary indexes, and when to abstain.

Major Downsides

• Decreased INSERT, UPDATE, and DELETE performance — In secondary indexes, every insert or update in a table requires the engine to touch all of the secondary indexes. This leads to performance impacts when write operations occur and as a result can impact the performance of mass data loads and batch updates.
• Decreased available space — Secondary indexes persist a copy of the indexed columns along with the index structure that includes pointers to the indexed rows. This can consume quite a bit of space when a table is large.
• Decreased maintenance performance — Secondary indexes require maintenance. This entails reviewing, rebuilding, and reorganizing the indexes. After a time, secondary indexes can become fragmented which can degrade the performance that the secondary indexes provide (especially when not in HANA).
• Indexer perplexity — Secondary indexes can perplex a database, as having many indexes can confuse a database's query optimizations. The result of this can lead the database to traverse a performance tradeoff.
• Over-indexing — In many cases developers don't analyze the root cause of the performance impacts which leads to multiple performance tradeoffs resulting in a majority slowdown of read performance over a minority speedup of the write performance.
• Low Selectivity Fields — An index created on a field such as GENDER can only be either M, F, or left as NULL essentially. Indexes should only be created for fields that contain a wide variety of non-consecutive values.
• SAP HANA — In some instances, creating a secondary index in SAP HANA may be a performance detractor. HANA uses a columnar database to optimize many query patterns without requiring a secondary index.

9. Recommendations

When using secondary indexes, a favorable strategy will yield a performance gain without introducing additional complications.

Start with Analysis

• Always trace the query using Transaction ST05 or record using Transaction SE30 to see if the query is in need of a full table scan.

Select Value Fields

• If the field values contain high selectivity — that means their values narrow the end result of the query significantly.
• Indexes should be avoided on fields with very few distinct values, such as boolean fields or fields that are used to indicate the gender.
• Keep the indexes to 2–4 fields wherever feasible as wider ones utilize more space and contribute to increased maintenance overhead.

Match with Query Expectations

• The leading secondary index field must be the first field in the WHERE clause of your SELECT statement.
• Draft the index to address the chief distinguished SELECT statements that access the table and note the most common criterion used.

Monitor After Creation

• Once you have activated the index, you can run the SQL trace once more to determine whether the index is being picked up by the database optimizer.
• Also, from time to time, you can check the indexes with DB02 (Database Performance Monitor), and observe the amount of fragmentation or unused indexes.

Basis and DBA Coordination

• When you are going to create or remove secondary indexes on big production tables, you have to notify your Basis team and Database Administrator.
• For SAP HANA systems, HANA optimization guidelines have to be followed before you choose to manually add indexes.

10. Wrap-Up

The secondary index in SAP ABAP is one of the great options available to optimize database query performance in the context of custom ABAP developments. It turns a report or batch job that is slow and of uncertain execution into a prompt and reliable process, through the correct use of indexes in the relevant tables.

Key Points in this Guide:

Using secondary indexes will help you with better response times, optimal performance, and quality of your ABAP applications regardless of whether you are implementing a new SAP S/4HANA system, or you are trying to improve an existing ECC system.

FAQs