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)
In order to better understand the Concept of the Secondary keys, let me first show you what Primary Key is.
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.
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:
- What is New TYPE Category TYPE … BOXED in ABAP?
- SAP ABAP Pragmas in 7.02 EhP2, replacing SLIN Pseudo Comments
- ABAP Method Chaining New Feature in ABAP 7.0 EhP2
- ABAP Internal Table Secondary Keys, New in ABAP 7.0 EhP2
- ABAP New Debugger Keys in Table Tool from 7.0 EhP2
- ABAP New Debugger Conditional Breakpoints
- ABAP New Debugger Skip Breakpoints
- ABAP Database Connectivity ADBC
- ABAP String Templates New Feature in ABAP 731
- Difference Between && (Chaining Operator) and & (Literal Operator)
- ABAP RegEx – Regular Expressions
- Memory Allocation – Why extra 8 bytes!!
- ABAP 740 – NEW Operator to instantiate the objects
- ABAP 740 – NEW Operator to create ITAB entries
- ABAP 740 – VALUE Operator to create ITAB entries
- ABAP 740 – Table Expressions to Read & Modify ITAB line
- ABAP 740 – LINE_EXISTS to check record in ITAB
- Why not to use ABAP Text Symbols instead of Constants
- ABAP 740 – Meshes – A new complex type of Structures
- ABAP 740 – Mesh Path – Forward and Inverse Association
- ABAP 740 – FOR Iteration Expression
- ABAP 740 SWITCH – Conditional Operator
- ABAP 740 – LOOP AT with GROUP BY