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.
INDEX RANGE SCAN
To execute the SQL statement, the DBMS first reads a root block (1) and a branch block (2). The branch block contains pointers to two leaf blocks (3 and 4). In order to find the index records that fulfill the criteria in the WHERE clause of the SQL statement, the DBMS searches through these leaf blocks sequentially. The index records found point to the table records within the table blocks (5 and 6).
If index records from different index blocks point to the same table block, this table block must be read more than once. In the example above, an index record from index block 3 and an index record from index block 4 point to table records in table block 5. This table block must therefore be read twice. In total, seven data blocks (four index blocks and three table blocks) are read.
The index search string is determined by the concatenation of the WHERE conditions for the fields contained in the index. To ensure that as few index blocks as possible are checked, the index search string should be specified starting from the left, without placeholders ('_' or %). Because the index is stored and sorted according to the index fields, a connected range of index records can be checked, and fewer index blocks need to be read.
The range that can be used to select the index blocks (that is, the fully specified range starting from the left), therefore only consists of the field MANDT. Since many index records fulfill the condition MANDT = ‘001’, a large number of index blocks are read, and their index records checked. All the index records are then filtered out that fulfill the condition VBELN = ‘0000123’. The relevant index records point to the table records.
If a further WHERE condition had been specified in the SQL statement for a field that was not in the index, this WHERE condition would only have been evaluated after the table records had been read from the table blocks.
Due to old update statistics, database 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 are 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 WHERE condition to filter the index records. The table records corresponding to all suitable index records are therefore read.
More data blocks are read if an unselective index range scan is performed, than if the table is read sequentially. 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 total number of table blocks and the number of index records per index block.
All index blocks and table blocks read during an index range scan are stored in the data buffer at the top of an 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.
RELATED POST
DATA BASE INDEX OVER VIEW
Message control Configuration
Message control architecture part one and two
EDI output types part one and two
Message control and control table
Working of message control part one and two
No comments:
Post a Comment