ABAP Developmentadvanced

Performance Part 10: Indexes, Selectivity and WHERE Conditions

Key takeaway

Understand how indexes serve WHERE conditions: primary key and secondary indexes (SE11), field order and leading fields, selectivity, conditions that prevent index use (NOT, < , leading wildcards, functions), client field, checking the execution plan in ST05, HANA specifics, and when (not) to create a new index.

Explanation

📌 KEY POINTS
✅ An index helps only if the WHERE condition specifies its leading fields (in order: MANDT, then the first key fields) with selective values.
✅ Selectivity matters: a condition that matches 1% of rows benefits from an index; a condition matching 50% usually doesn't.
✅ Use ST05 "Explain" to see which index the database actually uses — don't guess.
⚠️ New secondary indexes cost insert/update performance and storage; on HANA, column store scans are often fast enough — create indexes only after measurement and with Basis/DBA alignment.

━━━━━━━━━━━━━━━━━━━━
▶ WHERE WE ARE
Parts 1–9 reduced calls and data volume. Finally, each remaining SELECT must find its rows efficiently.

━━━━━━━━━━━━━━━━━━━━
▶ 1. PRIMARY KEY AND SECONDARY INDEXES

Primary index: key fields in table order (e.g. VBAP: MANDT, VBELN, POSNR).
Secondary indexes in SE11 (Indexes button): e.g. BSEG, VBAK~A (ERDAT) depending on release; check what exists before designing WHERE.

━━━━━━━━━━━━━━━━━━━━
▶ 2. LEADING FIELDS

Index on (MANDT, KUNNR, ERDAT): WHERE kunnr = … AND erdat IN … → good; WHERE erdat IN … only → cannot use leading KUNNR efficiently.
Open SQL adds MANDT automatically (client handling).

━━━━━━━━━━━━━━━━━━━━
▶ 3. CONDITIONS THAT HURT

NOT, <>, NOT IN on indexed fields.
LIKE '%abc' (leading wildcard).
Very broad ranges or IN lists with many values.
Selecting via non-key text fields (e.g. names) without an index.

━━━━━━━━━━━━━━━━━━━━
▶ 4. ALTERNATIVE ACCESS PATHS

Use existing index tables instead of the big table: e.g. VBAK by customer via VAKPA, FI open items via BSID/BSAD (ECC) or suitable CDS views in S/4HANA, material documents via MKPF/MSEG indexes (ECC) or MATDOC (S/4).
Document flow via VBFA with preceding document key.

━━━━━━━━━━━━━━━━━━━━
▶ 5. HANA SPECIFICS

Column store with in-memory scans: many queries don't need secondary indexes; the primary key and good filters are usually enough.
Still important: selective filters, avoiding huge result sets, correct join conditions.

━━━━━━━━━━━━━━━━━━━━
▶ 6. CREATING AN INDEX
Only after measurement (ST05 explain shows full scan on frequent, selective queries), with analysis of write load and DBA/Basis approval; transport via SE11.

━━━━━━━━━━━━━━━━━━━━
▶ STEP BY STEP

Look at the indexes of VBAK in SE11.
Trace a SELECT by ERDAT and one by KUNNR in ST05 and compare the execution plans.
Rewrite a query to use an existing index table or a more selective leading field.

✅ CHECKPOINT: you write WHERE conditions that match indexes and verify access paths with ST05.

Code example

ABAP Code

REPORT zperf_index. DATA: lt_vbak TYPE STANDARD TABLE OF vbak,      lv_rows TYPE i. PARAMETERS: p_kunnr TYPE kunnr OBLIGATORY,            p_from  TYPE erdat DEFAULT '20260101'. START-OF-SELECTION.* ===== Poor: no leading index field, leading wildcard, negation =====*  SELECT * FROM vbak INTO TABLE lt_vbak*    WHERE bstnk LIKE '%123'        " leading wildcard -> no index use*      AND auart <> 'ZRE'.          " negation -> not index-friendly * ===== Better: selective fields matching an index (check SE11 indexes first) =====  SELECT * FROM vbak INTO TABLE lt_vbak    WHERE kunnr = p_kunnr            " leading field of a customer-based index (if present)      AND erdat >= p_from.   DESCRIBE TABLE lt_vbak LINES lv_rows.  WRITE: / 'Orders:', lv_rows. * ===== Alternative access path via an index table (ECC-style example) =====*  VAKPA: sales documents by partner -> read VBELN list, then VBAK by primary key*  SELECT vbeln FROM vakpa INTO TABLE lt_keys WHERE kunde = p_kunnr AND parvw = 'AG'. * ===== Verify =====* ST05 -> trace -> select statement -> Explain -> which index / full scan?

Real project scenario

A customer service transaction searched orders by purchase order number (BSTNK) with LIKE '%…'. On a large ECC system each search took 40 seconds with a full table scan.
The team changed the search to require the beginning of the PO number (no leading wildcard), used an existing secondary index on BSTNK after checking SE11, and verified the plan with ST05 explain — searches now return in under a second. A proposed new index on a rarely used field was rejected after write-load analysis.

Common mistakes

WHERE without leading index fields.
Leading wildcards and negations.
Creating indexes without measurement.
Ignoring existing index tables/views.
Assuming HANA needs the same indexes as other databases.

Best practices

Check SE11 indexes before writing WHERE.
Selective conditions on leading fields.
Verify with ST05 explain.
Use index tables or suitable views.
New indexes only with evidence and DBA alignment.

Interview angle

Questions: "When is an index used?", "Should you create a secondary index?", "How do you check the access path?"
Strong answer: index used when WHERE has selective leading fields (MANDT added automatically); avoid NOT/<>/leading wildcards; check SE11 indexes and ST05 explain plan; alternative index tables (VAKPA, BSID etc.) or views; new indexes only after measurement, considering write costs; HANA column store often doesn't need secondary indexes.

Practice this lesson

Quick check

1. Which WHERE condition is least likely to use an index?

2. Which index type is generally used for high-selectivity WHERE?

3. Before requesting a custom database index, what should you do?

Interview questions

1. A report filtered only by posting date and returned millions of rows slowly. How do you use indexes and WHERE conditions to fix it?

Answer it in your own words first, then compare.

2. How would you use parallel processing (aRFC or bgRFC) to shorten a batch run, and what risks do you plan for?

Answer it in your own words first, then compare.

Practice more with Interview AI →

Reference-verified: the steps are checked against official SAP documentation and tutorials, and object and field names in the sample code are checked for consistency. The code has not been compiled or run on an SAP system by ERPClimb. Syntax marked release-dependent varies by SAP release, so check it in your own system.