ABAP Internal Table Secondary Keys, New in ABAP 7.0 EhP2

By | March 6, 2013 | Concepts | 20,333 | 6

ABAP Internal Table Secondary Keys are introduced in ABAP release 7.0 EhP 2. Secondary keys are added advantage for Internal Tables with huge data.

Now you have more options: Primary key (remote) and Secondary Key (actual key if dont want to use remote) :)

Primary and Secondary Key

In order to better understand the Concept of the Secondary keys, let me first show you what Primary Key is.

Primary key

Every internal table has a key – a Primary Key. This allows us to do access on the table. Based on your Table type, you need to include Primary Key in your definition or you can ignore it. From release 7.0 EhP2, Primary key would have a predefined name primary_key. When a table is accessed using the Fields defined in the Primary key, access would be made using the Primary key.

For Standard table, the Primary key could be left without any field. For Sorted table, you must define fields for the Primary key. Same is true for the HASH table. Sorted & Hashed table would have their own key administrations to facilitate better access. But this will come with cost and this administration would need its own resources to function.

Secondary Key

From ABAP release 7.0 EhP2, you can define Secondary key as well to your internal tables. Access using the Secondary tables would be always optimized as it is managed by the Key Administration. Secondary Key works similarly as Database Indexes – Access using the Primary key fields and index fields make DB access faster. Index on DB is also managed by internal DB administration.

Internal table with Secondary key would be created like this:

Define ITAB Secondary Key

 
TYPES:
  BEGIN OF ty_vbap,
    vbeln TYPE vbap-vbeln,
    posnr TYPE vbap-posnr,
    matnr TYPE vbap-matnr,
    arktx TYPE vbap-arktx,
    netpr TYPE vbap-netpr,
    werks TYPE vbap-werks,
  END   OF ty_vbap.
 
TYPES:
  tt_vbap TYPE STANDARD TABLE OF ty_vbap
    WITH KEY vbeln posnr
    WITH NON-UNIQUE SORTED KEY matnr_werks COMPONENTS matnr werks.
 
DATA: T_VBAP TYPE TT_VBAP.
 

In this type TT_VBAP, we have created a non-unique secondary key matnr_werks with components MATNR and WERKS. It is mandatory to give a name to the secondary Key as there could be multiple key defined on the Type.

Why do you use Secondary Key

When you define the ITAB secondary key, you provide the name. E.g. name of the key is matnr_werks in the above mentioned code.

When you use Secondary Key with addition USING matnr_werks on table T_VBAP, what you do is influence the behavior of how data is being accessed. When you do a READ or LOOP, system traverse through the table rows in a predefined manner. Like a LOOP on T_VBAP would access the table sequentially. Whereas, when you use USING matnr_werks, you are telling the system to access the table in SORTED sequence of MATNR and WERKS on T_VBAP.

 
LOOP AT t_vbap INTO ls_vbap
               USING KEY matnr_werks
               WHERE matnr = ' '
               AND   werks = ' '.
ENDLOOP.
 

When Secondary key is defined, system creates internal administration to track the records. So, think like a pointer table which has a Key fields and a row number of the actual table to track that. So, when you need to access the table using the fields and you want to make it faster, you should consider using the Secondary Key.

Syntax on using the Secondary keys

You can use the Secondary keys on the statements LOOP, READ, DELETE, MODIFY, APPEND, INSERT. See below code lines for complete Syntax when you want to use the Secondary Key.

Standard Access Access Via Secondary Key

 
* normal access
* 
READ TABLE t_vbap INTO ls_vbap
  WITH KEY vbeln = ' '
           posnr = ' '.
*
*
* 
LOOP AT t_vbap INTO ls_vbap
               WHERE vbeln = ' '
               AND   posnr = ' '.
ENDLOOP.
*
*
*
INSERT LINES OF t_vbap
  INTO itab INDEX 1.
*
*
*
APPEND LINES OF t_vbap
  FROM 1 TO 10
  TO itab.
*
*
*
MODIFY t_vbap FROM ls_vbap
  TRANSPORTING arktx
  WHERE vbeln = ' '.
*
*
*
*
DELETE t_vbap
  WHERE vbeln = ' '.
*
*
 

 
* Using Secondary Key
* 
READ TABLE t_vbap INTO ls_vbap
  WITH KEY matnr_werks COMPONENTS
    matnr = ' '
    werks = ' '.
*
* 
LOOP AT t_vbap INTO ls_vbap
               USING KEY matnr_werks
               WHERE matnr = ' '
               AND   werks = ' '.
ENDLOOP.
*
*
INSERT LINES OF t_vbap
  USING KEY matnr_werks
  INTO itab INDEX 1.
*
*
APPEND LINES OF t_vbap
  FROM 1 TO 10
  USING KEY matnr_werks
  TO itab.
*
*
MODIFY t_vbap FROM ls_vbap
  USING KEY matnr_werks
  TRANSPORTING arktx
  WHERE matnr = ' '
  AND   werks = ' '.
*
*
DELETE t_vbap
  USING KEY matnr_werks
  WHERE matnr = ' '
  AND   werks = ' '.
 

When to Use Secondary Keys

Some guidelines on when to use Secondary keys

  • Secondary Keys should be used for tables with huge records
  • Secondary Keys should NOT be used when there are very few records. SAP Help suggests to use it for more than 50 records. For smaller tables, the cost of administration of the key would be higher than the added performance gain
  • Secondary Keys should NOT be used for frequently changing records
  • Hashed Key would have more cost than Sorted Key so limit the number of fields in the Hashed Secondary Key

Update of Secondary Keys

Internal administration on the ITAB would be responsible for Making the update in the Secondary Keys. There different these different types, of how update could take palce:

  • Direct Update: Secondary Keys are updated immediately
  • Delayed Update: Secondary Keys are Updated when TABLE is accessed
  • Lazy Update: Secondary Keys are updated only when Secondary Keys are used accessing ITAB

When you Insert or Update an entry in NON-UNIQUE Secondary Key, the Key would be updated when the table is first accessed using Secondary key LOOP or READ performing Lazy Update. It won’t be updated when you appending the Entries in the table to reduce the cost of the update on secondary key. For Non-Unique key, to keep the index System has to go through the logic of updating the key every time a row is inserted. Because of this, the Secondary keys would be updated late.

For UNIQUE Secondary Keys, the keys would be updated when the table entry is being INSERTED right away performing Direct update. For Changing an existing entry, the keys would be updated at specified time performing Delayed Update.

Read more on Secondary Keys at SAP Help.

Have you used Secondary Keys yet? Share you experience.

Related articles on new Concept in ABAP

Check out the other articles on new ABAP concepts:

Like It? Share!!

Don't miss an Update

Get notified of the new post, right into your inbox

Naimesh Patel{272 articles}

I'm SAP ABAP Consultant for more than a decade. I like to experiment with ABAP especially OO. I have been SDN Top Contributor.
Follow :

Explore all of his 272 articles.

Load comments

6 Comments

  • steve oldner

    Saw this in Heilmann and Jung’s Next Generation ABAP development, but haven’t had a chance to use this. I am thinking this would be more efficient that re-sorting the itab for different keys, something we do occasionally.

    Anyone test the performance yet?

  • Hello Steve,

    Book Next Generation ABAP has covered this topic but I first heard about this in TechED 08 (I believe).

    Sure it would be beneficial when the data is not keep on changing. Otherwise it would be similar cost like SORT within LOOP to keep the internal administration up-to-date with the current record pointers.

    I would try to cover the Performance aspect of using Secondary Keys.

    Regards,
    Naimesh Patel

  • steve oldner

    Good post Naimesh,

    That is my thought also. For very large static or reference itabs, this would be preferable to sorting the itab for different binary or loop reads.

    Aa always, a faithful follower of your blog!

  • Thanks Steve, I appreciate it :)

  • Ravi Kiran

    Hi Naimesh,

    Very Good Post and thanks for sharing..
    I always found interesting things from all of your blogs and eagarly waits for your next blogs…

    Thanks again for a nice post.

    Regards
    Ravi Kiran

  • Vaibhav

    Hi Naimesh,

    Great site containing tons of information for the beginners and experts to ponder upon..Thank you!!

    Would like to request you to talk about Shared Objects…it’s usage, pros and cons etc…,

    Thanks once again,
    Vaibhav.

Comments on this Post are now closed. If you have something important to share, you can always contact me.

You seem to be new here. Subscribe to stay connected.