Data Base Index over view

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

Partner profiles configuration part one and two
Overview of outbound parameters part one and two
EDI outbound parameters for message control
EDI inbound parameter views
Partner profile maintenance for sap abap

No comments:

Post a Comment