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 VVBAK 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.

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.

17
RELATED POST

DATA BASE INDEX OVER VIEW
flow here with this four posts.

sap work flow part 1
sap work flow part 2
sap work flow part 3
sap work flow part 4

Overview

When you create a database table in the ABAP Dictionary, you must specify the combination of fields that enable an entry within the table to be clearly identified. The key fields must be specified at the top of the table field list, and define them as key fields. A minimum of 1 key field and a maximum of 16 key fields can be defined.

When the table is activated, an index formed from all key fields is created on the database (with Oracle, Informix, DB2), in addition to the table itself. This index is called the primary index. The primary index is unique by definition.

In addition to the primary index, you can define one or more secondary indexes for a table in the ABAP Dictionary, and create them on the database. Secondary indexes can be unique or non-unique.

If you dispatch an SQL statement from an ABAP program to the database, the program searches for the data records requested either in the database table itself (full table scan) or by using an index (index unique scan or index range scan). If all fields requested are found in the index using an index scan, the table records do not need to be accessed.

A data block shows the level of detail in which data is written to the hard disk or read from the hard disk. Data blocks can contain several data records. Conversely, a data record can extend over several data blocks.

Data blocks can be index blocks or table blocks. The database organizes the index blocks in the form of a multi-level B* tree. There is a single index block at root level, which contains pointers to the index blocks at branch level. The branch blocks contain either some of the index fields and pointers to index blocks at leaf level, or all index fields and a pointer to the table records organized in table blocks. The index blocks at leaf level contain all index fields and pointers to the table records from the table blocks.

The pointer that identifies one or more table records has a specific name. It is called, for example, ROWID for Oracle databases. The ROWID consists of the number of the database file, the number of the table block, and the row number within the table block.

The index records are stored in the index tree and sorted according to index field. This enables accelerated access using the index. The table records in the table blocks are not sorted.

An index should not consist of too many fields. Having a few very selective fields increases the chance of reusability, and reduces the chance of the database optimizer selecting an unsuitable access path.

RELATED POST

DATA BASE OPTIMIZER PART TWO

posts.

Work flow for MM PART 1

Work flow for MM PART 2

Work flow for MM PART 3

Work flow for MM PART 4

Work flow for MM PART 5

Work flow for MM PART 6

PERFORMACE DURING TABLE AINDEXINGCCESS :


An index can be used to speed up the selection of data records from a table.

An index can be considered to be a copy of a database table reduced to certain fields. The data is stored in sorted form in this copy. This sorting permits fast access to the records of the table (for example using a binary search). Not all of the fields of the table are contained in the index. The index also contains a pointer from the index entry to the corresponding table entry to permit all the field contents to be read.

When creating indexes, please note that:

An index can only be used up to the last specified field in the selection! The fields which are specified in the WHERE clause for a large number of selections should be in the first position.

Only those fields whose values significantly restrict the amount of data are meaningful in an index.

When you change a data record of a table, you must adjust the index sorting. Tables whose contents are frequently changed therefore should not have too many indexes.

Make sure that the indexes on a table are as disjunct as possible.


The database optimizer decides which index on the table should be used by the database to access data records.

You must distinguish between the primary index and secondary indexes of a table. The primary index contains the key fields of the table. The primary index is automatically created in the database when the table is activated. If a large table is frequently accessed such that it is not possible to apply primary index sorting, you should create secondary indexes for the table.

The indexes on a table have a three-character index ID. '0' is reserved for the primary index. Customers can create their own indexes on SAP tables; their IDs must begin with Y or Z.

If the index fields have key function, i.e. they already uniquely identify each record of the table, an index can be called a unique index. This ensures that there are no duplicate index fields in the database.

When you define a secondary index in the ABAP Dictionary, you can specify whether it should be created on the database when it is activated. Some indexes only result in a gain in performance for certain database systems. You can therefore specify a list of database systems when you define an index. The index is then only created on the specified database systems when activated.

Table buffering increases the performance when the records of the table are read.

The records of a buffered table are read directly from the local buffer of the application server on which the accessing transaction is running when the table is accessed. This eliminates time-consuming database accesses. The access improves by a factor of 10 to 100. The increase in speed depends on the structure of the table and on the exact system configuration. Buffering therefore can greatly increase the system performance.


If the storage requirements in the buffer increase due to further data, the data that has not been accessed for the longest time is displaced. This displacement takes place asynchronously at certain times which are defined dynamically based on the buffer accesses. Data is only displaced if the free space in the buffer is less than a predefined value or the quality of the access is not satisfactory at this time.

Entering $TAB in the command field resets the table buffers on the corresponding application server. Only use this command if there are inconsistencies in the buffer. In large systems, it can take several hours to fill the buffers. The performance is considerably reduced during this time.


The R/3 System manages and synchronizes the buffers on the individual application servers. If an application program accesses data of a table, the database interfaces determines whether this data lies in the buffer of the application server. If this is the case, the data is read directly from the buffer. If the data is not in the buffer of the application server, it is read from the database and loaded into the buffer. The buffer can therefore satisfy the next access to this data.

The buffering type determines which records of the table are loaded into the buffer of the application server when a record of the table is accessed. There are three different buffering types.

With full buffering, all the table records are loaded into the buffer when one record of the table is accessed.

With generic buffering, all the records whose left-justified part of the key is the same are loaded into the buffer when a table record is accessed.

With single-record buffering, only the record that was accessed is loaded into the buffer.


With full buffering, the table is either completely or not at all in the buffer. When a record of the table is accessed, all the records of the table are loaded into the buffer.

When you decide whether a table should be fully buffered, you must take the table size, the number of read accesses and the number of write accesses into consideration. The smaller the table is, the more frequently it is read and the less frequently it is written, the better it is to fully buffer the table.

Full buffering is also advisable for tables having frequent accesses to records that do not exist. Since all the records of the table reside in the buffer, it is already clear in the buffer whether or not a record exists.

The data records are stored in the buffer sorted by table key. When you access the data with SELECT, only fields up to the last specified key field can be used for the access. The left-justified part of the key should therefore be as large as possible for such accesses. For example, if the first key field is not defined, the entire table is scanned in the buffer. Under these circumstances, a direct access to the database could be more efficient if there is a suitable secondary index there.

With generic buffering, all the records whose generic key fields agree with this record are loaded into the buffer when one record of the table is accessed. The generic key is a left-justified part of the primary key of the table that must be defined when the buffering type is selected. The generic key should be selected so that the generic areas are not too small, which would result in too many generic areas. If there are only a few records for each generic area, full buffering is usually preferable for the table. If you choose too large a generic key, too much data will be invalidated if there are changes to table entries, which would have a negative effect on the performance.

A table should be generically buffered if only certain generic areas of the table are usually needed for processing.

Client-dependent, fully buffered tables are automatically generically buffered. The client field is the generic key. It is assumed that not all of the clients are being processed at the same time on one application server. Language-dependent tables are a further example of generic buffering. The generic key includes all the key fields up to and including the language field.

The generic areas are managed in the buffer as independent objects. The generic areas are managed analogously to fully buffered tables. You should therefore also read the information about full buffering.

Only those records that are actually accessed are loaded into the buffer. Single-record buffering saves storage space in the buffer compared to generic and full buffering. The overhead for buffer administration, however, is higher than for generic or full buffering. Considerably more database accesses are necessary to load the records than for the other buffering types.

Single-record buffering is recommended particularly for large tables in which only a few records are accessed repeatedly with SELECT SINGLE. All the accesses to the table that do not use SELECT SINGLE bypass the buffer and directly access the database.

If you access a record that was not yet buffered using SELECT SINGLE, there is a database access to load the record. If the table does not contain a record with the specified key, this record is recorded in the buffer as non-existent. This prevents a further database access if you make another access with the same key.

You only need one database access to load a table with full buffering, but you need several database accesses with single-record buffering. Full buffering is therefore generally preferable for small tables that are frequently accessed.


Since the buffers reside locally on the application servers, they must be synchronized after data has been modified in a buffered table. Synchronization takes place at fixed time intervals that can be set in the system profile. The corresponding parameter is rdisp/bufreftime and defines the length of the interval in seconds. The value must lie between 60 and 3600. A value between 60 and 240 is recommended.


Advantages and disadvantages of this method of buffer synchronization:

Advantage: The load on the network is kept to a minimum. If the buffers were to be synchronized immediately after each modification, each server would have to inform all other servers about each modification to a buffered table via the network. This would have a negative effect on the performance.

Disadvantage: The local buffers of the application servers can contain obsolete data between the moments of synchronization.

This means that:

Only those tables which are written very infrequently (read mostly) or for which such temporary inconsistencies are of no importance may be buffered.

Tables whose entries change frequently should not be buffered. Otherwise there would be a constant invalidation and reload, which would have a negative effect on the performance.
(48)


RELATED POSTS

DESCRIBE PART THREE

DESCRIBE PART FOUR

DESCRIBE PART FIVE

DESCRIBE PART SIX

DESCRIBE PART SEVEN

DETAIL

DIVIDE

DO