Indexes - What are they and how to use?
An index helps to speed up selection from the database. An index is a sorted copy of selected database table fields.
When you activate the table (in Oracle, Informix, and DB2) in the database, an index that is formed from all the key fields is created in addition to the table itself.
This index is called the primary index. It is unique by definition.
If the primary index cannot be used to determine selection result, (for example, the WHERE condition does not contain any primary index fields), the system searches the whole table. To prevent this, and determine the selection result by searching through a restricted number of database records, you can create a secondary index.
In addition to the primary index, you can define one or more secondary indexes for a table in the ABAP Dictionary and save them in the database. Secondary indexes can be either unique or non-unique.
When to create an index
It is worth creating a secondary index when:
- You want to select table entries based on fields that are not contained in an index, and the response times are very slow.
- The EXPLAIN function in the SQL trace shows which index the system is using. You can generate a list of the database queries involved in an action by entering Transaction ST05 and choosing Trace on → Execute action → Trace off → List trace. If you execute the EXPLAIN SQL function on a EXEC, REEXEC, OPEN, REOPEN or PREPARE statement, the system returns a list containing the index used in the database query.
- The field or fields of the new secondary index are so selective that each index entry corresponds to at most 5% of the total number of table entries. Otherwise, it is not worth creating the index.
- The database table is accessed mainly for reading entries.
The Database Optimizer
The database optimizer is a database function that analyzes SQL statements and defines an access strategy. It is the database optimizer that determines whether one of the existing indexes will be used, and if so, which one.
A rule-based optimizer analyzes the structure of an SQL statement (mainly the SELECT and WHERE conditions without values), and the index of the table(s) involved. It then uses a set of analysis rules to decide the most appropriate procedure for executing the statement.
A cost-based optimizer in addition analyzes some of the values in the WHERE condition and the table statistics. The statistics contain low and high values for the fields, or, in some cases, a histogram of the distribution of the data in the table.
ORACLE databases up to and including 7.1 use a rule-based optimizer in SAP systems with R/3 3.1 and earlier. From Oracle 7.2 (R/3 4.0), they use a cost-based optimizer. All other database systems use a cost-based optimizer.
Related: How to generate indexes?
The index search string:
The index search string is formed from the WHERE condition. To do so, the desired values of the fields contained in the index are concatenated. To minimize the number of index blocks that have to be checked, the index search string should be specified from the left without placeholders (._. or .%.) wherever possible. Because the index is saved sorted by the index fields, a contiguous range of index records can be checked, and only a few index blocks have to be read.
1. Negations in the WHERE Condition
Avoid use of NOT. Use positive formulations instead. Negations in the WHERE condition (NOT operator) will usually prevent the optimizer from considering the field in question when searching for a suitable index. If this makes it impossible to find a suitable search range, determining the corresponding hit-list can become very processing-intensive, resulting in long runtimes.
Therefore, you should avoid negations of potential index fields whenever possible.
In contrast, negations involving fields that are not contained in the index do not pose a problem. They are only used to reduce the number of hits anyway.
2. Intervals in the WHERE Condition (using operators)
The quality of operators used:
3. SELECT-OPTIONS and RANGES Tables
These two statements implicitly declare an internal table with the following fields: SIGN, OPTION, LOW, and HIGH.
RANGES tables that are declared with SELECT-OPTIONS are usually filled dynamically on screen by the end user, while RANGES tables that are declared with RANGES are filled by the program. The database interface translates the individual rows in a RANGES table into a format that the DBMS can interpret and joins them with OR. The created SQL statement is then passed on to the database. The RANGES table can contain rows with complex expressions (BT = BETWEEN, CP = LIKE, and so on). Accordingly, the SQL statement that results from a ranges table can be extremely complex and difficult for the DBMS to process. If you have a program fill the RANGES tables, you have to make sure that the number of items is limited. If the RANGES table is too large, the analysis of the OR or IN list will result in a complex database statement that will be extremely expensive for the database to process.
4. Inner ORs
Avoid inner ORs. Formulate with IN instead
You should replace .inner. OR conditions with IN conditions whenever possible.
In general, .outer. OR conditions, those outside the parenthesis of the WHERE condition are better than complex clauses with inner ORs.
5. Access with the ORDER BY Addition
If you want an SQL statement to return a sorted result, you can either use an ORDER BY clause to do so in the database, or use the ABAP SORT statement for an internal table.
SORT statement or ORDER BY PRIMARY KEY are preferred.
Rules for creating indexes
- Few indexes per table (4-7)
- Fewer short fields in the index
- Selective fields in the front
- Disjunctive indexes
- No indexes of transaction data
- Change existing indexes before you build new ones
- Under certain circumstances, it can even be advantageous to delete indexes
- Do not change any SAP indexes or SAP tables (unless instructed to do so by SAP, refer the SAP notes)
Related: Rebuilding Indexes in SAP
Forcing an index:
SAP chooses whether to use a particular index or not (based on the performance) We can create an index and see if SAP is choosing it while executing the select statement. However, if SAP does not choose to use the index, we can force the program to use the index by using the statement:
%_HINTS ORACLE 'INDEX("BSAK" "BSAK~1")'
(For Example if the name of the index is 1 and name of the table is BSAK)