Figure 76: Index Usage Graphic – Example 1
The way in which the database optimizer selects and uses database indexes depends partly on the WHERE conditions specified in the SQL statement. The order of the WHERE conditions in the SQL statement is not important (note that this is not the case with the order of the fields in the database index).
The index search string is based on the fields that are specified without gaps and with '=' in the WHERE condition. The index blocks to be checked are selected using the index search string.
Note: The sequence of fields in the WHERE clause does not affect index usage, however to ensure that the software can be easily read and maintained, it makes sense to program the fields in the sequence that they are defined in the table.
Figure 77: Index Usage Graphic – Example 1
When qualifying index fields it is essential that the first fields in the index are qualified. Gaps later on in the index search string are not as critical as gaps towards the front.
Caution: Always observe the sequence of the fields in the index.
The index fields are always defined from top to bottom in the Data Dictionary (SE11). The top fields correspond to the fields further towards the front and the bottom fields to those towards the back of the index.
Note: The following lesson will give a few rules of thumb. These statements should be understood as guidelines; they are intended as a rough orientation for the developer. You should always consult user manuals or the specifications of the database manufacturer for exact, reliable information.
Figure 78: Introduction to DB Indexes: Index Unique Scan
If, for all fields in a unique index (primary index or unique secondary index), WHERE conditions are specified with '=' in the WHERE clause, the database optimizer selects the access strategy index unique scan.
For the index unique scan access strategy, the database usually needs to read a maximum of four data blocks (three index blocks and one table block) to access the table record.
In the SELECT statement shown above, the table VBAK is accessed. The fields MANDT and VBELN form the primary key, and are specified with '=' in the WHERE clause. The database optimizer therefore selects the Index Unique Scan access strategy, and only needs to read four data blocks to find the table record requested.
Figure 79: Introduction to DB Indexes – Index Range Scan II
In this example, an index range scan is performed for table VVBAP, using the primary index. Because no WHERE condition is specified for field VBELN in the SQL statement, the corresponding places for VBELN are set to "_" in the index search string.
Therefore, the fully specified area that is used to select the index blocks only consists of the MANDT field. Because many index records fulfill the condition MANDT = "001", many index blocks are read and their index records are checked.
From the set of index records, all those that fulfill the condition POSNR="0001"
are filtered out. The relevant index records point to the table records.
If the SQL statement contains an additional WHERE condition for a field that is not contained in the index, this is not evaluated until after the table have been read from the table blocks.
Figure 80: Introduction to Database Indexes – Concatenation
In the concatenation access strategy, one index is reused repeatedly. Therefore, various index search strings also exist. An index unique scan or an index range scan can be performed for the various index search strings. Duplicate entries in the results set are filtered out when Concatenation is performed for the search results.
In the SQL statement above, a WHERE condition with an IN operation is specified over field VBELN. The fields MANDT and VBELN are shown on the left of the primary index. Various index search strings are created, and an index range scan is performed over the primary index for each index search string. Finally, concatenation is performed for the result.
Figure 81: Introduction to DB Indexes – Index Range Scan III
Due to old update statistics, database optimizer errors, missing indexes, or inappropriate ABAP coding, the database optimizer may select a completely unsuitable index, and then perform an unselective index range scan.
In the example above, WHERE conditions were only specified for MANDT and MATNR. In spite of this, the database optimizer chooses to perform an index range scan over the primary index (MANDT, VBELN, POSNR). Since there is only one unselective WHERE condition (MANDT = '001') to select the index blocks (due to the WHERE clause), a large number of index blocks are read.
There is no further WHERE condition to filter the index records. The table records corresponding to all suitable index records are therefore read.
More data blocks are read than if the table is read sequentially, if an unselective index range scan is performed. This is because a table block is read more than once if index records in different index blocks point to the same table record.
The maximum number of data blocks to be read per SQL statement execution is thus calculated from the product of the number of table blocks and the number of index blocks.
All index blocks and table blocks read during an index range scan are stored in the data buffer at the top of a LRU (least recently used) list. This can lead to many other data blocks being forced out of the data buffer. Consequently, more physical read accesses become necessary when other SQL statements are executed.
Figure 82: Full Table Scan
If the database optimizer selects the full table scan access strategy, the table is read sequentially. Index blocks do not need to be read.
For a full table scan, the read table blocks are added to the end of an LRU list.
Therefore, no data blocks are forced out of the data buffer. As a result, in order to process a full table scan, comparatively little memory space is required within the data buffer.
The full table scan access strategy becomes ever more effective the more data there is to be read in the table (for example, more than 5% of all table records).
In the example above, a full table scan is more efficient than access using the primary index.