ABAP Keyword Documentation → ABAP - Reference → Processing Internal Data → Internal Tables → Processing Statements for Internal Tables → DELETE itab
Internal Tables, Deleting Rows Using Keys
This example measures the runtime of the statement DELETE ... WHERE
with various table keys.
Other versions: 7.31 | 7.40 | 7.54
Source Code
REPORT demo_delete_table_using_key.
CLASS measure DEFINITION.
PUBLIC SECTION.
CLASS-METHODS main.
PRIVATE SECTION.
CLASS-DATA: BEGIN OF tline,
col1 TYPE i,
col2 TYPE i,
END OF tline,
itab LIKE HASHED TABLE
OF tline
WITH UNIQUE KEY primary_key
COMPONENTS col1
WITH NON-UNIQUE SORTED KEY secondary_key
COMPONENTS col2,
jtab LIKE SORTED TABLE
OF tline
WITH NON-UNIQUE KEY primary_key
COMPONENTS col2.
CLASS-METHODS refresh_itab.
ENDCLASS.
CLASS measure IMPLEMENTATION.
METHOD main.
DATA: t1 TYPE i,
t2 TYPE i,
t TYPE i.
DATA(out) = cl_demo_output=>new( ).
refresh_itab( ).
CLEAR t.
GET RUN TIME FIELD t1.
DELETE itab WHERE col2 = 10 ##primkey[secondary_key].
GET RUN TIME FIELD t2.
t = t + t2 - t1.
out->write_text(
|Delete without using secondary sorted key: { t }| ).
refresh_itab( ).
CLEAR t.
GET RUN TIME FIELD t1.
DELETE itab USING KEY secondary_key
WHERE col2 = 10.
GET RUN TIME FIELD t2.
t = t + t2 - t1.
out->write_text(
|Delete without using secondary sorted key: { t }| ).
refresh_itab( ).
jtab = itab.
CLEAR t.
GET RUN TIME FIELD t1.
cl_abap_itab_utilities=>flush_itab_key(
EXPORTING keyname = 'SECONDARY_KEY'
CHANGING itab = itab ).
GET RUN TIME FIELD t2.
t = t + t2 - t1.
out->write_text( |Create secondary index: { t }| ).
CLEAR t.
GET RUN TIME FIELD t1.
DELETE itab USING KEY secondary_key
WHERE col2 = 10.
GET RUN TIME FIELD t2.
t = t + t2 - t1.
out->write_text(
|Delete via secondary sorted key with existing index: { t }| ).
CLEAR t.
GET RUN TIME FIELD t1.
DELETE jtab WHERE col2 = 10.
GET RUN TIME FIELD t2.
t = t + t2 - t1.
out->write_text(
|Delete via primary sorted key: { t }| )->display( ).
ENDMETHOD.
METHOD refresh_itab.
DATA prng TYPE REF TO cl_abap_random_int.
prng = cl_abap_random_int=>create( seed = + sy-uzeit
min = 1
max = 10 ).
itab = VALUE #(
FOR j = 1 UNTIL j > 100000 ( col1 = j
col2 = prng->get_next( ) ) ).
ENDMETHOD.
ENDCLASS.
START-OF-SELECTION.
measure=>main( ).
Description
The table itab
is a hashed table with a unique primary key and a non-unique
secondary sorted key. The method refresh_itab
fills the table with 100,000, with the second column being given random numbers between 1 and 10.
The program measures the runtime of the statement DELETE itab
, with a condition being set after WHERE
on the column that determines the secondary key.
- For demonstration purposes, we will suppress the syntax check warning in the first
DELETE
statement using Pragma##PRIMKEY
that the primary key is being used to access the internal table without a component being specified. This access must perform a linear scan of the entire internal table.
- The secondary key is specified in the second
DELETE
statement. Since this key is not used to access the internal table after the table is filled, the secondary table key must first be constructed (lazy update). This time is included in theDELETE
statement measurement and seems to make it considerably slower than the previous non-optimized statement.
- Once the internal table is filled again, the secondary key is constructed explicitly using the method FLUSH_ITAB_KEY of the class CL_ABAP_ITAB_UTILITIES. The time measured here is largely the runtime needed to create the index.
- The third
DELETE
statement specifies the secondary key again, but it already exists due to the preceding method call. As expected, the runtime of thisDELETE
statement is faster than the previous two statements. The total of this runtime and the runtime required to construct the index gives you the runtime of the secondDELETE
statement.
- The fourth
DELETE
statement performs the same delete operation on a sorted internal tablejtab
, which as the same row type and content as the previous tableitab
but no secondary key as its only key. The runtime is approximately the same as an access toitab
using the secondary sorted key, but is a bit shorter since only one key needs to be administered for the internal table. If you were to declare a secondary key forjtab
, such as a hash key for the first column, the runtime of the last twoDELETE
statements would be about the same.
The example demonstrates that you can improve performance significantly by using secondary table keys, and therefore avoiding copying data to tables with appropriate keys. It also demonstrates, however, that the construction of a secondary index is costly. The use of lazy update also means that these costs are often incurred in unexpected places. Secondary keys are more suitable for frequent read accesses to large tables, rather than infrequent write accesses.