ABAP REPORTS: DATA BASE INDEX

Showing posts with label DATA BASE INDEX. Show all posts

Index design in data base for SAP

Changing the index design of a table in the R/3 System can affect SQL statements other than the one you want to optimize. You must therefore ensure that other SQL statements are not negatively influenced by a new, changed or deleted index.

To do this, you must first establish which database views are referenced on the table. You can find this information in the where-used list in the ABAP Dictionary.

Next, perform an object-related shared SQL area analysis. This means that you restrict the analysis to the relevant tables or database views, rather than finding inefficient SQL statements in the whole system.

After that, change the index design (also activate the indexes, and update the table and index statistics). Immediately after you have changed the index design, reset the DB SQL cache (use the Reset pushbutton).

Repeat the object-related DB SQL cache analysis approximately two days after changing the index design. Because you reset the DB SQL cache, only information is displayed for SQL statements executed after the index design was changed.

To establish in which database views the tables to be analyzed are used, go to the initial screen of the ABAP Dictionary. To do this, from the SAP standard menu choose Tools >> ABAP Workbench >> Dictionary, or use Transaction SE12. Enter the table name in the appropriate field, and choose Where-used list.

In the dialog box that appears, select the checkbox Views and confirm your selection. A list appears, showing all the database views that use the table. You must perform the object-related DB SQL cache analysis for the database views and for the table itself.

To perform an object-related DB SQL cache analysis, first go to the initial screen of the database monitor. To do this, from the SAP standard menu choose Tools >> Administration >> Monitor >> Performance >> Database >> Activity, or use Transaction ST04.

Choose Detail analysis menu >> SQL request. A dialog box appears, where you can limit the SQL statements displayed. Delete the entries in the fields Buffer Gets and Disk Reads. In the Table field, replace the * with the name of the table to be analyzed.

Download to a local file the list of SQL statements contained in the DB SQL cache for the table. Save the Explain SQL display for each SQL statement. Perform the analysis in the same way for all database views that use the table.

A few days after you changed the index design, perform the same analysis again, and for each SQL statement compare the values for logical and physical accesses per execution. This second analysis enables you to ensure that none of the SQL statements analyzed is negatively influenced by the change in index design. The change should affect only the SQL statement to be optimized.

Changing Index design rules part two

Selective Analysis and distinct values :

When you are sure about what the index fields mean (that is, their semantic meaning), check how many distinct values there are for each index field. Distinct values are the number different values per field in a particular database table.

The relationship between the total number of data records in a table and the distinct values per field indicates how selective the index field is.

When the table and index statistics are updated, the distinct values are re-calculated. For index fields, the number of distinct values can be determined using Explain SQL (DB SQL cache or SQL trace).

For an overview of the distinct values for all fields of a database table, from the SAP standard menu choose Tools >> Administration >> Monitor >> Performance >> Database >> Activity >> Detail analysis menu >> State on disk, or use Transaction DB02. Then choose Detailed analysis. In the dialog box that appears, enter the table name under Object name, and confirm. In the next screen, choose Table columns.

Histograms:

The number of data records returned for each combination of index fields shows how selective an index is. This means that, if only a few data records are returned for a large number of index field combinations, an index is selective.

For an overview of how selective an index is for all index field combinations, a histogram can be calculated. Calculating a histogram is very expensive, because the number of data records returned must be checked for all index field combinations over the whole table.

To perform a selectivity analysis, use Transaction DB05. In the initial screen of DB05, enter a table name, select an analysis for primary key or for specified fields, specify the index fields if applicable, and submit the analysis in background or dialog mode.

If quotients are calculated from the total number of table records (635,336) and the number of distinct values for the index fields (16,607), the results show that an average of 30 data records are returned when the index is used.

If the number of distinct values does not increase from one index field to the next index field, this index field is not selective. It should therefore be omitted.

Remember that a selectivity analysis is an expensive operation, which should only be performed at times of low system activity.

CHANGING THE INDEX DESIGN IN ABAP DICTIONARY PART ONE

series.

abap communication interface 1

abap communication interface 2

abap communication interface 3

abap communication interface 4

abap communication interface 5

abap communication interface 6

Changing Index design rules

An index only makes sense if SQL statements that use the index return less than 5% of the table records. The index fields must therefore significantly reduce the resulting sets. Otherwise, the database optimizer would perform a full table scan anyway.

Indexes must not be contained in other indexes (that is, they must be disjunct), because the cost-based database optimizer can also select the index with the upper quantity. In addition, do not create indexes that can be selected accidentally.

To keep additional work for the database to a minimum, create as few indexes as possible for each table (approximately 5 indexes per table).

As a general rule, an index should consist of a maximum of 4 fields. If too many fields are specified in an index, additional work is created every time a database operation is performed, and the memory space required grows accordingly. Consequently, the index becomes less effective and the probabilty of it being selected is reduced.

The selective fields in an index should be as near to the beginning of the index as possible (see index range scan access strategy). Selective fields are, for example, document number, material number, and customer number. Unselective fields are client, company code, header account, and plant.

Do not change SAP standard indexes unless SAP explicitly recommends that you do so.

Before you perform a technical selectivity analysis, you must be sure what the various index fields in question mean. You should therefore type index fields according to their meaning.

Identifiers: These are particularly selective table fields. They are usually characterized by consecutive numbers (document number, object number, and so on) since they are assigned using a number range object.

Organizational units: These are fields such as sales organization, company code, or distribution channel. They are often very unselective, and should only be used in secondary indexes in exceptional circumstances.

Status fields: These can be very selective if, in an SQL statement, values are selected where only a few corresponding data records exist in the table (for example, open orders, if most of them are complete). Conversely, they can also be very unselective.

Classifiers: These are fields where typing is performed (for example, sales order, planned order, and production order). In general, classifiers are not selective, as few different versions exist, and they are usually distributed relatively evenly.

Date and time: These are often selective, and can be used in a secondary index.

Text fields: These are generally selective, but they are also very long. They should therefore not be used in a secondary index, because it would become too wide.

Data Base Joins part two

Sort merge join

When a join is processed using the sort merge join access strategy, the following steps are performed:

The table records that correspond to the WHERE clause are selected

The tables in the join are sorted according to the JOIN condition

The table records are merged

For the SQL statement above, the selective WHERE condition for field CUOBJ, table VVBAP, and the selective WHERE condition for field OBJNR, table VVBAK are evaluated. The resulting sets are sorted according to VBELN. The results are then merged.

n If selective WHERE conditions exist for the relevant tables, a sort merge join is very effective. If the JOIN conditions are not selective for any of the relevant tables, the sort merge join access strategy is more effective than a nested loop. If there are more than two tables in the join, you can combine the nested loop and sort merge join access strategies.

Access staginess for data base joins

Nested loop: This strategy is relevant for database views and ABAP JOINs. First, the WHERE clause is used as a basis for selecting the (outer) table to be used for access. Next, starting from the outer table, the table records for the inner tables are selected according to the JOIN condition.

Sort merge join: First, the WHERE clause is evaluated for all tables in the join, and a resulting set is produced for each table. Each resulting set is sorted according to the JOIN conditions and then merged, also according to the JOIN conditions.

RELATED POST

DATA BASE JOINS PART ONE

SAP ABAP SAMPLE CODE 1 REPLACE COMMENTARY IN ALV REPORT

SAP ABAP SAMPLE CODE FOR ALV EXECUTABLE PROGRAM

SAP ABAP SAMPLE CODE FOR HIRACHICAL REPORT

SAP ABAP SAMPLE CODE FOR ALV LIST DISPLAY REPORT

SAP ABAP SAMPLE CODE FOR ALV LAYOUT DISPLAY REPORT

Data Base Joins

In the R/3 System, users often need to access information stored in different tables. The database-logical operator used to do this is called the JOIN operator.

In ABAP, there are various ways to implement the JOIN operator (nested SELECTs, SELECT FOR ALL ENTRIES, DB views, ABAP JOINS, subqueries, explicit cursor).

If you want the database system to determine the resulting set of the JOIN operator, you can use DB views, ABAP JOINs or subqueries. DB views are first created in the ABAP Dictionary, and are created on the database when the Dictionary object is activated. DB views created by other developers can also be used (reusability). ABAP JOINs are formulated in ABAP directly.

The following important access strategies are available to the database optimizer for processing ABAP JOINs, subqueries, or SQL statements against database views:

Nested Loop:

When a join is processed using the nested loop strategy, two steps are performed:

The order of access is determined

Ÿ Data is selected from the tables

The database optimizer first determines the order in which the tables in the join are to be accessed. To do this, it uses the WHERE clause to estimate the number of returned table records or data blocks for each table in the join.

To determine which table will be the outer table, the optimizer determines the table with the lowest number of returned table records or data blocks, because it assumes that in total the fewest data blocks need to be read in this table.

Therefore, the goal once more is to minimize the number of data blocks to be read (index blocks or table blocks). If there are more than two tables in the join, the inner table is selected in the same way.

In the second step, the table records from the outer table are first selected. For these table records, the table records from the next innermost table are read according to the join condition, and so on.

For the SQL statement above, the optimizer selects the table VVBAK as the outer table, because a selective WHERE condition exists for the field VVBAK-OBJNR. For each table record that fulfills the WHERE condition, table records from VVBUK are selected according to the join condition.

23
RELATED POST

DATA BASE INDEX IN DETAIL
Outbound process with out message control with scenario
With out message control edi with example
EDI with message control scenario with purchase order and part two

Data Base Index in detail

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 is very effective if a large part of a table (for example, 5% of all table records) needs to be read. In the above example, the full table scan access strategy is not effective, because only a few table records are required, but many table blocks need to be read.

Concatenation

In the concatenation access strategy, one index is reused. 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 resulting set are filtered out when the search results are concatenated.

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, the result is concatenated (combined).

Access Staginess

Index unique scan: The index selected is unique (primary index or unique secondary index) and specified fully. One or no table record is returned. This type of access is very effective, because a maximum of four data blocks need to be read.

Index range scan: The index selected is unique or non-unique. In the case of a unique index, not all index fields are specified in the WHERE clause. A range of the index is read and checked. An index range scan may not be as effective as a full table scan. The table records returned can range from none to all.

Full table scan: The whole table is read sequentially. Each table block is read once. Since no index is used, no index blocks are read. The table records returned can range from none to all.

Concatenation: An index is used more than once. Various areas of the index are read and checked. To ensure that the application reads each table record only once, the search results are concatenated, and duplicate entries are eliminated. The table records returned can range from none to all.

SAP AND ABAP TOPICS