HANA Inverted Individual Indexes Interview Questions and Answers

SAP HANA Inverted Individual Indexes FAQs

1. Explain inverted individual indexes?

An inverted individual index is referred to as a special type of SAP HANA index specifically for column store tables. Quite Unlike the traditional column store indexes (inverted value, inverted hash) an inverted individual index does not require a dedicated concat attribute. Only the much light-weight inverted index structures on the individual index columns exist. Consistency and uniqueness is maintained thoroughly based on these individual columns.

2. List the advantages of inverted individual indexes?

The following are the advantages which exist in context of inverted individual indexes:

• The memory footprint which is due to the absence of concat attribute is considerably reduced.
• Less I/O for data and redo logs.
• The delta merge efforts is reduced.
• DML change operations can be much faster because of the reduced concat attribute maintenance.

3. List the disadvantage of inverted individual indexes?

The following are the disadvantages of the inverted individual indexes:

• The primary disadvantage of inverted individual indexes is to do with the risk of performance regressions of queries. With standard indexes it is quite possible to evaluate an amalgamation of important index columns as an initial filter but for the inverted individual indexes always only a single column can be evaluated. In case of a single, very selective index column which has been quantified in the WHERE clause there won’t much of an impact on the performance. In case of several less selective columns which only restrict the result set meaningfully incase multiple column conditions are put together there can be substantial performance overhead (20 % and more).

• Reliant on the characteristics of DML change operations they can suffer because of a more complicated uniqueness check and WHERE clause processing.

4. Define the pre-requirements for utilizing the inverted individual indexes?

The following are the prerequisites which exist for utilizing the inverted individual indexes:

• These are Available starting with SAP HANA 2.0 SPS 03
• Are only available for column store tables
• Only available for the primary keys and unique indexes as non-unique indexes can be directly reduced to single column indexes in case it considered as appropriate

5. Which indexes are good inverted individual index candidates?

Indexes which have the following characteristics are the most promising candidates specifically for inverted individual indexes:

• Large multi-column indexes which are required for uniqueness or primary key purposes
• Existence of a selective index column which is typically utilized in the WHERE clause
• No very frequent query access as part of critical workload (unless slight performance overhead is doable)

6. How can the user determine indexes candidates for important memory saving?

User can utilize SQL: "HANA_Tables_ColumnStore_Columns"  for determining the indexes with specifically high space saving opportunities:

• Primary keys: ATTRIBUTE_TYPE = 'TREX_EXTERNAL_KEY', USED_FOR = 'Index ...'
• Unique indexes: ATTRIBUTE_TYPE = 'CONCAT_ATTRIBUTE', USED_FOR = 'Index ...'

The output column MEM_SIZE_MB displays the possible gain if the user switches to an inverted individual index.

7. How can a user create an inverted individual index?

The following options exist for implementing an inverted individual index:

Note: Creation and redefinition need an exclusive table lock so concurrent alterations on the underlying table are impossible and the user should utilize a downtime for making any modifications.