Suggestion on Secondary index columns using indexAdvisor.py in HANA

Here I wish to discuss about how to make use of index advisor python script in your HANA Database for recommendation with respect to secondary index creation.

How to use the index advisor in HANA:

Syntax: python indexAdvisor.py -H <server name> -P 3<nn>15 -u SYSTEM -p <password>  -s <schema_name> -i 500

Output:

List of create index command:

More about INDEX ADVISOR::

indexAdvisor.py

The indexAdvisor.py script is part of SAP HANA system installation and runs from the command line.

It is located in $DIR_INSTANCE/exe/python_support directory.

 indexAdvisor.py is used to:

• create indexes on non-primary key columns to enhance the performance of some queries.
• find out for which tables and columns indexing would be most valuable.

Purpose:

SAP HANA automatically creates indexes for all primary key columns. So, Indexing the primary key  columns are usually sufficient because queries typically put filter conditions on primary key columns. When filter conditions are on non-key fields and tables have many records, creating an index on the non-primary key columns may improve the performance.

You can create indexes on non-primary key columns to enhance the performance of some queries, particularly highly selective queries on non-primary key columns.

Pros and Cons:There is a trade-off between indexing and memory consumption: While indexing non-primary key columns can make query execution faster, the downside is that memory consumption increases. The index adviser takes this trade-off into account: In dynamic mode, the index adviser looks for the tables and columns that are used most often. The higher the selectivity is, that is, the more different the values are in the column, the higher the performance gains are from indexing the columns.

Location of index advisor:

IndexAdvisor Help:

Thanks for reading!

Please leave your comments and suggestions and like if you found this useful!