Indexing in sap hana?

[Former Member]

Hi,

Can any body guide me to understanding Index concepts in hana?

a) I know Column stores are optimized for reading and data in column stores are compressed.

Will hana system takes care of creating index(s) on column store tables or we need to create them ?

If Yes, how to create indexes on column store tables and what are different types of index available?

If No, why?

b) If we create a primary key for a column in table, will that wont work as index (like in oracle) or still we need to create index(s) on row-store/column store tables?

Please answer my basic questions or provide any references for the same.

Thank you. Looking forward to see your reply.

Thanks,

Sree

[Former Member]

Hi Sreedhar,

1) You can create indexes on column tables in HANA.

2) If you have primary key defined on your column table, then that works as primary index for that table. If you want any secondary indexes on other columns, then you can create using below syntax.

CREATE INDEX <INDEX_NAME> ON <TABLE_NAME>.<COLUMN_NAME>

We can create three types of indexes in HANA namely UNIQUE,BTREE,CPBTREE. Please refer the below links for more details.

https://help.sap.com/saphelp_hanaone/helpdata/en/20/d44b4175191014a940afff4b47c7ea/content.htm

Regards,

Venkat N

[Former Member]

Thank you Venkat !!

Could you please differentiate exactly or with some example for

Column table with index and without index?

i.e any specific scenarios we should created index on column tables (ex:data volume..etc)

Thanks,

Sree

[Former Member]

The short answer is you never create indexes in normal operations. They just aren't required.

In certain obscure scenarios where you 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.

99% of scenarios will never benefit from an index. They take up space and will slow insert operations, so should be avoided.

[Former Member]

Thank you so much John !!

Sree

[lbreddemann]

Hey John,

actually I wouldn't generalise so much.

Indexes do play a role in SAP HANA and e.g for ECC quite many will get created.

However, this is a different story than what we were used to with classic DBMS platforms.

: for more details on inverted indexes in SAP HANA.

- Lars

[Former Member]

I don't think I am Lars but that's subjective 🙂

There are of course scenarios where secondary indexes help but they are few and far between. Let me give an example.

Yesterday I was having mild performance issues on a join between two large DSOs. Document and Schedule line item level. They are joined on two fields and I thought aha, maybe a sorted secondary index on those two fields will help. It didn't 🙂

So I think that what most people need to know is you almost never use secondary indexes apart from for OLTP scenarios. They take up space and usually don't help, especially if you apply traditional RDBMS logic.

I don't ever think I found an OLAP scenario where they helped. That said, a best practice guide on them would be a gem. *hint hint* 🙂

[Former Member]

So thanks for the link Lars and it explains why we didn't see a performance improvement with a secondary index.

BW had generated a concat attribute for the second table, with the primary key of the first table. Very clever, and another example of how HANA separates intent from optimization.

[lbreddemann]

Spot on with seperating the use cases/access pattern here.

Indexes really won't help much when most of the data needs to be read anyhow (OLAP).

But they can help with queries on LARGE data sets (finally a good use for all upper case typing ) that whould only return a small fraction of records, which is the typical retailer order line-item query.

As you will know by now I am not the biggest fan of "best practice" papers. I rather see that the technology is sufficiently understood and then applied. Anyhow *hinting back*: this is of course covered in Richard's and my book ...