cancel
Showing results for 
Search instead for 
Did you mean: 

HANA - Index Usage (when no index is created)

BenedictV
Active Contributor
0 Kudos

Hi,

I created a temporary table to check how partitioning works and while going through the execution plan, I saw that the 'Basic Predicate' node had "Inverted Index - used".

I did not create any index and the index details is also empty when I look at the table definition. So, how does the plan show the index as used. Does HANA create any temporary indexes during execution?

System- HANA SPS10

Accepted Solutions (1)

Accepted Solutions (1)

lucas_oliveira
Advisor
Advisor

Hi Benedict,

You're probably either have a primary key (composite maybe?) on your table or you're joining tables with more than one column as combining key. That's very well explained on this presentation from Lars.

Regards,

Lucas de Oliveira

lbreddemann
Active Contributor

Thanks for fishing out that pre-historic piece of powerpoint dabbling... I'm equally grateful and surprised that this document actually survived the recent tsunami called 1DX community migration...

Answers (3)

Answers (3)

lbreddemann
Active Contributor

Once again, my first answer didn't seem to have been saved...*grrrr*

Anyhow. Thanks for pushing the amazing PSEUDODSO tables into the limelight again. They had been gone for too long 😄

I tried to reproduce the effect on a HCP 1.00.112.04 system but didn't succeed. The index on ZDATE got always used, whenever the filter would have actually reduced the result set and when the data was read from the main store.

Maybe it's revision/version dependent!?

Another point (which is likely to answer the initial question... *cough*): when a primary key on multiple columns gets created, SAP HANA adds the following:

  • $trexexternalkey$ column
  • inverted index on the $trexexternalkey$ column
    AND
  • inverted indexes on every column in the primary key

This is the reason that you have seen the 'index used' after defining a PK and only referencing a single column in the WHERE clause.

I had completely forgotten this - admittedly well documented (see the SAP FAQ note!) - and was surprised when I saw this. Looks like I'm getting forgetful... 😞

BenedictV
Active Contributor
0 Kudos

Thanks Lars. You said "Can you provide the example....?" and I was sure that I have made a mistake 🙂

It never occurred to me to check INDEX_COLUMNS tables to see the indexes

lbreddemann
Active Contributor
0 Kudos

As 2160391 - FAQ: SAP HANA Indexes lists, partial index qualifications are not supported by indexes on HANA column store tables.

So, if you have a primary key or index over say MANDT, BUCHDAT, LINENO and only specify MANDT and LINENO in your query WHERE condition, then the index or primary key won't be used.

BenedictV
Active Contributor
0 Kudos

Hello Lars,

Thanks for commenting.

I ran a few more simple SELECT tests and this is what I observed,

Even when a single field in the composite pk is used in a WHERE condition or in as a (inner)join field, the inverted index is used.

When a index is created on a non pk column and it is used in a WHERE condition, then the index is used. Also, the plan before and after index creation looks the same except that the filter on the index fields are rearranged to execute first.

lbreddemann
Active Contributor
0 Kudos

Can you provide the example where you see a single column condition on a multi-column index is evaluated by that index?

Would be really interesting to see that...

BenedictV
Active Contributor
0 Kudos

Hello Lars,

I couldn't get back to you on this earlier. Since the original conditions contained customer information, I created a similar scenario and here's the results,

Does the SQL look familiar 🙂 ?

I created a additional index on a non-pk field in the second table. Also, the first table is partitioned.

But, I was wrong about the second point in my previous reply. The extra index is not used here and I do not understand why. Maybe you can throw some light on it.

Benedict

BenedictV
Active Contributor
0 Kudos

Thank Lucas. I saw the presentation and also the 'further playing...' one. In it Lars specifically mentions the creation of inverted index and how it shows up in the PlanViz.

Its true that my key is composite and I see the composite values in 'trexexternalkey'. But I am only using one field(key) in my join. Is the 'trexexternalkey' accessed nevertheless and what shows as index access?