We all know Index in a database table helps to fetch the selected row faster as an index is a sorted copy of the selected database table fields. In practical scenarios and real projects, very often we do not have the primary keys in our selections parameters and therefore, the whole table has to be scanned in the absence of the primary index. In such cases, we are tempted to create Secondary Index in classic non-HANA SAP Database tables.
In most cases, creating Secondary Index makes data retrieval faster. But we have to pay something for this Secondary Index. What is the expense of having Secondary Index?
1) Every time an entry is saved in the database table, there is an additional overhead of updating the Secondary Indexes. Each additional index slows down the insertion of rows in the table.
2) If we have too many Secondary Indexes for the database table, chances are there that the storage memory consumed for these indexes is almost as huge as that needed for the whole database table itself.
3) Basis Team needs to spend a good amount of time regularly to reorganize the indexes that get fragmented over time.
4) Too many indexes can also cause the database system optimizer to select the wrong index. To prevent this, the indexes in a table must share as few fields as possible.
How many Secondary Indexes should we have in a classic database table?
Answer: SAP recommends no more than five secondary indexes.
When should we create Secondary Index?
1) The fields in a Secondary Index should be fields through which we often select. The field or fields of a secondary index should be so selective that each index entry corresponds to very minimum percent of the total entries in the table. SAP recommends Secondary Index should hold a maximum of 5% of the total number of table entries.
2) Secondary indexes are to be created only for database tables where the read accesses are more time-critical than the write accesses since each created index has to be maintained for write accesses.
3) The fields that are most likely to be queried with the = operator should be at the beginning of the index.
WHERE Clause for Secondary Index:
1) ‘=’ (Equal to Operator), IN conditions and ‘AND’ links are efficiently supported by Secondary Index, i.e they love Positive operators. Secondary Index also works for LIKE clause if you are not able to provide EQ, IN clause.
IN means multiple EQ for a column. Therefore, IN is compatible in WHERE Clause.
2) Negative conditions like <>, NE, NOT should be avoided. If possible, we should rewrite the WHERE clause to make it Positive. If this is not possible at all then we should still specify the conditions in the WHERE condition and not omit them completely. This is the only way in which the required data records will be selected. Otherwise, you would read unnecessary records that you would then have to remove in the ABAP program, which would contradict the performance rule.
3) If you do not specify all fields in the index, make sure that you enclose the initial section of the index in the WHERE condition. Otherwise, the use of an index is not possible in many cases.
The optimizer generally stops if the selection condition contains an ‘OR’. In other words, it does not evaluate the fields checked by ‘OR’ when selecting and applying the index. An exception to this is ‘OR’ relationships standing on their own. Therefore, conditions containing an OR join for one of the indexed fields should be reformulated if necessary.
Check this example from SAP Help Documentation
The optimizer stops working when it encounters OR in the following SELECT statement.
SELECT * FROM spfli
WHERE carrid = 'LH' AND
( CITYFROM = 'FRANKFURT' OR cityfrom = 'NEW YORK' ).
When replaced by the equivalent statement (below), the entire condition can be optimized with respect to the existing indexes.
Better use of ‘OR’ clause.
WHERE ( carrid = 'LH' AND cityfrom = 'FRANKFURT' ) OR
( carrid = 'LH' AND cityfrom = 'NEW YORK' ).
What is the SAP Table which stores the index names and information for any database table?
Answer: DD12L – R/3 S_SECINDEX: secondary indexes, header
Above we learnt that SAP recommends maximum 5 Secondary Indexes be created. Technically, how many Secondary Indexes can we create in traditional (non-HANA) SAP database?
There are many answers in popular SAP Forum. Some say 9 others say 16 and some say unlimited.
Answer: I checked our SAP system and found that we have Secondary Indexes to the count of 25. Check, even the MARA table has 16 indexes. 🙂
Somewhere in the Forum, I read that, SAP throws a message when we try to create more than 16 Secondary Indexes. Check we have 16 indexes in the system.
I am trying to create the 17th Secondary Index and system does not warn me or throw any message.
Looks like we can create unlimited Secondary Indexes. SAP recommends minimum indexes but does not stop you if you do not obey SAP. That is the beauty of SAP. 🙂
What is the maximum number of fields that can be there in a Secondary Index?
Answer: In one popular SAP blog, an author has mentioned that he could use maximum 16 fields to create the index.
I tried to replicate the hard stop where SAP stops you from using more than 16 fields. Check the below image, I was able to add more than 25 fields in an Index and SAP did not cry. 🙂
Also, check the length of these fields are more than 255 characters and still SAP did not stop. Looks like, technically Secondary Index can have unlimited fields. Can any expert confirm this?
Note: As for SAP Help Document, an index should only consist of a few fields and as a rule, no more than four fields.
Also Read: SAP HANA for Beginners from a Beginner.
There are so many considerations to be considered while creating and using Secondary Indexes. Isn’t it little confusing too? Good news. With the HANA innovation, we might never need Secondary Indexes in HANA Database.
In most cases, SAP HANA does not require secondary indices for good search performance. HANA database store data in column store by default. So, every column is an index in itself. SAP recommends using Row store only in exceptional cases.
To reduce main memory consumption, and to improve insert performance all existing non-unique secondary database indices on columnar tables are removed during migration or do not get created during installation. Unique indices stay as they represent a constraint on the table.
This is valid for all AS ABAP systems from SAP NetWeaver 7.4 onwards!
This is our traditional system for all the above screenshots.
Let’s repeat the question one more time. How can HANA Database afford to get rid of Secondary Indexes?
Answer: HANA system can scan the database table at lightening speed as it is different from classical databases. HANA database tables are Column based as opposed to Row based in traditional databases. This means we do not need indexes anymore. As already mentioned above, HANA database column fields behave as if we have manually created an index on every field of the table but without any cost/expense/drawbacks of secondary indexes as mentioned above for traditional database.
Removing indexes mean we need less database space, i.e. even the 5% memory which secondary indexes would have consumed is saved. It is like the index pages at the back of the physical books are removed and the overall page numbers of a book are reduced. :). And you have the super power to read a million words per second and find any word in a fraction of the second in the whole book. 🙂 . So you do not need to refer to the indexes at the back of the book to find the page number where that word is used in the book.
Does it mean, we cannot create Indexes in HANA Database?
Answer: Yes, we can create Indexes in HANA Database. HANA allows the CREATE INDEX command.
SAP expert John Appleby says: Never create Index in HANA. When we create a table in HANA, it is, in fact, creating a set of sorted, compressed and linked indexes. As a result, secondary indexes almost never improve performance.
He says: “There is one scenario when a secondary index can improve performance: when you have a query which selects a very small amount of data from a very large table (or group of joined tables). In this instance, creating a secondary index on all your sort columns can allow HANA to find the data faster. But this is a very specific situation – the simple advice is, never create indexes“.
In other words, in certain obscure scenarios where we find a performance problem, a secondary index can help. This is only in OLTP scenarios, where you have complex joins and only return a small subset of data from the table. Ninety-nine percent of scenarios will never benefit from an index. They take up space and will slow insert operations, so should be avoided in HANA Database.
Since we learnt that we can technically create Indexes in HANA. What are the types of Indexes in HANA?
Answer: There are two types of Indexes in HANA. 1) Inverted Index and 2) Composite Index.
Inverted Index: Inverted indexes refer to only one column. Here, the index data is stored in internal memory structures that belong to the respective column.
Composite Index: Composite indexes refer to more than one column. First, the contents these columns are grouped together in an internal column, and an inverted index is then created for this internal column.
We will discuss details about Inverted/Composite Indexes in a separate post.
If you want to get such useful and practical articles straight to your inbox, please SUBSCRIBE. We respect your privacy and take protecting it seriously.
If you liked this post, please hit the share button and also like us on facebook.
Thank you very much for your time!!
Also check: ABAP for SAP HANA Series.