ARTICLE
Optimization of the WHERE Condition
The statements LOOP AT ,
DELETE , and
MODIFY can be specified together with a WHERE condition
that selects specific rows 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 when:
processing a sorted table using its
primary key.
processing a hashed table using
its primary key.
Processing a table (any type) using a
secondary table key specified by
USING KEY .
The optimization takes place because all
relational expressions or a
group of the relational expressions in the WHERE condition are
mapped to a specified key. This specified key performs an appropriate
optimized key read (binary search or hash algorithm as in the
statement READ TABLE or a
table expression ), to find one or more
rows. The row or rows in question 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, as follows:
The relational expressions of the WHERE condition can be
transformed to a specified key.
The corresponding key read returns the same results as would the
evaluation of this part of the logical expression. 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 reads.
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 relational expressions or too few to meet both of these
prerequisites, no optimizations are possible and the behavior is as
followed:
If the primary table key is used to read a sorted table or a hashed
table, all rows of the internal table are checked (as in a standard
table).
If the table is read using a secondary table key (if specified after
USING KEY ), a syntax error or warning is produced or an exception
raised. Reads that use a secondary table key must always be optimized.
The following sections describe exactly when a read can be optimized.
Prerequisites for the Optimization of Hash Keys
In the case of hash key reads, there must be
precisely one relational expression joined using AND for each
component of the key. This expression is either
a comparison expression with
the relational operator = (or EQ ), whose operands meet the
requirements below or
a predicate expression IS
INITIAL without the addition NOT .
These relational expressions construct the part of the logical
expression used for the key read. This part cannot contain duplicate key
components. The remainder of the logical expression can contain any
number of relational expressions joined using AND . Optimization
may not be possible, however, if the boolean operator NOT or an
OR join is used. Key components may be used in the relational
expressions not used for the key read.
Note
Relational expressions other than comparisons using = (or EQ
) or predicate expressions IS INITIAL are not involved in the
key read. This applies particularly to the tabular relational operator
IN seltab , 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 reads 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 joined using OR instead of AND .
Two non-optimizable relational 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 joined 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 read in partly sequential form. The starting point
for the processing of the table is determined by a binary search using
the subconditions that cover the table key in full or in part. 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 reads 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.
An inequality comparison does not place.
A comparison in a selection 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 read must
select the same rows 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 influence 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, the table is read using the primary key and no
optimization. Here, however, the table is read using a secondary table
key and a syntax check warning is produced and an exception raised (when
the program is executed). The number of rows 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 }| ).
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved