Are secondary indexes required to provide an optimal performance?
Thanks in advance.
12 Feb 2015 5:20 pm Sonia Barwar Best Answer
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:
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.