Optimal Performance by Secondary Indexes

SAP HANA is able to process data efficiently so that often a good performance is possible even without the use of indexes. In case of frequent, selective accesses to large tables it is nevertheless useful to create additional secondary indexes. As a rule of thumb column store indexes should be created on single columns whenever possible, because single column indexes require much less memory compared to multi column indexes.

While the individual design of secondary indexes is typically an outcome of SQL optimization there are already the following general approaches available to determine useful secondary indexes:

Area                                                     Details

SAP Suite on HANA                        See SAP Note 1794297 that provides some reports for identifying useful indexes.

SAP Bank Analyzer                         See SAP Note 2015986 for general suggestions on index design in Bank Analyzer environments.

General                                             $DIR_INSTANCE/exe/python_support/indexAdvisor.py is a Python script that identifies useful indexes.