Ansicht
Dokumentation

ABENITAB_WHERE_OPTIMIZATION - ITAB WHERE OPTIMIZATION

TXBHW - Original Tax Base Amount in Local Currency BAL Application Log Documentation
This documentation is copyright by SAP AG.

- Optimizing the WHERE Condition

The statements LOOP AT, DELETE, and MODIFY can be specified together with a WHERE condition that selects specific lines of the internal table. Searches in a standard table using the primary table key are always linear and cannot be optimized. Searches using a sorted key or a hash key, however, can be optimized in certain circumstances. These keys are used for:

accessing a sorted table using its primary key.

accessing a hashed table using its primary key.

accessing a table of any table type using a secondary table key specified by USING KEY.

The optimization takes place by mapping all relational expressions or a group of the relational expressions in the WHERE condition to a specified key. This specified key performs a corresponding optimized key access (binary search or hash algorithm as in the statement READ TABLE or a table expression), to find one or more lines. The found line or lines are then checked using those remaining relational expressions from the WHERE condition that were not mapped to the specified key.

Prerequisites for the optimization are therefore:

The relational expressions of the WHERE condition can be transformed to a specified key.

The corresponding key access returns the same results as the evaluation of this part of the logical expression would. This is guaranteed only for compatible data types, since in the case of incompatible data types:

the content of the specified data objects is converted to the data type of the columns before the evaluation in the case of key accesses.

The comparison rules for incompatible data types apply when evaluating a comparison expression. Here, all the data types involved play a part in determining which operand is converted into which comparison type.

If there are no or insufficient relational expressions to meet both of these prerequisites, no optimization is possible and the behavior is as follows:

If a sorted table or a hashed table is accessed using the primary table key, all lines of the internal table are checked as in a standard table.

If the table is read using a secondary table key, that is, if said key is specified after USING KEY, a syntax error or syntax check warning, or an exception is raised. Access using a secondary table key must always be executed in an optimized way.

The following sections describe exactly when an access can be optimized.

Note

If the lines selected using WHERE are modified or deleted using MODIFY or DELETE and not just read using LOOP AT, further update costs are incurred in addition to the search for the lines. When deleting lines from standard tables, it should be noted that searches using a secondary key optimize the selection of lines to be deleted, but not the update required for the primary key, which is usually performed using a linear search.

Prerequisites for the Optimization of Hash Keys

In the case of hash key accesses, there must be exactly one relational expression combined using AND for each component of the key. This expression is either

a comparison expression with the comparison operator = (or EQ), whose operands meet the requirements above or

a predicate expression IS INITIAL without the addition NOT.

These relational expressions construct the part of the logical expression used for the key access. This part must not contain duplicate key components. The remainder of the logical expression can contain any number of relational expressions combined using AND. Optimization may not be possible, however, if the Boolean operators NOT or OR are used. Key components may be used in the relational expressions not used for the key access.

Note

Relational expressions other than comparisons using = (or EQ) or predicate expressions IS INITIAL are not part of the key access. This applies particularly to the tabular comparison operator IN range_tab, even if it can be traced back to optimizable comparisons.

Example

In the following example, the first two WHERE conditions can be optimized as key accesses with the secondary table key key. This is because:

The optimizable conditions for b, d, and e cover the entire key.
The optimizable conditions b, d, and e cover the entire key and a is a condition on a non-key column that is not involved in the part of the WHERE condition required for the optimization.
The optimizable conditions b, d, and e cover the entire key and the other two conditions on the key columns b and d are not optimizable and are hence not involved in the part of the WHERE condition required for the optimization.

The next six WHERE conditions cannot be optimized and produce syntax errors. This is because:

The key component d is not specified.
A key component is combined using OR instead of AND.
Two non-optimizable comparison operators are used.
An operand of type i is used in the comparison b of type c, which does not meet the requirements made on the operands.
The Boolean operator NOT is used in front of a key component.
A further comparison is combined using OR.
A further comparison is negated using NOT.

DATA: BEGIN OF line,
        a TYPE c LENGTH 3,
        b TYPE c LENGTH 3,
        c TYPE c LENGTH 3,
        d TYPE c LENGTH 3,
        e TYPE c LENGTH 3,
        f TYPE c LENGTH 3,
      END OF line.

DATA itab LIKE STANDARD TABLE OF line
               WITH UNIQUE HASHED KEY key COMPONENTS b e d.

DATA b_tab LIKE RANGE OF line-b.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d = '...' AND e IS INITIAL.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE a = '...' AND b = '...' AND d = '...' AND e IS INITIAL.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE  b = '...' AND d = '...' AND e IS INITIAL AND
            b IN b_tab AND d <> '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND e IS INITIAL.                     "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' OR d = '...' AND e IS INITIAL OR      "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d <> '...' AND e IS NOT INITIAL.  "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = 333 AND d = '...' AND e IS INITIAL.         "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...'  AND NOT d = '...' AND e IS INITIAL.  "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d = '...' AND e IS INITIAL OR     "syntax error
           a = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
     WHERE b = '...' AND d = '...' AND e IS INITIAL AND    "syntax error
           NOT a = '...'.
ENDLOOP.

Prerequisites for the Optimization of Sorted Keys

In the case of reads using a sorted key, the same applies as to a hash key. The only difference is that only an initial section of the key consisting of at least one component needs to be covered rather than the entire key.

The internal table is accessed in part sequentially. The starting point for the processing of the table is determined by a binary search using the subconditions that cover the table key completely or partially. From the starting point onwards, the table is only processed for as long as these subconditions remain fulfilled.

Example

In the following example, the first five WHERE conditions can be optimized as key accesses with the secondary table key key. This is because:

b is an initial part of the key.
b is an initial part of the key and a is an independent condition.
b and e are an initial part of the key.
b, e, and d are an initial part of the key and the order in the WHERE condition is ignored.
b is an initial part of the key and in this case, d is an independent condition, even though it is part of the key.

The next five WHERE conditions cannot be optimized and produce syntax errors. This is because:

e is not an initial part of the key.
No inequality comparison takes place.
A comparison in a ranges table is specified.
The Boolean operator NOT is used.
An additional OR relationship is used.

DATA: BEGIN OF line,
        a TYPE c LENGTH 3,
        b TYPE c LENGTH 3,
        c TYPE c LENGTH 3,
        d TYPE c LENGTH 3,
        e TYPE c LENGTH 3,
        f TYPE c LENGTH 3,
      END OF line.

DATA itab LIKE STANDARD TABLE OF line
               WITH UNIQUE SORTED KEY key COMPONENTS b e d.

DATA b_tab LIKE RANGE OF line-b.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE a = '...' AND b IS INITIAL.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...' AND e = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...' AND d IS INITIAL AND e = '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...' AND d <> '...'.
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE e = '...'.                    "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b <> '...'.                   "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b IN b_tab.                   "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key
             WHERE b = '...'  AND NOT e = '...'. "syntax error
ENDLOOP.

LOOP AT itab INTO line USING KEY key             "syntax error
             WHERE b = '...'  AND ( d = '...' OR e IS INITIAL ).
ENDLOOP.

Requirements Made on the Operands

The part of the logical expression that can be mapped to a key access must select the same lines as a statement READ TABLE or a corresponding table expression that specifies the corresponding components as keys.

When comparing incompatible data objects, the WHERE condition is subject to the same comparison rules for incompatible data types. Here, the data types involved determine which operand is converted to which comparison type.

If the additions WITH TABLE KEY and WITH KEY of the statement READ or KEY in a table expression are used, however, the content of the specified data objects is always converted to the data type of the columns before the comparison.

If this produces differing results, an optimization is not possible. Due to the complexity of the comparison rules, particularly for elementary data types, it is not a good idea to construct a set of rules detailing exactly when the comparison type matches the data type of the left operand. Generally speaking,

only fully compatible operands are optimizable,

in some cases, elementary operands with different data types can be optimized, if the value ranges or lengths are suitable. For example, a comparison of a column with the type of a floating point number with an operand of type integer or of a column of c with a similar operand, if its length is less than the length of the column.

For this reason, it is advisable to use only pairs of compatible operands in the WHERE condition. This guarantees that the differences in behavior of the WHERE condition and the specified key do not affect the result.

Example

The following example is largely similar to the example in the section WHERE log_exp in LOOP AT itab. In that case, access takes place using the primary key and no optimization. Here, however, access takes place using a secondary table key and a syntax check warning is produced and an exception raised when the program is executed. The number of lines in the internal table determines whether the exception is raised.

DATA text_short TYPE c LENGTH 2.
DATA text_long  TYPE c LENGTH 4.

DATA itab LIKE TABLE OF text_short
          WITH UNIQUE HASHED KEY key COMPONENTS table_line.

itab = VALUE #( ( 'AA' )
                ( 'BB' )
                ( 'CC' )
                ( 'DD' )
                ( 'EE' )
                ( 'FF' )
                ( 'GG' )
                ( 'HH' )
                ( 'II' )
                ( 'JJ' )
                ( 'KK' )
                ( 'LL' )
                ( 'MM' ) ).

text_short = 'AA'.
text_long  = 'AAXX'.

LOOP AT itab INTO text_short USING KEY key
             WHERE table_line = text_long.
ENDLOOP.
cl_demo_output=>write( |LOOP: { sy-subrc }| ).

"Statement
READ TABLE itab INTO text_short WITH TABLE KEY key
                                COMPONENTS table_line = text_long.
cl_demo_output=>write( |READ: { sy-subrc }| ).

"Expression
TRY.
    text_short = itab[ KEY key COMPONENTS table_line = text_long ].
  catch CX_SY_ITAB_LINE_NOT_FOUND.
    ...
ENDTRY.
cl_demo_output=>display( |Expression: { text_short }| ).

BAL Application Log Documentation Addresses (Business Address Services)
This documentation is copyright by SAP AG.

Length: 21063 Date: 20240425 Time: 022810 sap01-206 ( 218 ms )

Ansicht Dokumentation

ABENITAB_WHERE_OPTIMIZATION - ITAB WHERE OPTIMIZATION

- Optimizing the WHERE Condition

Note

Prerequisites for the Optimization of Hash Keys

Note

Example

Prerequisites for the Optimization of Sorted Keys

Example

Requirements Made on the Operands

Example

Ansicht
Dokumentation