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