Indexes in Database Tables
An index in a database table helps to speed up the selection of rows. An index is a sorted copy of selected database table fields. An additional field contains a pointer to the actual table rows. Sorting enables faster access to the rows in the table, for example in binary searches. A database table has at least one primary index defined by its key fields. It can also have one or more optional secondary indexes.
7.31 | 7.40 | 7.54
The primary index is a unique index constructed from the key fields of the primary key. It is always created automatically in AS ABAP. A maximum of one record exists in the table for each combination of index fields. If the primary index cannot be used to identify the results set, for example because no field from the primary index was selected, the table is scanned in full or an attempt is made to use a suitable secondary index (if one exists).
Alongside the primary index defined using the primary key, both unique and non-unique secondary indexes can be created for a database table. Creating secondary indexes usually improves the performance of database reads that evaluate the indexes of the database.
The secondary indexes of a database table consist of a series of table fields and are identified by an alphanumeric index ID with a maximum of three characters. The ID 0 is reserved for the primary index. Table fields with the predefined data types STRING and RAWSTRING must not be index fields.
The secondary indexes defined for a database table are created when the table itself is created in the database system. Furthermore, new secondary indexes can be added later in the same system. When further secondary indexes are added in other systems without making modifications, they are created as extension indexes. The following are recommended as namespaces for indexes added at a later time:
- The IDs of indexes added to delivered tables by customers start with "Y" or "Z".
- The IDs of indexes added to delivered tables by partners start with "J". There can be conflicts between the index names of different partners in follow-on systems.
- The IDs of indexes added to other tables can have any names, but cannot start with "Y", "Z", or "J".
The name of an index on the database is usually DBTAB~ID, where DBTAB is the name of the database table and ID is the three-character ID. Other names can occur, however, and blanks can be padded using underscores.
A secondary index can be unique, but (unlike the primary index) does not have to be. In the case of unique indexes, the database table cannot contain multiple rows with the same values in the index fields. Any attempts to insert a row like this cancel processing in the database and raise an appropriate exception in ABAP. A unique index of a client-specific table must always contain the client field.
When the database is accessed, the optimizer of the database system checks whether a suitable index exists and uses it if appropriate. The index selected depends on the platform, which means it is possible in ABAP Dictionary to define to which database systems a non-unique secondary index is applied or not:
- Index in all database systems
- In selected database systems
- No database index
Unique secondary indexes are always created and can then no longer be deleted from the database. The SQL Trace function in the Performance Trace tool (transaction ST05) can be used to determine which index is being used by the database system to access data.
The value of an index for selecting data from a table depends on how well the data set selectable using the index represents the final set selected. Only those fields are useful in an index that make a significant restriction on the results set of a selection. If an index is constructed from multiple fields, it can also be used if only a few of these fields are specified in a selection condition. Here, the order of the fields in the index is an important factor in how quickly they can be accessed. The first fields must be those filled with constant values in a large number of selections. In selections, an index is useful only until the first field not specified in the selection condition. Alternatively, an index field is generally used only when all index fields located in front of it are specified in the selection condition. The speed at which a field is accessed is not affected by whether or not an index is defined as unique.
Creating secondary indexes is beneficial in the following cases:
- If table entries are to be selected based on fields that are not contained in an index, and the response times are very slow, a suitable secondary index should be created.
- The field or fields of a secondary index are so selective that each index entry corresponds to a maximum of 5% of the total number of table entries.
- The database table is accessed mainly to read entries. When accessing a table to modify entries, each additional index must also be updated.
- If only those fields are read that also exist in the index, then the data does not need to be accessed a second time after the index access. If only a very small number of fields are selected, there can be significant efficiency gains if these fields are included in an index in their entirety.
Secondary indexes can also place a load on the system, since they need to be adjusted each time the table content is modified. Each additional index slows down the insertion of rows in the table. Tables where new rows are often created should only have a small number of indexes. Too many indexes can also cause the database system optimizer to select the wrong index. To prevent this, the indexes in a table must be as disjoint as possible (meaning that they share as few fields as possible).
An index should only consist of a few fields; as a rule, no more than four. This is because the index has to be updated each time its fields are updated in a database operation. Fields that are suitable for indexes are:
- Fields that are selected often and that have a high level of selectivity. The most selective fields should be placed at the beginning of the index.
- A field should not be included in an index if its value is initial for most of the table entries.
- If more than one index is used for a database table, they should not overlap.
No more than five indexes should be created for any one table because
- Each index produces additional update costs.
- The amount of data increases.
- The optimizer of the database system is given too many selection options and becomes more error-prone.
An index can only support those selection conditions that describe the search value positively, such
LIKE. The response times of conditions
<>, for example, are not improved by an index. The optimizer
generally stops if the selection condition contains an
OR. In other words,
it does not evaluate the fields checked by
OR when selecting and applying
the index. An exception to this are
OR relationships standing on their own.
Therefore, conditions containing an
OR join for one of the indexed fields should be reformulated if necessary.
The zero value in some database systems is ignored by the indexes, meaning that no index can be used when selecting by zero values.
The optimizer stops working when it encounters
OR in the following
WHERE carrid = 'LH' AND
( CITYFROM = 'FRANKFURT' OR cityfrom = 'NEW YORK' ).
When replaced by the equivalent statement (below), the entire condition can be optimized with respect to the existing indexes.
WHERE ( carrid = 'LH' AND cityfrom = 'FRANKFURT' ) OR
( carrid = 'LH' AND cityfrom = 'NEW YORK' ).
Full Text Index
The SAP HANA database supports a full text index as a secondary table index. A full text index is created as an additional invisible column on the database. The content of the column created for a full text index is saved to this additional column with appropriate formatting and is evaluated when the relevant data is accessed.
The following conditions must be met:
- A full text index can only be created for the SAP HANA database and for tables with the storage type column store.
- A full text index can only be created for precisely one column in a database table whose predefined data type is CHAR, SHORTSTRING, STRING, or RAWSTRING.
- The database table must have a column for the text language.
A full text index is always non-unique. Accesses that make use of the full text index are based on the SQL language element WHERE CONTAINS .... This is not currently supported by Open SQL and Native SQL must be used instead.
For more information about the full text index, see the SAP HANA Developer Guide.