The system has multiple indexes, but your selections are not performing ? The system will automatically determine which index is used, which may not be what you expect at all. Here's how you can find out.
The data dictionary: indexes
The first thing you will need to do is find out which indexes are set up for the table. In these examples, the MARD material stock table is focussed on, but the setup applies to all transparent tables (including Z tables). Check the data dictionary SE11:
Each index listst the fields as description, but don't be deceived: this is a default description and only a description. Check the actual list of fields (index key fields) Double click on the index of choice to get to the index details and key fields.
Trial run: which index(es) is/are used ?
To check which index is used, the SQL trace functionality can be used (this is also the only way I know how to make this visible). Choose or set up a report that performs the selection you want to check and initiate the SQL trace - transaction ST05. Switch the trace on, perform your reporting actions (in another session) and switch the trace is off again. Display the trace results and find the selection you want to check. Use button "Explain" reveal the index used.
The example shows index usage for MARD and also for MAKT. The primary key for the MAKT is used, as it states: MAKT~0. The ~0 is reserved for the first available index of the table - the primary key. The table key itself is used. Notice the MARD~Z02 ? This shows that the Z02 custom index is used. The logging also reveals the type of index usage, one as INDEX RANGE SCAN and the other one as INDEX UNIQUE SCAN. A unique scan is an accurate hit, the result of the index search is in fact a single table record. Range scans return a table of results, which is then ploughed through line by line to get to selection results. The system is clever enough to pick the optimal index and even use several indexes - all dependant on the actual database server.
Trian run: which indexes are available
The information flow from the ST05 doesn't stop there: click on the index from the report above to reveal more info. Specific to table MARD this would show all indexes with the number of distinct hits per field on the index. Along with information on what is on the table (137mljn rows in the example below) this yields a lot of insight information - which is not easy to understand. Yet it;s all there - let it sink in and get a good understanding of the situation. Note that the nr of lines are gathered in a performance monitoring run, and these statistics are pretty old (2014) even though the screenshot is much more recent (july 2017).
There is also more detailed information on the actual index that was used for the ST05 simulation run. Branch levels of a B-tree ? A search method, which indicates the number of times it needed to read and compare to get to the selection result. Binary search trees. All covered for you, no need to do anything about it.
