GUIDELINE 6.31
Secondary Key
ABAP_BACKGROUND
You can declare secondary keys for internal tables. The following
options are possible:
Sorted keys (non-unique or unique)
Hash keys that can only be unique
These keys are assigned a name when they are declared and can be created
for any table type. The primary key (nameless up to this point) is
assigned a predefined name ( primary_key ), which allows it to be
explicitly addressed. A secondary table index is created internally for
each sorted secondary key. This enables index access to hashed tables.
When accessing internal tables, you must specify which table key or
index should be used. If nothing is explicitly specified, the internal
table is accessed using the primary key or primary index by default.
Access to internal tables using secondary keys is always optimized. This
generally increases read access performance significantly. Previously,
no optimized key access was possible for this. On the other hand,
secondary keys also incur additional administration costs due to memory
consumption and runtime. Additional runtime costs arise, if a secondary
table index needs to be updated after changes to table content. The ABAP
runtime environment delays these runtime costs for as long as possible,
until they are actually required ( lazy update and delayed
update ). The administration
costs for a secondary key are just as high as for the primary table
keys: on average, 6 bytes for each secondary index row and 18 or 30 b
ytes for each hash key row. On average, another 8 bytes per row are
required, if the table has at least one non-unique sorted secondary key.
Additional memory costs are generated, if a secondary key needs to be
updated after changes to internal table content. These memory costs are
on the same scale as the costs above and also depend on the number of
rows.
ABAP_RULE
Use secondary keys in a way that benefits the table.
Use secondary keys sparingly and only in cases where the benefits
outweigh the extra costs.
ABAP_DETAILS
The internal management of secondary keys in an internal table can
involve significant memory consumption and updates. Secondary keys are
useful for accelerating read access in the following cases:
The standard scenario for sensibly using secondary table keys relates to
a very large internal table that is filled once and the content is
changed very infrequently. In this case, runtime costs for secondary key
administration are only generated when the internal table is created and
in a few other rare situations. These costs are more than outweighed by
more efficient, more frequent read access.
Secondary keys might be useful for small internal tables in some
circumstances because they ensure unique table entries in relation to
particular components. This justifies the extra costs for the secondary
key. This applies to standard tables in particular, whose primary keys
can never be unique.
When using secondary keys, remember:
As a matter of principle, secondary hash keys should not have too many
components. Otherwise, the system has to deal with a high system load
caused by the additional hash management. Sorted keys are preferable for
secondary keys with a large number of components. The same applies to
primary keys.
In the case of pure read access where unique table entries are not
relevant, non-unique secondary keys are usually sufficient. Read access
is just as fast as for unique keys (or nearly as fast if duplicate
entries exist). Updates are only performed after table modifications if
necessary.
If index table rows are deleted using secondary key accesses, the
primary index of the table must be updated. This cannot be optimized for
standard tables; a linear search needs to be performed instead.
You should not use secondary table keys in the following situations:
For small internal tables (less than 50 rows), the performance gain for
read accesses is greatly outweighed by the increased memory and
administration costs.
With tables that are subject to a large number of write accesses, the
load incurred by updating the secondary keys outweighs the performance
benefit for read accesses. Delayed updates and lazy updates
in particular can generate update costs for precisely those read
accesses for which the optimization was actually intended. This results
in either no gain or a negative gain.
Note
It is relatively easy to provide secondary keys later on for existing
internal table types in ABAP programs. And it is especially easy to
provide secondary keys later on for table types that are defined in the
ABAP Dictionary. Remember that � contrary to database accesses � you
must explicitly specify the secondary key of an internal table at the
where-used position. Otherwise, it is not evaluated. In these cases, you
should usually only define non-unique sorted keys. Otherwise, programs
that use tables of this type (and populate these tables with non-unique
rows based on this component) will no longer function properly.
Note that the sy-tabix system field is populated by the assigned
secondary index, if sorted secondary keys are used. If this value is
used for the subsequent index access to the internal table, the same
table index must be explicitly used here. If used implicitly, the value
would be interpreted as a primary index.
Bad Example
In the following source code, a large hashed table is accessed using a
free key, which does not correspond to the table�s primary key.
Therefore, a linear search is required for this access. This is very
time-intensive.
DATA itab TYPE HASHED TABLE OF dbtab
WITH UNIQUE KEY col1 col2 ...
"fill itab with a large amount of data
...
READ TABLE itab
WITH KEY col3 = ... col4 = ...
ASSIGNING ...
Good Example
The following source code optimizes the above example by adding a
secondary table key to the table declaration. This key replaces the free
key during access. Sequential processing (in the order defined with this
key) can now be performed.
DATA itab TYPE HASHED TABLE OF dbtab
WITH UNIQUE KEY col1 col2 ...
WITH NON-UNIQUE SORTED KEY second_key
COMPONENTS col1 col2 ...
"fill itab with a large amount of data
...
ASSIGN itab[ KEY second_key
COMPONENTS col3 = ... col4 = ... ]
TO ...
...
LOOP AT itab USING KEY second_key.
...
ENDLOOP.
Documentation extract taken from SAP system, � Copyright SAP AG. All rights reserved